Re: sql query help

<jeffchirco_at_gmail.com> a écrit dans le message de news: 1144433792.309380.275330_at_e56g2000cwe.googlegroups.com...
| 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
|
|

As always for this kind of question:

- Oracle version
- test case: create and insert statements
- wanted result for the test case

Regards
Michel Cadot Received on Fri Apr 07 2006 - 20:38:05 CEST