help with query - 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 2000 Forums

Transact-SQL (2000)

help with query

Author

Topic

PIERCA
Starting Member

2 Posts

Posted - 2004-08-12 : 10:53:39

Good morning to All

I would like to modify this query :

SELECT x.iss_no, x.pro_cod, x.pro_name, x.distrib, x.dis_dat, sum(ret_qty) AS unsold,

CASE
WHEN DIS_DAT BETWEEN '01/05/2003' AND '05/31/2003'
THEN X.DISTRIB else 0 end as DISTRIB_2003,
CASE
WHEN DIS_DAT BETWEEN '01/05/2003' AND '05/31/2003'
THEN sum(RET_QTY) else 0 end as UNSOLD_2003,
CASE
WHEN DIS_DAT BETWEEN '01/05/2004' AND '05/31/2004'
THEN DISTRIB else 0 end as DISTRIB_2004,
CASE
WHEN DIS_DAT BETWEEN '01/05/2004' AND '05/31/2004'
THEN sum(ret_qty) else 0 end as UNSOLD_2004

FROM
(SELECT iss_no, pro_name, dis_dat, distribution.pro_cod, sum(dis_qty) AS distrib
FROM distribution
INNER JOIN product ON distribution.pro_cod = product.pro_cod
INNER JOIN publisher on product.pub_cod = publisher.pub_cod

WHERE product.pub_cod = '031'

GROUP BY distribution.iss_no, distribution.pro_cod, pro_name, dis_dat, distribution.pro_cod

) AS x(iss_no, pro_name, dis_dat, pro_cod, distrib)
JOIN returns ON returns.pro_cod = x.pro_cod
AND returns.iss_no = x.iss_no

GROUP BY x.iss_no, x.pro_cod, x.pro_name, x.dis_dat, x.distrib, ret_qty

ORDER BY x.pro_name, x.iss_no

iss_no pro_cod pro_name distrib dis_dat unsold DISTRIB_2003 UNSOLD_2003 DISTRIB_2004 UNSOLD_2004
------------ ------- --------------- ----------- ----------------------- --------- ------------ ----------- ------------ -----------
03.0018 516 AUTO OGGI 25 2003-05-01 00:00:00 2 25 2 0 0
03.0018 516 AUTO OGGI 25 2003-05-01 00:00:00 4 25 4 0 0
03.0023 516 AUTO OGGI 25 2003-06-05 00:00:00 6 0 0 0 0
03.0023 516 AUTO OGGI 25 2003-06-05 00:00:00 10 0 0 0 0
03.0024 516 AUTO OGGI 25 2003-06-12 00:00:00 6 0 0 0 0
04.0019 516 AUTO OGGI 25 2004-05-06 00:00:00 2 0 0 25 2
04.0019 516 AUTO OGGI 25 2004-05-06 00:00:00 4 0 0 25 4
04.0023 516 AUTO OGGI 25 2004-06-03 00:00:00 5 0 0 0 0
04.0023 516 AUTO OGGI 25 2004-06-03 00:00:00 2 0 0 0 0

hOw can i modify the query to have in 1 row the sum of DISTRIB_2003 UNSOLD_2003 DISTRIB_2004 UNSOLD_2004

for each different months and different year as example below ?

iss_no pro_cod pro_name month DISTRIB_2003 UNSOLD_2003 DISTRIB_2004 UNSOLD_2004
------------ ------- --------------- ----------- --------- ------------ ----------- ------------
03.0018 516 AUTO OGGI may 50 6 50 6

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-08-12 : 11:01:47

will this do?

select iss_no, pro_cod, pro_name, distrib, datepart(month, datdis_dat),
sum(DISTRIB_2003) sum(UNSOLD_2003), sum(DISTRIB_2004), sum(UNSOLD_2004)
from tables...
where ....
group by iss_no, pro_cod, pro_name, distrib, datepart(month, datdis_dat)

Go with the flow & have fun! Else fight the flow :)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources