May 24, 2016 at 8:51 am
Hello and thanks to all who take the time to read this and help out!
I have an issue I'm trying to query for and not sure how to do it. I need to know the time spent between each status change for each ID. There can me more than 8 statuses per ID. But for simplicity I limited to the 8.
Example of desired result set:
idstatusrecord_dateDateDiff_MS
491627I2016-04-29 11:52:11.1270
491627MR2016-04-29 11:52:11.20073
491627A2016-04-29 12:02:13.083601883
The DateDiff break down is to Milliseconds. Here is some sample data to work with:
create table #temp_Example1([id] int, [status] varchar(10), [record_date] datetime)
insert into #temp_Example1([id], [status], [record_date])
values(460659,'I','2016-02-17 18:22:00.493');
insert into #temp_Example1([id], [status], [record_date])
values(460659,'MR','2016-02-17 18:22:00.557');
insert into #temp_Example1([id], [status], [record_date])
values(460659,'A','2016-02-17 18:42:05.430');
insert into #temp_Example1([id], [status], [record_date])
values(460659,'CR','2016-02-24 16:57:09.750');
insert into #temp_Example1([id], [status], [record_date])
values(460659,'VI','2016-02-25 08:42:01.637');
insert into #temp_Example1([id], [status], [record_date])
values(460659,'RB','2016-03-01 12:17:07.533');
insert into #temp_Example1([id], [status], [record_date])
values(460659,'NB','2016-03-01 12:22:05.467');
insert into #temp_Example1([id], [status], [record_date])
values(460659,'B','2016-03-01 14:17:27.843');
insert into #temp_Example1([id], [status], [record_date])
values(491627,'I','2016-04-29 11:52:11.127');
insert into #temp_Example1([id], [status], [record_date])
values(491627, 'MR','2016-04-29 11:52:11.200');
insert into #temp_Example1([id], [status], [record_date])
values(491627,'A','2016-04-29 12:02:13.083');
insert into #temp_Example1([id], [status], [record_date])
values(491627,'CR','2016-05-06 14:22:06.367');
insert into #temp_Example1([id], [status], [record_date])
values(491627,'VI','2016-05-06 14:22:11.613');
insert into #temp_Example1([id], [status], [record_date])
values(491627,'RB','2016-05-17 12:32:29.380');
insert into #temp_Example1([id], [status], [record_date])
values(491627,'NB','2016-05-17 12:37:03.237');
insert into #temp_Example1([id], [status], [record_date])
values(491627,'B','2016-05-17 12:47:25.657');
go
select * from #temp_Example1
drop table #temp_Example1
Thanks again for any and all help!!
Frederick (Fred) J. Stemp, Jr.
Database Administrator / Database Developer
Dealer Funding, LLC
'...if they take my stapler then I'll set the building on fire...'
May 24, 2016 at 9:00 am
This is what I use for "previous row" calculations on 2008. For 2012+, I would use LAG.
WITH CTE AS(
select *, ROW_NUMBER() OVER(PARTITION BY ID ORDER BY record_date) rn
from #temp_Example1
)
SELECT a.id,
a.status,
a.record_date,
ISNULL( DATEDIFF(MS, b.record_date, a.record_date), 0) AS DateDiff_MS
FROM CTE a
LEFT
JOIN CTE b ON a.id = b.id AND a.rn = b.rn + 1;
May 24, 2016 at 9:12 am
Thanks Luis!
You rock! I was trying to do it with a CTE and was having difficulty. I knew it was me. Thanks again this works perfectly!
Frederick (Fred) J. Stemp, Jr.
Database Administrator / Database Developer
Dealer Funding, LLC
'...if they take my stapler then I'll set the building on fire...'
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply