Remove From My Forums

Pass Date Parameter in CTE in SQL

Question

User2101416554 posted

How to pass date parameter for the below query ...

Like where Date between '01-12-2019' and '31-12-2019' and also Date column should not be NULL

declare @i int=1
declare @d NVARCHAR(50)
declare @dd date
declare @s NVARCHAR(MAX)=' '
while @i<=(select count(distinct(Date)) from ShoppingDetail )
begin
set @dd=(select top 1 s.Date from (select distinct(Date) from ShoppingDetail)s where s.Date not in (select top (@i-1) s.Date from (select distinct(Date) from ShoppingDetail )s))
set @d= (REPLACE(CONVERT(CHAR(11),@dd, 106),' ','-'))
set @s+=QUOTENAME(REPLACE(CONVERT(CHAR(11),@dd, 106),' ','-'))+'as '''+@d+' ('+ LEFT( datename(weekday,@dd) ,3) +' | Wk '+datename(ISO_WEEK,@dd)+')'' ,'
set @i+=1
end
set @s=SUBSTRING(@s,1,LEN(@s)-1)

DECLARE @DynamicPivotQuery AS NVARCHAR(MAX)
DECLARE @ColumnName AS NVARCHAR(MAX)
SELECT @ColumnName = ISNULL(@ColumnName + ',','')+ QUOTENAME(REPLACE(CONVERT(CHAR(11), Date, 106),' ','-') ) FROM (SELECT DISTINCT REPLACE(CONVERT(CHAR(11), Date, 106),' ','-') as Date FROM ShoppingDetail ) AS [Date] Order By Date
DECLARE @ColumnForSum AS NVARCHAR(MAX)
SELECT @ColumnForSum = REPLACE(@ColumnName,',','+')
SELECT @ColumnForSum = REPLACE(@ColumnForSum,'[','ISNULL([')
SELECT @ColumnForSum = REPLACE(@ColumnForSum,']','],0)')
   
DECLARE @RowForSum AS NVARCHAR(MAX)
SELECT @RowForSum = REPLACE(@s,',',',')
SELECT @RowForSum = REPLACE(@RowForSum,'[','SUM(ISNULL([')
SELECT @RowForSum = REPLACE(@RowForSum,']','],0))')

   
SET @DynamicPivotQuery =
';WITH cte
AS (
    SELECT * FROM
    (
        SELECT CustomerName,ProductCategory,'+@ColumnName+' ,SUM('+@ColumnForSum+') Summa
        FROM  ( SELECT Distinct CustomerName,ProductCategory,TotalAmount,Date from [ShoppingDetail] ) t
        pivot ( MAX(TotalAmount) FOR Date IN ('+ @ColumnName +') ) piv GROUP BY CustomerName,ProductCategory,'+ @ColumnName +'
    ) r
)
SELECT CustomerName,ProductCategory,'+ @s +',Summa as Total FROM cte 
UNION ALL
SELECT '' '',''Total'', '+ @RowForSum +',SUM(Summa) FROM cte  '
exec (@DynamicPivotQuery)

SELECT * FROM
    (
        SELECT CustomerName,ProductCategory,'+@ColumnName+' ,SUM('+@ColumnForSum+') Summa
        FROM  ( SELECT Distinct CustomerName,ProductCategory,TotalAmount,Date from [ShoppingDetail] Date is NOT NULL and Date between '01-12-2019' and  '31-12-2019' ) t
        pivot ( MAX(TotalAmount) FOR Date IN ('+ @ColumnName +') ) piv GROUP BY CustomerName,ProductCategory,'+ @ColumnName +'
    )

I have added this but its not working .... Date is NOT NULL and Date between '01-12-2019' and '31-12-2019'

Monday, January 6, 2020 10:30 AM

Answers

User288213138 posted

Hi umerfaiz123@outlook.com,

I have added this but its not working .... Date is NOT NULL and Date between '01-12-2019' and '31-12-2019'

According to your description, you can refer to below code:

