dynamic pivot query for customer ageing

[mk3]

Dear Experts,

I have the below dynamic pivot query for customer ageing

Declare @fromdate as datetime

Declare @Todate as datetime

set @fromdate = /* select min(Ta.[fromDate]) from osrt ta where Ta.[fromDate] >= */ [%0]

set @Todate = /* select max(Tb.[toDate]) from osrt tb where Tb.[toDate] <= */ [%1]

declare @cols nvarchar (max)= N''

declare @A nvarchar (max)

select @cols = @cols + case when @cols = N'' then QUOTENAME((month([TAXDATE]))) else N',' + QUOTENAME((month([TAXDATE]) )) end

from JDT1

group by month([TAXDATE])

order by month([TAXDATE]) DESC

set @A = 'select * from (select month(T0.[TAXDATE]) as date,T1.[CardName] as name, T1.[Balance] as balance,YEAR(T0.[TAXDATE]) AS YEAR, ( T0.[BalDueDeb]-T0.[BalDueCred]) as total FROM JDT1 T0 INNER JOIN OCRD T1 ON T0.[ShortName] = T1.CardCode WHERE T0.[TaxDate] between @fromdate and @todate and T1.[CardType]= ''c'' and ( T0.[BalDueDeb]-T0.[BalDueCred])<>0)s

pivot

(sum (Total) for date in (' + @cols +'))p ORDER BY NAME,YEAR'

exec sp_executesql @A , N'@FROMDATE date, @TODATE date', @FROMDATE = @FROMDATE, @TODATE =@TODATE

The query works fine. But I would like to concat the month and year. I have tried using concat,

concat(month([taxdate]),'-',year([taxdate])

but it gives me error

1). 'CONCAT' is not a recognized built-in function name. (SWEI) (s) could not be prepared.

I have also tried using

month([taxdate]) + '' + year([taxdate])

but even that too also gives an error

1). The column '2019' was specified multiple times for 'p'. 'User-Defined Values' (CSHS)

What am I doing wrong?

Furthermore,

a) I have noticed that the above query also displays columns that are out of range of the date parameters. For these out of range from the date parameters, the columns are displayed as blanks. How to exclude these out of range of date parameters columns?

b) Another thing is that even within the date range parameters, columns that are blank are also shown. How to ensure that it does not show blank columns?

Is it even possible to resolve (a) and (b)? If not possible, then that is OK. My main problem is to be able to concat the month and year.

Any help in resolving the above will be appreciated.

Thanking you,

With regards

MK Shah

[LoHa · Accepted solution]

Hi MK

I managed it by using a with. But the query must be used twice

It is naything else but nice. But it seems to work

/**SELECT FROM [OITM] T0 **/
DECLARE @ItemCode as nvarchar(max)
/* WHERE */
Set @ItemCode = /* T0.ItemCode */ '[%0]'

/**SELECT FROM [OITM] T1 **/
DECLARE @PrdGrp as nvarchar(max)
/* WHERE */
Set @PrdGrp = /* T1.ItmsGrpCod */ '[%1]'


DECLARE @Column AS NVARCHAR(max) = ''
DECLARE @Query AS NVARCHAR(max) = ''


/*DEBUG ONLY*/
--DECLARE @ItemCode AS NVARCHAR(max) = ''
--DECLARE @PrdGrp AS NVARCHAR(max) = '108'

;
with Datatable AS 
(
SELECT
	 YEAR([RDR1].[Shipdate]) AS [Year]
	,Month([RDR1].[ShipDate]) AS [Month]
	,CAST(year([RDR1].[Shipdate]) AS char(4)) + '-' + CAST(Format(Month([RDR1].[Shipdate]),'00') AS nvarchar(max)) AS [Period]
	,[ORDR].[DocNum]
	,[ORDR].[DocEntry] 
	,[ORDR].[DocDate]
	,[RDR1].[ItemCode]
	,[OITM].[ItemName]
	,[OITM].[ItmsGrpCod]
	,[RDR1].[ShipDate]
	,[RDR1].[OpenQty]
FROM
	[RDR1]
	INNER JOIN [ORDR] ON 
		[ORDR].[DocEntry] = [RDR1].[DocEntry]
	INNER JOIN [OITM] ON 
		[OITM].[ItemCode] = [RDR1].[ItemCode]
WHERE 

		[ORDR].[DocStatus] = 'O'
	AND
		[RDR1].[LineStatus] = 'O'
	AND
		([OITM].[ItemCode] = @ItemCode OR @ItemCode = '')
	AND
		([OITM].[ItmsGrpCod] = @PrdGrp OR @PrdGrp = '')

--ORDER BY Period ASC
)
--SELECT * FROm Datatable

