I'd like to write a query that will calculate the total amount of activity that occurred within each 15 minute interval of the day using only timestamps that correspond to activity start and stop times.
Here is a sample data set:
DATE StartDateTime StopDateTime
2/2/2015 2/2/2015 7:00 2/2/2015 7:25
2/2/2015 2/2/2015 7:20 2/2/2015 7:29
2/2/2015 2/2/2015 7:35 2/2/2015 7:42
2/2/2015 2/2/2015 8:05 2/2/2015 8:14
2/2/2015 2/2/2015 8:16 2/2/2015 8:20
2/2/2015 2/2/2015 8:29 2/2/2015 8:40
2/2/2015 2/2/2015 8:55 2/2/2015 9:25
And this is what I'd like to be able to get:
DATE Interval activityTime(min)
2/2/2015 2/2/2015 7:00 15
2/2/2015 2/2/2015 7:15 19
2/2/2015 2/2/2015 7:30 7
2/2/2015 2/2/2015 7:45 0
2/2/2015 2/2/2015 8:00 9
2/2/2015 2/2/2015 8:15 5
2/2/2015 2/2/2015 8:30 10
2/2/2015 2/2/2015 8:45 5
2/2/2015 2/2/2015 9:00 15
2/2/2015 2/2/2015 9:15 10
I've searched to find a way to organize the data in the way that I need and this is the closest that I've been able to find so far though I haven't been able to get it to work:
Splitting time + duration into intervals in t-sql
I'm pretty new to SQL so any explanation of solutions would be much appreciated. This is also my first post on stackoverflow so please let me know if the data are not in the preferred format or if there any additional questions. Thanks!
Assuming some reasonable recent version of SQL Server, this ought to be a good start:
-- Some sample data.
declare @Samples as Table ( SampleId Int Identity, Start DateTime, Stop DateTime );
insert into @Samples ( Start, Stop ) values
( '2/2/2015 7:00', '2/2/2015 7:25' ),
( '2/2/2015 7:20', '2/2/2015 7:29' ),
( '2/2/2015 7:35', '2/2/2015 7:42' ),
( '2/2/2015 8:05', '2/2/2015 8:14' ),
( '2/2/2015 8:16', '2/2/2015 8:20' ),
( '2/2/2015 8:29', '2/2/2015 8:40' ),
( '2/2/2015 8:55', '2/2/2015 9:25' );
select * from @Samples;
-- Find the limits and align them to quarter hours.
declare @Min as DateTime;
declare @Max as DateTime;
select @Min = min( Start ), @Max = max( Stop )
from @Samples;
set @Min = DateAdd( minute, -DatePart( minute, @Min ) % 15, @Min );
set @Max = DateAdd( minute, 15 - DatePart( minute, @Max ) % 15, @Max );
select @Min as [Min], @Max as [Max];
-- Go for it.
with QuarterHours ( QuarterStart, QuarterStop )
as (
select @Min, DateAdd( minute, 15, @Min )
union all
select QuarterStop, DateAdd( minute, 15, QuarterStop )
from QuarterHours
where QuarterStop < @Max ),
Overlaps
as ( select QH.QuarterStart, QH.QuarterStop, S.Start, S.Stop,
case
when S.Start <= QH.QuarterStart and S.Stop >= QH.QuarterStop then 15
when S.Start <= QH.QuarterStart and S.Stop < QH.QuarterStop then DateDiff( minute, QH.QuarterStart, S.Stop )
when S.Start > QH.QuarterStart and S.Stop >= QH.QuarterStop then DateDiff( minute, S.Start, QH.QuarterStop )
when S.Start > QH.QuarterStart and S.Stop < QH.QuarterStop then DateDiff( minute, S.Start, S.Stop )
else 0 end as Overlap
from QuarterHours as QH left outer join
@Samples as S on S.Start <= QH.QuarterStop and S.Stop >= QH.QuarterStart )
select QuarterStart, sum( Overlap ) as [ActivityTime]
from Overlaps
group by QuarterStart
order by QuarterStart;
You can change the last select to either select * from QuarterHours or select * from Overlaps to see some of the intermediate values.
Explanatory notes:
You can use any range (@Min/@Max) you want, I just took them from the sample data so that the example would run. I used a table variable for the same reason, no need to create a "real" table for the sake of an example.
The Common Table Expression (CTE) creates, via recursion, a table of QuarterHours that covers the desired range. (A numbers table or tally table could also be used to generate the quarter hours.) Then a LEFT OUTER JOIN with the sample data is used to locate all of the Overlaps, if any, with each quarter hour. That preserves the quarter hours for which there is no activity.
The final SELECT summarizes the results.