Skip to Content
0
Former Member
Aug 28, 2014 at 06:20 PM

Pivot in SAP Business ON with multiple Aggregates per Quarter

18 Views

HI,

The SQL Script below tabulates the Sales Amount (TotalPrice) for each Quarter. What should I do to have both Sales Amount and Quantity in each Quarter?

Thanks,

Noel

SELECT P.CardCode

,P.CardName

,P.ItemCode

,P.Year

,[1] as [Q1]

,[2] as [Q2]

,[3] as [Q3]

,[4] as [Q4]

FROM

(SELECT

T0.CardCode

,T0.CardName

,T1.ItemCode

,CASE WHEN T1.LineTotal IS NULL

THEN 0

ELSE

CASE WHEN T1.LineTotal <> 0

THEN T1.LineTotal

ELSE 0

END

END AS 'TotalPrice'

,DatePart( QQ, T0.docdate) AS 'Quarter'

,DatePart( YYYY, T0.docdate) AS 'Year'

FROM OINV T0

INNER JOIN INV1 T1 ON T0.DocEntry = T1.DocEntry

LEFT JOIN OSLP T2 ON T0.SlpCode = T2.SlpCode

INNER JOIN OITM T3 ON T1.ItemCode = T3.ItemCode

INNER JOIN OITB T4 ON T3.ItmsGrpCod = T4.ItmsGrpCod

UNION ALL

SELECT

T0.CardCode

,T0.CardName

,T1.ItemCode

,CASE WHEN T1.LineTotal IS NULL

THEN 0

ELSE

CASE WHEN T1.LineTotal <> 0

THEN 0 - T1.LineTotal

ELSE 0

END

END AS 'TotalPrice'

,DatePart( QQ, T0.docdate) AS 'Quarter'

,DatePart( YYYY, T0.docdate) AS 'Year'

FROM ORIN T0

INNER JOIN RIN1 T1 ON T0.DocEntry = T1.DocEntry

LEFT JOIN OSLP T2 ON T0.SlpCode = T2.SlpCode

INNER JOIN OITM T3 ON T1.ItemCode = T3.ItemCode

INNER JOIN OITB T4 ON T3.ItmsGrpCod = T4.ItmsGrpCod) AS S

PIVOT

(SUM(TotalPrice)

FOR Quarter IN ([1],[2],[3],[4])) AS P