March 25, 2020 at 2:21 am
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.
March 25, 2020 at 6:17 am
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;
March 25, 2020 at 12:19 pm
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://qa.sqlservercentral.com/scripts/a-daterange-table-valued-function
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
March 25, 2020 at 12:52 pm
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.
I want to be the very best
Like no one ever was
March 25, 2020 at 2:29 pm
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
March 25, 2020 at 2:41 pm
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
March 25, 2020 at 3:55 pm
Thank You All for your help.
I am able to achieve what I was looking with few changes to the script.
March 25, 2020 at 8:24 pm
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
March 26, 2020 at 9:59 am
One of the internet forum classics:
I want to be the very best
Like no one ever was
March 26, 2020 at 9:20 pm
>> 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.
March 26, 2020 at 9:50 pm
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
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply