Have Geek Brain

PL / SQL Sum per hour

I have data with start and end dates that I need to summarize. I'm not sure how to code it.

Here's the data I should be using:

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 will need the amount from 1pm to 2pm, 2pm to 3pm, and 3pm to 4pm

Any suggestions are greatly appreciated.

Steve

+2


a source to share


3 answers


How about something like:



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


a source


You need a table of numbers:

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


The number table has one column (value) and is populated 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)
);
+1


a source


I'm not sure about the exact syntax of PL / SQL, but something like this should do it (although it's rather 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
0


a source






More articles:


All Articles