With ROWNUM, you can simulate a thru_date, which can then be used for ranking reports or point-in-time queries....
By submitting your email address, you agree to receive emails regarding relevant topic offers from TechTarget and its partners. You can withdraw your consent at any time. Contact TechTarget at 275 Grove Street, Newton, MA.
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.