Hi All,
I want to count active customers for each day and store It on a table.
Active Customer= If a customer has done shopping at least once in the last12 months then the customer is called active. For example, if a customer has shopped on date 1-Jan-2019 then the customer will be counted active up to 31-Dec-2019.
So just with one customer, my table looks like
[TransDate], [Active_Customer_Count]
1-Jan-2019, 1
2-Jan-2019, 1
........
........
31-Dec-2019, 1
I get around 1 million count for everyday
I have written this query but it runs for an hour to do this calculation:
SELECT DD.[ToDate] AS [TransDate],
(T1.[Store]) AS [Store],
COUNT(DISTINCT T1.[CardID]) AS [Active_Customer_Count]
FROM dbo.DimDate DD
LEFT JOIN dbo.Customer_Sales T1 ON T1.[TransDate] BETWEEN DD.[FromDate] AND DD.[ToDate] --[FromDate]=DATEADD(Month, -12, CAST(DD.[ToDate] AS DATE))
WHERE 1 = 1
AND DD.[ToDate] >= DATEADD(Month, -48, CAST(GETDATE() AS DATE))
GROUP BY DD.[ToDate],
(T1.[Store])
Is there another way I can achieve the same with better performance? I already index on tables but no gain.