/*Befülle die Variabele um diese dann als Spaltenkopf nutzen zu können und darauf zu filtern*/
SELECT @Column = 
				STUFF
					(
						(SELECT 
							',' + QUOTENAME(Period) 
						 FROM Datatable
						 GROUP BY Period
						 ORDER BY Period ASC
						 FOR XML PATH(''), TYPE
						).value('.', 'NVARCHAR(MAX)'),1,1,''
					)
--SELECT @Column

/*Erstelle dynamische Pivot-Abfrage*/
SELECT @Query=
				'SELECT * FROM 
								(
								SELECT 
								DISTINCT
										[Dat].[ItemCode],
										[Dat].[ItemName],
										[Dat].[Period],
										[Dat].[PrdGrp],
										sum([Dat].[OpenQty]) SumOpen
								FROM 
									(
									SELECT
									 YEAR([RDR1].[Shipdate]) AS [Year]
									,Month([RDR1].[ShipDate]) AS [Month]
									,CAST(year([RDR1].[Shipdate]) AS char(4)) + ''-'' + CAST(Format(Month([RDR1].[Shipdate]),''00'') AS nvarchar(max)) AS [Period]
									,[ORDR].[DocNum]
									,[ORDR].[DocEntry] 
									,[ORDR].[DocDate]
									,[RDR1].[ItemCode]
									,[OITM].[ItemName]
									,[OITM].[ItmsGrpCod] AS [PrdGrp]
									,[RDR1].[ShipDate]
									,[RDR1].[OpenQty]
								FROM
									[RDR1]
									INNER JOIN [ORDR] ON 
										[ORDR].[DocEntry] = [RDR1].[DocEntry]
									INNER JOIN [OITM] ON 
										[OITM].[ItemCode] = [RDR1].[ItemCode]
								WHERE 

										[ORDR].[DocStatus] = ''O''
									AND
										[RDR1].[LineStatus] = ''O''
									AND	[OITM].[ItemCode] = ''@ItemCode''
									AND	[OITM].[ItmsGrpCod] = ''@PrdGrp''
									) Dat

								GROUP BY 
									[Dat].[ItemCode],[Dat].[ItemName],[Dat].[PrdGrp],[Dat].[Period]

								) P
									Pivot (Sum([SumOpen]) FOR Period in ('+@column+')) AS PVTable 
									ORDER BY 
										[PrdGrp] ASC , [ItemCode] ASC'
--SELECT @Query

SELECT @Query = 
	CASE WHEN @Itemcode = '' THEN 
	REPLACE(@Query,'AND	[OITM].[ItemCode] = ''@ItemCode''','')
	ELSE
	REPLACE(@Query,'@ItemCode',@ItemCode) END 
SELECT @Query = 
	CASE WHEN @PrdGrp = '' THEN
	REPLACE(@Query,'AND	[OITM].[ItmsGrpCod] = ''@PrdGrp''','')
	ELSE
	REPLACE(@Query,'@PrdGrp',@PrdGrp) END 
--SELECT @Query
EXECUTE (@Query)

regards Lothar

[mk3 · Accepted solution]

Hii Lothar,

Thanks for the above query. I have accepted it as correct even though it does not directly answer my question. Nevertheless it does do what I wanted after modifications.

With regards,

mkshah

[LoHa]

Hi

than do the query twice

DECLARE @Column AS NVARCHAR(max) = ''
DECLARE @Query AS NVARCHAR(max) = ''



