Query Refinement: count and group by

Sorry for the subject, I couldn't really come up with a good/short description for what I'm trying to do.

I am refining a report for our ER department that uses a Postgres database. The managers would like a report which lists the population of our ER department by hour.

The relevant table setup looks like (excluding fields that aren't necessary).

inc_id | evttype_id | evtdate
Primary key is a composite of inc_id, evttype_id.

The inc_id is an incident id which is a foreign key to an incidents table with additional incident information, the evttype_id is an id code for the specific event (arrival, departure, triage, er, etc.) and the evtdate is a timestamp of when the event occurred. For each action that happens for a particular incident an entry is placed into this incident_event table.

I also have a view (call it all_report)which groups everything the info together so via joins and subselects it looks like:
inc_id | arrivaltime | triagetime | ertime | departuretime | other times... | patient info.... | other info.

Would it be possible to write a population by hour summary in one query?

For instance, if I wanted to do a query for each hour I could do something like:

SELECT count(*) FROM all_report WHERE arrivaltime BETWEEN '12-8-2003 02:00'::timestamp AND '12-8-2003 03:00'::timestamp AND (departuretime IS NULL OR departuretime > '12-8-2003 03:00'::timestamp)

for each hour in the day, and repeat (if necessary), however I was wondering if I could somehow come up with a more elegant solution (either with the view, or just by querying the incident_event table itself.

Thanks,
-b

PS I can provide the full table schemas I just chose to leave out unrelated portions to avoid confusion.

generate the hours using an integers table

for an example of generating months, see
http://searchdatabase.techtarget.co...x285649,00.html

let me know if you need further help

rudy
http://r937.com/

Thanks!
The minimalist in me wants to do it without an extra table, but this looks to be the most efficient way of doing it.

-b

i don't know postgresql very well, but in sql server you can generate a derived table "out of thin air" as it were

so since you only need integers 1 through 24, perhaps you could use

select stuff from
(select 1 union select 2 union ... select 24) dt
left outer join ...

You can do:
SELECT * FROM (SELECT 1 UNION SELECT 2 ...) AS tempint

but I figure I might as well just keep the int table around in case I need to do more date related stuff or any other random items that require left joins and an integer lookup stuff.

Also, just for clarification purposes for others that would like to apply this to postgres, in postgres hours are specified from 0 to 23.

Here's what the select query ended up looking like:

SELECT i AS hr, count(inc_id) AS hrcount FROM integers LEFT JOIN all_report ON i BETWEEN EXTRACT('hour' FROM arrivaltime) AND EXTRACT('hour' FROM departure) AND arrivaltime > NOW() - '1 day'::interval WHERE i BETWEEN 0 AND 23 GROUP BY hr

Thanks again r937.

-b

dontcha just *heart* the integers table?

your query looks mighty spiffy

glad i could help

get it? "i" could help? oh, never mind