Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.tools -> sql query help

sql query help

From: <jeffchirco_at_gmail.com>
Date: 7 Apr 2006 11:16:32 -0700
Message-ID: <1144433792.309380.275330@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 Received on Fri Apr 07 2006 - 13:16:32 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US