TSQL, break period by Month only for given year-CodePudding

I'm trying to break membership periods by month, but only for given year, in our example it 2022 Self inclusive code below works fine but it breaks all period, I only trying to process by month for 2022. Code below also holds definition for test data inside (#t temp table). I'm using #numbers table as a tally. I'm using SQL Server 2017

/*  
SELECT TOP 100  ROW_NUMBER() OVER(ORDER BY (SELECT NULL))-1 AS n     
INTO #numbers     FROM   master.dbo.spt_values  --- Select top 100 * from #t numbers
  -----    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 , 100 Idx
    UNION SELECT 200,  '2022-01-11' ,   '2022-03-27', '2 Same Month Start 1', 2001
    UNION SELECT 200,  '2022-02-15' ,   '2022-5-15', '2 Same Month Start 2', 2002
    UNION SELECT 300,  '2021-05-08' ,   '2024-12-31', '3 Future eEnd ' ,3000
    UNION SELECT 440,  '2022-10-15' ,   '2023-10-10', '4 Future end 2' ,4400 
) b      --    SELECT * FROM #t
*/

select DISTINCT t.ID,
  CASE WHEN YEAR(DATEADD(MONTH, DATEDIFF(MONTH, 0, t.eStart)   n, 0)) < 2022 THEN eStart
       WHEN YEAR(DATEADD(MONTH, DATEDIFF(MONTH, 0, t.eStart)   n, 0)) > 2022 THEN '1/1/2023'  -- first day after 2022  
       ELSE  CASE WHEN n = 0  then t.eStart   ELSE   dateadd(month, datediff(month, 0, t.eStart)   n, 0)   END 
                                                                          END  eStart,  
  case when n = datediff(month, eStart, eEnd)   then t.eEnd
       else eomonth(dateadd(month, datediff(month, 0, t.eStart)   n, 0))  END eEnd 
    --   ,n, note ,eStart eStartOrig, eEnd eEndOrig
from   #t t
join #numbers n on n.n <= datediff(month, eStart, eEnd)
order by t.ID, eStart

CodePudding user response：

My suggested approach would be to build a "months" table for the desired range, join with your membership data, and keep pairs with overlapping date ranges. From there you can adjust the start and end dates to be the more restrictive between the actual mempership period and the selected month.

When comparing date ranges for overlap, a standard comparison is Start1 < End2 AND Start2 < End1 when dealing with exclusive end-dates or date/times. When dealing with inclusive end dates (as is the case here), the comparison becomes Start1 <= End2 AND Start2 <= End1

The resulting logic would then be something like:

-- Membership data #t as defined in original post

SELECT
    A.MonthStart,
    EOMONTH(A.MonthStart) AS MonthEnd
INTO #months
FROM (VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10), (11), (12)) N(N)
CROSS APPLY (SELECT DATEFROMPARTS(2022, N.N, 1) AS MonthStart) A

SELECT * from #months

SELECT
    t.ID,
    CASE WHEN m.MonthStart >= t.eStart THEN m.MonthStart ELSE t.eStart END AS eStart,
    CASE WHEN m.MonthEnd <= t.eEnd THEN m.MonthEnd ELSE t.eEnd END AS eEnd,
    t.Idx
FROM #t t
JOIN #months m -- Overlapping months (inclusive dates)
    ON m.MonthStart <= t.eEnd 
    AND t.eStart <= m.MonthEnd 
ORDER BY t.ID, t.Idx, eStart -- Added t.Idx to better match desired results

See this db<>fiddle for a demo.

If you want to include prior and following membership date ranges, you can include extra rows in the #months table such as:

1900-01-01 to 2022-12-31
2024-01-01 to 12-31-9999

or specific full-year ranges if that is what is wanted.

CodePudding user response：

select DISTINCT t.ID,
  CASE WHEN YEAR(DATEADD(MONTH, DATEDIFF(MONTH, 0, t.eStart)   n, 0)) < 2022 THEN eStart
       WHEN YEAR(DATEADD(MONTH, DATEDIFF(MONTH, 0, t.eStart)   n, 0)) > 2022 THEN '1/1/2023'  -- first day after 2022  
       ELSE  CASE WHEN n = 0  then t.eStart   ELSE   dateadd(month, datediff(month, 0, t.eStart)   n, 0)   END 
                                                                          END  eStart,  
 CASE WHEN YEAR(         
              CASE WHEN n = DATEDIFF(MONTH, t.eStart, t.eEnd)  THEN t.eEnd  ELSE EOMONTH(DATEADD(MONTH, DATEDIFF(MONTH, 0, t.eStart)   n, 0))    END) < 2022
              THEN CASE WHEN t.eEnd < '1/1/2022'  THEN t.eEnd  ELSE DATEADD(d,-1,'1/1/2022') END
          WHEN YEAR(
              CASE WHEN n = DATEDIFF(MONTH, t.eStart, t.eEnd)  THEN t.eEnd  ELSE EOMONTH(DATEADD(MONTH, DATEDIFF(MONTH, 0, t.eStart)   n, 0))    END) > 2022
              THEN t.eEnd
          ELSE
              CASE WHEN n = DATEDIFF(MONTH, t.eStart, t.eEnd)  THEN t.eEnd  ELSE EOMONTH(DATEADD(MONTH, DATEDIFF(MONTH, 0, t.eStart)   n, 0))    END        
 END                                     eEnd  

from   #t t
join #numbers n on n.n <= datediff(month, eStart, eEnd)