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
asked
May 02 '12 at 02:17 PM
in Default
bsdz
14
●
2
●
3