Oracle SQL question- grouping by "worst" status?

I got such good responses yesterday, I have another question.

I have an Oracle 9i table that looks like this (pesudocode):

Report
(

  Report_ID       NUMBER(9)                        NOT NULL,
  DEPT_ID        NUMBER(9)                        NOT NULL,
  Report_TYPE_ID       NUMBER(9)                        NOT NULL,
  END_DATE     DATE                             NOT NULL,
  DESCRIPTION  VARCHAR2(4000)                   NOT NULL,
  BEGIN_DATE   DATE                             NOT NULL

I need to group these by DEPT_ID and Report_Type_ID and determine the status of all the items in that category. It will be something like: For Department 1 and Report Type 1, are any of the reports overdue?

Overdue (red) - begin_date > sysdate and end_date is null Danger (yellow) - begin_date + 7 > sysdate and end_date is null On Schedule (green) - begin_date + 7 < sysdate or end_date not null Null - No records for that Department and Report

My hopeful result set would be something like:

Dept 1 / Report Type 1   Red
Dept 1 / Report Type 2   Green
Dept 1 / Report Type 3   Null

The issue is that we will have multiple records per group. Any time something is overdue or in danger then the result I need to send back is overdue or in danger even if there are dozens of "on schedule" items.

Can this be done in a single query? I presume only with detailed subselects.

Where's Joe Celko these days? this reminds me of his SQL puzzles.

Thanks!

Don Received on Tue May 23 2006 - 09:03:34 CDT