Get dates for last 30 days dynamically in SQL

Question

I have below SQL which gives me count of files received in particular country according to date. But here dates are hard coded. I want them dynamically. I want it in such a way that whenever I run this query, I get result for last 30 days. Below is the SQL:

with t (Country ,Date,total)
as
(
select b.country as Market, CAST(a.ProcessDate AS Date) AS DATE, count(a.ProcessDate) AS total from Log a LEFT JOIN File b ON a.FileID = b.FileID where a.ProcessDate BETWEEN '2022-11-01' AND '2022-11-07' GROUP BY b.country, CAST(a.ProcessDate AS DATE)
)

Select 
    *
from
    (
Select 
    Date,
    Total,
    Country
from t
    ) x
Pivot(
    sum(total)
    for Date in (
           
            [2022-11-01],
            [2022-11-02],
            [2022-11-03],
            [2022-11-04]

    )
) as pivottable

Below is Result of the query with dummy data:

Country	2022-11-01	2022-11-02	2022-11-03	2022-11-04
Brazil	2			1
Chile		1	1
Switzerland				1

Below is the structure of MasterFile and FileProcessLog with dummy data:

MasterFile:

FileID	Country
1	Brazil
2	Brazil
3	Chile
4	Chile
5	Switzerland

FileProcessLog:

FileID	ProcessDate
1	2022-11-01T15:31:53.0000000
2	2022-11-01T15:32:28.0000000
3	2022-11-02T15:33:34.0000000
4	2022-11-03T15:33:34.0000000
5	2022-11-04T15:37:10.0000000

Does this answer your question? [SQL Server dynamic PIVOT query?](https://stackoverflow.com/questions/10404348/sql-server-dynamic-pivot-query) — Thom A, Nov 14 '22 at 12:42
Honestly, however, this really feels like a task for your presentation layer, *not* the SQL layer. — Thom A, Nov 14 '22 at 12:43
If it's not be displayed to anyone, why do you want/need to denormalise your data? — Thom A, Nov 15 '22 at 08:07
Actually we are manually updating the files count, so I thought to automate it using SQL. So whenever we need record of last 30 dates we just need to run the query — AKHIL OMAR, Nov 15 '22 at 08:09
Because I need it country wise otherwise if country will be header than same country will appear multiple times for different files received — AKHIL OMAR, Nov 15 '22 at 08:57

Parth M. Dave · Accepted Answer · 2022-11-15T13:43:24.353

Create function as below to return last 30 day dates:

CREATE FUNCTION [dbo].[RETURNDATE]()            
RETURNS                                                
@ParsedList table                                                
(                                                
DATEINFO DATE
)                                                
AS                                                
BEGIN                                            

DECLARE @Counter Int
SET @Counter=1 
WHILE ( @Counter <= 30)
BEGIN
    --PRINT 'The counter value is = ' + CONVERT(VARCHAR,Convert(Date,DATEADD(DAY, -(@Counter), getdate())))
    INSERT INTO @ParsedList (DATEINFO)                                                
    VALUES (CONVERT(VARCHAR,Convert(Date,DATEADD(DAY, -(@Counter), getdate()))))
    SET @Counter  = @Counter  + 1
END                                             
RETURN                                                
END

now use inside your code as below:

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

select @cols = STUFF((SELECT distinct ',' + QUOTENAME([DateInfo]) 
                    from [DBO].RETURNDATE()
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')


set @query = 'WITH t (Country ,Date,total) AS (
                SELECT b.country as Market, 
                CAST(a.ProcessDate AS Date) AS DATE, 
                COUNT(a.ProcessDate) AS total 
                FROM [dbo].[FileProcessLog] a 
                LEFT JOIN [dbo].[MasterFile] b ON a.FileID = b.FileID where a.ProcessDate BETWEEN ''2022-11-01'' AND ''2022-11-07'' 
                GROUP BY b.country, CAST(a.ProcessDate AS DATE)
             )

SELECT * FROM (SELECT Date,Total,Country from t) x
PIVOT(SUM(total)
    FOR Date IN ('
          + @cols +
    ')
) as PIVOTTABLE
'
execute(@query)

I think your full answer is ready now. Happy Coding.

I have created function as above and then run the query seperately SELECT * FROM [DBO].[RETURNDATE](), It returns me last 30 days date which is fine but when I put the same query inside for "Date in ( )" it shows incorrect syntax. What I am thinking is that it need dates in closed bracket like hardcoded values. — AKHIL OMAR, Nov 15 '22 at 04:14
can you please provide table structure & dummy data of table FileProcessLog & MasterFile — Parth M. Dave, Nov 15 '22 at 05:21
Updated the dummy data for main table along with MasterFile and FileProcessLog structure in the question for better understanding :) — AKHIL OMAR, Nov 15 '22 at 08:38

Get dates for last 30 days dynamically in SQL

1 Answers1