|
tracy5436
Yak Posting Veteran
50 Posts |
 Posted - 2009-03-31 : 17:45:42
|
| Hi,I have written a query which I need to use a case statement or if statement in. The value of SalesQty should be the sum of all soptype = 3 minus all soptype = 4. Right now it is only picking up soptype = 3. My query is as follows :INSERT INTO SALES([STOCKLIST CODE], [PROCESS YYYYMM], [PRODUCT CODE], [CUSTOMER CODE], [SALESTEAM CODE], [SALES QTY],[SALES VALUE],[FREE QTY],[LAND VALUE],[NO. OF ORDERS],[NET VALUE])SELECT '5555550101' ,D1.ProcessYYYYMM ,D1.Item ,D1.Cust ,D1.Salesman ,D1.SalesQty ,D1.SalesValue ,'0.00' ,D1.LandValue ,D1.NumOrders ,D1.NetValue FROM( SELECT LTRIM(STR(YEAR(S1.DOCDATE)) + REPLACE(STR(MONTH(S1.DOCDATE), 2),' ', '0')) AS ProcessYYYYMM ,I1.[CP PRODUCT CODE] as Item ,LEFT(S1.CUSTNMBR,6) as Cust ,S2.SLPRSNID As Salesman ,SUM(S2.QUANTITY*I1.[UNITS/CASE]) as SalesQty ,SUM(S2.XTNDPRCE/70) as SalesValue ,SUM(I1.[UNIT PRICE]*I1.[UNITS/CASE]*S2.QUANTITY) as LandValue ,COUNT(S1.SOPNUMBE) as NumOrders ,SUM(S2.XTNDPRCE/70) as NetValue FROM SOP30200 S1 JOIN SOP30300 S2 ON S1.SOPNUMBE = S2.SOPNUMBE AND S1.DOCDATE between '12/01/2007' and '12/31/2007' /*AND S1.SOPTYPE = '3' */ AND S1.LOCNCODE = 'MGD_MAIN' JOIN [ITEM_MAPPING] I1 ON S2.ITEMNMBR = I1.[KIRK ITEM NUMBER] GROUP BY I1.[CP PRODUCT CODE] ,LEFT(S1.CUSTNMBR,6) ,S2.SLPRSNID ,LTRIM(STR(YEAR(S1.DOCDATE)) + REPLACE(STR(MONTH(S1.DOCDATE), 2),' ', '0')) ,S2.SOPTYPE ) D1Help please ! |
|
case statement in sql query