Filling dates between the status changes

  • sql_novice_2007

    Hall of Fame

    Points: 3307

    Hi ,

     

    I have a table with a sample data like this.

     

     

    CREATE TABLE EMployeeStatus(

    EMployeeID Int,

    TranDate datetime,

    OldStatus varchar(50),

    NewStatus varchar(50)

    )

    INSERT INTO EMployeeStatus(EMployeeID , TranDate , OldStatus , NewStatus ) VALUES( 12345657, '07/11/2018', 'SUSPENDED', 'ACTIVE')

    INSERT INTO EMployeeStatus(EMployeeID , TranDate , OldStatus , NewStatus ) VALUES( 12345657, '7/18/2018', 'SUSPENDED', ' TERMINATED')

    INSERT INTO EMployeeStatus(EMployeeID , TranDate , OldStatus , NewStatus ) VALUES( 12345657, '7/25/2018', 'TERMINARED', 'ACTIVE')

    INSERT INTO EMployeeStatus(EMployeeID , TranDate , OldStatus , NewStatus ) VALUES( 12345657, '7/29/2018', 'ACTIVE' , ' HOLD')

    INSERT INTO EMployeeStatus(EMployeeID , TranDate , OldStatus , NewStatus ) VALUES( 12345657, '8/2/2018', 'HOLD', 'TERMINATED')

     

    I need to generate data like the following.

     

    EMployeeID    TranDate   Status

    12345657       07/11/2018    ACTIVE

    12345657      07/12/2018     ACTIVE

    12345657      07/13/2018     ACTIVE

    12345657      07/14/2018     ACTIVE

    12345657      07/15/2018     ACTIVE

    12345657      07/16/2018     ACTIVE

    12345657     07/17/2018      ACTIVE

    12345657     07/18/2018      TERMINATED

    12345657     07/19/2018      TERMINATED

    12345657     07/20/2018     TERMINATED

    12345657     07/21/2018      TERMINATED

    12345657     07/22/2018     TERMINATED

    12345657     07/23/2018     TERMINATED

    12345657     07/24/2018    TERMINATED

    12345657     07/25/2018    ACTIVE

    12345657     07/26/2018    ACTIVE

    12345657     07/27/2018    ACTIVE

    12345657     07/28/2018    ACTIVE

    12345657     07/29/2018    HOLD

    12345657     07/30/2018    HOLD

    12345657     07/31/2018     HOLD

    12345657    08/01/2018     HOLD

    12345657    08/02/2018     TERMINATED

     

     

    Thanks.

  • pietlinden

    SSC Guru

    Points: 62735

    Thought I had it...but I'm missing a date (because the LAG/LEAD is cutting it off). Anyway, here's the code:

    SELECT *
    FROM
     (SELECT EmployeeID
      , TranDate
      , DATEADD(day,-1,LEAD(TranDate,1) OVER (PARTITION BY EmployeeID ORDER BY TranDate)) AS NextDate
      , NewStatus
     FROM EMployeeStatus) es
    INNER JOIN Calendar2 c
     ON c.TheDate >= es.TranDate AND c.TheDate <= es.NextDate
    ORDER BY es.EmployeeID,
      c.TheDate;
  • scdecade

    Say Hey Kid

    Points: 709

    drop table if exists #EMployeeStatus;
    go
    create table #EMployeeStatus(
    EMployeeID Int,
    TranDate datetime,
    OldStatus varchar(50),
    NewStatus varchar(50));
    go

    insert #EMployeeStatus(EMployeeID, TranDate, OldStatus, NewStatus) values
    (12345657, '07/11/2018', 'SUSPENDED', 'ACTIVE'),
    (12345657, '7/18/2018', 'SUSPENDED', 'TERMINATED'),
    (12345657, '7/25/2018', 'TERMINARED', 'ACTIVE'),
    (12345657, '7/29/2018', 'ACTIVE' , 'HOLD'),
    (12345657, '8/2/2018', 'HOLD', 'TERMINATED');

    with
    range_cte(EMployeeID, TranDate, NewStatus, dt_diff) as (
    select
    EMployeeID, TranDate, NewStatus,
    isnull(datediff(day, TranDate, lead(TranDate,1) over (partition by EmployeeID order by TranDate))-1,0)
    from
    #EMployeeStatus)
    select
    rc.EMployeeID, cast(d.[value] as date) TranDate, rc.NewStatus
    from
    range_cte rc
    cross apply
    dbo.daterange(rc.TranDate, dateadd(day, rc.dt_diff, rc.TranDate), 'dd', 1) d
    order by
    EMployeeID, TranDate;

    The dbo.daterange function is from:

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

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

  • ktflash

    Ten Centuries

    Points: 1122

    scdecade wrote:

    with
    range_cte(EMployeeID, TranDate, NewStatus, dt_diff) as (
    select
    EMployeeID, TranDate, NewStatus,
    isnull(datediff(day, TranDate, lead(TranDate,1) over (partition by EmployeeID order by TranDate))-1,0)
    from
    #EMployeeStatus)
    select
    rc.EMployeeID, cast(d.[value] as date) TranDate, rc.NewStatus
    from
    range_cte rc
    cross apply
    dbo.daterange(rc.TranDate, dateadd(day, rc.dt_diff, rc.TranDate), 'dd', 1) d
    order by
    EMployeeID, TranDate;

    What is the necessity of dateadd/datediff functions here?

    Lead gets you the next date and from what ive seen in the daterange post, it accepts 2 dates aswell.

    So why not use the lead date itself instead of the dt_diff in the cte and instead of the dateaddd in the cross apply?

    I guess i am missing something?

    That daterange function is lovely never seen it.

    • This reply was modified 1 week, 3 days ago by  ktflash.

    I wanna be the very best
    Like no one ever was

  • scdecade

    Say Hey Kid

    Points: 709

    ktflash wrote:

    What is the necessity of dateadd/datediff functions here?

    Lead gets you the next date and from what ive seen in the daterange post, it accepts 2 dates aswell.

    So why not use the lead date itself instead of the dt_diff in the cte and instead of the dateaddd in the cross apply?

    I guess i am missing something?

    That daterange function is lovely never seen it.

    Imo the daterange function is truly sublime.  Literally, it is the ability to conjure up a calendar of any granularity nearly instantly.   Just cross apply and voila!!  Ad hoc at least once a week and it's running nonstop in production now too.  Big thanks as always to Jonathan Roberts for his very excellent offering!

    If you execute this:

    select cast([value] as date) day_dt from dbo.daterange('2020-03-22', '2020-03-25', 'dd', 1);

    The result is this:

    day_dt
    2020-03-22
    2020-03-23
    2020-03-24
    2020-03-25

    The daterange function generates rows of date values between the start and end dates for a given granularity and interval (in this case 1 day).

    For the OP question the essential task is to calculate the number of days which need to be added to the end of the TranDates.  Once that's calculated the daterange function can generate the rows.

    If the cte is executed but with only 2 columns (TranDate and CalcDateDiff):

    select
    TranDate,
    isnull(datediff(day, TranDate, lead(TranDate,1) over (partition by EmployeeID order by TranDate))-1,0) CalcDateDiff
    from
    #EMployeeStatus;

    The result:

    TranDate	CalcDateDiff
    2018-07-11 00:00:00.000 6
    2018-07-18 00:00:00.000 6
    2018-07-25 00:00:00.000 3
    2018-07-29 00:00:00.000 3
    2018-08-02 00:00:00.000 0

    The TranDate is the start date needed for the daterange function.  The end date is calculated by adding the CalcDateDiff to the start date.  🙂

     

     

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

  • scdecade

    Say Hey Kid

    Points: 709

    You're right tho it could be simpler

    with
    range_cte(EMployeeID, TranDate, NewStatus, EndDate) as (
    select
    EMployeeID, TranDate, NewStatus,
    isnull(dateadd(day, -1, lead(TranDate,1) over (partition by EmployeeID order by TranDate)), TranDate)
    from
    #EMployeeStatus)
    select
    rc.EMployeeID, cast(d.[value] as date) TranDate, rc.NewStatus
    from
    range_cte rc
    cross apply
    dbo.daterange(rc.TranDate, rc.EndDate, 'dd', 1) d
    order by
    rc.EMployeeID, cast(d.[value] as date);

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

  • sql_novice_2007

    Hall of Fame

    Points: 3307

    Thank You All for your help.

    I am able to achieve what I was looking with few changes to the script.

  • Jeff Moden

    SSC Guru

    Points: 996046

    sql_novice_2007 wrote:

    Thank You All for your help.

    I am able to achieve what I was looking with few changes to the script.

    So share... what changes did you make?

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • ktflash

    Ten Centuries

    Points: 1122

    One of the internet forum classics:

    • Link to deleted File/Image
    • Or simply: Thanks i found a different solution that works THE END

    I wanna be the very best
    Like no one ever was

  • jcelko212 32090

    SSCrazy Eights

    Points: 8948

    >> I have a table with sample data like this. <<

    Thank you for trying to post DDL, but you have some fundamental errors. A table must have a key. This is not an option; it Is a definition. The identifier is not used for computations so it cannot be a numeric data type by definition. Status codes use what is called a nominal scale, but nobody has a nominal scale was that insanely long! We need a list of values. Finally, status is an attribute, not an entity..

    CREATE TABLE Personnel

    (emp_id CHAR (10) NOT NULL,

    trans_date DATE NOT NULL DEFAULT CURRENT_TIMESTAMP,

    prior_status CHAR (?) NOT NULL

    . CHECK (prior_status IN (..))

    current_status CHAR (?) NOT NULL,

    CHECK current_status IN (..)).),

    CHECK(prior_status <> current_status),

    ..

    )

    At this point, I'm not going to do your work for you. I want you to Google an old article of mine online about state transition constraints in SQL. You're probably smart enough you can figure it out once you find the articles.

    However, the rest of your posting tells me that you don't know the only format for dates in ANSI/ISO standard SQL is "YYYY-MM-DD" and not the local dialect which you used.

    >> I need to generate data like the following. <>

    Do you have a calendar table? This way very easily join with a between predicate

     

     

    Please post DDL and follow ANSI/ISO standards when asking for help. 

  • Jeff Moden

    SSC Guru

    Points: 996046

    Hey, Joe.  Please read a good book on SQL Server, Databases, Scales, and do actually read the ANSI/ISO standards before attempting to quote them. 😉

     

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

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

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