Select repeat occurrences within time period <x days

If I had a large table (100000 + entries) which had service records or perhaps admission records. How would I find all the instances of re-occurrence within a set number of days.

The table setup could be something like this likely with more 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 am trying to do is work out a way to select the records where there is a re-occurrence of the field [Customer ID] within a certain time period (< X days). (Where the time period is Start Date Time of the 2nd occurrence - Finish Date Time of the first occurrence.

This is what I would like it to look like once 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 have struggled to come up with a SQL solution in MS Access. I do have some SQL queries that I have tried but I am not sure I am on the right track.

Any advice would be appreciated.

I think this is a clear expression of what you want. It's not extremely high performance but I'm not sure that you can avoid either correlated sub-query or a cartesian JOIN of the table to itself to solve this problem. It is standard SQL and should work in most any engine, although the details of the date math may differ:

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

In order to accomplish this you would need to make a self join as you are comparing the entire table to itself. Assuming similar names it would look 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 wouldn't be able to easily get both the record_id and the number of occurences, but you could go back and find it by correlating the start time to the record number with that customer_id and start_time.

This will do it:

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: https://data.stackexchange.com/stackoverflow/q/112808/

I just realize it should be done in access. I am so sorry, this was written for sql server 2005. I don't know how to rewrite it for access.