Select - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Select

Author

Topic

xflekacm
Starting Member

2 Posts

Posted - 2010-03-02 : 04:29:23

Hello
I need help
I have this question and this select
SELECT d.OZ + ' - ' + d.nazev OZ,
s.MO_SIT + ' - ' + s.nazev mo_sit,
o.nazev_firma + ' - ' + o.mesto_firma + ' - ' + o.ico firma,
o.cislo + ' - ' + o.mesto + ' ,' + o.ULICE prodejna,
o.ico_cislo,
t.OBICIS as sortiment,
SUM(ISNULL(f.PRODEJNI_CENA_CELKEM, 0)) cena,
SUM(ISNULL(f.kusy, 0)) kusy

from vystavene_faktury as f

left join odberatele as o on o.ico_cislo = f.ico_cislo
left join sortiment as t on t.OBICIS = f.OBICIS

INNER JOIN DEALER AS d ON o.OZ1_ID = d.OZ_ID
Inner JOIN maloobchodni_sit AS s ON s.mosit_id = o.mosit_id

WHERE f.DATUM_ZDAN_PLNENI between '2010-01-05' and '2010-01-06'
AND f.DRUHPS = 'S' and f.obicis = '0072170030'

Group by
d.OZ,
d.nazev,
s.MO_SIT,
s.nazev,
o.nazev_firma,
o.mesto_firma,
o.ico,
o.cislo,
o.mesto,
o.ULICE,
o.ico_cislo,
t.obicis
....

The select is failing to collect all the records from the table the company.

................

SELECT xx.OZ, xx.mo_sit, xx.firma, xx.prodejna,
SUM(xx.cena) as cena, SUM(xx.kusy) as kusy
FROM
(SELECT x.OZ, x.mo_sit, x.firma, x.prodejna,
SUM(ISNULL(f.PRODEJNI_CENA_CELKEM, 0)) cena,
SUM(ISNULL(f.kusy, 0)) kusy
FROM (SELECT d.OZ + ' - ' + d.nazev OZ,
s.MO_SIT + ' - ' + s.nazev mo_sit,
o.nazev_firma + ' - ' + o.mesto_firma + ' - ' + o.ico firma,
o.cislo + ' - ' + o.mesto + ' ,' + o.ULICE prodejna,
o.ico_cislo
FROM odberatele AS o
INNER JOIN DEALER AS d ON o.OZ1_ID = d.OZ_ID
JOIN maloobchodni_sit AS s ON s.mosit_id = o.mosit_id
GROUP BY
d.OZ,
d.nazev,
s.MO_SIT,
s.NAZEV,
o.nazev_firma,
o.mesto_firma,
o.ico,
o.cislo,
o.mesto,
o.ulice,
o.ico_cislo ) AS x
LEFT JOIN (SELECT f.DATUM_ZDAN_PLNENI, f.DRUHPS, f.ico_cislo, f.PRODEJNI_CENA_CELKEM, f.kusy
FROM dbo.VYSTAVENE_FAKTURY AS f
WHERE f.DATUM_ZDAN_PLNENI between '2010-01-05' and '2010-01-06'
AND DRUHPS = 'S' and obicis = '0072170030') AS f ON
f.ico_cislo = x.ico_cislo
GROUP BY x.OZ, x.mo_sit, x.firma, x.prodejna) as xx
GROUP BY xx.OZ, xx.mo_sit, xx.firma, xx.prodejna

This selection selects While all firms but appears OBICIS.

Please help. Thanks in advance

bhuvnesh.dogra
Starting Member

22 Posts

Posted - 2010-03-02 : 05:20:41

Always post your Sql code in "

" to give better presentation and understanding

---Bhuvnesh-----
While(learning Sql...)

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-03-02 : 05:22:14

you mean formatted code between
[C0DE][/C0DE]

No, you're never too old to Yak'n'Roll if you're too young to die.

bhuvnesh.dogra
Starting Member

22 Posts

Posted - 2010-03-02 : 05:23:16

Always post your Sql code with third last option avalalble in FORMAT tab in POST window to give better presentation and understanding

---Bhuvnesh-----
While(learning Sql...)

---Bhuvnesh-----
While(learning Sql...)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources