question

bsdz avatar image
bsdz asked

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
sqlsub-queryfilterrankingvisitors
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

0 Answers

· Write an Answer

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.