declare @i int=1
declare @d NVARCHAR(50)
declare @dd date
declare @s NVARCHAR(MAX)=' '
while @i<=(select count(distinct(Date)) from ShoppingDetail where Date between '2019-12-1' and '2019-12-31' )
begin
set @dd=(select top 1 s.Date from (select distinct(Date) from ShoppingDetail where Date between '2019-12-1' and '2019-12-31')s where s.Date not in (select top (@i-1) s.Date from (select distinct(Date) from ShoppingDetail )s))
set @d= (REPLACE(CONVERT(CHAR(11),@dd, 106),' ','-'))
set @s+=QUOTENAME(REPLACE(CONVERT(CHAR(11),@dd, 106),' ','-'))+'as '''+@d+' ('+ LEFT( datename(weekday,@dd) ,3) +' | Wk '+datename(ISO_WEEK,@dd)+')'' ,'
set @i+=1
end
set @s=SUBSTRING(@s,1,LEN(@s)-1)
DECLARE @DynamicPivotQuery AS NVARCHAR(MAX)
DECLARE @ColumnName AS NVARCHAR(MAX)
SELECT @ColumnName = ISNULL(@ColumnName + ',','')+ QUOTENAME(REPLACE(CONVERT(CHAR(11), Date, 106),' ','-') ) FROM (SELECT DISTINCT REPLACE(CONVERT(CHAR(11), Date, 106),' ','-') as Date FROM ShoppingDetail ) AS [Date] Order By Date
DECLARE @ColumnForSum AS NVARCHAR(MAX)
SELECT @ColumnForSum = REPLACE(@ColumnName,',','+')
SELECT @ColumnForSum = REPLACE(@ColumnForSum,'[','ISNULL([')
SELECT @ColumnForSum = REPLACE(@ColumnForSum,']','],0)')
   
DECLARE @RowForSum AS NVARCHAR(MAX)
SELECT @RowForSum = REPLACE(@s,',',',')
SELECT @RowForSum = REPLACE(@RowForSum,'[','SUM(ISNULL([')
SELECT @RowForSum = REPLACE(@RowForSum,']','],0))')

   
SET @DynamicPivotQuery =
';WITH cte
AS (
    SELECT * FROM
    (
        SELECT CustomerName,ProductCategory,'+@ColumnName+' ,SUM('+@ColumnForSum+') Summa
        FROM  ( SELECT Distinct CustomerName,ProductCategory,TotalAmount,Date from [ShoppingDetail] ) t
        pivot ( MAX(TotalAmount) FOR Date IN ('+ @ColumnName +') ) piv GROUP BY CustomerName,ProductCategory,'+ @ColumnName +'
    ) r
)
SELECT CustomerName,ProductCategory,'+ @s +',Summa as Total FROM cte 
UNION ALL
SELECT '' '',''Total'', '+ @RowForSum +',SUM(Summa) FROM cte  '
exec (@DynamicPivotQuery)

The result:

Best regards,

Sam

Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM

Tuesday, January 7, 2020 5:39 AM

All replies

0

Sign in to vote

User452040443 posted

Hi,

I believe the Where clause was missing, and the part to check if the date is null is not necessary, because when the date is null it will not be within the period.

Remembering that Date is a reserved word and must be enclosed in brackets or preceded by an alias.

Hope this help

Monday, January 6, 2020 12:21 PM

User2101416554 posted

I have added the condition but still it is not working ...

declare @i int=1
declare @d NVARCHAR(50)
declare @dd date
declare @s NVARCHAR(MAX)=' '
while @i<=(select count(distinct(Date)) from ShoppingDetail )
begin
set @dd=(select top 1 s.Date from (select distinct(Date) from ShoppingDetail)s where s.Date not in (select top (@i-1) s.Date from (select distinct(Date) from ShoppingDetail )s))
set @d= (REPLACE(CONVERT(CHAR(11),@dd, 106),' ','-'))
set @s+=QUOTENAME(REPLACE(CONVERT(CHAR(11),@dd, 106),' ','-'))+'as '''+@d+' ('+ LEFT( datename(weekday,@dd) ,3) +' | Wk '+datename(ISO_WEEK,@dd)+')'' ,'
set @i+=1
end
set @s=SUBSTRING(@s,1,LEN(@s)-1)

DECLARE @DynamicPivotQuery AS NVARCHAR(MAX)
DECLARE @ColumnName AS NVARCHAR(MAX)
SELECT @ColumnName = ISNULL(@ColumnName + ',','')+ QUOTENAME(REPLACE(CONVERT(CHAR(11), Date, 106),' ','-') ) FROM (SELECT DISTINCT REPLACE(CONVERT(CHAR(11), Date, 106),' ','-') as Date FROM ShoppingDetail ) AS [Date] Order By Date
DECLARE @ColumnForSum AS NVARCHAR(MAX)
SELECT @ColumnForSum = REPLACE(@ColumnName,',','+')
SELECT @ColumnForSum = REPLACE(@ColumnForSum,'[','ISNULL([')
SELECT @ColumnForSum = REPLACE(@ColumnForSum,']','],0)')
   
