So I have a Start Time (hh:mm:ss) and an Endtime (hh:mm:ss) I have created an inline table like the following and after created an intervalmatch with the proper data to link this interval with how many events happened in those 30mins. It did work for many records but got a huge chunk of data as nulls meaning it did not match.
Time Intervals Table
Range is split in 30mins. Now I have connected it with a Tournament table where Im checking how many activities happened in each 30mins range and added the intervalmatch like this
Hour, time(Hour_Min) as Hour_Mina , time(Hour_Max) as Hour_Maxa ,
Hour_Min & '-' & Hour_Max as HourInterval
DROP TABLE TimeListTemp;
RENAME Field Hour_Mina to Hour_Min;
RENAME Field Hour_Maxa to Hour_Max;
Hour_Min , Hour_Max Resident TimeList;
Why would some of this data (sample) not match?
Start time has been converted to Time : time(maketime(Hour(StartTime), Minute(StartTime),Second(StartTime)),'hh:mm:ss TT') as ConvertedStartTime
Not sure if I explained my self well but if not let me know, also from 23:30 till 00:00.. there are no matches when in reality there is data
This morning seems I been fresh so I have noticed that the time not captured was always the same so I checked the time ranges and realised that I had a missing couple of 30mins intervals hence the no matches.
I also noticed that if the range is from 23:30:00 to 00:00:00. it does not work but from 23:30:30 to 23:59:59 it picks up the data up to 23:59:58 leaving out 23:59:59
Any idea what I can do to capture the 23:59:59 data within that range?