I have an Azure SQL Database, one Table that has 3 Relevant Columns: Brand, ID and Month/Year and a Temporary Table that simply lists the Month/Year with an ID.
The table shows a list of all IDs that were active for that given month.
Month/Year is a datestamp column, with the date as the first of the Month. The ID Column is just an ID.
The goal is to have an output that for each month will show the count of all the records that were active in this month, but not active in the next month (Churn) and IDs that weren't active in the previous month but where active in this month (New) and have these grouped by brand and Month so:
Brand | Churn | New | Month
1 | 10 | 12 | 2019-12-01
2 | 11 | 9 | 2019-12-01
I should also note that Table1 has 19 million rows.
I tried the below code:
Declare @Id int declare @my date set @Id = 300 set @my = '2019-12-01' select t1.[brand], count(c.[ID]) as [churn], count(n.[ID]) as [new], t1.[month/year] from Table1 T1 left join Table1 c on c.[ID] = t1.[ID] and c.[ID] not in (select [ID] from Table1 where [Month/Year] = (select [month/year] from Temp_Date where id = (@Id +1))) left join Table1 n on n.[ID] = T1.[ID] and n.[ID] not in (select [ID] from Table1 where [Month/Year] = (select [month/year] from Temp_Date where id = (@Id -1))) where t1.[Month/Year] = @my group by t1.[Brand], t1.[Month/Year]
Which although it produced an output, I don't think it was right and it took ages, probably due to my liberal use of Sub-Queries in Joins.
My question is 2-fold - can someone assist in a better way to count the number of rows and join them and is there a better way to do this without so many sub-queries?