日期列,查找连续的日期区间及对应的天数
分类:SQL
--生成测试数据
drop table if exists Test;
CREATE TABLE Test (
Id int IDENTITY(1,1) PRIMARY KEY,
DateColumn date
);
DECLARE @StartDate date = '2023-06-01';
DECLARE @EndDate date = '2023-06-30';
WHILE(@StartDate <= @EndDate)
BEGIN
INSERT INTO Test (DateColumn) VALUES (@StartDate);
SET @StartDate = DATEADD(day, 1, @StartDate)
END;
-- 删除一些随机日期以使其不连续
DELETE FROM Test WHERE Id % 5 = 0;
-- 查询连续的日期区间及对应的天数
SELECT MIN(DateColumn) AS StartDate, MAX(DateColumn) AS EndDate,
COUNT(*) AS TotalDays
FROM (
SELECT DateColumn, DATEADD(day, - ROW_NUMBER() OVER (ORDER BY DateColumn), DateColumn) AS Grp
FROM Test
) T
GROUP BY Grp
HAVING COUNT(*) > 1
ORDER BY StartDate;