October 29, 2014 at 8:48 am
Hi
I am trying to produce a report that will show a duration in minutes of a time when a room was occupied for a category. Whilst I have the start and end dates and times, the end user must be able to specify not only a range of dates, but a start and endtime of hours in the day in which they are interested in (it will be applied to all days in the range - they are not allowed to specify a different start/endtime per day).
The example I have is a date range of 6 to 17 October, but they only want the times from 09:00 to 21:00, so if a room was occupied from 08:00 to 11:00 they would only want to know the duration as 120 minutes (09:00 to 11:00) not 180.
The data is supplied by a third party, and duration in minutes is supplied, but it is not much use when they are not interested in the 'real' duration.
CREATE TABLE [dbo].[Evts](
[Location_name] [nvarchar](200) NULL,
[Event_Category] [nvarchar](500) NULL,
[Start_Time] [datetime] NULL,
[End_Time] [datetime] NULL,
[Duration] [int] NULL
)
insert into Evts values
('RS8','SODT','2014-10-07 14:00:00.000','2014-10-07 17:00:00.000',180),
('RS8','SODT','2014-10-07 10:00:00.000','2014-10-07 13:00:00.000',180),
('RS8','SODT', '2014-10-08 14:00:00.000','2014-10-08 17:00:00.000',180),
('RS8','SODT','2014-10-08 10:00:00.000','2014-10-08 13:00:00.000',180),
('RS8','CM','2014-10-06 14:00:00.000','2014-10-06 16:00:00.000',120),
('RS8','SODT','2014-10-09 14:00:00.000','2014-10-09 17:00:00.000',180),
('RS8','SODT','2014-10-09 10:00:00.000','2014-10-09 13:00:00.000',180),
('RS8','SODT','2014-10-10 10:00:00.000','2014-10-10 13:00:00.000',180),
('RS8','TR','2014-10-10 13:00:00.000','2014-10-10 14:00:00.000',60),
('RS8','SODTNT','2014-10-10 14:00:00.000','2014-10-10 17:00:00.000',180),
('RS8','CM','2014-10-13 14:00:00.000','2014-10-13 16:00:00.000',120),
('RS8','OOT','2014-10-13 08:00:00.000','2014-10-13 14:00:00.000',360),
('RS8','SODT','2014-10-14 14:00:00.000','2014-10-14 17:00:00.000',180),
('RS8','SODT','2014-10-15 14:00:00.000','2014-10-15 17:00:00.000',180),
('RS8','SODT','2014-10-15 09:00:00.000','2014-10-15 11:00:00.000',120),
('RS8','SCC','2014-10-16 18:00:00.000','2014-10-16 20:30:00.000',150),
('RS8','SODT','2014-10-16 14:00:00.000','2014-10-16 17:00:00.000',180),
('RS8','SODTB','2014-10-17 14:00:00.000','2014-10-17 15:00:00.000',60),
('RS8','OD','2014-10-17 08:00:00.000','2014-10-17 14:00:00.000',360),
('RS8','OD','2014-10-17 15:00:00.000','2014-10-17 18:00:00.000',180)
select
e.Location_name, e.Event_Category, SUM(duration) as duration
from evts e
group by Location_name, Event_category
--gives
Location_nameEvent_Categoryduration
RS8CM240
RS8OD540
RS8OOT360
RS8SCC150
RS8SODT1740
RS8 SODT180
RS8SODTB60
RS8SODTNT180
RS8TR60
---but the expected output is
Location_nameEvent_Categoryduration
RS8CM240
RS8OD480 -- eliminating 08:00 - 09:00 on 17th
RS8OOT300 -- eliminating 08:00 - 09:00 on 13th
RS8SCC150
RS8SODT1740
RS8 SODT180
RS8SODTB60
RS8SODTNT180
RS8TR60
Can anyone help point me in the right direction with regard to the minute calculation when the start time specified by the user differs from that of the actual startime?
Many thanks
Bex
October 29, 2014 at 10:05 am
This should point you in the right direction:
select e.*, datediff(minute,
case
when convert(time,e.Start_Time) < '09:00'
then convert(time,'09:00:00')
else convert(time,e.Start_Time)
end,
case
when convert(time,e.End_Time) > '21:00'
then convert(time,'21:00:00')
else convert(time,e.End_Time)
end
) as mins
from @Evts e
You might have to put this in a subquery and then do the sum on the new "mins" column rather than the old "duration" column.
October 29, 2014 at 10:15 am
Thanks Daniel.
I think I was just having one of those "can't see the wood for the trees days."
Appreciate your time and input.
Bex
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply