Problem with query

[Mike Hughes]

Trying to write query, which will give me the “STAT_CASE_ID” for each case that had an “O” (open) CASE_STATUS between two dates. Some of the cases may have opened and closed during the two dates. All I want to know is what cases had a CASE_STATUS of “O” anytime during the two dates. Here is the query that I have, but this does not pick up all the cases that it should. I have also attached a sample DB. Any help would be appreciated.

SELECT DSERDBA_CASE_STATUS_CHANGE.STAT_CASE_ID
FROM DSERDBA_CASE_STATUS_CHANGE
WHERE (((DSERDBA_CASE_STATUS_CHANGE.STAT_FROM_CASE_STATUS)="O") AND ((DSERDBA_CASE_STATUS_CHANGE.STAT_BEGIN_DATE)<[BEGIN DATE]) AND ((DSERDBA_CASE_STATUS_CHANGE.STAT_END_DATE)>[END DATE] Or (DSERDBA_CASE_STATUS_CHANGE.STAT_END_DATE) Is Null)) OR (((DSERDBA_CASE_STATUS_CHANGE.STAT_BEGIN_DATE)>=[BEGIN DATE]) AND ((DSERDBA_CASE_STATUS_CHANGE.STAT_END_DATE)=[STAT_BEGIN_DATE]) AND ((DSERDBA_CASE_STATUS_CHANGE.STAT_TO_CASE_STATUS)="O"))
GROUP BY DSERDBA_CASE_STATUS_CHANGE.STAT_CASE_ID;

 

[Rickster57]

sql

See if the SQL I sent to you in private message helps.

 

[Brianwarnock]

I don't think you are doing anything like enough checking for the STAT_FROM ="O" you also need to check if the BEGIN-date or the end_date is between the dates, then do the same 3 checks for the STATS_TO ="O", in other words there are 6 conditions that can apply, assuming that neither the FROM or TO need be O , if one must be O if a change takes place then the situation would be different.

As I'm off till Monday shortly this will be my only response I hope it helps.


Brian

 

[Brianwarnock]

See if the SQL I sent to you in private message helps.

This was posted whilst I was typing my response, I hope it works for Mike but is it in the spirit of the forum?

Brian

 

[alastair69]

Going any were nice Brian

 

[Brianwarnock]

Going any were nice Brian

Nope, I retire in 4 weeks and have holidays to use up.

Brian

 

[Rickster57]

Query

Mike,
I just took a very quick look. Try this and see if this is what is needed

SELECT 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
FROM DSERDBA_CASE_STATUS_CHANGE
WHERE (((DSERDBA_CASE_STATUS_CHANGE.STAT_BEGIN_DATE)>=[BEGIN DATE]) AND ((DSERDBA_CASE_STATUS_CHANGE.STAT_END_DATE)<=[END DATE] Or (DSERDBA_CASE_STATUS_CHANGE.STAT_END_DATE)=IsNull( [end date])) AND ((DSERDBA_CASE_STATUS_CHANGE.STAT_FROM_CASE_STATUS )="O")) OR (((DSERDBA_CASE_STATUS_CHANGE.STAT_TO_CASE_STATUS) ="O"));

 

[Mike Hughes]

Query problem

That worked! Thanks to everyone for your help