Rewrite pivot query where columns use dates select from query

[jbrotto]

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

[LoHa]

Hi Jonathan,

you can do it like that.

DECLARE @cols AS NVARCHAR(MAX)
DECLARE @query  AS NVARCHAR(MAX)

IF OBJECT_ID ('tempdb..#TempData','U') IS NOT NULL
DROP TABLE dbo.#TempData

CREATE TABLE #TempData
(
ItemCode  nvarchar(max) NOT NULL,
ItemName  nvarchar(max),
OpenQty int,
DateMonth nvarchar(max)
)


;
INSERT INTO #TempData (ItemCode,ItemName,OpenQty,DateMonth)
SELECT 
	 [OITM].[ItemCode]
	,[OITM].[ItemName]
	,[RDR1].[OpenQty]
	,CAST(year([RDR1].[ShipDate]) AS char(4)) + '-' + CAST(Format(Month([RDR1].[ShipDate]),'00') AS nvarchar(max)) as [DateMonth] 
FROM 
	[OITM]
	LEFT JOIN [RDR1] ON [RDR1].[ItemCode] = [OITM].[ItemCode] AND [RDR1].[LineStatus] = 'O'
	LEFT JOIN [ORDR] ON [ORDR].[DocEntry] = [RDR1].[DocEntry]
WHERE 
	[OITM].[ItemCode] IN ('KR M1156-1','KR M1177-1','OPT1150090011')

SELECT @cols = STUFF(
						(SELECT ',' + QUOTENAME([DateMonth]) 
						FROM dbo.#TempData
						GROUP BY DateMonth
						ORDER BY DateMonth ASC
						FOR XML PATH(''), TYPE
						).value('.', 'NVARCHAR(MAX)') 
					,1,1,'')


set @query = 'SELECT [ItemCode],[ItemName], ' + @cols + ',[Total]  from 
             (
                SELECT 
				[ItemCode],[ItemName],[OpenQty],[DateMonth],sum([OpenQty]) over (partition by [ItemCode]) AS [Total]
                FROM dbo.#TempData
            ) X
            pivot 
            (
                SUM([OpenQty])
                FOR [DateMonth] in (' + @cols + ')
            ) p '

execute(@query)

It also answers your question about TempTable

regards Lothar

[jbrotto]

I get no results from my query is that normal?

[LoHa]

From your or mine ?

Mine shows open quatities in sales order with specific items. You have to change the items in the where-clause

[jbrotto]

I copied your code but not sure why I get nothing but maybe it is this section?

('KR M1156-1','KR M1177-1','OPT1150090011')

[LoHa]

Hi Jonathan,

you r right. These are ItemCodes I used.

If you need aother values than ItemCodes, you have to change it on your own.

Try to understand what the query do. Then use it on your own porposes.

First I add all needed data (open qty with shipdate from SO) in a temp-table. Important is I generate a key from the date like 01-2022 / 03-2022 and so on.

This wil be put in a variable (stuff - try: select @cols), so this can be used in the main-query

regards Lothar