Search code examples
datet-sqlmonthcalendar

Break monthly data by complete month and keep not complete month intact in one step?

I need to break monthly membership data by single complete month and it looked simple, but then I found that there are not complete month segments in different flavors so it became more complex.
Do you think it's possible to achieve in single step (without breaking input by complete/non complete month ) ? I tried and looks like in this case I need to modify eStart/eEnd dates which I don't want to deal. Trying to keep input intact.

Below is my self inclusive script setup, input and desired output. Current code does only job for complete month, do you think it's possible to include also all head and tails ??.

        --- SQL Server 2019
        SELECT DISTINCT t.*, '--' f, d.*
    
    FROM #t t
    JOIN #date_dim d     ON    d.CalDate  BETWEEN eStart AND eEnd
        AND d.dd = 1  
    JOIN #date_dim d2    ON    d2.CalDate BETWEEN eStart AND eEnd
        AND d2.dd = d2.mm_Last_DD 
    
    
    /*  -----    data prep part
    SELECT * INTO #t  FROM (    --   DROP TABLE IF EXISTS  #t
              SELECT 100 ID,  CAST('2022-03-02' AS DATE) eStart ,  CAST('2022-03-15' AS DATE) eEnd, '1 Same Month island' note  
        UNION SELECT 200,  '2022-03-01' ,   '2022-03-27', '2 Same Month Start'
        UNION SELECT 300,  '2022-03-08' ,   '2022-03-31', '3 Same Month End' 
    
        UNION SELECT 440,  '2022-01-15' ,   '2022-02-28', '4 Diff Month End'  
        UNION SELECT 550,  '2022-03-08' ,   '2022-05-10', '5 Diff Month Island'  
        UNION SELECT 660,  '2022-03-1'  ,   '2022-6-15',  '6 Diff Month Start' 
    ) b      --    SELECT * FROM #t
    
    ;WITH cte AS (    --DROP TABLE IF EXISTS  #date_dim
      SELECT TOP 180  
      CAST('1/1/2022' AS DATETIME) + ROW_NUMBER() OVER(ORDER BY  number) CalDate 
      FROM  master..spt_values  )
    
      SELECT CalDate
      , MONTH(Caldate)  MM,   DATEADD(dd, -( DAY( Caldate ) -1 ), Caldate) MM_start,  EOMONTH(Caldate) MM_End, day(Caldate) dd, DAY(EOMONTH(Caldate)) mm_Last_DD
, CONVERT(nvarchar(6), Caldate, 112) YYYYMM, YEAR(CalDate) YYYY 
  ,CASE WHEN CalDate =  EOMONTH(Caldate) THEN 'Y' ELSE 'N' END month_End_YN
      INTO #date_dim       ----         SELECT * FROM #date_dim
      FROM cte
    
    */

enter image description here

Solution

  • One way is to use recursive query

    -- recursive query way
with rcte as
(
  select ID, 
         eStart, 
         eEnd = case when eEnd < eomonth(eStart) 
                     then eEnd 
                     else eomonth(eStart) 
                     end, 
         fEnd = eEnd,
         note
  from   #t

  union all

  select t.ID, 
         eStart = dateadd(day, 1, r.eEnd),
         eEnd   = case when fEnd < eomonth(dateadd(day, 1, r.eEnd)) 
                       then fEnd 
                       else eomonth(dateadd(day, 1, r.eEnd)) 
                       end,
         fEnd,
         r.note
  from   #t t
         inner join rcte r on t.ID = r.ID
  where  r.fEnd > r.eEnd
)
select ID, eStart, eEnd, note
from   rcte
order by ID, eStart

    Another is to use a number / tally table.

    -- tally table way
with numbers as  -- using recursive cte to create a number table
(
  select n = 0
  union all
  select n = n + 1
  from   numbers
  where  n < 99
)
select t.ID,
       eStart = case when n = 0 
                     then t.eStart 
                     else convert(date, dateadd(month, datediff(month, 0, t.eStart) + n, 0))
                     end,
       eEnd    = case when n = datediff(month, eStart, eEnd)
                     then t.eEnd
                     else eomonth(dateadd(month, datediff(month, 0, t.eStart) + n, 0))
                     end,
       note
from   #t t
       inner join numbers n on n.n <= datediff(month, eStart, eEnd)
order by t.ID, eStart

    db<>fiddle demo