T-SQL to Calculating Active Members if a customer is done shopping in last 12 months

Question

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.

Answer 1

Hi @Shivendoo Kumar ,

Left join is a very resource-consuming operation. If the associated field is not indexed, the speed is very slow, so if there is a left join, it is best to use the index field to get the association. Or index the associated field.

One million pieces of data is not too much. I used to run one million pieces of data and it only took one or two minutes or less to count.

I tried to rewrite your query,could you please share us your table structure (CREATE TABLE …) and some sample data(INSERT INTO …)along with your expected result? So that we’ll get a right direction and make some test.Only a minimal example is needed.

Can left join be replaced with inner join?If yes, you can try inner join first.

Regards
Echo

---

If the answer is helpful, please click "Accept Answer" and upvote it.

Answer 2

I am not sure that the query you have matches the problem description. If nothing else, you say 12 months in the text, but you have 48 months in the query.

In any case, to help with performance issues, we need to see:

1. The CREATE TABLE statements for the tables.
2. The CREATE INDEX statements for the tables.
3. The actual execution plan in XML format.

This is more than a mouthful to include in a post, but zip it into a file that you attach.