I am trying to split a specific date range into chunks of 3 days to find the number of records per each three day chunks.

For example, assume I have this table:

And I want to produce this table:

The reason I ended it off there is because the remaining days are not enough to make up 3 days.

CodePudding user response：

You need in something like

WITH RECURSIVE
cte AS ( 
    SELECT User, 
           MinimumDate, 
           MinimumDate   INTERVAL 2 DAY MaximumDate,  -- N-1
           MaximumDate FinalDate
    FROM sourcetable
    WHERE MinimumDate   INTERVAL 2 DAY <= MaximumDate -- N-1
  UNION ALL
    SELECT User,
           MinimumDate   INTERVAL 3 DAY,              -- N
           MinimumDate   INTERVAL 5 DAY,              -- 2*N-1
           FinalDate
    FROM cte
    WHERE MinimumDate   INTERVAL 5 DAY <= FinalDate   -- 2*N-1
)
SELECT User,
       MinimumDate,
       MaximumDate
FROM cte
ORDER BY 1, 2;

or (not tested)

WITH RECURSIVE
cte1 AS ( SELECT (MAX(DATEDIFF(MaximumDate, MinimumDate)) DIV 3) - 1 maxrange
          FROM sourcetable ),
cte2 AS ( SELECT 0 num
          UNION ALL
          SELECT num   1 FROM cte2 WHERE num < maxrange )
SELECT User,
       MinimumDate   INTERVAL 3 * num DAY MinimumDate,
       MinimumDate   INTERVAL 2   3 * num DAY MaximumDate 
FROM sourcetable
JOIN cte2 ON MinimumDate   INTERVAL 3 * num DAY <= MaximumDate
ORDER by 1,2