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 '12 at 02:17 PM in Default

bsdz gravatar image

bsdz
14 1 2 3

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

0 answers: sort newest
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.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

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:

x672
x46
x10
x7
x2

asked: May 02 '12 at 02:17 PM

Seen: 590 times

Last Updated: May 02 '12 at 02:17 PM