August 12, 2004 at 5:07 am
Good morning to all :
I have this query to get out sum of product distribution and unsold
SELECT x.iss_no, x.pro_cod, x.pro_name, x.distrib, x.dis_dat, sum(ret_qty) AS unsold
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
ORDER BY x.pro_name, x.iss_no
this is the results
iss_no pro_cod pro_name distrib dis_dat unsold
------------ ------- ----------------------------------- ----------- ------------------------------------
03.0018 516 AUTO OGGI 25 2003-05-01 00:00:00 9
03.0019 516 AUTO OGGI 25 2003-05-08 00:00:00 12
03.0020 516 AUTO OGGI 25 2003-05-15 00:00:00 12
03.0021 516 AUTO OGGI 25 2003-05-22 00:00:00 9
03.0022 516 AUTO OGGI 25 2003-05-29 00:00:00 7
03.0023 516 AUTO OGGI 25 2003-06-05 00:00:00 16
03.0024 516 AUTO OGGI 25 2003-06-12 00:00:00 6
03.0025 516 AUTO OGGI 25 2003-06-19 00:00:00 8
03.0026 516 AUTO OGGI 25 2003-06-26 00:00:00 7
04.0019 516 AUTO OGGI 25 2004-05-06 00:00:00 12
04.0020 516 AUTO OGGI 25 2004-05-13 00:00:00 10
04.0021 516 AUTO OGGI 25 2004-05-20 00:00:00 12
04.0022 516 AUTO OGGI 25 2004-05-27 00:00:00 11
04.0023 516 AUTO OGGI 25 2004-06-03 00:00:00 10
04.0024 516 AUTO OGGI 25 2004-06-10 00:00:00 11
04.0025 516 AUTO OGGI 25 2004-06-17 00:00:00 15
04.0026 516 AUTO OGGI 25 2004-06-24 00:00:00 13
03.0005 342 SALE E PEPE 275 2003-05-08 00:00:00 133
03.0006 342 SALE E PEPE 275 2003-05-29 00:00:00 128
03.0007 342 SALE E PEPE 275 2003-06-26 00:00:00 112
04.0005 342 SALE E PEPE 275 2004-05-06 00:00:00 117
04.0006 342 SALE E PEPE 275 2004-06-03 00:00:00 96
Now the question is:
how can i modify the query ?
i need to have: the sum for each prod_cod and each different
month(dis_dat) for rows that are presents in 2 period of dates from
05/01/2003 to 05/31/2003 and from 05/01/2004 to 05/31/2004
try several times with SUM CASE with unsuccesfull result
the result would be something like this: i write 1 rows for example
pro_cod pro_name month distrib_2003 unsold_2003 distrib_2004 unsold_2004
-------- ------------- -------- ------ ------------ ----------- ------------
516 AUTO OGGI may 125 49 100 45
can anyone help me to fix the query?
thanks in advance
Pierca
August 12, 2004 at 7:13 am
See my answe to this post I put in this morning;
http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=8&messageid=131367&post=true
You just need to replace the WHEN statement with between start and end dates.
August 12, 2004 at 8:06 am
I modify the query and put CASE WHEN condition for periods i need
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_codWHERE 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
these are 2 rows of results
how can i have on the same row the total of
DISTRIB_2003 UNSOLD_2003 DISTRIB_2004 UNSOLD_2004 ?
because i have different rows for 2003 and different for 2004
Piercarlo
August 12, 2004 at 9:16 am
You need to put the sum function around your case statement, not inside of it.
E.g
sum(CASE
WHEN DIS_DAT BETWEEN '01/05/2003' AND '05/31/2003'
THEN RET_QTY else 0 end) as UNSOLD_2003,
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply