Show Latest date <= Specified Date per person 2

[sxschech]

I'm looking for a query that can give me the latest (most current) date that is less than a specified date for each person. I found a query in thread

http://www.tek-tips.com/viewthread.cfm?qid=1281410&page=1

that seems to do this, however, I was only able to get it to provide only one record rather than 2,356 records (distinct ids), so obviously, I misunderstood how to apply it.

Here is the query modified for our table name:

SELECT * FROM (
 Select *
 from   mst_admissions_decision decision
 Where  decision.person_uid = person_uid
 And    decision_date is not null
 and    decision.decision_date <= '01-MAY-2008'
 ORDER BY decision.decision_date DESC)
WHERE rownum = 1;

Here are some sample data:

[tt]
Row# ID Period Apno DecNo DecDate Dec
1 44219 Fall 2008 1 1 12/18/2007 12:00 FX
2 44219 Fall 2008 1 2 2/28/2008 13:09 WL
3 44219 Fall 2008 1 3 3/10/2008 10:12 CC
4 44206 Fall 2008 2 1 7/2/2008 23:56 CC
5 44206 Fall 2008 2 2 3/16/2008 11:30 WA
6 44103 Fall 2008 1 1 7/21/2008 14:35 CC
[/tt]

Based on the criteria, I would like to return rows 3 and 5 as these fall within the date specified.

 

[LKBrwnDBA]

Try this:

SELECT *
  FROM (SELECT   person_uid, MAX (decision_date) decision_date
            FROM mst_admissions_decision decision
           WHERE decision_date IS NOT NULL AND decision_date <= '01-MAY-2008'
        GROUP BY person_uid) d,
       mst_admissions_decision m
 WHERE d.person_uid = m.person_uid AND d.decision_date = m.decision_date;

----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[LKBrwnDBA]

Or this:

SELECT *
  FROM (SELECT decision.*,
               ROW_NUMBER () OVER (PARTITION BY person_uid ORDER BY decision_date DESC)
                                                                           dt
          FROM mst_admissions_decision decision
         WHERE decision_date IS NOT NULL AND decision_date <= '01-MAY-2008')
 WHERE dt = 1;

----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[sxschech]

Thank you for the two queries. The "Max" query is returning 341 more records than the "Partition" query. We are looking at them now to try and figure out which one is the one to give proper results.

 

[Dagon]

It's probably because decision_date and person_uid aren't unique. The Max query gets a unique person_id/decision_date combination and then joins back to the same table. If the person_id/decision_date is not unique, it will match more than one row. Because the second one uses ROW_NUMBER (as opposed to RANK), it is guaranteed to get only one row regardless of whether or not there is more than one record with the same decision_date.

 

[LKBrwnDBA]

True, that is the disadvantage of using the "=max()" query.




----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[Dagon]

Another commonly used technique is with subqueries:

Select *
 from   mst_admissions_decision decision
 Where  decision.person_uid = person_uid
 And    decision_date is not null
 and    decision.decision_date <= '01-MAY-2008'
 and    decision.decision_date =
(select max(d2.decision_date)
   from mst_admissions_decision d2
  where d2.person_uid = decision.person_uid
    And d2.decision_date is not null
    and d2.decision.decision_date <= '01-MAY-2008')

This would give similar results to the MAX query but you would probably need an index on the person_uid column to ensure it works efficiently.

 

[sxschech]

Thanks guys for your help. It was good that more than one type of query was provided as that helped us to identify the issue. In looking at the multiple record data returned by the max queries, we found that the extra records were due to having more than one decision on the same date. Once we identified a "tie breaker" field (decision_number), we incorporated that into the partition query and it seems to be producing the results as I couldn't figure out how to easily modify the max query.

SELECT *
 FROM (SELECT decision.*,
              ROW_NUMBER () OVER (PARTITION BY person_uid ORDER BY decision_date DESC, [red]decision_number DESC[/red])
                                                                          dt
         FROM mst_admissions_decision decision
        WHERE decision_date IS NOT NULL AND decision_date <= '01-MAY-2008'
      )
WHERE dt = 1;