带有日期范围的棘手作业- MS Access/SQL

Question

我有一个棘手的小工作要做，包括计算某些日期范围之间的重叠。我用VBA/Excel编写了一些代码，它运行得很好，但速度慢得令人震惊--我认为这类东西应该属于Access (或类似的)，而不是Excel。

非常简单的总结什么需要做..。

有一个主表(可能是数百万行)，它看起来类似于：

Main_Table

• Account_Number_Index
• Start_Date
• End_Date
• Data_Item_1
• Data_Item_2 .
• Data_Item_N

我还有一个小的“分析周期”表(通常只有4-6行)。

Analysis_Periods

• Period_Index
• Period_Start_Date
• Period_End_Date
• Period_Label

我需要从所有这些创建一个输出表，该表对Account_Number_Index和Period_Index的每个组合都有一个记录。字段将是：

Output_Table

• Account_Number_Index
• Overlap_Start_Date (解释如下)
• Overlap_End_Date (解释如下)
• Period_Index
• Period_Lable
• Data_Item_1
• Data_Item_2 .
• Data_Item_N

因此，在松散的伪代码中，它看起来如下所示：

For each row in Main_Table

    For each row in Analysis_Periods

        Work out the overlapping period (Overlap_Start_Date...Overlap_End_Date) for time intervals (Start_Date...End_Date) and (Period_Start_Date...Period_End_Date)

        If there is no overlap at all

            Do nothing

        Else

            Output a row of data to Output_Table which is identical to the row in the Main_Table except that:
                 * Overlap_Start_Date used instead of Start_Date
                 * Overlap_End_Date used instead of End_Date
                 * Period_Index and Period_Label tagged also output

         End If

    Next row in Analysis_Periods

Next row in Main_Table

计算重叠周期只是在以下时间间隔的情况：(a)最大(Start_Date，Period_Start_Date) (b) min(End_Date，Period_End_Date)

我想我已经成功地在下面加入了一个超链接，上面有一个小小的涂鸦:重叠的时间间隔，如果这有助于想象问题的话。

有人能建议在Access中使用一种高效的方法吗?要么使用一些SQL代码，要么使用一些VBA (或者两者兼而有之)？

我不是这方面的专家，所以我希望我做的sense...apologies如果不是。

非常感谢你的帮助。

Thx A

https://i.stack.imgur.com/Al3pO.jpg

Answer 1

考虑两个表之间筛选的CROSS JOIN，其中将表放置在FROM子句中逗号分隔的列表中，而不带任何JOIN，后面跟着日期上的WHERE。然后将查询放在条件聚合GROUP BY中，以确定所需的MIN和MAX日期。

SELECT m.Account_Number_Index, 
       p.Period_Index, 
       MIN(IIF(m.Start_Date < a.Period_Start_Date, 
               m.Start_Date, a.Period_Start_Date)) AS Overlap_Start_Date,
       MAX(IIF(m.End_Date < a.Period_End_Date, 
               a.Period_End_Date, m.End_Date)) As Overlap_End_Date

FROM MainTable m, AnalysisPeriods a
WHERE m.Start_Date <= a.Period_Start_Date 
  AND a.Period_End_Date <= m.End_Date
GROUP BY m.Account_Number_Index, 
         p.Period_Index

在第二个查询中，将原始表连接到索引字段上的该查询。甚至考虑构建一个临时表overlap_table (带有SELECT ... INTO子句)并使用它来代替查询：

SELECT m.Account_Number_Index, 
       p.Period_Index, 
       q.Overlap_Start_Date, 
       q.Overlap_End_Date
       p.Period_Label,
       m.Data_Item_1,
       m.Data_Item_2,
       m.Data_Item_3, 
       ...
       m.Data_Item_n

FROM (overlap_query_or_table q
INNER JOIN MainTable m 
  ON q.Account_Number_Index = m.Account_Number_Index)
INNER JOIN AnalysisPeriods a
  ON q.Period_Index = p.Period_Index

注意事项：这个处理涉及交叉连接，它返回两个表之间的笛卡儿积(即大约100万行X5-6行)，因此效率得不到保证。

Answer 2

如果使用access，为什么不使用联接查询？相似的东西

Select m.Account_Number_Index, m.Start_Date,m.End_Date, ap.Period_Index, ap.Period_Lable,
m.Data_Item_1, m.Data_Item_2 ... ... ... m.Data_Item_N
From Analysis_Periods as ap left join Main_Table as m on (m.Start_Date >= ap.Period_Start_Date and m.End_date <= ap.Period_End_Date)

为了正确的重叠，开始和结束使用iif函数。