Quering a group of records by time gaps

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 2000 Forums

SQL Server Development (2000)

Quering a group of records by time gaps

Author

Topic

jns
Starting Member

19 Posts

Posted - 2002-08-13 : 13:49:18

I have a table of information that tracks a userid and the date/time which they visit a site. What I need to be able to do is figure out how many people visited only once, two, three times, etc. The differenciating factor will be a gap in the time of four hours or more. More over, I need to compare from one year to another, month to month, etc. Any help would be appreciated

table

uid INT
vdate DATETIME

Sample data

1 2002-08-12 18:01:26.390
1 2002-08-12 18:01:27.607
1 2002-08-12 18:01:32.950
1 2002-08-13 10:20:58.577
1 2002-08-13 10:20:59.500
1 2002-08-13 10:21:13.607
1 2002-08-13 10:21:13.920
1 2002-08-13 10:59:19.640
1 2002-08-13 10:59:21.390
1 2002-08-13 10:59:31.187
1 2002-08-13 12:58:44.640
1 2002-08-13 12:58:45.827
1 2002-08-13 12:58:51.920
1 2002-08-13 12:59:02.107
1 2002-08-13 12:59:03.200
1 2002-08-13 12:59:07.577
10027 2002-07-16 18:03:33.640
10027 2002-07-16 18:03:34.293
10027 2002-07-16 18:03:34.420
10027 2002-07-16 18:03:58.747
10027 2002-07-16 18:04:12.950
10027 2002-08-13 07:08:59.123
10027 2002-08-13 07:09:00.373
10027 2002-08-13 07:09:00.450
10027 2002-08-13 07:09:32.437
10027 2002-08-13 07:10:21.480
10027 2002-08-13 07:12:17.653
10079 2002-08-12 07:05:15.403
10079 2002-08-12 07:05:15.857
10079 2002-08-12 07:05:21.170
10079 2002-08-12 07:05:45.420
10079 2002-08-12 07:05:50.420
10113 2002-07-16 15:26:02.090
10113 2002-07-16 15:26:02.920
10113 2002-08-10 13:29:57.763
10113 2002-08-10 13:29:58.030
10113 2002-08-10 13:29:58.030
10113 2002-08-10 13:30:13.373
10113 2002-08-10 13:30:13.450
10113 2002-08-10 13:30:24.903
10113 2002-08-10 13:30:38.500
10118 2002-07-17 00:14:47.433
10118 2002-07-17 00:14:47.780
10118 2002-07-17 00:18:19.170
10118 2002-07-17 00:18:27.327
10118 2002-07-17 00:18:32.983
10118 2002-07-17 00:22:55.780
10118 2002-07-17 00:28:12.700
10118 2002-07-17 00:28:17.077
10118 2002-07-17 00:29:29.577

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2002-08-13 : 14:22:11

You didn't post your DDL or the INSERT statements required to insert the sample data, so I didn't test this solution (If you're too lazy to type it out, then so am I

), but I think this will work....


create table #jns (
    rownum int identity(1,1),
    uid int,
    vdate datetime,
    visitnumber int)

insert into #jns (uid,vdate)
select uid,vdate from <sourcetable>
order by uid,vdate

declare @lastuid int, @lastvdate datetime, @visitnumber int
select @lastuid = 0, @lastvdate = 0, @visitnumber = 1

update #jns
set
    @visitnumber = visitnumber = case
                                     when @lastuid <> uid then 1
                                     when datadiff(hh,@lastvdate,vdate) < 4 then @visitnumber
                                     else @visitnumber + 1
                                 end,
    @lastvdate = vdate,
    @lastuid = uid

select
    a.[number of visits]
    count(*) as [the count]
from (
    select
        uid,
        max(visitnumber) as [number of visits]
    from
        #jns
    group by
        uid ) a
group by
    a.[number of visits]
go

Jay White
{0}

1fred
Posting Yak Master

158 Posts

Posted - 2002-08-13 : 14:31:13

You can try converting your datetime data in float. The only rough part is to find what number is your right interval of time.

select uid,count(uid)
from table
where cast(vdate as float) > cast(getdate() as float)- 5 -- this is your number you'll have to find to fit it to 4hours
group by uid
order by count(uid) desc

to compare year and month use datepart function

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2002-08-13 : 14:47:31

1fred, one of us misread the problem.

(And by "one of us", I mean YOU

.... I think

)

jns, to use my script to compare from year to year, etc, you should probably store off the post-aggregated/churned data for each time-frame and do you comparisons from there....

Jay White
{0}

1fred
Posting Yak Master

158 Posts

Posted - 2002-08-13 : 14:56:16

I know my solution is not exactly what the guy asked for, I just try to give him some tools by the time I can take to answer. Btw while I was writing, your post was not there.

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2002-08-13 : 14:57:46

No worries, just giving you a hard time ...

here you go...

Jay White
{0}

jns
Starting Member

19 Posts

Posted - 2002-08-14 : 12:07:35

Ok here's what I came up with to do total visits without using a temp table or cursor

SELECT t3.crmid, count(*) + 1 FROM
(SELECT t1.crmid, t1.created
FROM activity t1, activity t2
WHERE t1.crmid=t2.crmid AND datediff(hh,t1.created,(SELECT TOP 1 t2.created FROM activity t2 WHERE t1.crmid=t2.crmid AND t2.created > t1.created)) >= 4
GROUP by t1.crmid, t1.created
HAVING datediff(hh,t1.created,(SELECT TOP 1 t2.created FROM activity t2 WHERE t1.crmid=t2.crmid AND t2.created > t1.created)) > 4) t3
GROUP by t3.crmid
ORDER by count(*) DESC

The + 1 considers that the query only returns the next visit that is 4 hours greater then the previous and adds 1 to the count to account for the initial visit. From here I'm sure I can group by day, week, month, quarter, year.

Does anyone see any flaws with this?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources