0

Basically I have a table of membership dates and status changes and I want to create a rolling count of each number of members by status for each date like:

Table Data

And I want to output it like:

Desired Output

Not really sure on the best way to construct this query I suppose I'd have to create a recursive query to count the events at the date and then pivot the output to create separate columns for the member status?

TIA

create table MyTable(status int,st datetime, memberID int)
insert into MyTable values(1,'2022-02-01',1),
(1,'2022-03-01' ,2),
(2,'2022-04-01',1)

select [st],[1],[2]
from (select [st],memberID,status from MyTable) as SrcTable
pivot(
count(memberID) for status in ([1],[2])
) as pivottable
order by [st] asc

But it doesnt aggregate in March like I want it to

Benzo
  • 13
  • 4
  • This doesn't need recursion, it's a `PIVOT` or conditional aggregation. – Thom A Feb 07 '23 at 16:34
  • Please use the [edit] feature; the comments aren't for your attempt(s). – Thom A Feb 07 '23 at 17:02
  • Status 1 increases from 1 to 2 and then decreases to 1. Is that what you want? Also, can there be more than one record per month in the source table? I can't tell if there's a restriction on how many member records can exist per moth. The sample should be the "worst" case. Otherwise, the solution could become incorrect with more data. Perhaps group by is needed. Also, this might be a windowed function sort of thing. – Randy in Marin Feb 07 '23 at 18:03
  • You have a two-step problem. First you need to actually generate snapshot of statuses per member for every day. Right now you don't repeat status unless it changes. Then you can pivot it like you wanna do. To repeat status is a bit complicated but if you have a calender table, it should be pretty easy – siggemannen Feb 07 '23 at 18:07

1 Answers1

0

Figured it out I put the last date as get_date() since that what my problem in real-life requires but I did make use of a recursive CTE and pivot

create table MyTable(status int,st datetime, memberID int)
insert into MyTable values(1,'2022-02-01',1),
(1,'2022-03-01' ,2),
(2,'2022-04-01',1);

with cte as (
 select status,st,memberID,(case when Nxt_sts is null then 0 else Nxt_sts end) 
 as Nxt_sts,
(case when Nxt_st is null then  FORMAT(DateAdd(Month,1,getdate()), 'yyyyMM01') 
else Nxt_st end) as Nxt_st
from (select
A.status,A.st,A.memberID,B.status as Nxt_sts,B.st as nxt_st
from MyTable A
left join MyTable B
 on A.MemberID = B.MemberID
 and A.status+1 = B.status ) A
)

, cte_2 as 
(
select status,st,memberID,Nxt_sts,Nxt_st
from cte
Union All
select status,DateAdd(Month, 1 ,st),memberID,Nxt_sts,Nxt_st from cte_2
where DateAdd(Month, 1 ,st) < Nxt_st
 )

select [st],[1],[2],[3]
from (select [st],memberID,status from cte_2) as SrcTable
pivot(
count(memberID) for status in ([1],[2],[3])
) as pivottable
order by [st] asc
Benzo
  • 13
  • 4