Query help

[northwest]

Hi,
I have to write a query. where I need, to utilize the Case Status field and see within the time period of the query(month) when a case status goes from Closed to Active for particular caseID


CASE ID DATE STATUS
ABC 2-Jun ACTIVE
DSA 4-Jun ACTIVE
EDD 4-Jun CLOSED
QDK 3-May ACTIVE
ASD 2-May CLOSED
ABC 4-Jun CLOSED
DAJ 4-Feb ACTIVE
FKD 5-Feb CLOSED
ABC 10-Jun ACTIVE

Here in the above table, Case ID ABC has 3 records in the month of june which started with active(2/june) and
was closed on (4/june) and again was made active on (10/june), I have to get the count of the case ID of cases which goes from closed to active just using a SQL Query(No Stored Procs)


Thanks in advance.

[OraMaster]

Hi,
I have to write a query. where I need, to utilize the Case Status field and see within the time period of the query(month) when a case status goes from Closed to Active for particular caseID


CASE ID DATE STATUS
ABC 2-Jun ACTIVE
DSA 4-Jun ACTIVE
EDD 4-Jun CLOSED
QDK 3-May ACTIVE
ASD 2-May CLOSED
ABC 4-Jun CLOSED
DAJ 4-Feb ACTIVE
FKD 5-Feb CLOSED
ABC 10-Jun ACTIVE

Here in the above table, Case ID ABC has 3 records in the month of june which started with active(2/june) and
was closed on (4/june) and again was made active on (10/june), I have to get the count of the case ID of cases which goes from closed to active just using a SQL Query(No Stored Procs)


Thanks in advance.

Hope it will help.

SELECT   caseid, status, TO_CHAR (casedt, 'MON'), COUNT (*)
    FROM sqlhelp
   WHERE UPPER (status) = 'ACTIVE'
GROUP BY caseid, status, TO_CHAR (casedt, 'MON')
  HAVING COUNT (*) > 1