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')
CodePudding user response：
First of all thanks for your reply. Yes, you're right, it has to be 6. I've try it but the issue is that I need the duration for every month. That means that if a period (start-end) goes beyond the month, the duration (start-end) may only be determined up to the end of the month. The rest from the duration should be determined and add to the next month.
For example: A | 2022-01-26 | 2022-02-05 |AA
Result:
A | 2022-01-26 | 2022-01-31 | AA | 6
A | 2000-02-01 | 2022-02-05 | AA | 5
CodePudding user response：
Yikes, unless you're really super sure you need a global temporary table, you likely should not be using them at all.
Here's a good way to present your demo data and tables:
DECLARE @table TABLE (item NVARCHAR(30), S_date DATE, Cur_Status NVARCHAR(30));
INSERT INTO @table (item, S_date, Cur_Status) 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') ;
Now on to the answer. This looks like an rCTE to me:
;WITH base AS (
SELECT item, S_date, Cur_Status, LAG(Cur_Status,1) OVER (PARTITION BY item ORDER BY S_date) AS prev_Status, CASE WHEN Cur_Status = LAG(Cur_Status,1) OVER (PARTITION BY item ORDER BY S_date) AND DATEPART(MONTH,S_date) = DATEPART(MONTH,LAG(S_date,1) OVER (PARTITION BY item ORDER BY S_date)) THEN 1 END AS Counter
FROM @table
), rCTE AS (
SELECT item, S_date, S_Date AS StartDate, Cur_Status, 1 AS Counter, S_date AS StopDate
FROM base
WHERE Counter IS NULL
UNION ALL
SELECT a.item, r.S_date, a.StartDate, a.Cur_Status, a.Counter r.Counter, r.S_date AS StopDate
FROM rCTE a
INNER JOIN base r
ON a.item = r.item
AND a.Cur_Status = r.Cur_Status
AND a.S_date = DATEADD(DAY,-1,r.S_date)
AND r.Counter IS NOT NULL
)
SELECT item, rCTE.StartDate AS Start_date, MAX(rCTE.StopDate) AS End_Date, rCTE.Cur_Status, MAX(Counter) AS Duration
FROM rCTE
GROUP BY item, rCTE.StartDate, rCTE.Cur_Status
ORDER BY item, End_Date
OPTION (MAXRECURSION 0)
Basically what we're doing here is iterating over all the rows to make groups where they don't naturally exist.
It looks like your expected data is off too, I found this line:
item Start_date End_date Cur_Status Duration
----------------------------------------------------
A 2022-01-07 2022-01-12 AA 4
Should that not be 6?
Here's some of the example out put:
item Start_date End_Date Cur_Status Duration
----------------------------------------------------
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 6
A 2022-01-13 2022-01-14 CC 2
A 2022-01-15 2022-01-15 AA 1
A 2022-01-16 2022-01-18 DD 3
A 2022-01-19 2022-01-19 EE 1
A 2022-01-20 2022-01-20 AA 1
Edit:
I modified the query to take into account the end of month.
The case statement expression handing the NULLABLE counter is now:
CASE WHEN Cur_Status = LAG(Cur_Status,1) OVER (PARTITION BY item ORDER BY S_date) AND DATEPART(MONTH,S_date) = DATEPART(MONTH,LAG(S_date,1) OVER (PARTITION BY item ORDER BY S_date)) THEN 1 END
and an additional predicate was applied in the rCTE:
AND r.Counter IS NOT NULL
Example results are now:
item Start_date End_Date Cur_Status Duration
----------------------------------------------------
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 1