SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Time diff between 2 values in the same column?
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

panthagani
Yak Posting Veteran

58 Posts

Posted - 08/27/2006 :  04:15:36  Show Profile  Reply with Quote
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)

Singapore
17431 Posts

Posted - 08/27/2006 :  04:53:19  Show Profile  Reply with Quote
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

Go to Top of Page

panthagani
Yak Posting Veteran

58 Posts

Posted - 08/27/2006 :  05:07:50  Show Profile  Reply with Quote
Thanks a ton! I just need to find the time diff only between the "first" 1 and "first" 96, though.
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17431 Posts

Posted - 08/27/2006 :  05:18:55  Show Profile  Reply with Quote
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

Go to Top of Page

panthagani
Yak Posting Veteran

58 Posts

Posted - 08/27/2006 :  08:59:28  Show Profile  Reply with Quote
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
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17431 Posts

Posted - 08/27/2006 :  09:23:51  Show Profile  Reply with Quote
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
*/



KH

Go to Top of Page

panthagani
Yak Posting Veteran

58 Posts

Posted - 08/27/2006 :  09:40:58  Show Profile  Reply with Quote
Works like a charm,thanks. The use of case in max function was interesting and informative!
Go to Top of Page

panthagani
Yak Posting Veteran

58 Posts

Posted - 08/27/2006 :  23:05:01  Show Profile  Reply with Quote
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.
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17431 Posts

Posted - 08/27/2006 :  23:24:59  Show Profile  Reply with Quote
thanks for posting back


KH

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000