on 02-27-2015 8:18 AM
Dear Experts,
i have managed to learn how to use the pivot in queries. the original query was to list all qty in each warehouse in a pivot table (http://scn.sap.com/thread/3703055). Now I want to add the cumulative value beside the warehouse. the format should be like this
Item Code 'WHSE1' 'Cumulative Value of WHSE1' 'WHSE 2' 'Cumulative Value of WHSE2'
itm001 50 1500 50 1200
below is my query:
SELECT [Code] as Code, [Item] as Name,
[ABU],
[ABUC],
[ALF],
[ALFC],
[AUR],
[AURC],
[BAG],
[BAGC],
[BAM],
[BAMC],
[BAR],
[BARC],
[BAT],
[BATC],
[BIC],
[BICC],
[CAL],
[CALC],
[CAM],
[CAMC],
[CAU],
[CAUC],
[DUB],
[DUBC],
[HER],
[HERC],
[ILC],
[ILCC],
[MIN],
[MINC],
[OFF],
[OFFC],
[PNG],
[PNGC],
[PTB],
[PTBC],
[ROX],
[ROXC],
[SW1],
[SW1C],
[SW2],
[SW2C],
[SW3],
[SW3C],
[SW4],
[SW4C],
[TAL],
[TALC],
[TU2],
[TU2C],
[TU1],
[TU1C]
FROM(SELECT T1.[ItemCode] as Code, T1.[ItemName] as Item, sum(T0.[InQty] - T0.[OutQty]) AS QTY,sum(tO.tRANSVALUE)as Cost, T0.[Warehouse] AS WAREHOUSE
FROM OINM T0 INNER JOIN OITM T1 ON T0.ItemCode = T1.ItemCode
WHERE T0.[DocDate] <= [%0] AND t1.itemcode BETWEEN [%1] and [%2] group by T1.[ItemCode], T1.[ItemName],T0.[Warehouse]) A
PIVOT
(SUM(QTY) FOR WAREHOUSE IN ([ABU],[ALF],[AUR],[BAG],[BAM],[BAR],[BAT],[BIC],[CAL],[CAM],[CAU],[DUB],[HER],[ILC],[MIN],[OFF],[PNG],[PTB],[ROX],[SW1],[SW2],[SW3],[SW4],[TAL],[TU2],[TU1])) P
FROM(SELECT T1.[ItemCode] as Code, T1.[ItemName] as Item,sum(T0.[TransValue]) as Cost
FROM OINM T0 INNER JOIN OITM T1 ON T0.ItemCode = T1.ItemCode
WHERE T0.[DocDate] <= [%0] AND t1.itemcode BETWEEN [%1] and [%2] group by T1.[ItemCode], T1.[ItemName],T0.[TransValue]) B
(SUM(Cost) FOR WAREHOUSE IN ([ABUC],[ALFC],[AURC],[BAGC],[BAMC],[BARC],[BATC],[BICC],[CALC],[CAMC],[CAUC],[DUBC],[HERC],[ILCC],[MINC],[OFFC],[PNGC],[PTBC],[ROXC],[SW1C],[SW2C],[SW3C],[SW4C],[TALC],[TU2C],[TU1C])) P
Try this query:
SELECT [Item] as Item#,
[OPEN],Opencost,[REJ_FG],REJ_FGcost,[SFTO-618],SFTO618cost,[TOOL],TOOLcost
FROM(SELECT T0.[ItemCode] as Item, sum(T0.[InQty] - T0.[OutQty]) AS QTY, T0.[Warehouse] AS WAREHOUSE,
Case when T0.[Warehouse] = 'open' then sum(T0.[TransValue]) end as 'Opencost',
Case when T0.[Warehouse] = 'REJ_FG' then sum(T0.[TransValue]) end as 'REJ_FGcost',
Case when T0.[Warehouse] = 'SFTO-618' then sum(T0.[TransValue]) end as 'SFTO618cost',
Case when T0.[Warehouse] = 'TOOL' then sum(T0.[TransValue]) end as 'TOOLcost'
FROM OINM T0
WHERE T0.[DocDate] <= [%0] AND T0.[ItemCode] = [%1] group by T0.[ItemCode],T0.[Warehouse]) S
PIVOT
(SUM(QTY) FOR WAREHOUSE IN ([OPEN],[REJ_FG],[SFTO-618],[TOOL])) P
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
here is the modified query:
SELECT [Item] as Item#,
[ALF],[REJ_FG],[SFTO-618],[TOOL],[qty]
FROM(SELECT T0.[ItemCode] as Item, sum(T0.[TransValue]) AS Cost, T0.[Warehouse] AS WAREHOUSE,sum(T0.[InQty] - T0.[OutQty]) AS QTY
FROM OINM T0
WHERE T0.[DocDate] <= [%0] AND T0.[ItemCode] = [%1] group by T0.[ItemCode],T0.[Warehouse]) S
PIVOT
(SUM(cost) FOR WAREHOUSE IN ([ALF],[REJ_FG],[SFTO-618],[TOOL])) P
Hi,
Try this query. Let me know whether it meets your requirement or not:
Note: Replace with correct warehouse code.
SELECT [Item] as Item#,
[OPEN],[REJ_FG],[SFTO-618],[TOOL],[qty]
FROM(SELECT T0.[ItemCode] as Item, sum(T0.[TransValue]) AS Cost, T0.[Warehouse] AS WAREHOUSE,sum(T0.[InQty] - T0.[OutQty]) AS QTY
FROM OINM T0
WHERE T0.[DocDate] <= [%0] AND T0.[ItemCode] = [%1] group by T0.[ItemCode],T0.[Warehouse]) S
PIVOT
(SUM(cost) FOR WAREHOUSE IN ([OPEN],[REJ_FG],[SFTO-618],[TOOL])) P
Thanks & Regards,
Nagarajan
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
91 | |
11 | |
10 | |
6 | |
5 | |
5 | |
5 | |
3 | |
3 | |
3 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.