Re: Complex Query

From: Dom <notreal_at_notreal.com>
Date: Sun, 08 Feb 2004 06:36:07 GMT
Message-ID: <pan.2004.02.08.06.34.02.736908.17463@notreal.com>

On Thu, 05 Feb 2004 11:06:50 -0500, Daniel Morgan wrote:

> You aren't looking for a SQL statement ... this is not about "a query"
> this is a situation where a stored procedure would be the best solution.
>
> Not to say someone couldn't write it ... but if they did ... you
> couldn't maintain it.
 

Well, let's see if this is really that complicated. Admittedly I haven't tried this out, but maybe it'll give the original requestor some ideas:

SELECT TO_CHAR(month.first_day, 'MON') as month,

       COUNT(CASE WHEN case.end_date BETWEEN month.first_day and
       month.last_day
                  THEN 1
                  ELSE NULL) AS resolved_cases,
       COUNT(CASE WHEN month.last_day BETWEEN case.start_date AND
       case.end_date
                  THEN 1
                  ELSE NULL) as pending_cases

FROM
  (SELECT ADDMONTHS(TRUNC(SYSDATE, 'MM'), -ROWNUM) as first_day,

          ADDMONTHS(TRUNC(SYSDATE, 'MM'), -ROWNUM+1) as last_day    FROM all_objects
   WHERE ROWNUM <= 6) month,
  case
GROUP BY month.first_day

So what's happening here ? For Sameer's benefit: The "all_objects" inner query is just being used to get 6 rows -- it'll give us the first and last days of the last 6 months.
All the magic happens in the "CASE WHEN" statements. You could use DECODE as well, but I find this is prettier. Did I mention that I haven't tried this?
The count function is rather simple: it counts a row if it's not null, and otherwise it doesn't. Doesn't seem too hard to read, does it?

Hopefully your newsreader won't mangle the formatting.

Rgds.

I. Received on Sun Feb 08 2004 - 00:36:07 CST