Split dates based on Start and End Dates

  • Hi Team,

    I'm seeking help on date split, the dates can start between month also. below is the sample code and output. Thank you!

     

    Create Table #DATE_SPLIT
    (
    ID INT,
    StartDate DATE,
    EndDate DATE
    )

    INSERT INTO #DATE_SPLIT
    SELECT 10,'01/10/2020','03/21/2020'
    UNION ALL
    SELECT 10,'03/22/2020','12/31/9999'



    --OUTPUT
    ID StartDate EndDate
    10 '01/10/2020' '01/31/2020'
    10 '02/01/2020' '02/29/2020'
    10 '03/01/2020' '03/21/2020'
    10 '03/22/2020' '03/31/2020'
    10 '04/01/2020' '12/31/9999'

    • This topic was modified 1 year, 2 months ago by  koti.raavi. Reason: Updated code
  • drop table if exists #date_split;
    go
    Create Table #date_split
    (
    ID INT,
    StartDate DATE,
    EndDate DATE
    )
    go

    insert #date_split(ID, StartDate, EndDate) values
    (10,'01/10/2020','03/21/2020'),
    (10,'03/22/2020','12/31/9999');

    drop function if exists dbo.fnNextMonth;
    go
    create function dbo.fnNextMonth(
    @StartDate date,
    @EndDate date)
    returns table as
    return
    select iif(@EndDate='12/31/9999', dateadd(month, 1, datefromparts(year(@StartDate), month(@StartDate), 1)), @EndDate) NextMonth;
    go

    select
    ID,
    iif(ds.EndDate='12/31/9999',
    iif(f.n=0, ds.StartDate, fnm.NextMonth),
    iif(f.n=0, ds.StartDate, dateadd(month, f.n, datefromparts(year(ds.StartDate), month(ds.StartDate), 1)))) StartDate,
    iif(ds.EndDate='12/31/9999',
    iif(f.n=0, dateadd(day, -1, fnm.NextMonth), ds.EndDate),
    iif(f.n=datediff(month, ds.StartDate, fnm.NextMonth),
    ds.EndDate,
    dateadd(day, -1, dateadd(month, f.n+1, datefromparts(year(ds.StartDate), month(ds.StartDate), 1))))) EndDate
    from
    #date_split ds
    cross apply
    dbo.fnNextMonth(ds.StartDate, ds.EndDate) fnm
    cross apply
    dbo.fnTally(0, datediff(month, ds.StartDate, fnm.NextMonth)) f;

    Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

  • Awesome! Thank you so much!

  • One small update needed for this. sorry for updating requirement. While performing QA found it. Some member can have deceased populated , so loop should end based on deceased end. and other cases loop should till current date. Thanks!

     

    Create Table #DATE_SPLIT
    (
    ID INT,
    StartDate DATE,
    EndDate DATE,
    Deceased_Date DATE
    )

    INSERT INTO #DATE_SPLIT
    SELECT 10,'01/10/2020','03/21/2020',NULL
    UNION ALL
    SELECT 10,'03/22/2020','12/31/9999',NULL
    UNION ALL
    SELECT 20,'01/01/2020','02/21/2020','03/20/2020'
    UNION ALL
    SELECT 20,'02/22/2020','12/31/9999','03/20/2020'



    --OUTPUT
    ID StartDate EndDate Deceased_Date
    10 '01/10/2020' '01/31/2020' NULL
    10 '02/01/2020' '02/29/2020' NULL
    10 '03/01/2020' '03/21/2020' NULL
    10 '03/22/2020' '03/31/2020' NULL
    10 '04/01/2020' '04/30/2020' NULL
    10 '05/01/2020' '05/31/2020' NULL
    10 '06/01/2020' '06/30/2020' NULL
    10 '07/01/2020' '12/31/9999' NULL


    20 '01/10/2020' '01/31/2020' '03/20/2020'
    20 '02/01/2020' '02/21/2020' '03/20/2020'
    20 '02/22/2020' '02/29/2020' '03/20/2020'
    20 '03/01/2020' '03/20/2020' '03/20/2020' ---LOOP Has to end here, since member is decease. thanks!

    • This reply was modified 1 year, 2 months ago by  koti.raavi. Reason: updated
  • Set End_Date = ISNULL (Deceased_date, End_Date)

    _____________
    Code for TallyGenerator

  • It's been refactored into an fnSplitMonth function WITH SCHEMABINDING.  Then I did what Sergiy suggested 🙂

    /* updated */
    drop table if exists #date_split;
    go
    Create Table #date_split(
    ID INT,
    StartDate DATE,
    EndDate DATE,
    Deceased_Date DATE);
    go

    insert #date_split(ID, StartDate, EndDate, Deceased_Date) values
    (10, '01/10/2020', '03/21/2020', NULL),
    (10, '03/22/2020', '12/31/9999', null),
    (20, '01/01/2020', '02/21/2020', '03/20/2020'),
    (20, '02/22/2020', '12/31/9999', '03/20/2020');

    drop function if exists dbo.fnSplitMonth;
    go
    create function dbo.fnSplitMonth(
    @StartDate date,
    @EndDate date)
    returns table with schemabinding as
    return
    select
    iif(@EndDate='12/31/9999',
    iif(f.n=0, @StartDate, nxt.NextMonth),
    iif(f.n=0, @StartDate, dateadd(month, f.n-1, nxt.NextMonth))) SplitStartDate,
    iif(@EndDate='12/31/9999',
    iif(f.n=0, dateadd(day, -1, nxt.NextMonth), @EndDate),
    iif(f.n=datediff(month, @StartDate, @EndDate),
    @EndDate,
    dateadd(day, -1, dateadd(month, f.n, nxt.NextMonth)))) SplitEndDate
    from
    (select dateadd(month, 1, datefromparts(year(@StartDate), month(@StartDate), 1)) NextMonth) nxt
    cross apply
    dbo.fnTally(0, datediff(month, @StartDate, iif(@EndDate='12/31/9999', nxt.NextMonth, @EndDate))) f;
    go

    select ds.*, fsm.*
    from
    #date_split ds
    cross apply
    dbo.fnSplitMonth(ds.StartDate, isnull(ds.Deceased_Date, ds.EndDate)) fsm
    order by
    1, 2, 5;

    Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

  • There is a small glitch in data, please see attached screenshot. No 7 is not correct and 8,9 rows are dups if you take a look at split start and date.

    Capture

  • Ok, if the end date is less than the deceased date, then use the end date, else use the deceased date.

    select ds.*, fsm.*
    from
    #date_split ds
    cross apply
    dbo.fnSplitMonth(ds.StartDate, iif(ds.Deceased_Date is null, ds.EndDate, iif(ds.EndDate<ds.Deceased_Date, ds.EndDate, ds.Deceased_Date))) fsm
    order by
    1, 2, 5;

     

     

    Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

  • With a Calendar table in place it seems to be easier:

    select DS.*, C.Date, EOM,
    CASE WHEN ds.StartDate > C.Date then ds.StartDate ELSE c.Date END SplitBegin,
    case when EOM > GETDATE() then ds.EndDate
    when ds.EndDate > EOM Then EOM
    ELSE ds.EndDate end SplitEnd
    from (
    select ID, StartDate,
    case when Deceased_Date < EndDate then Deceased_Date else EndDate end EndDate
    from #date_split ) ds
    inner join (
    select Date, dateadd(dd, -1, dateadd(mm, 1, Date)) EOM
    FROM dbo.Calendar
    where DayOfMN = 1 and Date < GETDATE()
    ) C on C.Date >= dateadd(mm, datediff(mm, 0, ds.StartDate), 0)
    and C.Date < ds.EndDate

    _____________
    Code for TallyGenerator

  • Afaik the OP didn't state the end dates couldn't be in the future.  It seems safer to test against '9999-12-31'.  Still WITH SCHEMABINDING so no physical table.  Here's using the daterange function from this script:

    https://www.sqlservercentral.com/scripts/a-daterange-table-valued-function

    drop function if exists dbo.fnSplitMonth;
    go
    create function dbo.fnSplitMonth(
    @StartDate date,
    @EndDate date,
    @Deceased date=null)
    returns table with schemabinding as
    return
    with
    open_cte(SplitStartDate, SplitEndDate) as (
    select @StartDate, eomonth(@StartDate) where @EndDate='9999-12-31' and @Deceased is null
    union all
    select dateadd(day, 1, eomonth(@StartDate)), '9999-12-31' where @EndDate='9999-12-31' and @Deceased is null),
    closed_cte(SplitStartDate, SplitEndDate) as (
    select
    iif(dt.dt=@StartDate, @StartDate, dt.StartMonth),
    iif(dt.EndMonth>ce.CalcEndDate, ce.CalcEndDate, dt.EndMonth)
    from
    (select iif(@Deceased is null, @EndDate, iif(@EndDate<@Deceased, @EndDate, @Deceased)) CalcEndDate) ce
    cross apply
    (select
    cast(dr.[value] as date) dt,
    datefromparts(year(dr.[value]), month(dr.[value]), 1) StartMonth,
    eomonth(dr.[value]) EndMonth
    from dbo.daterange(@StartDate, ce.CalcEndDate, 'mm', 1) dr
    where not
    (@EndDate='9999-12-31' and @Deceased is null)) dt)
    select oc.SplitStartDate, oc.SplitEndDate from open_cte oc
    union all
    select cc.SplitStartDate, cc.SplitEndDate from closed_cte cc;
    go

    select ds.*, fsm.*
    from
    #date_split ds
    cross apply
    dbo.fnSplitMonth(ds.StartDate, ds.EndDate, ds.Deceased_Date) fsm
    order by
    1, 2, 5;

    Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

  • Steve Collins wrote:

    Afaik the OP didn't state the end dates couldn't be in the future.

    He kinda did. Right here

    Capture

    Any other explanation why the splitting ends on July and does not go month by month all the way to 9999-12-31?

    _____________
    Code for TallyGenerator

  • Hmmm... that's odd.  Maybe we're both right until the OP clarifies?!?

    When the OP first posted: Input

    SELECT 10,'03/22/2020','12/31/9999'

    Output:

    10 '03/22/2020' '03/31/2020'

    10 '04/01/2020' '12/31/9999'

    Then the second post breaks down the same data differently (as you've shown).  Then the 3rd post it was back to the way it was originally.   My code works with 1st and 3rd examples and not the 2nd.  Does it depend on when the report is run?  Maybe

    Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

Viewing 12 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply