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

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

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

  1. SET dateformat dmy;
  2. CREATE TABLE SampleData (
  3. [Group] Varchar(20),
  4. [StartDate] Date,
  5. [EndDate] Date
  6. );
  7. INSERT INTO SampleData ([Group], [StartDate], [EndDate])
  8. VALUES ('1', '01-01-2020', '01-07-2020'),
  9. ('1', '01-08-2020', '01-07-2021'),
  10. ('2', '01-02-2020', '01-03-2020'),
  11. ('2', '01-09-2020', '01-12-2020');
  12. SELECT * FROM SampleData
  13. DECLARE @MinDate DATE = (SELECT MIN(StartDate) FROM SampleData)
  14. DECLARE @MaxDate DATE = (SELECT MAX(EndDate) FROM SampleData)
  15. ;WITH n AS
  16. (
  17. SELECT 0 AS n
  18. UNION ALL
  19. SELECT n + 1
  20. FROM n
  21. WHERE n + 1 <= DATEDIFF(DAY, @MinDate, @MaxDate)
  22. )
  23. SELECT DATEADD(DAY, n, @MinDate) AS Date
  24. INTO #Calendar
  25. FROM n
  26. OPTION (MAXRECURSION 0)
  27. CREATE TABLE #Result
  28. (
  29. Date DATE,
  30. [Group] VARCHAR(20),
  31. Counted INT,
  32. CumulativeCount INT,
  33. ResetDate DATE
  34. )
  35. DECLARE @Group VARCHAR(20), @Date DATE
  36. DECLARE GroupCursor CURSOR FOR
  37. SELECT DISTINCT [Group] FROM SampleData
  38. OPEN GroupCursor
  39. FETCH NEXT FROM GroupCursor INTO @Group
  40. WHILE @@FETCH_STATUS = 0
  41. BEGIN
  42. DECLARE DateCursor CURSOR FOR
  43. SELECT Date FROM #Calendar ORDER BY Date
  44. OPEN DateCursor
  45. FETCH NEXT FROM DateCursor INTO @Date
  46. DECLARE @CumulativeCount INT = 0, @ResetDate DATE = NULL
  47. WHILE @@FETCH_STATUS = 0
  48. BEGIN
  49. DECLARE @Counted INT =
  50. (
  51. SELECT CASE WHEN COUNT(*) > 0 THEN 1 ELSE 0 END
  52. FROM SampleData
  53. WHERE [Group] = @Group AND @Date BETWEEN StartDate AND EndDate
  54. )
  55. IF @Counted = 1 AND (@CumulativeCount = 0 OR DATEDIFF(DAY, @ResetDate, @Date) > 365)
  56. BEGIN
  57. SET @CumulativeCount = 1
  58. SET @ResetDate = @Date
  59. END
  60. ELSE IF @Counted = 1 AND @CumulativeCount < 100
  61. SET @CumulativeCount = @CumulativeCount + 1
  62. ELSE IF @Counted = 1 AND @CumulativeCount >= 100
  63. SET @Counted = 0
  64. INSERT INTO #Result (Date, [Group], Counted, CumulativeCount, ResetDate)
  65. VALUES (@Date, @Group, @Counted, @CumulativeCount, @ResetDate)
  66. FETCH NEXT FROM DateCursor INTO @Date
  67. END
  68. CLOSE DateCursor
  69. DEALLOCATE DateCursor
  70. FETCH NEXT FROM GroupCursor INTO @Group
  71. END
  72. CLOSE GroupCursor
  73. DEALLOCATE GroupCursor
  74. SELECT
  75. [Group],
  76. FORMAT(Date, 'yyyyMM') AS YearMonth,
  77. SUM(Counted) AS CountDays
  78. FROM #Result
  79. GROUP BY [Group], FORMAT(Date, 'yyyyMM')
  80. ORDER BY [Group], YearMonth
