Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Complex Query

Re: Complex Query

From: Daniel Morgan <damorgan_at_x.washington.edu>
Date: Thu, 05 Feb 2004 08:06:50 -0800
Message-ID: <1075997156.210740@yasure>


Sameer wrote:

> Hi There,
>
> I am facing a problem in writing a query/PL-SQL procedure for the
> following. We have a requirement to list the month-wise count of
> resolved and pending fraud cases for the last 6 months. Our table
> contains the columns case_id, start_date, end_date along with some
> other columns and the business rules as are as follows.
>
> If a case has been opened and resolved in the same month, then it is
> counted as a resolved case, otherwise it is 'pending' for the month in
> which it has been started and the following months, until it is
> 'resolved' in the month in which it has been closed.
>
> For ex, Consider the following.
> 1. case1 has been opened on 1Apr2003 and closed on 2May2003
> 2. case2 has been opened on 15Apr2003 and closed on 20Apr2003
> 3. case3 has been opened on 1May2003 and closed on 3Aug2003.
>
> So when I run a query in Oct2003, result should be as below:
>
> Apr May Jun July Aug Sept
> Resolved 1(case2) 1(case1) 0 0 1 0
> Pending 1(case1) 1(case3) 1(case3) 1(case3) 0 0
>
> we need to show the count only. the case_id has been mentioned just
> for understanding.
>
> Hope I sound clear;-)
> reply asap
>
> TIA,
> Sameer

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.

-- 
Daniel Morgan
http://www.outreach.washington.edu/ext/certificates/oad/oad_crs.asp
http://www.outreach.washington.edu/ext/certificates/aoa/aoa_crs.asp
damorgan_at_x.washington.edu
(replace 'x' with a 'u' to reply)
Received on Thu Feb 05 2004 - 10:06:50 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US