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
 SQL Server 2005 Forums
 Transact-SQL (2005)
 Average Time Distance

Author  Topic 

Ali T
Starting Member

22 Posts

Posted - 2008-12-25 : 02:50:25
Dear all;
I have a table with 2000 records which contains the call records of 100 customers during 3 months. the columns of this table are ID, Datetime (Date and time of each call), Day_NO (I have labled the days of theses 3 months from 1 to 92 according to its date), Time (this column contains the hh part of hh:mm:ss of time of each call record).
Now I want to measure the Average time distance between each customer's calls. in order to do this I have to calculate the time distance between each sequential pair of calls of each customer and divide it by the number of calls...what I beg for naw is the Sql query which enables me to do this in sql server.

Regards

Ali

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2008-12-25 : 12:09:17
Here is one way:

------------------------------------------
--Set up a test table
set nocount on
if object_id('tempdb..#t') > 0 drop table #t
create table #t (id int, dt datetime)
go
------------------------------------------
--populate with sample data for 2000 rows for about 100 customers over about 3 month period at random times
insert #t (id, dt)
select convert(int, ceiling(rand()*100))
,dateadd(hour, rand()*24*30*3, dateadd(month,-3,getdate()))
go 2000
--select * from #t order by 1,2

------------------------------------------
--SELECT statement

select t.id
,avg(datediff(hour, t.dt, ca.dt)) [AvghoursBetweenCustCalls]
,min(datediff(hour, t.dt, ca.dt)) [MinHoursBetweenCustCalls]
,max(datediff(hour, t.dt, ca.dt))[MaxHoursBetweenCustCalls]
,count(*) [CustCallCount]
from #t t
cross apply (
select top 1 dt
from #t
where id = t.id
and dt > t.dt
order by dt
) ca
group by t.id
order by t.id

output:
id AvghoursBetweenCustCalls MinHoursBetweenCustCalls MaxHoursBetweenCustCalls CustCallCount
----------- ------------------------ ------------------------ ------------------------ -------------
1 85 4 282 22
2 148 11 412 14
3 81 1 262 24
4 114 13 327 18
5 102 3 526 20
6 117 0 456 16
7 111 9 375 17
8 106 0 346 19
9 92 1 348 19
10 107 1 342 19
11 128 6 512 16
12 155 14 399 13
13 92 1 377 22
14 227 30 645 8
15 117 0 318 16
16 149 18 396 13
17 84 0 382 25
18 96 0 238 22
19 114 1 334 15
20 110 8 371 19
21 87 1 286 22
22 87 10 409 24
23 128 12 306 12
24 79 5 203 26
25 89 8 192 18
26 101 6 415 20
27 110 14 348 18
28 95 17 275 22
29 71 1 278 29
30 131 8 436 15
31 131 3 395 16
32 80 3 270 25
33 114 1 528 18
34 79 0 292 25
35 106 8 337 20
36 153 1 621 13
37 91 4 436 21
38 80 3 236 25
39 93 8 260 19
40 155 3 680 13
41 109 47 286 19
42 106 17 223 20
43 99 8 421 20
44 107 9 375 12
45 148 8 379 14
46 85 3 374 19
47 116 7 252 17
48 96 4 492 21
49 118 2 309 17
50 104 12 306 16
51 129 13 275 13
52 142 4 388 15
53 110 5 429 17
54 107 22 296 19
55 100 5 259 21
56 91 3 462 23
57 75 3 237 25
58 65 2 207 31
59 162 2 420 13
60 99 9 314 19
61 141 13 363 14
62 76 3 304 27
63 91 6 272 22
64 120 12 360 17
65 71 4 307 29
66 88 2 335 22
67 88 4 363 22
68 250 5 423 7
69 114 7 246 18
70 82 2 425 25
71 126 9 521 16
72 123 7 394 16
73 100 2 340 19
74 89 2 308 21
75 93 3 391 21
76 103 13 290 19
77 80 2 200 25
78 145 17 484 14
79 92 1 373 21
80 79 3 327 25
81 86 0 287 21
82 153 2 651 9
83 86 1 365 23
84 102 6 334 20
85 105 0 303 20
86 177 1 371 11
87 181 30 668 11
88 93 0 548 22
89 106 2 333 18
90 114 5 275 18
91 97 6 526 22
92 128 6 414 15
93 95 8 266 20
94 91 3 305 20
95 67 1 267 28
96 97 16 410 21
97 83 1 413 25
98 96 3 375 19
99 102 19 205 13
100 121 5 279 15


Be One with the Optimizer
TG
Go to Top of Page

Ali T
Starting Member

22 Posts

Posted - 2008-12-26 : 04:13:15
thanks so much ...I'll check it.
Go to Top of Page
   

- Advertisement -