我有一个表(##表(，其中包含ID和每天的状态。我需要每月(！(每个ID处于哪个状态的天数摘要。(##table_result(。请参见下文。
这就是我的做法。但它不起作用。如何总结每个ID的天数和每个月的每个状态？
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
数据：
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
预期结果：
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')
Yikes，除非你真的非常确定你需要一个全局临时表，否则你可能根本不应该使用它们。
这里有一个展示演示数据和表格的好方法：
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') ;
现在来看答案。这在我看来像是一个rCTE:
;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)
基本上，我们在这里所做的是对所有行进行迭代，以在它们不自然存在的地方生成组。
看起来你的预期数据也偏离了，我发现了这一行：
item Start_date End_date Cur_Status Duration
----------------------------------------------------
A 2022-01-07 2022-01-12 AA 4
应该不是6吗？
下面是一些例子：
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
编辑：
我修改了查询以考虑月底。处理NULLABLE计数器的case语句表达式现在是：
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
并且在rCTE:中应用了额外的谓词
AND r.Counter IS NOT NULL
现在的示例结果是：
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
我相信有一种更有效的方法可以做到这一点，但这似乎是一个缺口和孤岛类型的问题。因此，这里有一种不同的方法：
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
首先感谢您的回复。是的，你是对的，一定是6。我试过了，但问题是我需要每个月的持续时间。这意味着，如果一个时期(开始-结束(超过了一个月，则持续时间(开始-终止(只能确定到月底。应确定剩余时间，并将其添加到下个月。
例如：A|2022-01-26|2022-02-05|AA
结果：
A|2022-01-26|2022-01-31|AA|6
A | 2000-02-01 | 2022-02-05 | AA | 5