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

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
    
    */

CodePudding user response：

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

CodePudding user response：

SELECT DISTINCT t.ID, -- distinct could be dropped
  CAST(CASE WHEN t.eStart > d.MM_start THEN  t.eStart  ELSE  d.MM_start END  AS DATE) eStart, 
  CASE WHEN t.eEnd <   d.MM_END   THEN  t.eEnd    ELSE  d.MM_END   END  eEnd, 
  t.Note,
  '....' [....], t.eStart eStartOrig, t.eEnd  eEndOrig

FROM #t t
JOIN #date_dim d     ON  ( d.CalDate  between  t.eStart  AND  t.eEND   AND d.dd  = 1     )  
                     OR  ( DAY(t.eStart) > 1  AND  d.CalDate  = t.eStart  AND  d.dd =  DAY(t.eStart)  )
                     
                     
                     
ID  eStart      eEnd        Note    ....                eStartOrig  eEndOrig
100 2022-03-02  2022-03-15  1 Same Month island ....    2022-03-02  2022-03-15
200 2022-03-01  2022-03-27  2 Same Month Start  ....    2022-03-01  2022-03-27
300 2022-03-08  2022-03-31  3 Same Month End    ....    2022-03-08  2022-03-31
440 2022-01-15  2022-01-31  4 Diff Month End    ....    2022-01-15  2022-02-28
440 2022-02-01  2022-02-28  4 Diff Month End    ....    2022-01-15  2022-02-28
550 2022-03-08  2022-03-31  5 Diff Month Island ....    2022-03-08  2022-05-10
550 2022-04-01  2022-04-30  5 Diff Month Island ....    2022-03-08  2022-05-10
550 2022-05-01  2022-05-10  5 Diff Month Island ....    2022-03-08  2022-05-10
660 2022-03-01  2022-03-31  6 Diff Month Start  ....    2022-03-01  2022-06-15
660 2022-04-01  2022-04-30  6 Diff Month Start  ....    2022-03-01  2022-06-15
660 2022-05-01  2022-05-31  6 Diff Month Start  ....    2022-03-01  2022-06-15
660 2022-06-01  2022-06-15  6 Diff Month Start  ....    2022-03-01  2022-06-15