Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Splitting dates into intervals using Start Date and End Date

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');
like image 488
kkr Avatar asked Nov 15 '18 07:11

kkr


People also ask

Does datediff include start and end?

The DATEDIFF() function returns an integer value that represents the difference between the start date and end date, with the date part as the unit. If the result is out of range for the integer (-2,147,483,647), the DATEDIFF() function returns an error.

How do I separate the start date and end date in SQL?

declare @StartDate date = '20170401' , @EndDate date = '20170731'; ;with Months as ( select top (datediff(month, @startdate, @enddate) + 1) [Month] = dateadd(month, row_number() over (order by number) -1, @StartDate), MonthEnd = dateadd(day,-1,dateadd(month, row_number() over (order by number), @StartDate)) from master ...

How do you bifurcate date and time in SQL?

Another way: SELECT TOP (10) [CUSTOMERID], [DATEPART] = CONVERT(date, [ENTRYTRIPDATETIME]), [TIMEPART] = CONVERT(time, [ENTRYTRIPDATETIME]) FROM [ISSUER].


2 Answers

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).

like image 51
Salman A Avatar answered Sep 21 '22 05:09

Salman A


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
like image 45
wnutt Avatar answered Sep 22 '22 05:09

wnutt