| Author |
 Topic  |
|
|
Zoomer36
Starting Member
USA
9 Posts |
 Posted - 06/26/2012 : 13:20:37
|
Hello,
I need to combine data in a SQL query. I have it very close but need some help. I need to get a report of all the invoices for a year. On each invoice, I need to sum the number or cartons sold so I can convert that to the number of packs sold. Problem is that we have two types of cartons. A CARTON of 10 and a 5/20-PK of 5.
I do a CASE statement that SALE_MEAS = 'CARTON' then SUM(QTY_SHIPPED * 10) else SUM(QTY_SHIPPED * 5) END as PACKS, it gives me 2 invoice entries for the different types of cartons. I need to add those two together so there is only one entry for each invoice.
This is part of a larger report and I need to cut and paste the carton/pack numbers into each invoice.
Thanks in advance for any and all help!
Charlie
This is my script:
SELECT
CASE WHEN I.STATUS = 9 THEN 'Invoice' ELSE 'Return' END AS DOCTYPE,
I.ORDER_NO, I.ORDER_DATE,
CASE WHEN I.STATUS = 9 THEN SUM(X.QTY_SHIP) ELSE SUM(X.QTY_SHIP * -1) END AS QTY_SHIPPED,
CASE WHEN IT.SALE_MEAS = 'CARTON' THEN (SUM(X.QTY_SHIP) * 10) ELSE (SUM(X.QTY_SHIP) * 5) END AS PACKS
FROM INVOICES I WITH (NOLOCK)
INNER JOIN X_INVOIC X WITH (NOLOCK) ON I.DOC_NO = X.ORDER_NO AND I.STATUS = X.STATUS
INNER JOIN ITEMS IT WITH (NOLOCK) ON X.ITEM_CODE = IT.ITEMNO
WHERE I.STATUS IN (9,12)
AND IT.CATEGORY IN ('CIGS','CIGSSPL','CIG25')
AND (I.ORDER_DATE >= '01/01/2008') AND (I.ORDER_DATE < '1/01/2009')
GROUP BY I.ORDER_DATE, I.STATUS, I.ORDER_NO, IT.SALE_MEAS
ORDER BY I.ORDER_DATE, I.ORDER_NO
Have a great day! |
|
|
jimf
Flowing Fount of Yak Knowledge
USA
2865 Posts |
Posted - 06/26/2012 : 14:18:10
|
SELECT
CASE WHEN I.STATUS = 9 THEN 'Invoice' ELSE 'Return' END AS DOCTYPE,
I.ORDER_NO, I.ORDER_DATE,
CASE WHEN I.STATUS = 9 THEN SUM(X.QTY_SHIP) ELSE SUM(X.QTY_SHIP * -1) END AS QTY_SHIPPED,
SUM(CASE WHEN IT.SALE_MEAS = 'CARTON' THEN X.QTY_SHIP* 10 ELSE X.QTY_SHIP * 5 END
AS PACKS
FROM INVOICES I WITH (NOLOCK)
INNER JOIN X_INVOIC X WITH (NOLOCK) ON I.DOC_NO = X.ORDER_NO AND I.STATUS = X.STATUS
INNER JOIN ITEMS IT WITH (NOLOCK) ON X.ITEM_CODE = IT.ITEMNO
WHERE I.STATUS IN (9,12)
AND IT.CATEGORY IN ('CIGS','CIGSSPL','CIG25')
AND (I.ORDER_DATE >= '01/01/2008') AND (I.ORDER_DATE < '1/01/2009')
GROUP BY I.ORDER_DATE, I.STATUS, I.ORDER_NO, IT.SALE_MEAS
ORDER BY I.ORDER_DATE, I.ORDER_NO
Jim
Everyday I learn something that somebody else already knew |
 |
|
|
Zoomer36
Starting Member
USA
9 Posts |
Posted - 06/26/2012 : 14:31:48
|
Hi Jim,
Thank you but that didn't work. I got the message:
Server: Msg 195, Level 15, State 10, Line 7
'SUM' is not a recognized function name.
I am using SQL Server 2000.
Have a great day! |
|
|
|
Zoomer36
Starting Member
USA
9 Posts |
Posted - 06/27/2012 : 16:38:43
|
Hi Jim,
You are a genius! Thank you. I have been playing with it for the past 2 days. I just now finally realized that I had left the SUM function inside the SUM. Of course that wasn't going to work.
Thanks for your help! This one is solved and I can't thank you enough.
Have a great day! |
Edited by - Zoomer36 on 06/27/2012 16:41:53 |
|
|
| |
Topic |
|
|
|
Need to combine results in SQL query
