| Author |
 Topic  |
|
|
panthagani
Yak Posting Veteran
58 Posts |
 Posted - 08/27/2006 : 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)
Singapore
16746 Posts |
Posted - 08/27/2006 : 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 - 08/27/2006 : 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)
Singapore
16746 Posts |
Posted - 08/27/2006 : 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 - 08/27/2006 : 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)
Singapore
16746 Posts |
Posted - 08/27/2006 : 09:23:51
|
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
|
|
|
|
panthagani
Yak Posting Veteran
58 Posts |
Posted - 08/27/2006 : 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 - 08/27/2006 : 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)
Singapore
16746 Posts |
Posted - 08/27/2006 : 23:24:59
|
thanks for posting back
KH
|
|
|
| |
Topic |
|
|
|
Time diff between 2 values in the same column?
