Hello forum,
I've got a tricky little job to do which involves calculating the overlap between certain date ranges. I've written a bit of code in VBA/Excel which works fine, but it's appallingly slow - and I think this sort of thing belongs in Access (or similar) rather than Excel anyway.
Very brief summary of what needs doing...
Have a main table (could be millions of rows) which looks something like:
Main_Table
=======
* Account_Number_Index
* Start_Date
* End_Date
* Data_Item_1
* Data_Item_2
...
...
...
* Data_Item_N
I also have a small table of "analysis periods" (typically 4-6 rows only)
Analysis_Periods
===========
* Period_Index
* Period_Start_Date
* Period_End_Date
* Period_Label
I need to create an output table from all of this which has one record for each combination of Account_Number_Index and Period_Index. Fields would be:
Output_Table
=========
* Account_Number_Index
* Overlap_Start_Date (explained below)
* Overlap_End_Date (explained below)
* Period_Index
* Period_Lable
* Data_Item_1
* Data_Item_2
...
...
...
* Data_Item_N
So in loose pseudo-code it would look something like this:
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
Working out the overlapping period is just a case of the time interval between:
(a) max(Start_Date, Period_Start_Date)
(b) min(End_Date, Period_End_Date)
Have attached a scribble on a bit of paper re: overlapping time intervals, if that helps visualise the problem.
Can anyone suggest an efficient way of doing this in Access please, either with some SQL code or some VBA (or both)?
I'm not an expert in this stuff so I hope I'm making sense...apologies if not.
Most grateful for any assistance.
Thx
A