t-sqlstatusdurationsummaryyearmonth

Summary for duration per month for each ID for TSQL / sql server 2016

I have a table (##table) with an ID and the status for each day. I need a summary per month(!) how many days each ID was in which status. (##table_result). See below.

This was my approach. But it does not work. How can I summarize the days for each ID and per status for each month?

select item, Cur_Status, convert(varchar(7), s_date, 126) as YM_S_Date, lag(s_date) over(order by month(s_date) asc) as Start_date ,s_date , datediff (day, lag(s_date) over( order by month(s_date) asc), s_date) as duration from ##table order by item, Start_date

Data:

create table ##table  (item nvarchar(30), S_date date, Cur_Status nvarchar(30));

insert into ##table values
('A','2022/01/01','AA'),
('A','2022/01/02','AA'),
('A','2022/01/03','AA'),
('A','2022/01/04','BB'),
('A','2022/01/05','BB'),
('A','2022/01/06','BB'),
('A','2022/01/07','AA'),
('A','2022/01/08','AA'),
('A','2022/01/09','AA'),
('A','2022/01/10','AA'),
('A','2022/01/11','AA'),
('A','2022/01/12','AA'),
('A','2022/01/13','CC'),
('A','2022/01/14','CC'),
('A','2022/01/15','AA'),
('A','2022/01/16','DD'),
('A','2022/01/17','DD'),
('A','2022/01/18','DD'),
('A','2022/01/19','EE'),
('A','2022/01/20','AA'),
('A','2022/01/21','BB'),
('A','2022/01/22','FF'),
('A','2022/01/23','FF'),
('A','2022/01/24','FF'),
('A','2022/01/25','FF'),
('A','2022/01/26','AA'),
('A','2022/01/27','AA'),
('A','2022/01/28','AA'),
('A','2022/01/29','AA'),
('A','2022/01/30','AA'),
('A','2022/01/31','AA'),
('A','2022/02/01','AA'),
('A','2022/02/02','AA'),
('A','2022/02/03','AA'),
('A','2022/02/04','AA'),
('A','2022/02/05','AA'),
('A','2022/02/06','BB'),
('A','2022/02/07','AA'),
('A','2022/02/08','AA'),
('A','2022/02/09','AA'),
('A','2022/02/10','AA'),
('A','2022/02/11','AA'),
('A','2022/02/12','AA'),
('A','2022/02/13','CC'),
('A','2022/02/14','CC'),
('A','2022/02/15','AA'),
('A','2022/02/16','DD'),
('A','2022/02/17','DD'),
('A','2022/02/18','DD'),
('A','2022/02/19','EE'),
('A','2022/02/20','AA'),
('A','2022/02/21','BB'),
('A','2022/02/22','AA'),
('A','2022/02/23','AA'),
('A','2022/02/24','AA'),
('A','2022/02/25','FF'),
('A','2022/02/26','AA'),
('A','2022/02/27','AA'),
('A','2022/02/28','AA'),
('A','2022/03/01','AA'),
('A','2022/03/02','AA'),
('A','2022/03/03','BB'),
('A','2022/03/04','AA'),
('B','2022/01/01','AA'),
('B','2022/01/02','AA'),
('B','2022/01/03','AA'),
('B','2022/01/04','BB'),
('B','2022/01/05','BB'),
('B','2022/01/06','BB'),
('B','2022/01/07','AA'),
('B','2022/01/08','AA'),
('B','2022/01/09','AA'),
('B','2022/01/10','AA'),
('B','2022/01/11','AA'),
('B','2022/01/12','AA'),
('B','2022/01/13','AA'),
('B','2022/01/14','AA'),
('B','2022/01/15','AA'),
('B','2022/01/16','AA'),
('B','2022/01/17','AA'),
('B','2022/01/18','AA'),
('B','2022/01/19','AA'),
('B','2022/01/20','AA'),
('B','2022/01/21','AA'),
('B','2022/01/22','AA'),
('B','2022/01/23','AA'),
('B','2022/01/24','AA'),
('B','2022/01/25','AA'),
('B','2022/01/26','AA'),
('B','2022/01/27','AA'),
('B','2022/01/28','AA'),
('B','2022/01/29','AA'),
('B','2022/01/30','AA'),
('B','2022/01/31','AA'),
('B','2022/02/01','AA'),
('B','2022/02/02','AA'),
('B','2022/02/03','AA'),
('B','2022/02/04','FF'),
('B','2022/02/05','FF'),
('B','2022/02/06','FF'),
('B','2022/02/07','AA'),
('B','2022/02/08','AA'),
('B','2022/02/09','AA'),
('B','2022/02/10','AA'),
('B','2022/02/11','AA'),
('B','2022/02/12','AA'),
('B','2022/02/13','CC'),
('B','2022/02/14','CC'),
('B','2022/02/15','AA'),
('B','2022/02/16','DD'),
('B','2022/02/17','DD'),
('B','2022/02/18','DD'),
('B','2022/02/19','EE'),
('B','2022/02/20','AA'),
('B','2022/02/21','AA'),
('B','2022/02/22','AA'),
('B','2022/02/23','AA'),
('B','2022/02/24','AA'),
('B','2022/02/25','FF'),
('B','2022/02/26','AA'),
('B','2022/02/27','AA'),
('B','2022/02/28','AA'),
('B','2022/03/01','BB'),
('B','2022/03/02','AA'),
('B','2022/03/03','AA'),
('B','2022/03/04','AA'),
('B','2022/03/05','AA'),
('B','2022/03/06','AA'),
('B','2022/03/07','AA'),
('B','2022/03/08','AA'),
('B','2022/03/09','BB'),
('B','2022/03/10','BB'),
('B','2022/03/11','BB'),
('B','2022/03/12','BB'),
('B','2022/03/13','BB'),
('B','2022/03/14','AA'),
('B','2022/03/15','AA'),
('B','2022/03/16','AA'),
('B','2022/03/17','AA'),
('B','2022/03/18','AA'),
('B','2022/03/19','DD'),
('B','2022/03/20','DD'),
('B','2022/03/21','AA'),
('B','2022/03/22','AA'),
('B','2022/03/23','AA'),
('B','2022/03/24','AA'),
('B','2022/03/25','BB'),
('B','2022/03/26','AA'),
('B','2022/03/27','AA'),
('B','2022/03/28','BB'),
('B','2022/03/30','AA'),
('B','2022/03/31','BB'),
('B','2022/04/01','BB'),
('B','2022/04/02','BB'),
('B','2022/04/04','BB'),
('C','2022/04/04','BB'),
('C','2022/04/05','BB'),
('C','2022/04/06','BB'),
('C','2022/04/07','AA'),
('C','2022/04/08','AA'),
('C','2022/04/09','AA'),
('C','2022/04/10','AA'),
('C','2022/04/11','AA'),
('C','2022/04/12','AA'),
('C','2022/04/13','CC'),
('C','2022/04/14','CC'),
('E','2022/04/15','AA'),
('E','2022/04/16','DD'),
('E','2022/04/17','DD'),
('E','2022/04/18','DD'),
('E','2022/04/19','EE'),
('E','2022/04/20','AA'),
('E','2022/04/21','BB'),
('E','2022/04/22','FF'),
('E','2022/04/23','FF'),
('E','2022/04/24','FF'),
('E','2022/04/25','FF'),
('E','2022/04/26','AA'),
('E','2022/04/27','AA'),
('E','2022/04/28','AA'),
('E','2022/04/29','AA'),
('E','2022/04/30','FF'),
('E','2022/05/01','FF'),
('E','2022/05/01','FF')
;
select * from ##table order by item, S_date

Expected result:

create table ##table_Result  (item nvarchar(30), Start_date date, End_date date, Cur_Status nvarchar(30), Duration int);

insert into ##table_result values
('A','2022/01/01','2022/01/03','AA','3' ),
('A','2022/01/04','2022/01/06','BB','3' ),
('A','2022/01/07','2022/01/12','AA','4' ),
('A','2022/01/13','2022/01/14','CC','2' ),
('A','2022/01/15','2022/01/15','AA','2' ),
('A','2022/01/16','2022/01/18','DD','2' ),
('A','2022/01/19','2022/01/19','EE','1' ),
('A','2022/01/20','2022/01/20','AA','1' ),
('A','2022/01/21','2022/01/21','BB','1' ),
('A','2022/01/22','2022/01/25','FF','4' ),
('A','2022/01/26','2022/01/31','AA','6' ),
('A','2022/02/01','2022/02/05','AA','5' ),
('A','2022/02/06','2022/02/06','BB','5' ),
('A','2022/02/07','2022/02/12','AA','6' ),
('A','2022/02/13','2022/02/14','CC','2' ),
('A','2022/02/15','2022/02/15','AA','1' ),
('A','2022/02/16','2022/02/18','DD','3' ),
('A','2022/02/19','2022/02/19','EE','1' ),
('A','2022/02/20','2022/02/20','AA','1' ),
('A','2022/02/21','2022/02/21','BB','1' ),
('A','2022/02/22','2022/02/24','AA','1' ),
('A','2022/02/25','2022/02/25','FF','1' ),
('A','2022/02/26','2022/02/28','AA','3' ),
('A','2022/03/01','2022/03/02','AA','2' ),
('A','2022/03/03','2022/03/03','BB','2' ),
('A','2022/03/04','2022/03/04','AA','2' ),
('B','2022/01/01','2022/01/02','AA','2' ),
('B','2022/01/03','2022/01/03','AA','1' ),
('B','2022/01/04','2022/01/06','BB','2' ),
('B','2022/01/07','2022/01/31','AA','25'),
('B','2022/02/01','2022/01/03','AA','3')
Solution

  • I am sure there is a more eloquent way to do this, but it seems like a gaps and island type of problem. So here is a different way to go about it:

    SELECT Item,MIN(S_Date) Start_Date, MAX(S_Date) End_Date, Cur_Status, DATEDIFF(day,MIN(S_Date), MAX(s_DATE)) + 1 Duration
FROM
(
    SELECT * ,SUM(CASE WHEN Cur_Status <> LG THEN 1 ELSE 0 END) OVER(PARTITION BY Item,YR,MN ORDER BY s_Date) GRP
    FROM
    (
        select * ,MONTH(s_Date) MN, Year(s_Date) YR, LAG(Cur_Status,1) OVER(PARTITION BY Item ORDER BY S_Date) LG
        from #table
    ) X
) Y

GROUP BY Item,GRP,YR,MN,Cur_Status
ORDER BY Item,Start_Date