sql query help
From: <jeffchirco_at_gmail.com>
Date: 7 Apr 2006 11:16:32 -0700
Message-ID: <1144433792.309380.275330_at_e56g2000cwe.googlegroups.com>
[Quoted] 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.
4 12/26/2005 12:00:00 PM Received on Fri Apr 07 2006 - 20:16:32 CEST
Date: 7 Apr 2006 11:16:32 -0700
Message-ID: <1144433792.309380.275330_at_e56g2000cwe.googlegroups.com>
[Quoted] 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 - 20:16:32 CEST