Time diff between 2 values in the same column? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Time diff between 2 values in the same column?

Author

Topic

panthagani
Yak Posting Veteran

58 Posts

Posted - 2006-08-27 : 04:15:36

Here is the sample data
nType dCDServerTime
0 2006-05-12 03:50:46.770
1 2006-05-12 04:00:59.153
96 2006-05-12 04:01:23.827
96 2006-05-12 04:16:01.297
14 2006-05-15 05:02:46.857
20 2006-06-23 03:23:36.970

I am trying to find the time diff between dCDServerTime of the "first" ntype = 1 and the "first" ntype = 96. There can be any number of rows with 1 and 96 but rows with 1 always come before those with 96.

I tried by putting a subset of the above result into a table variable but don't seem to get ahead without declaring more variables.

declare @t table
(Type int, ServerTime datetime)
insert into @t
select top 1 nType,dCDServerTime from tblEvent
where nGlobalCaseID = 11901643 and nType = 1
UNION
select top 1 nType,dCDServerTime from tblEvent
where nGlobalCaseID = 11901643 and nType = 96
select * from @t

Is it possible to do it with a single select?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-08-27 : 04:53:19

is this what you want ?

declare @table table
(
	nType 		int,
	dCDServerTime	datetime
)
insert into @table
select	 0, '2006-05-12 03:50:46.770'	union all
select	 1, '2006-05-12 04:00:59.153'	union all
select	96, '2006-05-12 04:01:23.827'	union all
select	96, '2006-05-12 04:16:01.297'	union all
select	14, '2006-05-15 05:02:46.857'	union all
select	20, '2006-06-23 03:23:36.970'

select	nType, dCDServerTime, 
	datediff(minute, dCDServerTime_1, dCDServerTime),
	datediff(minute, dCDServerTime_96, dCDServerTime)
from
(
	select	nType, dCDServerTime,
		dCDServerTime_1  = (select max(dCDServerTime) from @table x where x.nType = 1 and x.dCDServerTime <= t.dCDServerTime),
		dCDServerTime_96 = (select max(dCDServerTime) from @table x where x.nType = 96 and x.dCDServerTime <= t.dCDServerTime)
	from	@table t
) a

KH

panthagani
Yak Posting Veteran

58 Posts

Posted - 2006-08-27 : 05:07:50

Thanks a ton! I just need to find the time diff only between the "first" 1 and "first" 96, though.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-08-27 : 05:18:55

sorry. I must had misread it.


declare @table table
(
	nType 		int,
	dCDServerTime	datetime
)
insert into @table
select	 0, '2006-05-12 03:50:46.770'	union all
select	 1, '2006-05-12 04:00:59.153'	union all
select	96, '2006-05-12 04:01:23.827'	union all
select	96, '2006-05-12 04:16:01.297'	union all
select	14, '2006-05-15 05:02:46.857'	union all
select	20, '2006-06-23 03:23:36.970'

select	datediff(second, min(n1.dCDServerTime), min(n96.dCDServerTime)) as [Time Diff of first 1 and 96 in secs]
from	@table as n1, @table as n96
where	n1.nType	= 1
and	n96.nType	= 96

KH

panthagani
Yak Posting Veteran

58 Posts

Posted - 2006-08-27 : 08:59:28

Np,Thanks.

A small modification needed.. Posting some sample results. Actually, I have around 50,000 records.

nType nGlobalCaseID dCDServerTime
1 9188963 2006-05-07 23:21:09.260
96 9188963 2006-05-07 23:43:08.230
1 9419947 2006-05-07 19:21:27.580
1 9440738 2006-05-07 23:37:47.690
1 9440738 2006-05-07 23:58:54.800
1 9440739 2006-05-07 23:22:11.103
96 9440739 2006-05-07 23:22:15.760
1 9570167 2006-05-07 23:48:25.740
1 10084421 2006-05-04 16:17:13.787
96 10084421 2006-05-04 16:18:46.743
1 10084422 2006-05-04 16:36:15.473

Now, I need to do the original calculation (time diff between "1" and "96" for each pair of values in nGlobalCaseID. Eg: nGlobalCaseID = 9188963,9440739,10084421 etc. Only the pairs with (1,96) for a nGlobalCaseID need to be considered. All other single "1"'s and single "96"'s (and multilple 1 or 96 for same nglobalcaseID)can be ignored.

So, the result would look lsomething like

nGlobalCaseID Timediff(between 1 and 96)
-------------- -----------------------

I am trying to extract the (1,96) pairs after inserting a subset of original data into a temp table, like this..

select nType,nGlobalCaseID,dCDServerTime into #FirstResp from tblEvent where
nGlobalCaseID is not null and nType is not NULL and tByRule is NULL and nType in
(1,96)
and dCDServerTime > '2006-05-01 00:00:00.000'
and dCDServerTime < '2006-06-01 00:00:00.000'
select <what goes in here?> from #FirstResp
drop table #FirstResp
Again, thanks for your valuable inputs

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-08-27 : 09:23:51

[code]declare @table table
(
nType int,
nGlobalCaseID int,
dCDServerTime datetime
)
insert into @table
select 1, 9188963, '2006-05-07 23:21:09.260' union all
select 96, 9188963, '2006-05-07 23:43:08.230' union all
select 1, 9419947, '2006-05-07 19:21:27.580' union all
select 1, 9440738, '2006-05-07 23:37:47.690' union all
select 1, 9440738, '2006-05-07 23:58:54.800' union all
select 1, 9440739, '2006-05-07 23:22:11.103' union all
select 96, 9440739, '2006-05-07 23:22:15.760' union all
select 1, 9570167, '2006-05-07 23:48:25.740' union all
select 1, 10084421, '2006-05-04 16:17:13.787' union all
select 96, 10084421, '2006-05-04 16:18:46.743' union all
select 1, 10084422, '2006-05-04 16:36:15.473'

select nGlobalCaseID, datediff(second, nType1, nType96) as [TimeDiff in Seconds]
from
(
select nGlobalCaseID,
max(case when nType = 1 then dCDServerTime end) as nType1,
max(case when nType = 96 then dCDServerTime end) as nType96
from @table
group by nGlobalCaseID
) a
where nType1 is not null
and nType96 is not null

/*
nGlobalCaseID TimeDiff in Seconds
------------- -------------------
9188963 1319
9440739 4
10084421 93
*/[/code]

KH

panthagani
Yak Posting Veteran

58 Posts

Posted - 2006-08-27 : 09:40:58

Works like a charm,thanks. The use of case in max function was interesting and informative!

panthagani
Yak Posting Veteran

58 Posts

Posted - 2006-08-27 : 23:05:01

khtan,did a small modification. Since I need to calculate the time diff between the first "1" and first "96" in a list of 1's and 96's for a 'nGlobalCaseID',I did the following (changed the max to min). Here is the entire query.

select nGlobalCaseID, datediff(second, nType1, nType96) as [First Response Time]
from
(
select nGlobalCaseID,
min(case when nType = 1 then dCDServerTime end) as nType1,
min(case when nType = 96 then dCDServerTime end) as nType96
from tblEvent with (nolock) where nGlobalCaseID is not null and nType is not NULL and tByRule is NULL and nType in (1,96)
and dCDServerTime > '2006-05-01 00:00:00.000'
and dCDServerTime < '2006-06-01 00:00:00.000'
group by nGlobalCaseID
) a
where nType1 is not null
and nType96 is not null

Thanks.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-08-27 : 23:24:59

thanks for posting back

KH

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources