英文:
Count number of days passed over the last year until a limit from multiple date intervals
问题
我想要能够根据组内的多个日期间隔,计算过去一年(任意设置)已经过去了多少天。当计算了100天时,我希望停止计数,然后在过去的一年(365或366天)中,如果没有计数100天,就重新开始计数。
示例样本数据(示例数据:https://dbfiddle.uk/SPVZol7L):
组别 | 开始日期 | 结束日期 |
---|---|---|
1 | 01-01-2020 | 01-07-2020 |
1 | 01-08-2020 | 01-07-2021 |
2 | 01-02-2020 | 01-03-2020 |
2 | 01-09-2020 | 01-12-2020 |
对于第一组,在2020年1月1日至2020年9月4日之间已经计算了100天。我希望在此后停止计数,并在接下来的时间间隔从2021年2月1日开始重新计数,如果在过去的一年(366天,闰年)中没有计算100天。
我已经尝试了与包含所有日期的维度表交叉连接,以便可以轻松地在多个日期间隔中计算连续的天数,如上所示。但我不知道如何回顾过去的365天,并查看是否已经计算了100天。如果可以在每个间隔旁边输出已计算的天数,或者更好地说是在每个间隔内已经过去的每个月,那将很好。任何帮助或提示将不胜感激。
预期结果可能如下所示:
组别 | 开始日期 | 结束日期 | 年月份 | 计数天数 |
---|---|---|---|---|
1 | 01-01-2020 | 01-07-2020 | 202001 | 31 |
1 | 01-01-2020 | 01-07-2020 | 202002 | 29 |
1 | 01-01-2020 | 01-07-2020 | 202003 | 31 |
1 | 01-01-2020 | 01-07-2020 | 202004 | 9 |
1 | 01-01-2020 | 01-07-2020 | 202005 | 0 |
1 | 01-01-2020 | 01-07-2020 | 202006 | 0 |
1 | 01-01-2020 | 01-07-2020 | 202007 | 0 |
1 | 01-08-2020 | 01-07-2021 | 202008 | 0 |
1 | 01-08-2020 | 01-07-2021 | 202009 | 0 |
1 | 01-08-2020 | 01-07-2021 | 202010 | 0 |
1 | 01-08-2020 | 01-07-2021 | 202011 | 0 |
1 | 01-08-2020 | 01-07-2021 | 202012 | 0 |
1 | 01-08-2020 | 01-07-2021 | 202101 | 30 |
1 | 01-08-2020 | 01-07-2021 | 202102 | 28 |
1 | 01-08-2020 | 01-07-2021 | 202103 | 31 |
1 | 01-08-2020 | 01-07-2021 | 202104 | 11 |
1 | 01-08-2020 | 01-07-2021 | 202105 | 0 |
1 | 01-08-2020 | 01-07-2021 | 202106 | 0 |
1 | 01-08-2020 | 01-07-2021 | 202107 | 0 |
2 | 01-02-2020 | 01-03-2020 | 202002 | 29 |
2 | 01-02-2020 | 01-03-2020 | 202003 | 1 |
2 | 01-09-2020 | 01-12-2020 | 202009 | 30 |
2 | 01-09-2020 | 01-12-2020 | 202010 | 31 |
2 | 01-09-2020 | 01-12-2020 | 202011 | 9 |
2 | 01-09-2020 | 01-12-2020 | 202012 | 0 |
英文:
I want to be able to count the number of days that have passed in the last year (arbitrarily set) based on multiple date intervals within a group. When 100 days have been counted, I would like it to stop counting, and then start again when the last year (365 or 366 days) have not had 100 days counted.
Example sample data (Sample data: https://dbfiddle.uk/SPVZol7L):
Group | StartDate | EndDate |
---|---|---|
1 | 01-01-2020 | 01-07-2020 |
1 | 01-08-2020 | 01-07-2021 |
2 | 01-02-2020 | 01-03-2020 |
2 | 01-09-2020 | 01-12-2020 |
For the first group there's been 100 days between 01-01-2020 and 09-04-2020. I would like to stop counting after that and start again on the 02-01-2021 from the next interval, where the last year (366 days, leap year) have not had 100 days counted.
I've tried cross joining a dimensional table containing all dates so I can easily count consecutive number of days also across multiple date intervals as shown above. But I do not know how to look back at the last 365 days and see, if 100 days have already been counted. It would be nice if I could output number of days counted next to each interval, or prefereably each month that has passed within each interval. Any help or tips would be much appreciated.
Expected result could look like this:
Group | StartDate | EndDate | YearMonth | CountDays |
---|---|---|---|---|
1 | 01-01-2020 | 01-07-2020 | 202001 | 31 |
1 | 01-01-2020 | 01-07-2020 | 202002 | 29 |
1 | 01-01-2020 | 01-07-2020 | 202003 | 31 |
1 | 01-01-2020 | 01-07-2020 | 202004 | 9 |
1 | 01-01-2020 | 01-07-2020 | 202005 | 0 |
1 | 01-01-2020 | 01-07-2020 | 202006 | 0 |
1 | 01-01-2020 | 01-07-2020 | 202007 | 0 |
1 | 01-08-2020 | 01-07-2021 | 202008 | 0 |
1 | 01-08-2020 | 01-07-2021 | 202009 | 0 |
1 | 01-08-2020 | 01-07-2021 | 202010 | 0 |
1 | 01-08-2020 | 01-07-2021 | 202011 | 0 |
1 | 01-08-2020 | 01-07-2021 | 202012 | 0 |
1 | 01-08-2020 | 01-07-2021 | 202101 | 30 |
1 | 01-08-2020 | 01-07-2021 | 202102 | 28 |
1 | 01-08-2020 | 01-07-2021 | 202103 | 31 |
1 | 01-08-2020 | 01-07-2021 | 202104 | 11 |
1 | 01-08-2020 | 01-07-2021 | 202105 | 0 |
1 | 01-08-2020 | 01-07-2021 | 202106 | 0 |
1 | 01-08-2020 | 01-07-2021 | 202107 | 0 |
2 | 01-02-2020 | 01-03-2020 | 202002 | 29 |
2 | 01-02-2020 | 01-03-2020 | 202003 | 1 |
2 | 01-09-2020 | 01-12-2020 | 202009 | 30 |
2 | 01-09-2020 | 01-12-2020 | 202010 | 31 |
2 | 01-09-2020 | 01-12-2020 | 202011 | 9 |
2 | 01-09-2020 | 01-12-2020 | 202012 | 0 |
答案1
得分: 2
SET dateformat dmy;
CREATE TABLE SampleData (
[Group] Varchar(20),
[StartDate] Date,
[EndDate] Date
);
INSERT INTO SampleData ([Group], [StartDate], [EndDate])
VALUES ('1', '01-01-2020', '01-07-2020'),
('1', '01-08-2020', '01-07-2021'),
('2', '01-02-2020', '01-03-2020'),
('2', '01-09-2020', '01-12-2020');
SELECT * FROM SampleData
DECLARE @MinDate DATE = (SELECT MIN(StartDate) FROM SampleData)
DECLARE @MaxDate DATE = (SELECT MAX(EndDate) FROM SampleData)
;WITH n AS
(
SELECT 0 AS n
UNION ALL
SELECT n + 1
FROM n
WHERE n + 1 <= DATEDIFF(DAY, @MinDate, @MaxDate)
)
SELECT DATEADD(DAY, n, @MinDate) AS Date
INTO #Calendar
FROM n
OPTION (MAXRECURSION 0)
CREATE TABLE #Result
(
Date DATE,
[Group] VARCHAR(20),
Counted INT,
CumulativeCount INT,
ResetDate DATE
)
DECLARE @Group VARCHAR(20), @Date DATE
DECLARE GroupCursor CURSOR FOR
SELECT DISTINCT [Group] FROM SampleData
OPEN GroupCursor
FETCH NEXT FROM GroupCursor INTO @Group
WHILE @@FETCH_STATUS = 0
BEGIN
DECLARE DateCursor CURSOR FOR
SELECT Date FROM #Calendar ORDER BY Date
OPEN DateCursor
FETCH NEXT FROM DateCursor INTO @Date
DECLARE @CumulativeCount INT = 0, @ResetDate DATE = NULL
WHILE @@FETCH_STATUS = 0
BEGIN
DECLARE @Counted INT =
(
SELECT CASE WHEN COUNT(*) > 0 THEN 1 ELSE 0 END
FROM SampleData
WHERE [Group] = @Group AND @Date BETWEEN StartDate AND EndDate
)
IF @Counted = 1 AND (@CumulativeCount = 0 OR DATEDIFF(DAY, @ResetDate, @Date) > 365)
BEGIN
SET @CumulativeCount = 1
SET @ResetDate = @Date
END
ELSE IF @Counted = 1 AND @CumulativeCount < 100
SET @CumulativeCount = @CumulativeCount + 1
ELSE IF @Counted = 1 AND @CumulativeCount >= 100
SET @Counted = 0
INSERT INTO #Result (Date, [Group], Counted, CumulativeCount, ResetDate)
VALUES (@Date, @Group, @Counted, @CumulativeCount, @ResetDate)
FETCH NEXT FROM DateCursor INTO @Date
END
CLOSE DateCursor
DEALLOCATE DateCursor
FETCH NEXT FROM GroupCursor INTO @Group
END
CLOSE GroupCursor
DEALLOCATE GroupCursor
SELECT
[Group],
FORMAT(Date, 'yyyyMM') AS YearMonth,
SUM(Counted) AS CountDays
FROM #Result
GROUP BY [Group], FORMAT(Date, 'yyyyMM')
ORDER BY [Group], YearMonth
英文:
SET dateformat dmy;
CREATE TABLE SampleData (
[Group] Varchar(20),
[StartDate] Date,
[EndDate] Date
);
INSERT INTO SampleData ([Group], [StartDate], [EndDate])
VALUES ('1', '01-01-2020', '01-07-2020'),
('1', '01-08-2020', '01-07-2021'),
('2', '01-02-2020', '01-03-2020'),
('2', '01-09-2020', '01-12-2020');
SELECT * FROM SampleData
Group | StartDate | EndDate |
---|---|---|
1 | 2020-01-01 | 2020-07-01 |
1 | 2020-08-01 | 2021-07-01 |
2 | 2020-02-01 | 2020-03-01 |
2 | 2020-09-01 | 2020-12-01 |
DECLARE @MinDate DATE = (SELECT MIN(StartDate) FROM SampleData)
DECLARE @MaxDate DATE = (SELECT MAX(EndDate) FROM SampleData)
;WITH n AS
(
SELECT 0 AS n
UNION ALL
SELECT n + 1
FROM n
WHERE n + 1 <= DATEDIFF(DAY, @MinDate, @MaxDate)
)
SELECT DATEADD(DAY, n, @MinDate) AS Date
INTO #Calendar
FROM n
OPTION (MAXRECURSION 0)
CREATE TABLE #Result
(
Date DATE,
[Group] VARCHAR(20),
Counted INT,
CumulativeCount INT,
ResetDate DATE
)
DECLARE @Group VARCHAR(20), @Date DATE
DECLARE GroupCursor CURSOR FOR
SELECT DISTINCT [Group] FROM SampleData
OPEN GroupCursor
FETCH NEXT FROM GroupCursor INTO @Group
WHILE @@FETCH_STATUS = 0
BEGIN
DECLARE DateCursor CURSOR FOR
SELECT Date FROM #Calendar ORDER BY Date
OPEN DateCursor
FETCH NEXT FROM DateCursor INTO @Date
DECLARE @CumulativeCount INT = 0, @ResetDate DATE = NULL
WHILE @@FETCH_STATUS = 0
BEGIN
DECLARE @Counted INT =
(
SELECT CASE WHEN COUNT(*) > 0 THEN 1 ELSE 0 END
FROM SampleData
WHERE [Group] = @Group AND @Date BETWEEN StartDate AND EndDate
)
IF @Counted = 1 AND (@CumulativeCount = 0 OR DATEDIFF(DAY, @ResetDate, @Date) > 365)
BEGIN
SET @CumulativeCount = 1
SET @ResetDate = @Date
END
ELSE IF @Counted = 1 AND @CumulativeCount < 100
SET @CumulativeCount = @CumulativeCount + 1
ELSE IF @Counted = 1 AND @CumulativeCount >= 100
SET @Counted = 0
INSERT INTO #Result (Date, [Group], Counted, CumulativeCount, ResetDate)
VALUES (@Date, @Group, @Counted, @CumulativeCount, @ResetDate)
FETCH NEXT FROM DateCursor INTO @Date
END
CLOSE DateCursor
DEALLOCATE DateCursor
FETCH NEXT FROM GroupCursor INTO @Group
END
CLOSE GroupCursor
DEALLOCATE GroupCursor
SELECT
[Group],
FORMAT(Date, 'yyyyMM') AS YearMonth,
SUM(Counted) AS CountDays
FROM #Result
GROUP BY [Group], FORMAT(Date, 'yyyyMM')
ORDER BY [Group], YearMonth
Group | YearMonth | CountDays |
---|---|---|
1 | 202001 | 31 |
1 | 202002 | 29 |
1 | 202003 | 31 |
1 | 202004 | 9 |
1 | 202005 | 0 |
1 | 202006 | 0 |
1 | 202007 | 0 |
1 | 202008 | 0 |
1 | 202009 | 0 |
1 | 202010 | 0 |
1 | 202011 | 0 |
1 | 202012 | 0 |
1 | 202101 | 31 |
1 | 202102 | 28 |
1 | 202103 | 31 |
1 | 202104 | 10 |
1 | 202105 | 0 |
1 | 202106 | 0 |
1 | 202107 | 0 |
2 | 202001 | 0 |
2 | 202002 | 29 |
2 | 202003 | 1 |
2 | 202004 | 0 |
2 | 202005 | 0 |
2 | 202006 | 0 |
2 | 202007 | 0 |
2 | 202008 | 0 |
2 | 202009 | 30 |
2 | 202010 | 31 |
2 | 202011 | 9 |
2 | 202012 | 0 |
2 | 202101 | 0 |
2 | 202102 | 0 |
2 | 202103 | 0 |
2 | 202104 | 0 |
2 | 202105 | 0 |
2 | 202106 | 0 |
2 | 202107 | 0 |
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论