Simulate a thru_date for point-in-time queries

With ROWNUM I can simulate a thru_date, which can then be used for ranking reports or point-in-time queries.

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 tdichiara@techtarget.com 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.

This was first published in January 2002

Dig Deeper

PRO+

Content

Find more PRO+ content and other member only offers, here.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close