With ROWNUM, you can simulate a thru_date, which can then be used for ranking reports or point-in-time queries. For example, here is the table data I have to work with:
PARTICIPANT_ID PARTICIPANT_STATUS_CD EFFECTIVE_DATE_TIME 676230 ENR 8/1/2000 676230 CAN 1/12/2001 676230 ENR 2/1/2001 676230 CAN 2/13/2001 676230 ENR 3/1/2001 676230 CAN 3/13/2001
And this is what I needed:
PARTICIPANT_ID PARTICIPANT_STATUS_CD EFFECTIVE_DATE_TIME THRU_DATE 676230 ENR 8/1/2000 1/11/2001 676230 CAN 1/12/2001 1/31/2001 676230 ENR 2/1/2001 2/12/2001 676230 CAN 2/13/2001 2/28/2001 676230 ENR 3/1/2001 3/12/2001 676230 CAN 3/13/2001 1/1/3000
Here is how you do it: NOTE: The current status is denoted by a 'NULL-DATE' value of 3000/01/01 in the through_date column.
SELECT from_date.participant_id,from_date.participant_status_cd, from_date.effective_date_time, NVL(thru_date.effective_date_time-1,TO_DATE('01013000','mmddyyyy')) thru_date -- [ we want to make the thru date one day before the next from date ] FROM (SELECT ROWNUM nbr, ps1.effective_date_time, ps1.participant_status_cd, ps1.participant_id FROM PARTIC_STATUS_HISTORY ps1 WHERE ps1.participant_id = 676230 ) from_date, (SELECT ROWNUM nbr, ps2.effective_date_time, ps2.participant_status_cd, ps2.participant_id FROM PARTIC_STATUS_HISTORY ps2 WHERE ps2.participant_id = 676230 ) thru_date WHERE from_date.nbr+1 = thru_date.nbr(+) -- [ we want to pull the from date from the following row for the participant ] AND from_date.participant_id = thru_date.participant_id(+)
For More Information
- What do you think about this tip? E-mail the Editor at firstname.lastname@example.org with your feedback.
- The Best Oracle Web Links: tips, tutorials, scripts, and more.
- Have an Oracle tip to offer your fellow DBA's and developers? The best tips submitted will receive a cool prize--submit your tip today!
- Ask your technical Oracle questions--or help out your peers by answering them--in our live discussion forums.
- Check out our Ask the Experts feature: Our SQL, database design, Oracle, SQL Server, DB2, metadata, and data warehousing gurus are waiting to answer your toughest questions.