Search code examples
sqlsql-serverdatet-sqlsql-server-2016

TSQL - Query a list of dates between two date fields

Data:

DECLARE @Dates TABLE
    (
        [MyDate]  DATE
      , [WkStart] DATE
      , [WkEnd]   DATE
    ) ;

INSERT INTO @Dates
SELECT  '2021-10-03'
      , '2021-09-27'
      , '2021-10-03'
UNION
SELECT  '2021-10-21'
      , '2021-10-18'
      , '2021-10-24'
UNION ALL
SELECT  '2021-10-23'
      , '2021-10-18'
      , '2021-10-24'
UNION
SELECT  '2021-10-27'
      , '2021-10-25'
      , '2021-10-31' ;

Goal:

To output 2 fields. 1st = a date field and 2nd = a bit field. Date field will have all the dates between [WkStart] and [WkEnd] for each record. Bit field will be true when [MyDate] equals the value of the first field. The table is quite large and so performance matters a lot. When 2+ [MyDate] values belong to the same week range, the Dates for the week should not repeat.

Expected output:

enter image description here

My try:

; WITH recrCTE AS
    (
        SELECT  CAST ( [WkStart] AS DATETIME ) AS [DateVal]
              , [WkEnd]
              , [MyDate]
        FROM    @Dates
        UNION ALL
        SELECT  [DateVal] + 1
              , [WkEnd]
              , [MyDate]
        FROM    recrCTE
        WHERE   [DateVal] + 1 <= [WkEnd]
    )
SELECT  [DateVal]
      , IIF ( [MyDate] = [DateVal], 1, 0 ) AS [isMyDate]
FROM    recrCTE
ORDER BY [DateVal]
OPTION ( MAXRECURSION 0 ) ;

Current output:

enter image description here

Two obvious issues with this solution. 1st, records repeat for 2+ dates that fall within same date range (21st Oct and 23rd Oct). 2nd, both of those dates repeat with 2 different bit values so can't simply use DISTINCT. 3rd that I feel might become an issue is performance. Maybe there's a more efficient way to achieve this (using a function perhaps) instead of using a recursive CTE.

Solution

  • You don't need recursion when there can only be 7 days in a week; just hard-code the 7 values 1-7 so you can use those to explode the set from WkStart to 6 days later. Then you can conditionally aggregate that output on DateVal.

    ;WITH alldays AS 
(
  SELECT DateVal = DATEADD(DAY, days.n-1, d.WkStart), 
         d.MyDate
  FROM @Dates AS d
  CROSS JOIN (VALUES(1),(2),(3),(4),(5),(6),(7)) AS days(n)
  -- if the table is large and performance is a concern, then:
  -- WHERE d.? -- some reasonable where clause belongs here?
)
SELECT DateVal, 
       IsMyDate = MAX(CASE MyDate WHEN DateVal THEN 1 ELSE 0 END)
FROM alldays 
GROUP BY DateVal
ORDER BY DateVal;