Odtnhj

I have scenario where I need to split the given date range into monthly intervals.

For example, the input is like below:

StartDate EndDate
2018-01-21 2018-01-29
2018-01-30 2018-02-23
2018-02-24 2018-03-31
2018-04-01 2018-08-16
2018-08-17 2018-12-31

And the expected output should be like below:

StartDate EndDate
2018-01-21 2018-01-29
2018-01-30 2018-01-31
2018-02-01 2018-02-23
2018-02-24 2018-02-28
2018-03-01 2018-03-31
2018-04-01 2018-04-30
2018-05-01 2018-05-31
2018-06-01 2018-06-30
2018-07-01 2018-07-31
2018-08-01 2018-08-16
2018-08-17 2018-08-31
2018-09-01 2018-09-30
2018-10-01 2018-10-31
2018-11-01 2018-11-30
2018-12-01 2018-12-31

Below is the sample data.

CREATE TABLE #Dates
(
 StartDate DATE,
 EndDate DATE
);


INSERT INTO #Dates
(
 StartDate,
 EndDate
)
VALUES
('2018-01-21', '2018-01-29'),
('2018-01-30', '2018-02-23'),
('2018-02-24', '2018-03-31'),
('2018-04-01', '2018-08-16'),
('2018-08-17', '2018-12-31');

You can use a recursive CTE. The basic idea is to start with the first date 2018-01-21 and build a list of all months' start and end date upto the last date 2018-12-31. Then inner join with your data and clamp the dates if necessary.

DECLARE @Dates TABLE (StartDate DATE, EndDate DATE);
INSERT INTO @Dates (StartDate, EndDate) VALUES
('2018-01-21', '2018-01-29'),
('2018-01-30', '2018-02-23'),
('2018-02-24', '2018-03-31'),
('2018-04-01', '2018-08-16'),
('2018-08-17', '2018-12-31');

WITH minmax AS (
 -- clamp min(start date) to 1st day of that month
 SELECT DATEADD(MONTH, DATEDIFF(MONTH, CAST('00010101' AS DATE), MIN(StartDate)), CAST('00010101' AS DATE)) AS mindate, MAX(EndDate) AS maxdate
 FROM @Dates
), months AS (
 -- calculate first and last day of each month
 -- e.g. for February 2018 it'll return 2018-02-01 and 2018-02-28
 SELECT mindate AS date01, DATEADD(DAY, -1, DATEADD(MONTH, 1, mindate)) AS date31, maxdate
 FROM minmax
 UNION ALL
 SELECT DATEADD(MONTH, 1, prev.date01), DATEADD(DAY, -1, DATEADD(MONTH, 2, prev.date01)), maxdate
 FROM months AS prev
 WHERE prev.date31 < maxdate
)
SELECT
 -- clamp start and end date to first and last day of corresponding month
 CASE WHEN StartDate < date01 THEN date01 ELSE StartDate END,
 CASE WHEN EndDate > date31 THEN date31 ELSE EndDate END
FROM months
INNER JOIN @Dates ON date31 >= StartDate AND EndDate >= date01

If rCTE is not an option you can always JOIN with a table of numbers or table of dates (the idea above still applies).

You can Cross Apply with the Master..spt_values table to get a row for each month between StartDate and EndDate.

SELECT * 
into #dates
FROM (values 
('2018-01-21', '2018-01-29')
,('2018-01-30', '2018-02-23')
,('2018-02-24', '2018-03-31')
,('2018-04-01', '2018-08-16')
,('2018-08-17', '2018-12-31')
)d(StartDate , EndDate)



SELECT
 SplitStart as StartDate 
 ,case when enddate < SplitEnd then enddate else SplitEnd end as EndDate
FROM #dates d
cross apply (
 SELECT 
 cast(dateadd(mm, number, dateadd(dd, (-datepart(dd, d.startdate) +1) * isnull((number / nullif(number, 0)), 0), d.startdate)) as date) as SplitStart
 ,cast(dateadd(dd, -datepart(dd, dateadd(mm, number+1, startdate)), dateadd(mm, number+1, startdate)) as date) as SplitEnd
 FROM 
 master..spt_values 
 where type = 'p' 
 and number between 0 and (((year(enddate) - year(startdate)) * 12) + month(enddate) - month(startdate)) 
) s

drop table #dates

The following should also work

1. First i put startdates and enddates into a single column in the cte-block data.


2. In the block som_eom, i create the start_of_month and end_of_month for all 12 months.


3. I union steps 1 and 2 into curated_set


4. I create curated_set which is ordered by the date column


5. Finally i reject the unwanted records, in my filter clause not in('som','StartDate')

with data
 as (select *
 from dates
 unpivot(x for y in(startdate,enddate))t
 )
 ,som_eom
 as (select top 12
 cast('2018-'+cast(row_number() over(order by (select null)) as varchar(2))+'-01' as date) as som
 ,dateadd(dd
 ,-1
 ,dateadd(mm
 ,1
 ,cast('2018-'+cast(row_number() over(order by (select null)) as varchar(2))+'-01' as date)
 )
 ) as eom
 from information_schema.tables
 )
 ,curated_set
 as(select *
 from data
 union all
 select *
 from som_eom
 unpivot(x for y in(som,eom))t
 )
 ,curated_data
 as(select x
 ,y
 ,lag(x) over(order by x) as prev_val
 from curated_set
 )
select prev_val as st_dt,x as end_dt
 ,y 
 from curated_Data
where y not in('som','StartDate')

Start with the initial StartDate and calculate the end of month or simply use the EndDate if it's within the same month.
Use the newly calculated EndDate+1 as StartDate for recursion and repeat the calculation.

WITH cte AS 
 ( SELECT StartDate, -- initial start date
 CASE WHEN EndDate < DATEADD(DAY,-1,DATEADD(MONTH, DATEDIFF(MONTH,0,StartDate)+1,0))
 THEN EndDate
 ELSE DATEADD(DAY,-1,DATEADD(MONTH, DATEDIFF(MONTH,0,StartDate)+1,0))
 END AS newEnd, -- LEAST(end of current month, EndDate)
 EndDate
 FROM #Dates

 UNION ALL

 SELECT dateadd(DAY,1,newEnd), -- previous end + 1 day, i.e. 1st of current month
 CASE WHEN EndDate <= DATEADD(DAY,-1,DATEADD(MONTH, DATEDIFF(MONTH,0,StartDate)+2,0)) 
 THEN EndDate 
 ELSE DATEADD(DAY,-1,DATEADD(MONTH, DATEDIFF(MONTH,0,StartDate)+2,0)) 
 END, -- LEAST(end of next month, EndDate)
 EndDate
 FROM cte
 WHERE newEnd < EndDate 
 )
SELECT StartDate, newEnd 
FROM cte