英文:
  1. SET dateformat dmy;
  2. CREATE TABLE SampleData (
  3. [Group] Varchar(20),
  4. [StartDate] Date,
  5. [EndDate] Date
  6. );
  7. INSERT INTO SampleData ([Group], [StartDate], [EndDate])
  8. VALUES (&#39;1&#39;, &#39;01-01-2020&#39;, &#39;01-07-2020&#39;),
  9. (&#39;1&#39;, &#39;01-08-2020&#39;, &#39;01-07-2021&#39;),
  10. (&#39;2&#39;, &#39;01-02-2020&#39;, &#39;01-03-2020&#39;),
  11. (&#39;2&#39;, &#39;01-09-2020&#39;, &#39;01-12-2020&#39;);
  12. 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
  1. DECLARE @MinDate DATE = (SELECT MIN(StartDate) FROM SampleData)
  2. DECLARE @MaxDate DATE = (SELECT MAX(EndDate) FROM SampleData)
  3. ;WITH n AS
  4. (
  5. SELECT 0 AS n
  6. UNION ALL
  7. SELECT n + 1
  8. FROM n
  9. WHERE n + 1 &lt;= DATEDIFF(DAY, @MinDate, @MaxDate)
  10. )
  11. SELECT DATEADD(DAY, n, @MinDate) AS Date
  12. INTO #Calendar
  13. FROM n
  14. OPTION (MAXRECURSION 0)
  15. CREATE TABLE #Result
  16. (
  17. Date DATE,
  18. [Group] VARCHAR(20),
  19. Counted INT,
  20. CumulativeCount INT,
  21. ResetDate DATE
  22. )
  23. DECLARE @Group VARCHAR(20), @Date DATE
  24. DECLARE GroupCursor CURSOR FOR
  25. SELECT DISTINCT [Group] FROM SampleData
  26. OPEN GroupCursor
  27. FETCH NEXT FROM GroupCursor INTO @Group
  28. WHILE @@FETCH_STATUS = 0
  29. BEGIN
  30. DECLARE DateCursor CURSOR FOR
  31. SELECT Date FROM #Calendar ORDER BY Date
  32. OPEN DateCursor
  33. FETCH NEXT FROM DateCursor INTO @Date
  34. DECLARE @CumulativeCount INT = 0, @ResetDate DATE = NULL
  35. WHILE @@FETCH_STATUS = 0
  36. BEGIN
  37. DECLARE @Counted INT =
  38. (
  39. SELECT CASE WHEN COUNT(*) &gt; 0 THEN 1 ELSE 0 END
  40. FROM SampleData
  41. WHERE [Group] = @Group AND @Date BETWEEN StartDate AND EndDate
  42. )
  43. IF @Counted = 1 AND (@CumulativeCount = 0 OR DATEDIFF(DAY, @ResetDate, @Date) &gt; 365)
  44. BEGIN
  45. SET @CumulativeCount = 1
  46. SET @ResetDate = @Date
  47. END
  48. ELSE IF @Counted = 1 AND @CumulativeCount &lt; 100
  49. SET @CumulativeCount = @CumulativeCount + 1
  50. ELSE IF @Counted = 1 AND @CumulativeCount &gt;= 100
  51. SET @Counted = 0
  52. INSERT INTO #Result (Date, [Group], Counted, CumulativeCount, ResetDate)
  53. VALUES (@Date, @Group, @Counted, @CumulativeCount, @ResetDate)
  54. FETCH NEXT FROM DateCursor INTO @Date
  55. END
  56. CLOSE DateCursor
  57. DEALLOCATE DateCursor
  58. FETCH NEXT FROM GroupCursor INTO @Group
  59. END
  60. CLOSE GroupCursor
  61. DEALLOCATE GroupCursor
  62. SELECT
  63. [Group],
  64. FORMAT(Date, &#39;yyyyMM&#39;) AS YearMonth,
  65. SUM(Counted) AS CountDays
  66. FROM #Result
  67. GROUP BY [Group], FORMAT(Date, &#39;yyyyMM&#39;)
  68. 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:

确定