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

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | 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?
 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
17689 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
17689 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
17689 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
17689 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  
 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.08 seconds. Powered By: Snitz Forums 2000