I am looking to create rolling retention userid counts for 30,60,90, and 365 days back for each store in a table. The inital table has more columns ,but the ones that matter most will be userid,store,PositionStartDate,PositionEndDate,and date. The date column is coming from me joining on a calendar table so that there are only records for each userid that are in between position start and end date. I have been working at this issue , and to no avail have not been able to figure out how to get counts for the same distinct user ids in each period. I currently have this code which works, but does not give me the same distinct id numbers that show up in the current date that were in the past. I have tried different WHERE clauses and JOINS but essentially sql seems to think I am looking for the same userid in both periods across the board and I end up getting the same count for what I have tried thus far. Any reccommendations,suggestions or solutions are appreciated.
Current Code:
select a1.date,a2.store,
count(distinct a1.userid) as count_current,
count(distinct a2.userid) as count30_back,
count(distinct a3.userid) as count60_back,
count(distinct a4.userid) as count90_back,
count(distinct a5.userid) as count365_back,
INTO ##retentioncounts
FROM ##empcnt2 as a1 inner join ##empcnt2 as a2
on date add(day,-30,a1.date)=a2.date
inner join ##empcnt2 as a3
on date add(day,-60,a1.date)=a3.date
inner join ##empcnt2 as a4
on date add(day,-90,a1.date)=a4.date
inner join ##empcnt2 as a5
on date add(day,-365,a1.date)=a5.date
GROUP BY a2.store,a1.date
ORDER BY 1
The ##empcnt2 table referenced above would look something like this with a couple random records being pulled for two user id's.
userid store PositionStartDate PositionEndDate date
A122599 067881 2017-01-28 2022-03-09 2018-08-28
A122599 067881 2017-01-28 2022-03-09 2018-06-21
A128743 075643 2018-09-12 2021-04-05 2019-07-22
A128743 075643 2018-09-12 2021-04-05 2019-07-21