DECLARE @RowForSum AS NVARCHAR(MAX)
SELECT @RowForSum = REPLACE(@s,',',',')
SELECT @RowForSum = REPLACE(@RowForSum,'[','SUM(ISNULL([')
SELECT @RowForSum = REPLACE(@RowForSum,']','],0))')

   
SET @DynamicPivotQuery =
';WITH cte
AS (
    SELECT * FROM
    (
        SELECT CustomerName,ProductCategory,'+@ColumnName+' ,SUM('+@ColumnForSum+') Summa
        FROM  ( SELECT Distinct CustomerName,ProductCategory,TotalAmount,Date from [ShoppingDetail] Date is NOT NULL and Date between '01-12-2019' and  '31-12-2019' ) t
        pivot ( MAX(TotalAmount) FOR Date IN ('+ @ColumnName +') ) piv GROUP BY CustomerName,ProductCategory,'+ @ColumnName +'
    ) r
)
SELECT CustomerName,ProductCategory,'+ @s +',Summa as Total FROM cte 
UNION ALL
SELECT '' '',''Total'', '+ @RowForSum +',SUM(Summa) FROM cte  '
exec (@DynamicPivotQuery)

See in the highlighted color parameter query ...

CREATE TABLE [dbo].[ShoppingDetail](
	[CustomerName] [varchar](256) NOT NULL,
	[ProductCategory] [varchar](256) NOT NULL,
	[TotalAmount] [numeric](18, 2) NOT NULL,
	[Date] [date] NULL
) ON [PRIMARY]
GO