/*Befülle die Variabele um diese dann als Spaltenkopf nutzen zu können und darauf zu filtern*/
SELECT @Column = 
				STUFF
					(
						(SELECT 
							',' + QUOTENAME(CAST(year([RDR1].[Shipdate]) AS char(4)) + '-' + CAST(Format(Month([RDR1].[Shipdate]),'00') AS nvarchar(max))) 
						 FROM
						[RDR1]
						INNER JOIN [ORDR] ON 
							[ORDR].[DocEntry] = [RDR1].[DocEntry]
						INNER JOIN [OITM] ON 
							[OITM].[ItemCode] = [RDR1].[ItemCode]
						WHERE 

							[ORDR].[DocStatus] = 'O'
						AND
							[RDR1].[LineStatus] = 'O'

						 GROUP BY CAST(year([RDR1].[Shipdate]) AS char(4)) + '-' + CAST(Format(Month([RDR1].[Shipdate]),'00') AS nvarchar(max))
						 ORDER BY CAST(year([RDR1].[Shipdate]) AS char(4)) + '-' + CAST(Format(Month([RDR1].[Shipdate]),'00') AS nvarchar(max)) ASC
						 FOR XML PATH(''), TYPE
						).value('.', 'NVARCHAR(MAX)'),1,1,''
					)


/*Erstelle dynamische Pivot-Abfrage*/
SELECT @Query=
				'SELECT * FROM 
								(
								SELECT 
								DISTINCT
										[RDR1].[ItemCode],
										[OITM].[ItemName],
										CAST(year([RDR1].[Shipdate]) AS char(4)) + ''-'' + CAST(Format(Month([RDR1].[Shipdate]),''00'') AS nvarchar(max)) AS [Period],
										[OITM].[ItmsGrpCod],
										sum([RDR1].[OpenQty]) SumOpen
								FROM
								[RDR1]
								INNER JOIN [ORDR] ON 
									[ORDR].[DocEntry] = [RDR1].[DocEntry]
								INNER JOIN [OITM] ON 
									[OITM].[ItemCode] = [RDR1].[ItemCode]
								WHERE 

									[ORDR].[DocStatus] = ''O''
								AND
									[RDR1].[LineStatus] = ''O''

								GROUP BY 
									[RDR1].[ItemCode],[OITM].[ItemName],[OITM].[ItmsGrpCod],CAST(year([RDR1].[Shipdate]) AS char(4)) + ''-'' + CAST(Format(Month([RDR1].[Shipdate]),''00'') AS nvarchar(max))

								) P
									Pivot (Sum([SumOpen]) FOR Period in ('+@column+')) AS PVTable 
									ORDER BY 
										[ItmsGrpCod] ASC , [ItemCode] ASC'

EXECUTE (@Query)

regards Lothar

[mk3]

Hii, Lothar ,

Thanks for the above sample query.

With regards,

MK Shah

[LoHa]

Hi MK,

you are welcome.

If it fits for you mark as solved regards

regards Lothar

[mk3]

Hii, Lothar,

Your use of a temporary table with dynamic pivot table query is appropriate. It is only by using a temporary table did I manage to have the query work to my requirement.

Regards

mkshah

[LoHa]

Hi MK,

I'm a little bit busy. I can't have a closer look on your query.

I did something similar before.

Perhabs you could adopt it

/*Prüfe Ob TempTable existiert, dann löschen*/
IF OBJECT_ID('tempdb.dbo.#Data') IS NOT NULL 
	DROP TABLE #Data

/*Erstelle TempTable um die Daten aufzunehmen*/
CREATE TABLE #Data				
	(
	 [Year] int
	,[Month] int
	,[Period] char(7)
	,[DocNum] int
	,[DocEntry] int
	,[DocDate] datetime
	,[ItemCode] nvarchar(20)
	,[ItemName] nvarchar(200)
	,[PrdGrp]  nvarchar(max)
	,[ShipDate] datetime
	,[OpenQty] numeric(19,6)
	)

