Geek Answers Handbook

Repeat selection over a period of time <x days

If I had a large table (100,000 + records) in which there were service records or, possibly, access records. How can I find all cases of reappearance within a given number of days.

A table setup might be something like this, with lots of columns.

Record ID Customer ID Start Date Time Finish Date Time 1 123456 24/04/2010 16:49 25/04/2010 13:37 3 654321 02/05/2010 12:45 03/05/2010 18:48 4 764352 24/03/2010 21:36 29/03/2010 14:24 9 123456 28/04/2010 13:49 31/04/2010 09:45 10 836472 19/03/2010 19:05 20/03/2010 14:48 11 123456 05/05/2010 11:26 06/05/2010 16:23

What I'm trying to do is work out a way to select records where there is a reappearance of the [Client ID] field for a certain period of time (<X days). (Where is the time period - Start date Time of the 2nd entry - End date Time of the first entry.

This is what I would like it to look as soon as it was run for say x = 7

 Record ID Customer ID Start Date Time Finish Date Time Re-occurence 9 123456 28/04/2010 13:49 31/04/2010 09:45 1 11 123456 05/05/2010 11:26 06/05/2010 16:23 2

I can solve this problem with a smaller set of records in Excel, but I tried my best to find a SQL solution in MS Access. I have some SQL queries that I have tried, but I'm not sure that I am on the right path.

Any advice would be appreciated.

+4
source share
3 answers

I think this is a clear expression of what you want. This is not very high performance, but I'm not sure that you can avoid either a correlated subquery or a Cartesian JOIN table for yourself to solve this problem. It is standard SQL and should work on most any engines, although the details of date math may vary:

  SELECT * FROM YourTable YT1 WHERE EXISTS (SELECT * FROM YourTable YT2 WHERE YT2.CustomerID = YT1.CustomerID AND YT2.StartTime <= YT2.FinishTime + 7)
+3
source

To accomplish this, you need to make a self-join by comparing the entire table with yourself. Assuming similar names, it looks something like this:

 select r1.customer_id, min(start_time), max(end_time), count(1) as reoccurences from records r1, records r2 where r1.record_id > r2.record_id -- this ensures you don't double count the records and r1.customer_id = r2.customer_id and r1.finish_time - r2.start_time <= 7 group by r1.customer_id

You cannot easily get both record_id and the number of entries, but you can return and find it by comparing the start time with the record number with this client_id and start_time.

0
source

This will be done:

 declare @t table(Record_ID int, Customer_ID int, StartDateTime datetime, FinishDateTime datetime) insert @t values(1 ,123456,'2010-04-24 16:49','2010-04-25 13:37') insert @t values(3 ,654321,'2010-05-02 12:45','2010-05-03 18:48') insert @t values(4 ,764352,'2010-03-24 21:36','2010-03-29 14:24') insert @t values(9 ,123456,'2010-04-28 13:49','2010-04-30 09:45') insert @t values(10,836472,'2010-03-19 19:05','2010-03-20 14:48') insert @t values(11,123456,'2010-05-05 11:26','2010-05-06 16:23') declare @days int set @days = 7 ;with a as ( select record_id, customer_id, startdatetime, finishdatetime, rn = row_number() over (partition by customer_id order by startdatetime asc) from @t), b as ( select record_id, customer_id, startdatetime, finishdatetime, rn, 0 recurrence from a where rn = 1 union all select a.record_id, a.customer_id, a.startdatetime, a.finishdatetime, a.rn, case when a.startdatetime - @days < b.finishdatetime then recurrence + 1 else 0 end from b join a on b.rn = a.rn - 1 and b.customer_id = a.customer_id ) select record_id, customer_id, startdatetime, recurrence from b where recurrence > 0

Result: http://data.stackexchange.com/stackoverflow/q/112808/

I just understand that this needs to be done upon access. I'm sorry, this was written for SQL Server 2005. I do not know how to rewrite it for access.

0
source

More articles:


All Articles