Help adding something to this query

[Mike Hughes]

I have a table called DSERDBA_CASE_STATUS_CHANGE and it contains the following fields:

DSERDBA_CASE_STATUS_CHANGE.STAT_CASE_ID,
DSERDBA_CASE_STATUS_CHANGE.STAT_BEGIN_DATE,
DSERDBA_CASE_STATUS_CHANGE.STAT_END_DATE,
DSERDBA_CASE_STATUS_CHANGE.STAT_FROM_CASE_STATUS,
DSERDBA_CASE_STATUS_CHANGE.STAT_TO_CASE_STATUS,
DSERDBA_CASE_STATUS_CHANGE.STAT_USERID,
DSERDBA_CASE_STATUS_CHANGE.STAT_ACTIVITY_CODE,
DSERDBA_CASE_STATUS_CHANGE.STAT_COMMAND,
DSERDBA_CASE_STATUS_CHANGE.STAT_REASON_CODE

I’m looking to find CASE_ID's where the BEGIN_DATE is greater than 09/30/2005 and less than 10/01/2006 where the CASE_STATUS went from O to C and where the STAT_REASON_CODE does not equal INT27 and I have come up with this query which works fine.

SELECT
DISTINCT
DSERDBA_CASE_STATUS_CHANGE.STAT_CASE_ID,
DSERDBA_CASE_STATUS_CHANGE.STAT_BEGIN_DATE,
DSERDBA_CASE_STATUS_CHANGE.STAT_END_DATE,
DSERDBA_CASE_STATUS_CHANGE.STAT_FROM_CASE_STATUS,
DSERDBA_CASE_STATUS_CHANGE.STAT_TO_CASE_STATUS,
DSERDBA_CASE_STATUS_CHANGE.STAT_ACTIVITY_CODE,
DSERDBA_CASE_STATUS_CHANGE.STAT_COMMAND,
DSERDBA_CASE_STATUS_CHANGE.STAT_REASON_CODE

FROM DSERDBA_CASE_STATUS_CHANGE

WHERE
(((DSERDBA_CASE_STATUS_CHANGE.STAT_BEGIN_DATE)>#9/30/2005# And
(DSERDBA_CASE_STATUS_CHANGE.STAT_BEGIN_DATE)<#10/1/2006#) AND
((DSERDBA_CASE_STATUS_CHANGE.STAT_FROM_CASE_STATUS)="O") AND
((DSERDBA_CASE_STATUS_CHANGE.STAT_TO_CASE_STATUS)="C") AND
((DSERDBA_CASE_STATUS_CHANGE.STAT_REASON_CODE)<>"INT27"));

My problem is that I also want to exclude cases where the case went from CASE_STATUS C to O on the same date that it went from O to C (it closed and reopened on the same day) And I don’t know how to adjust this query to accomplishment this.

Can someone show me how? Thanks

 

[gromit]

Hi Mike -

I'd break this down into smaller peices and try the NOT IN clauses. E.g.

SELECT * FROM TableName WHERE TableName.ID NOT IN (SELECT TbaleName.ID FROM tableName WHERE <some condition>)

Post back if I am not making sense.

- g