Complex Oracle query involving max date

I'm having trouble getting the data I want from an Oracle database. Any help would be greatly appreciated. Here's a sample of what my table looks like:

Table: Vaccinations

Patient_ID | Shot_ID | Series | Date_Taken
-------------------------------------------
123        | 5       | B      | 8/1/2011
123        | 5       | 3      | 2/1/2011
123        | 5       | 2      | 1/10/2011
123        | 5       | 1      | 1/1/2011
456        | 3       | 2      | 1/10/2011
456        | 3       | 1      | 1/1/2011
123        | 5       | 2      | 10/1/2010
123        | 5       | 1      | 9/1/2010

The series column indicates which shot was administered for a particular Shot_ID. 'B' indicates a booster was given, '2' means second, '1' means first, and so on but '3' is the max and then boosters follow. What I'm trying to do is grab all of the latest series of shots for a patient, for a particular type of shot (Shot_ID). For example, I want to grab patient 123's latest series of shots for Shot_ID = 5, so I would want to return the first four records in this case (all colums should be returned in those rows). The last two should be omitted because a new series of shots was started on 1/1/2011. Anyway, I have an algortihm in mind, but I'm having trouble writing the query for it. It would go something like this:

1. Get the Max Date for patient 123, shot_id = 5. Return the row and look at its series (in this case, 'B').

2. Get the next lowest date from the Max Date and look at its series (in this case, '3'). If the series is between 1 and B, return the row. If no other records exist, then end the query.

3. Get the next lowest date from step 2 and look at its series (in this case, '2'). If the series is less than the series from step 2, return the row. Otherwise, end the query.

You keep repeating th开发者_C百科ese steps till you get to series = 1, which is returned or till you reach a series that is greater than or equal to the current series, which is not returned. So , the output should look like this:

123 | 5 | B | 8/1/2011
123 | 5 | 3 | 2/1/2011
123 | 5 | 2 | 1/10/2011
123 | 5 | 1 | 1/1/2011

This query seems to be pretty complex, but maybe I'm just overthinking it. Thank y'all for your time.

I'd take a two-pronged approach. Get the most recent "series 1" shot and get all subsequent series after that one.

SELECT Patient_ID, Shot_ID, Series, Date_Taken
    FROM Vaccinations v
    WHERE Patient_ID = 123
        AND Shot_ID = 5
        AND Date_Taken >= (SELECT MAX(Date_Taken)
                               FROM Vaccinations v
                               WHERE Patine_ID = 123
                                   AND Shot_ID = 5
                                   AND Series = 1)

This queries ignores any shots where there are newer ones with the same or lower series (for same Patient_ID and Shot_ID):

select s.*
from Shot s
inner join (
    select Patient_ID, Shot_ID, max(Date_Taken) as MaxDate
    from Shot
    group by Patient_ID, Shot_ID
) sm on s.Patient_ID = sm.Patient_ID and s.Shot_ID = sm.Shot_ID
inner join Shot s2 on sm.Patient_ID = s2.Patient_ID and sm.Shot_ID = s2.Shot_ID and sm.MaxDate = s2.Date_Taken
where (s.Date_Taken = sm.MaxDate
        or (
            case when s.Series = 'B' then 4 else s.Series end < case when s2.Series = 'B' then 4 else s2.Series end
            and not exists (
                select 1 
                from Shot
                where Date_Taken > s.Date_Taken
                    and Shot_id = s.Shot_ID
                    and case when Series = 'B' then 4 else Series end <= case when s.Series = 'B' then 4 else s.Series end
            )
        )
    )
    and s.Patient_ID = 123  

select * from vaccinations as v
  inner join (
    select a.series , max(a.date_taken) as max_date
      from vaccinations as a, vaccinations as b 
      where a.series = b.series 
            and not exists (
              select * from vaccinations as c where c.series = a.series 
                       and c.date_taken between a.date_taken and b.date_taken)) as m
    on v.date_taken >= m.max_date and patient_id = 123 and shot_id = 5

not exists - to keep pairs of repeating series from which to start (or stop)... but if there only 1 series there problem

I would do:

SELECT v1.* FROM vaccinations v1
  LEFT JOIN vaccinations v2
    ON v2.patient_id = v1.patient_id AND v2.shot_id = v1.shot_id
   AND v2.series = '1' AND v2.date_taken > v1.date_taken
 WHERE v2.series IS NULL
   AND v1.patient_id = '123'
   AND v1.shot_id = '5';

The left join looks up for the start of a new shot that is more recent. If it does not return any row (IS NULL) then this is the latest shot. This is equivalent to:

SELECT * FROM vaccinations v1
 WHERE patient_id = '123'
   AND shot_id = '5'
   AND NOT EXISTS (SELECT NULL FROM vaccinations v2
                    WHERE v2.patient_id = v1.patient_id AND v2.shot_id = v1.shot_id
                      AND v2.series = '1' AND v2.date_taken > v1.date_taken
                  );

If the latest series does not necessarily begin with 1, then:

WITH all_series AS
   ( SELECT rownum rn, series, date_taken
       FROM vaccinations
      WHERE patient_id = '123' AND shot_id = '5' AND series <> 'B'
      ORDER BY date_taken ASC
   )
   , last_series_beginning AS
   ( SELECT MAX(as1.date_taken) x
       FROM all_series as1
       LEFT JOIN all_series as2        -- we need to keep first row in as1
         ON as2.rn = as1.rn - 1
      WHERE as1.series < as2.series
         OR as1.rn = 1
   )
SELECT * FROM vaccinations
 WHERE patient_id = '123' AND shot_id = '5'
   AND date_taken > ( SELECT x FROM last_series_beginning );

 `select A.patient_id , A.shot_id , A.series , max(date_taken)
From cs_study A
inner join 
(
select  max(shot_id) max_shotid, patient_id from cs_study
group by patient_id 
) b
on A.shot_id =B.max_shotid
and a.patient_id = B.patient_id 
group by A.patient_id , A.shot_id , A.series`