Query with dates

[Mike Hughes]

I have a table called NOLDBA_RPT_CASE_CHG_IND. The table has the fields shown below. The case status field can be “O” (open), “C” (closed), “H” (private). Each case in the system has several line entries for the date whenever the case status changes from one status to another. I’m trying to write a query to determine the dates when the case was in the “O” status. Can someone help me?

NOLDBA_RPT_CASE_CHG_IND.SYS_CURR_DATE,
NOLDBA_RPT_CASE_CHG_IND.CASE_ID,
NOLDBA_RPT_CASE_CHG_IND.BEF_CASE_STATUS,
NOLDBA_RPT_CASE_CHG_IND.AFT_CASE_STATUS,

An example:
Query1
SYS_CURR_DATE CASE_ID BEF_CASE_STATUS AFT_CASE_STATUS
5/14/1992 123 C
3/21/2007 123 C H
5/25/2010 123 H O
In this case the case when from “C” to H on 3/21/2007
Then on 5/25/2010 it went from “H” to “O”.
So it was in the “O” status between 5/25/2010 and today

In some cases the case the table can look like this, changing back and forth from O to H and H to O

Query1
SYS_CURR_DATE CASE_ID BEF_CASE_STATUS AFT_CASE_STATUS
8/3/1994 1234 H
2/9/2010 1234 H C
4/22/2010 1234 C O
7/8/2010 1234 O O
9/7/2010 1234 O H

 

[DCrake]

Working on the fact that when the record is first created it has a default status of Open, you want create your query basd on all records where status does not equal Open.

 

[Mike Hughes]

Was that a statement or a question?

 

[DCrake]

Bit of both.

When a record is first created I assumed that the status is set at open by default. And until someone changes it it will remain open. So to find out when a record changes its status from Open to either closed or private do a search on closed and or private.

 

[Mike Hughes]

ok
thanks for the help