Re: Oracle SQL question- grouping by "worst" status?

From: Micha³ Kuratczyk <kura_at_lj.pl>
Date: Tue, 23 May 2006 16:40:45 +0200
Message-ID: <e4v6ro$fam$1@abg.com.pl>

bbcrock_at_gmail.com wrote:
> 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
These rules doesn't make sense:
1. end_date is defined NOT NULL and you check it for being null 2. begin_date > sysdate = overdue. If a report is going to start

   in the future then why is it overdue already?

But generally, when you fix those rules, something like:

select dept_id, report_type_id, max(sysdate-begin_date) diff,   (case when max(sysdate-begin_date) > threshold_1 then 'OVERDUE'

        when max(sysdate-begin_date) > threshold_2 then 'DANGER'
        else 'GREEN'

  end)
from report
where end_date is null
group by dept_id, report_type_id;

PS It would be much easier to write the query if you provided exact CREATE TABLE and some INSERTs for testing.

-- 
Michal Kuratczyk

Received on Tue May 23 2006 - 09:40:45 CDT