So here is the situation:
A database used to track specific events, each having a start time and an end time that can coincide with other events.
Example:
Event1 starts at 9am and ends at 11am.
Event2 starts at 10am and ends at 12am.
The total duration of the 2 events is 4 hrs and is easily obtainable but how do we calculate the total time, which is 3 hrs, without including the coinciding or overlaping times?
1 possible solution:
IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = ‘HoursWorked’) DROP TABLE HoursWorked;
create table HoursWorked (
Time datetime,
[Count] bit);
declare @StartingDateTime datetime
declare @EndingDateTime datetime
set @StartingDateTime = ‘[Starting Date]‘
set @EndingDateTime = ‘[Ending Date]‘
insert into HoursWorked (Time) values (@StartingDateTime)
while (select max(Time) from HoursWorked) < @EndingDateTime
begin
insert into HoursWorked (Time) select dateadd(minute,1,(max(Time))) from HoursWorked
end;
IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = ‘temp’) DROP TABLE temp;
select Event, StartingDateTime, EndingDateTime into temp from EventTable where StartingDateTime between ‘[Starting Date]‘ and ‘[Ending Date]‘;
declare @StartingDateTime datetime
declare @EndingDateTime datetime
while @@fetch_status = 0
begin
declare StartingDateTime_Cursor cursor for
select StartingDateTime from temp
order by StartingDateTime
open StartingDateTime_Cursor
fetch next from StartingDateTime_Cursor into @StartingDateTime
declare EndingDateTime_Cursor cursor for
select EndingDateTime from temp
order by StartingDateTime
open EndingDateTime_Cursor
fetch next from EndingDateTime_Cursor into @EndingDateTime
update HoursWorked set [Count] = 1
where Time between @StartingDateTime and @EndingDateTime
end
close StartingDateTime_Cursor;
deallocate StartingDateTime_Cursor;
close EndingDateTime_Cursor;
deallocate EndingDateTime_Cursor;
Issue:
This particular method requires very large amount of processing resources. If you ran this query for a weeks time period, it would loop 10,080 times and create 10,080 records for every minute of every day for that entire week. So think about a month, approximately 40,320 records and a year, 483,840 records! This could easily get resource expensive.
