CODEWITHSUNDEEP
sqloracle-databasestored-proceduresplsql
Steve
Steve

Reputation: 1058

PL/SQL Sum by hour

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

Upvotes: 2

Views: 2969

Answers (3)

jmucchiello
jmucchiello

Reputation: 18984

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)
);

Upvotes: 1

tzaman
tzaman

Reputation: 47850

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

Upvotes: 0

DCookie
DCookie

Reputation: 43533

How about something like:

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

Upvotes: 4

Related Questions