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

Answer 1

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.