计算从多个日期区间开始的最后一年内过去的天数,直到达到一个限制。

huangapple go评论63阅读模式
英文:

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 (&#39;1&#39;, &#39;01-01-2020&#39;, &#39;01-07-2020&#39;), 
(&#39;1&#39;, &#39;01-08-2020&#39;, &#39;01-07-2021&#39;), 
(&#39;2&#39;, &#39;01-02-2020&#39;, &#39;01-03-2020&#39;), 
(&#39;2&#39;, &#39;01-09-2020&#39;, &#39;01-12-2020&#39;);
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 &lt;= 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(*) &gt; 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) &gt; 365) 
BEGIN
SET @CumulativeCount = 1
SET @ResetDate = @Date
END
ELSE IF @Counted = 1 AND @CumulativeCount &lt; 100
SET @CumulativeCount = @CumulativeCount + 1
ELSE IF @Counted = 1 AND @CumulativeCount &gt;= 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, &#39;yyyyMM&#39;) AS YearMonth,
SUM(Counted) AS CountDays
FROM #Result
GROUP BY [Group], FORMAT(Date, &#39;yyyyMM&#39;)
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

fiddle

huangapple
  • 本文由 发表于 2023年7月24日 14:54:36
  • 转载请务必保留本文链接:https://go.coder-hub.com/76752045.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定