x

selectively filtering previous visits

We are analysing reports of visitors and need to filter out repeat visits that may have occurred over the previous 5 days. The visits are stored in a table along with a visit date, visitor id, report date and reporter id. This is quite easy to do with a subquery. However, we would also like to be selective on which visits to filter based on a reporter rank. In the simplified example below the rank can be determined from the ReporterId, i.e. id 0 is of higher rank than 1 that is again higher in rank than 2. For example in the data below we wish those visits with ids 6 and 11 to actually be 7 and 12 respectively as there have been later visits reported by reporters of a higher rank. I've considered many approaches but haven't had any success.

Any suggestions welcome :)

 declare @visits table (
     VisitId int identity(1,1),
     ReportDate date,
     ReporterId int,
     VisitDate date,
     VistorId int
 )
 
 insert into @visits(ReportDate, VistorId, ReporterId, VisitDate) values 
 ('20120201',1,0,'20120115'),
 ('20120202',1,1,'20120118'),
 ('20120203',1,0,'20120129'),
 ('20120204',1,1,'20120131'),
 ('20120201',2,0,'20120116'),
 ('20120202',2,1,'20120128'),
 ('20120203',2,0,'20120129'),
 ('20120204',2,1,'20120130'),
 ('20120201',3,0,'20120108'),
 ('20120202',3,1,'20120109'),
 ('20120203',3,2,'20120130'),
 ('20120204',3,1,'20120131')
 
 ; with ct1 as (
     select
         t1.*,
         (select count(VistorId)  from @visits t1p 
             where t1p.VistorId = t1.VistorId and t1p.ReportDate < t1.ReportDate                     
             and datediff(day, t1p.VisitDate, t1.VisitDate) between 0 and 5) VisitsIn5Days
     from @visits t1
 )
 select t2.*
 from ct1 t2
     where VisitsIn5Days = 0
     
     
     VisitId     ReportDate ReporterId  VisitDate  VistorId    VisitsIn5Days
 ----------- ---------- ----------- ---------- ----------- -------------
 1           2012-02-01 0           2012-01-15 1           0
 3           2012-02-03 0           2012-01-29 1           0
 5           2012-02-01 0           2012-01-16 2           0
 *6          2012-02-02 1           2012-01-28 2           0
 9           2012-02-01 0           2012-01-08 3           0
 *11         2012-02-03 2           2012-01-30 3           0
more ▼

asked May 02, 2012 at 02:17 PM in Default

avatar image

bsdz
14 2 2 5

(comments are locked)
10|1200 characters needed characters left

0 answers: sort voted first
Be the first one to answer this question
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x1016
x50
x20
x10
x2

asked: May 02, 2012 at 02:17 PM

Seen: 914 times

Last Updated: May 02, 2012 at 02:17 PM

Copyright 2016 Redgate Software. Privacy Policy