选择在< x天的时间段内重复出现的次数 [英] Select repeat occurrences within time period <x days

问题描述

如果我有一张大桌子(100000个条目)，上面有服务记录或准入记录.如何在设定的天数内找到所有重复发生的实例.

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.

这是我希望运行x = 7以后的样子

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

我可以在Excel中使用较小的记录集来解决此问题，但一直努力在MS Access中提出SQL解决方案.我确实有一些SQL查询已经尝试过，但是我不确定自己走的路是否正确.

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.

推荐答案

我认为这是您想要的明确表达.它的性能不是很高，但是我不确定您是否可以避免使用关联子查询或表的笛卡尔JOIN自身来解决此问题.它是标准的SQL，并且在大多数引擎中都可以使用，尽管日期数学的详细信息可能有所不同:

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)

这篇关于选择在&lt; x天的时间段内重复出现的次数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！