Complex Query

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 Received on Thu Feb 05 2004 - 07:44:35 CST