Help needed in checking dates spans and status

  • dhanekulakalyan

    Default port

    Points: 1452

    Hello All,

    I have a requirement where we need to merge spans if the status is same for continues spans of a member. in the sample data below member 111 has continuedly having 3 status from 03/24/20 to 12/31/29 but in 2 lines in the raw data but it has to be merged because it has status 3 in both the lines but for member 222 continuedly there is no same status of a member. even though 5 and 4 status is repeating those are not for continues lines, so query should return 4 lines. for member 333 no change it should return one line.  Thanks in advance for your help and let me know if my explanation is not clear.

    IF OBJECT_ID('tempdb..#memberstatus_rawdata') IS NOT NULL BEGIN DROP TABLE #memberstatus_rawdata END

    create table #memberstatus_rawdata

    (

    ID varchar(25),

    Startdate date,

    enddate date,

    Status int)

    insert into #memberstatus_Rawdata

    values ('111', '2020-03-01', '2020-03-23', 4),

    ('111', '2020-03-24', '2020-03-26', 3),

    ('111', '2020-03-27', '2299-12-31', 3),

    ('222', '2020-01-01', '2020-01-08', 5),

    ('222', '2020-01-09', '2020-02-16', 4),

    ('222', '2020-02-17', '2020-02-19', 5),

    ('222', '2020-02-20', '2299-12-31', 4),

    ('333', '2020-01-01', '2299-12-31' , 4)

    --input data

    select * from #memberstatus_Rawdata

    IF OBJECT_ID('tempdb..#memberstatus_Output') IS NOT NULL BEGIN DROP TABLE #memberstatus_Output END

    create table #memberstatus_Output

    (

    ID varchar(25),

    Startdate date,

    enddate date,

    Status int)

    insert into #memberstatus_Output

    values ('111', '2020-03-01', '2020-03-23', 4),

    ('111', '2020-03-24', '2299-12-31', 3),

    ('222', '2020-01-01', '2020-01-08', 5),

    ('222', '2020-01-09', '2020-02-16', 4),

    ('222', '2020-02-17', '2020-02-19', 5),

    ('222', '2020-02-20', '2299-12-31', 4),

    ('333', '2020-01-01', '2299-12-31' , 4)

    --output data

    select * from #memberstatus_Output

    DROP TABLE #memberstatus_Rawdata

    DROP TABLE #memberstatus_Output

  • Steve Collins

    SSC Eights!

    Points: 889

    Isn't this the same question from 3 weeks ago?

    https://www.sqlservercentral.com/forums/topic/date-spans-for-continuoes-and-regular-dates-spans#post-3763906

    There were some good answers offered

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

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