Hi all,
First time post. Usually I can work these things out or find a post somewhere that explains an approach I could use. But not this time.
I have a fact table relating to call that I need to indicate if the duration of a call spans 5 minute intervals and display which 5 minute intervals the call was present in.
The source data has a aggregate granularity of 15 minutes in a reference table but that is too high level for what we need.
I have the start and end time of the call and want to show the 5 minute intervals the call was present in. For example A call that began at 12:30:34 and finished at 12:52:12 I want to show as:
CallID, Start Time, End time, 5 Min Period
12345, 12:30:34, 12:52:12, 12:30
12345, 12:30:34, 12:52:12, 12:35
12345, 12:30:34, 12:52:12, 12:40
12345, 12:30:34, 12:52:12, 12:45
12345, 12:30:34, 12:52:12, 12:50
I thought I could do this using a cross join using a reference table with unequal conditions ('>=' and '<=') but I'm not getting the correct results.
The reference table I set up has the from and to periods with a period label eg:
From, To, Period_label
12:00:00, 12:05:00, 12:00
12:05:00, 12:10:00, 12:05
12:10:00, 12:15:00, 12:10
12:15:00, 12:20:00, 12:15
etc - for the entire day
So my query is
Select Call_data.*, Time_ref. Period_label
from Call_data, Time_ref
where Call_data.Time_start >= Time_ref.from and Call_data.Time_end <=Time_ref.To
This does return results but only about 10% of records it could.
I have also tried
Select Call_data.*, Time_ref. Period_label
from Call_data inner join
Time_ref on Call_data.Time_start >= Time_ref.from and Call_data.Time_end <=Time_ref.To
This returns nothing at all.
Any advice would be greatly appreciated.