bbcrock_at_gmail.com wrote:
> 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
>
with status as (
SELECT DEPT_ID,REPORT_TYPE_ID,CASE
WHEN BEGIN_DATE > SYSDATE THEN 'RED'
WHEN BEGIN_DATE+7>SYSDATE THEN 'YELLOW'
ELSE 'GREEN'
END as stat
from report)
select dept_id,report_type_id,stat,count(stat)
from status
group by rollup(dept_id,report_type_id,stat)
--
Mladen Gogala
http://www.mgogala.com
Received on Tue May 23 2006 - 09:55:19 CDT
