PL/SQL Sum by hour

Question

Hi,

I have some data with start and stop date that I need to sum. I am not sure how to code for it.

Here are is the data I have to use:

STARTTIME,            STOPTIME,            EVENTCAPACITY
8/12/2009 1:15:00 PM, 8/12/2009 1:59:59 PM,     100

8/12/2009 2:00:00 PM, 8/12/2009 2:29:59 PM,     100

8/12/2009 2:30:00 PM, 8/12/2009 2:59:59 PM,      80

8/12/2009 3:00:00 PM, 8/12/2009 3:59:59 PM,      85

In this example I would need the sum from 1pm to 2pm, 2pm to 3pm and 3pm to 4pm

Any suggestions are appreciated.

Steve

Answer 1

How about something like:

SELECT TRUNC(stoptime,'HH'), sum(eventcapacity) 
  FROM yourtable
 GROUP BY TRUNC(stoptime,'HH');

Answer 2

I'm not sure about exact PL/SQL syntax, but something like this should do it (though it's pretty unweildy):

select sum(capacity), case when to_char(starttime, 'HH') between '13' and '14'
                            and to_char(stoptime, 'HH') between '13' and '14'
                            then '1pm-2pm'
                      case when to_char(starttime, 'HH') between '14' and '15'
                            and to_char(stoptime, 'HH') between '14' and '15'
                            then '2pm-3pm'
                      (etc.) 
                      as timeslot
from eventtable
group by timeslot

Answer 3

You need a numbers table:

select sum(e.capacity), n.value from eventtable e
left outer join numbers n on n.value between
    extract(hours from e.starttime) and extract(hours from e.stoptime)
where n.value between 0 and 23
group by n.value
order by n.value

A numbers table has a single column (value) and is filled with integer values from 0 to 100 (or more) although in this case you only need 0 to 23.

create table number (
    value number(4) not null,
    primary key (value)
);