Suppose I have data similar to below. What I am having trouble with is
getting a SQL statement to return all the centers based on a FROM and
TO date where their status stayed as "O" the whole time. Now if
their status changed to something other than "O", I want to also
return that center along with the status it changed to. Now the
forkball in the problem is if the center changed to something other
than "O" and then back to "O" on the same day (ie. DBCTR=1052),
that needs to be considered as not having a status change and would
need to return a status of "O". I have a field called
DBLAST_REC_FLAG that helps this problem. If there are more than one
record on a day, then the last record of the day will get a flag of 1,
the others on that day will be 0. Need to just return the DBCTR and
DBSTATUS field by passing in a START date and an END date. I just
can't seem to get this figured out. Thanks for any help in advance.
DBCTR DBEFF_DATE DBTHRU_DATE DBSTATUS
DBLAST_REC LDBLAST_REC_FLAG
1 10/14/2004 12:00:00 PM 11/9/2004 12:00:00 PM O
1
1 11/9/2004 12:00:00 PM 11/9/2004 1:00:00 PM R
0
1 11/9/2004 1:00:00 PM 12/27/2004 12:00:00 PM O
1
1 12/27/2004 12:00:00 PM 12/26/2005 12:00:00 PM O
1
1 12/26/2005 12:00:00 PM
O 1
2 6/2/2004 12:00:00 PM 12/27/2004 12:00:00 PM O
1
2 12/27/2004 12:00:00 PM 12/26/2005 12:00:00 PM O
1
2 12/26/2005 12:00:00 PM 3/14/2006 12:00:00 PM O
1
2 3/14/2006 12:00:00 PM
O 1
3 12/27/2004 12:00:00 PM 12/26/2005 12:00:00 PM O
1
3 12/26/2005 12:00:00 PM
O 1
4 10/14/2004 12:00:00 PM 12/9/2004 12:00:00 PM R
1
4 12/9/2004 12:00:00 PM 12/26/2005 12:00:00 PM O
1
4 12/26/2005 12:00:00 PM
Received on Fri Apr 07 2006 - 13:16:32 CDT
