Skip to Content
0
4 days ago

Rewrite pivot query where columns use dates select from query

85 Views

I need to rewrite this pivot query as my supervisor wants it based on current year and previous year but use the parameter of SAP along with selecting a sales person. Prior I hard coded the years. I took a shortcut with the pivot table

--Seems that we can not select dates as headers in SQL 
--Best Approach to change dates is to replace the dates in the code  
--Monthly Sales 
SELECT  
CASE  
WHEN MONTHS = 1 THEN 'JAN' 
WHEN MONTHS = 2 THEN 'FEB' 
WHEN MONTHS = 3 THEN 'MAR' 
WHEN MONTHS = 4 THEN 'APR' 
WHEN MONTHS = 5 THEN 'MAY' 
WHEN MONTHS = 6 THEN 'JUN' 
WHEN MONTHS = 7 THEN 'JUL' 
WHEN MONTHS = 8 THEN 'AUG' 
WHEN MONTHS = 9 THEN 'SEP' 
WHEN MONTHS = 10 THEN 'OCT' 
WHEN MONTHS = 11 THEN 'NOV' 
WHEN MONTHS = 12 THEN 'DEC' END As 'Period', 
[PY], [CY],[CY] - [PY] AS 'Difference', (([CY] - [PY]) / [PY]) As 'Percentage' 
FROM  
(SELECT MONTH(T0.[DocDate]) AS 'Months', YEAR(T0.[DocDate]) AS 'Years', T1.[LineTotal] AS 'LineTotals'
FROM OINV T0 LEFT JOIN INV1 T1 ON T1.[DOCENTRY] = T0.[DOCENTRY] INNER JOIN OSLP T2 ON T0.SLPCODE = T2.SLPCODE   
WHERE T2.SLPName = '[%0]'
UNION ALL 
SELECT MONTH(T0.[DocDate]) AS 'Months', YEAR(T0.[DocDate]) AS 'Years', -T1.[LineTotal] AS 'LineTotals'  
FROM ORIN T0 LEFT JOIN RIN1 T1 ON T1.[DOCENTRY] = T0.[DOCENTRY] INNER JOIN OSLP T2 ON T0.SLPCODE = T2.SLPCODE  
WHERE T2.SLPName = '[%0]'
) AS SourceTable PIVOT  
( SUM(LineTotals) FOR Years  
IN (  [PY], [CY] ) )  
AS PivotTable  
--Q1 
UNION ALL 
SELECT  
'Q1' As 'Period', [PY], [CY],[CY] - [PY] AS 'Difference', (([CY] - [PY]) / [PY]) As 'Percentage' 
FROM  
(SELECT YEAR(T0.DocDate) AS 'Years', (T1.LineTotal) As 'Totals'  
FROM OINV T0 INNER JOIN INV1 T1 ON T1.DocEntry = T0.DocEntry  
INNER JOIN OSLP T2 ON T0.SLPCODE = T2.SLPCODE  
WHERE T2.SLPName = '[%0]' AND MONTH(T0.DOCDATE) BETWEEN 1 and 3 
UNION ALL 
SELECT YEAR(T0.DocDate) AS 'Years', -(T1.LineTotal) As 'Totals'  
FROM ORIN T0 INNER JOIN RIN1 T1 ON T1.DocEntry = T0.DocEntry INNER JOIN OSLP T2 ON T0.SLPCODE = T2.SLPCODE  
WHERE T2.SLPName = '[%0]' AND MONTH(T0.DOCDATE) BETWEEN 1 and 3) AS SourceTable PIVOT  
( SUM(Totals) FOR Years  
IN ( [PY], [CY] ) )  
AS PivotTable  
--Q2 
UNION ALL 
SELECT  'Q2' As 'Period', [PY], [CY],[CY] - [PY] AS 'Difference', (([CY] - [PY]) / [PY]) As 'Percentage' 
FROM  
(SELECT YEAR(T0.DocDate) AS 'Years', (T1.LineTotal) As 'Totals'  
FROM OINV T0 INNER JOIN INV1 T1 ON T1.DocEntry = T0.DocEntry  
INNER JOIN OSLP T2 ON T0.SLPCODE = T2.SLPCODE  
WHERE T2.SLPName = '[%0]' AND MONTH(T0.DOCDATE) BETWEEN 4 and 6 
UNION ALL 
SELECT YEAR(T0.DocDate) AS 'Years', -(T1.LineTotal) As 'Totals'  
FROM ORIN T0 INNER JOIN RIN1 T1 ON T1.DocEntry = T0.DocEntry INNER JOIN OSLP T2 ON T0.SLPCODE = T2.SLPCODE  
WHERE T2.SLPName = '[%0]' AND MONTH(T0.DOCDATE) BETWEEN 4 and 6) AS SourceTable PIVOT  
( SUM(Totals) FOR Years  
IN ( [PY], [CY] ) )  
AS PivotTable  
--Q3 
UNION ALL 
SELECT  
'Q3' As 'Period',  [PY], [CY],[CY] - [PY] AS 'Difference', (([CY] - [PY]) / [PY]) As 'Percentage' 
FROM  
(SELECT YEAR(T0.DocDate) AS 'Years', (T1.LineTotal) As 'Totals'  
FROM OINV T0 INNER JOIN INV1 T1 ON T1.DocEntry = T0.DocEntry  
INNER JOIN OSLP T2 ON T0.SLPCODE = T2.SLPCODE WHERE T2.SLPName = '[%0]' AND MONTH(T0.DOCDATE) BETWEEN 7 and 9 
UNION ALL 
SELECT YEAR(T0.DocDate) AS 'Years', -(T1.LineTotal) As 'Totals'  
FROM ORIN T0 INNER JOIN RIN1 T1 ON T1.DocEntry = T0.DocEntry 
INNER JOIN OSLP T2 ON T0.SLPCODE = T2.SLPCODE WHERE T2.SLPName = '[%0]' AND MONTH(T0.DOCDATE) BETWEEN 7 and 9) AS SourceTable PIVOT  
( SUM(Totals) FOR Years  
IN (  [PY], [CY] ) )  
AS PivotTable  
--Q4 
UNION ALL 
SELECT  
'Q4' As 'Period', [PY], [CY],[CY] - [PY] AS 'Difference', (([CY] - [PY]) / [PY]) As 'Percentage' 
FROM  
(SELECT YEAR(T0.DocDate) AS 'Years', (T1.LineTotal) As 'Totals'  
FROM OINV T0 INNER JOIN INV1 T1 ON T1.DocEntry = T0.DocEntry  
INNER JOIN OSLP T2 ON T0.SLPCODE = T2.SLPCODE WHERE T2.SLPName = '[%0]' AND MONTH(T0.DOCDATE) BETWEEN 10 and 12 
UNION ALL 
SELECT YEAR(T0.DocDate) AS 'Years', -(T1.LineTotal) As 'Totals'  
FROM ORIN T0 INNER JOIN RIN1 T1 ON T1.DocEntry = T0.DocEntry 
INNER JOIN OSLP T2 ON T0.SLPCODE = T2.SLPCODE WHERE T2.SLPName = '[%0]' AND MONTH(T0.DOCDATE) BETWEEN 10 and 12) AS SourceTable PIVOT  
( SUM(Totals) FOR Years
IN ( [PY], [CY] ) )
AS PivotTable
--Total 
UNION ALL 
SELECT  
'Total' As 'Period', [PY], [CY],[CY] - [PY] AS 'Difference', (([CY] - [PY]) / [PY]) As 'Percentage' 
FROM  
(SELECT YEAR(T0.DocDate) AS 'Years', (T1.LineTotal) As 'Totals' 
FROM OINV T0 INNER JOIN INV1 T1 ON T1.DocEntry = T0.DocEntry INNER JOIN OSLP T2 ON T0.SLPCODE = T2.SLPCODE  
WHERE T2.SLPName = '[%0]'  
UNION ALL 
SELECT YEAR(T0.DocDate) AS 'Years', -(T1.LineTotal) AS 'Totals' 
FROM ORIN T0 INNER JOIN RIN1 T1 ON T1.DocEntry = T0.DocEntry INNER JOIN OSLP T2 ON T0.SLPCODE = T2.SLPCODE  
WHERE T2.SLPName = '[%0]' ) AS SourceTable PIVOT  
( SUM(Totals) FOR Years  
IN ( [PY], [CY] ) )  
AS PivotTable