Filling dates between the status changes

Question

Filling dates between the status changes

sql_novice_2007

Hall of Fame

Points: 3307
More actions
March 25, 2020 at 2:21 am

#3736801

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.

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

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

pietlinden SSC Guru Points: 63510 More actions · Answer 1

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;

Steve Collins SSCrazy Points: 2174 More actions · Answer 2

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 Default port Points: 1400 More actions · Answer 3

Steve Collins 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 4 years, 9 months ago by ktflash.

I want to be the very best
Like no one ever was

https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help

Steve Collins SSCrazy Points: 2174 More actions · Answer 4

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:

TranDateCalcDateDiff
2018-07-11 00:00:00.0006
2018-07-18 00:00:00.0006
2018-07-25 00:00:00.0003
2018-07-29 00:00:00.0003
2018-08-02 00:00:00.0000

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

Steve Collins SSCrazy Points: 2174 More actions · Answer 5

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 More actions · Answer 6

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: 1004412 More actions · Answer 7

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.

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 Default port Points: 1400 More actions · Answer 8

One of the internet forum classics:

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

I want to be the very best
Like no one ever was

https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help

jcelko212 32090 SSCrazy Eights Points: 9303 More actions · Answer 9

>> 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: 1004412 More actions · Answer 10

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.

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)