INSERT [dbo].[ShoppingDetail] ([CustomerName], [ProductCategory], [TotalAmount], [Date]) VALUES (N'A', N'A1', CAST(7616.70 AS Numeric(18, 2)), CAST(N'2020-01-02' AS Date))
INSERT [dbo].[ShoppingDetail] ([CustomerName], [ProductCategory], [TotalAmount], [Date]) VALUES (N'A', N'A2', CAST(6377.94 AS Numeric(18, 2)), CAST(N'2020-01-02' AS Date))
INSERT [dbo].[ShoppingDetail] ([CustomerName], [ProductCategory], [TotalAmount], [Date]) VALUES (N'A', N'A3', CAST(6803.14 AS Numeric(18, 2)), CAST(N'2020-01-02' AS Date))
INSERT [dbo].[ShoppingDetail] ([CustomerName], [ProductCategory], [TotalAmount], [Date]) VALUES (N'A', N'A4', CAST(27408.96 AS Numeric(18, 2)), CAST(N'2019-12-28' AS Date))
INSERT [dbo].[ShoppingDetail] ([CustomerName], [ProductCategory], [TotalAmount], [Date]) VALUES (N'A', N'A5', CAST(31796.70 AS Numeric(18, 2)), CAST(N'2019-12-27' AS Date))
INSERT [dbo].[ShoppingDetail] ([CustomerName], [ProductCategory], [TotalAmount], [Date]) VALUES (N'A', N'A6', CAST(6117.54 AS Numeric(18, 2)), CAST(N'2019-12-27' AS Date))
INSERT [dbo].[ShoppingDetail] ([CustomerName], [ProductCategory], [TotalAmount], [Date]) VALUES (N'B', N'B1', CAST(15840.00 AS Numeric(18, 2)), CAST(N'2020-01-02' AS Date))
INSERT [dbo].[ShoppingDetail] ([CustomerName], [ProductCategory], [TotalAmount], [Date]) VALUES (N'B', N'B2', CAST(5346.00 AS Numeric(18, 2)), CAST(N'2020-01-02' AS Date))
INSERT [dbo].[ShoppingDetail] ([CustomerName], [ProductCategory], [TotalAmount], [Date]) VALUES (N'B', N'B3', CAST(8731.80 AS Numeric(18, 2)), CAST(N'2020-01-02' AS Date))
INSERT [dbo].[ShoppingDetail] ([CustomerName], [ProductCategory], [TotalAmount], [Date]) VALUES (N'B', N'B4', CAST(7900.20 AS Numeric(18, 2)), CAST(N'2020-01-02' AS Date))
INSERT [dbo].[ShoppingDetail] ([CustomerName], [ProductCategory], [TotalAmount], [Date]) VALUES (N'B', N'B5', CAST(6652.80 AS Numeric(18, 2)), CAST(N'2020-01-02' AS Date))
INSERT [dbo].[ShoppingDetail] ([CustomerName], [ProductCategory], [TotalAmount], [Date]) VALUES (N'B', N'B6', CAST(7484.40 AS Numeric(18, 2)), CAST(N'2020-01-02' AS Date))
INSERT [dbo].[ShoppingDetail] ([CustomerName], [ProductCategory], [TotalAmount], [Date]) VALUES (N'B', N'B7', CAST(3960.00 AS Numeric(18, 2)), CAST(N'2020-01-02' AS Date))
INSERT [dbo].[ShoppingDetail] ([CustomerName], [ProductCategory], [TotalAmount], [Date]) VALUES (N'B', N'B8', CAST(3960.00 AS Numeric(18, 2)), CAST(N'2020-01-02' AS Date))
INSERT [dbo].[ShoppingDetail] ([CustomerName], [ProductCategory], [TotalAmount], [Date]) VALUES (N'B', N'B9', CAST(8137.80 AS Numeric(18, 2)), CAST(N'2020-01-02' AS Date))
INSERT [dbo].[ShoppingDetail] ([CustomerName], [ProductCategory], [TotalAmount], [Date]) VALUES (N'B', N'B10', CAST(1247.40 AS Numeric(18, 2)), CAST(N'2020-01-02' AS Date))
INSERT [dbo].[ShoppingDetail] ([CustomerName], [ProductCategory], [TotalAmount], [Date]) VALUES (N'B', N'B11', CAST(1484.01 AS Numeric(18, 2)), CAST(N'2019-12-30' AS Date))
INSERT [dbo].[ShoppingDetail] ([CustomerName], [ProductCategory], [TotalAmount], [Date]) VALUES (N'B', N'B12', CAST(2518.56 AS Numeric(18, 2)), CAST(N'2020-01-02' AS Date))
INSERT [dbo].[ShoppingDetail] ([CustomerName], [ProductCategory], [TotalAmount], [Date]) VALUES (N'B', N'B13', CAST(2046.33 AS Numeric(18, 2)), CAST(N'2020-01-02' AS Date))
INSERT [dbo].[ShoppingDetail] ([CustomerName], [ProductCategory], [TotalAmount], [Date]) VALUES (N'B', N'B14', CAST(7378.27 AS Numeric(18, 2)), CAST(N'2020-01-02' AS Date))
INSERT [dbo].[ShoppingDetail] ([CustomerName], [ProductCategory], [TotalAmount], [Date]) VALUES (N'B', N'B15', CAST(2518.56 AS Numeric(18, 2)), CAST(N'2020-01-02' AS Date))
INSERT [dbo].[ShoppingDetail] ([CustomerName], [ProductCategory], [TotalAmount], [Date]) VALUES (N'B', N'B16', CAST(2203.74 AS Numeric(18, 2)), CAST(N'2020-01-02' AS Date))
INSERT [dbo].[ShoppingDetail] ([CustomerName], [ProductCategory], [TotalAmount], [Date]) VALUES (N'B', N'B17', CAST(9129.78 AS Numeric(18, 2)), CAST(N'2020-01-02' AS Date))
INSERT [dbo].[ShoppingDetail] ([CustomerName], [ProductCategory], [TotalAmount], [Date]) VALUES (N'B', N'B18', CAST(38030.26 AS Numeric(18, 2)), CAST(N'2020-01-02' AS Date))
INSERT [dbo].[ShoppingDetail] ([CustomerName], [ProductCategory], [TotalAmount], [Date]) VALUES (N'B', N'B19', CAST(7378.27 AS Numeric(18, 2)), CAST(N'2020-01-02' AS Date))
INSERT [dbo].[ShoppingDetail] ([CustomerName], [ProductCategory], [TotalAmount], [Date]) VALUES (N'B', N'B20', CAST(8137.80 AS Numeric(18, 2)), CAST(N'2020-01-02' AS Date))
INSERT [dbo].[ShoppingDetail] ([CustomerName], [ProductCategory], [TotalAmount], [Date]) VALUES (N'B', N'B21', CAST(1574.10 AS Numeric(18, 2)), CAST(N'2019-12-30' AS Date))
INSERT [dbo].[ShoppingDetail] ([CustomerName], [ProductCategory], [TotalAmount], [Date]) VALUES (N'B', N'B22', CAST(3148.20 AS Numeric(18, 2)), CAST(N'2020-01-02' AS Date))
INSERT [dbo].[ShoppingDetail] ([CustomerName], [ProductCategory], [TotalAmount], [Date]) VALUES (N'B', N'B23', CAST(10644.98 AS Numeric(18, 2)), CAST(N'2020-01-02' AS Date))
INSERT [dbo].[ShoppingDetail] ([CustomerName], [ProductCategory], [TotalAmount], [Date]) VALUES (N'B', N'B24', CAST(29909.88 AS Numeric(18, 2)), CAST(N'2020-01-02' AS Date))
INSERT [dbo].[ShoppingDetail] ([CustomerName], [ProductCategory], [TotalAmount], [Date]) VALUES (N'B', N'B25', CAST(21624.57 AS Numeric(18, 2)), CAST(N'2020-01-02' AS Date))
INSERT [dbo].[ShoppingDetail] ([CustomerName], [ProductCategory], [TotalAmount], [Date]) VALUES (N'B', N'B26', CAST(1851.30 AS Numeric(18, 2)), CAST(N'2020-01-02' AS Date))
INSERT [dbo].[ShoppingDetail] ([CustomerName], [ProductCategory], [TotalAmount], [Date]) VALUES (N'B', N'B27', CAST(2776.95 AS Numeric(18, 2)), CAST(N'2020-01-02' AS Date))
INSERT [dbo].[ShoppingDetail] ([CustomerName], [ProductCategory], [TotalAmount], [Date]) VALUES (N'B', N'B28', CAST(5936.04 AS Numeric(18, 2)), CAST(N'2020-01-02' AS Date))
INSERT [dbo].[ShoppingDetail] ([CustomerName], [ProductCategory], [TotalAmount], [Date]) VALUES (N'B', N'B29', CAST(2374.42 AS Numeric(18, 2)), CAST(N'2020-01-02' AS Date))
INSERT [dbo].[ShoppingDetail] ([CustomerName], [ProductCategory], [TotalAmount], [Date]) VALUES (N'B', N'B30', CAST(1780.81 AS Numeric(18, 2)), CAST(N'2020-01-02' AS Date))
INSERT [dbo].[ShoppingDetail] ([CustomerName], [ProductCategory], [TotalAmount], [Date]) VALUES (N'B', N'B31', CAST(1929.21 AS Numeric(18, 2)), CAST(N'2020-01-02' AS Date))
INSERT [dbo].[ShoppingDetail] ([CustomerName], [ProductCategory], [TotalAmount], [Date]) VALUES (N'B', N'B32', CAST(2077.61 AS Numeric(18, 2)), CAST(N'2020-01-02' AS Date))
INSERT [dbo].[ShoppingDetail] ([CustomerName], [ProductCategory], [TotalAmount], [Date]) VALUES (N'B', N'B33', CAST(3580.63 AS Numeric(18, 2)), CAST(N'2020-01-02' AS Date))
INSERT [dbo].[ShoppingDetail] ([CustomerName], [ProductCategory], [TotalAmount], [Date]) VALUES (N'C', N'C1', CAST(1356.39 AS Numeric(18, 2)), CAST(N'2019-12-27' AS Date))
INSERT [dbo].[ShoppingDetail] ([CustomerName], [ProductCategory], [TotalAmount], [Date]) VALUES (N'C', N'C2', CAST(38711.97 AS Numeric(18, 2)), CAST(N'2019-12-30' AS Date))
INSERT [dbo].[ShoppingDetail] ([CustomerName], [ProductCategory], [TotalAmount], [Date]) VALUES (N'C', N'C3', CAST(23.60 AS Numeric(18, 2)), CAST(N'2019-12-23' AS Date))
INSERT [dbo].[ShoppingDetail] ([CustomerName], [ProductCategory], [TotalAmount], [Date]) VALUES (N'C', N'C4', CAST(3941.38 AS Numeric(18, 2)), CAST(N'2019-12-30' AS Date))
INSERT [dbo].[ShoppingDetail] ([CustomerName], [ProductCategory], [TotalAmount], [Date]) VALUES (N'C', N'C5', CAST(3950.59 AS Numeric(18, 2)), CAST(N'2020-01-02' AS Date))
INSERT [dbo].[ShoppingDetail] ([CustomerName], [ProductCategory], [TotalAmount], [Date]) VALUES (N'C', N'C6', CAST(25901.87 AS Numeric(18, 2)), CAST(N'2020-01-10' AS Date))
INSERT [dbo].[ShoppingDetail] ([CustomerName], [ProductCategory], [TotalAmount], [Date]) VALUES (N'C', N'C7', CAST(4998.72 AS Numeric(18, 2)), CAST(N'2020-01-10' AS Date))
INSERT [dbo].[ShoppingDetail] ([CustomerName], [ProductCategory], [TotalAmount], [Date]) VALUES (N'D', N'D1', CAST(74477.79 AS Numeric(18, 2)), CAST(N'2020-01-10' AS Date))
INSERT [dbo].[ShoppingDetail] ([CustomerName], [ProductCategory], [TotalAmount], [Date]) VALUES (N'D', N'D2', CAST(145753.47 AS Numeric(18, 2)), CAST(N'2020-01-10' AS Date))

See the table detail ...

Tuesday, January 7, 2020 3:18 AM