/**SELECT FROM [OITM] T0 **/
DECLARE @ItemCode as nvarchar(max)
/* WHERE */
Set @ItemCode = /* T0.ItemCode */ '[%0]'

/**SELECT FROM [OITM] T1 **/
DECLARE @PrdGrp as nvarchar(max)
/* WHERE */
Set @PrdGrp = /* T1.ItmsGrpCod */ '[%1]'


DECLARE @Column AS NVARCHAR(max) = ''
DECLARE @Query AS NVARCHAR(max) = ''


--/*DEBUG ONLY*/
--DECLARE @ItemCode AS NVARCHAR(max) = ''
--DECLARE @PrdGrp AS NVARCHAR(max) = ''

INSERT INTO #Data

SELECT
	 YEAR([RDR1].[Shipdate]) AS [Year]
	,Month([RDR1].[ShipDate]) AS [Month]
	,CAST(year([RDR1].[Shipdate]) AS char(4)) + '-' + CAST(Format(Month([RDR1].[Shipdate]),'00') AS nvarchar(max)) AS [Period]
	,[ORDR].[DocNum]
	,[ORDR].[DocEntry] 
	,[ORDR].[DocDate]
	,[RDR1].[ItemCode]
	,[OITM].[ItemName]
	,[OITM].[ItmsGrpCod]
	,[RDR1].[ShipDate]
	,[RDR1].[OpenQty]
FROM
	[RDR1]
	INNER JOIN [ORDR] ON 
		[ORDR].[DocEntry] = [RDR1].[DocEntry]
	INNER JOIN [OITM] ON 
		[OITM].[ItemCode] = [RDR1].[ItemCode]
WHERE 

		[ORDR].[DocStatus] = 'O'
	AND
		[RDR1].[LineStatus] = 'O'
	AND
		([OITM].[ItemCode] = @ItemCode OR @ItemCode = '')
	AND
		([OITM].[ItmsGrpCod] = @PrdGrp OR @PrdGrp = '')

ORDER BY Period ASC

/*Befülle die Variabele um diese dann als Spaltenkopf nutzen zu können und darauf zu filtern*/
SELECT @Column = 
				STUFF
					(
						(SELECT 
							',' + QUOTENAME(Period) 
						 FROM #Data
						 GROUP BY Period
						 ORDER BY Period ASC
						 FOR XML PATH(''), TYPE
						).value('.', 'NVARCHAR(MAX)'),1,1,''
					)


/*Erstelle dynamische Pivot-Abfrage*/
SELECT @Query=
				'SELECT * FROM 
								(
								SELECT 
								DISTINCT
										[Dat].[ItemCode],
										[Dat].[ItemName],
										[Dat].[Period],
										[Dat].[PrdGrp],
										sum([Dat].[OpenQty]) SumOpen
								FROM 
									#Data Dat

								GROUP BY 
									[Dat].[ItemCode],[Dat].[ItemName],[Dat].[PrdGrp],[Dat].[Period]

								) P
									Pivot (Sum([SumOpen]) FOR Period in ('+@column+')) AS PVTable 
									ORDER BY 
										[PrdGrp] ASC , [ItemCode] ASC'

EXECUTE (@Query)

regards Lothar

[mk3]

Dear Lothar,

Thank you for sharing your above query. I have had a look at it and yes,with adaptation I can make it work for my use case.

But regretfully, it is against our company's policy to create,insert and drop user defined tables, even if only temporarily. So, it seems as if I will just have to use the dynamic pivot table query as is or use static pivot table query instead.

Thanks and regards,

MK Shah

[LoHa]

Hi MK,

concat can be used with a string. You try to concat a date. Cast it in the concat first like

concat(CAST(month([taxdate])as nvarchar(max)),'-',CAST(year([taxdate] as nvarchar(max)))

regards Lothar

[mk3]

Hii Lothar,

Thank you for your prompt response.

I have replaced the month([taxdate]) everywhere in my query with your suggested solution. But i am getting an error as below

1). Incorrect syntax near the keyword 'AS'. 2).' (SWEI) (s) could not be prepared.

Any idea as to what I could be doing wrong?

With regards

MK Shah