Multiple Values from the Same Table, but Differently 1

[Genimuse]

Greetings. Technically I'm using Jet SQL, but I don't think anything I'm trying to do isn't ANSI compatible, so I thought I'd try my question here.

I have two table, like this:

[b]MedicarePatients[/b]
ID           -- Primary Key
LastName
FirstName
...etc...
Hold         -- Boolean 

[b]MedicareCheckups[/b]
ID           -- Foreign key, with MedicarePatients' ID field
Checkup      -- DateTime
Scheduled    -- Boolean

There's a one-to-many relationship between MedicarePatients and MedicareCheckups.

Here's the select that I currently have. Among other things, it's designed to select all of the patients and the maximum date from checkups (and has an additional join so I can know whether this date is Scheduled or not). It looks like this:

SELECT P.ID, P.LastName, P.FirstName, P.Chart, P.BirthdayMonth, P.BirthdayDay, P.Phone, P.MCStatus, P.Hold, D.LastDate + IIF(S.Scheduled, 0, 28) AS ScheduleDate, S.Scheduled, P.Notes 
FROM (
    MedicarePatients P 
    INNER JOIN (
        SELECT ID, Max(Checkup) As LastDate 
        FROM MedicareCheckups 
        GROUP BY ID) D 
    ON P.ID = D.ID) 
INNER JOIN MedicareCheckups S 
ON D.ID=S.ID AND D.LastDate=S.Checkup 
WHERE P.Hold = False AND Checkup > #8/20/2004# 
ORDER BY D.LastDate + IIF(S.Scheduled, 0, 28) ASC, P.LastName ASC

What I need to differently now, though, is rather than just getting the latest checkup date and whether it's scheduled or not, I need to get both the latest scheduled date and the latest non-scheduled date (with the current trick where non-scheduled dates get 28 days added to them, such that they sort the way I need).

If I can have both of those then I won't need that scheduled flag, since I want both (and nulls in those fields where there simply isn't a scheduled or unscheduled checkup for that patient).

I tried adding a different join in myself, but my SQL skills just aren't strong enough. Any tips or ideas?

 

[r937]

select P.ID
     , P.LastName
     , P.FirstName
     , P.Chart
     , P.BirthdayMonth
     , P.BirthdayDay
     , P.Phone
     , P.MCStatus
     , S.Checkup  AS LatestScheduledCheckup
     , N.Checkup  AS LatestNonScheduledCheckup
     , P.Notes 
  from (
       MedicarePatients P 
left outer 
  join MedicareCheckups S
    on (
       P.ID = S.ID
   and S.Checkup
     = ( select max(Checkup)
           from MedicareCheckups
          where ID = P.ID
            and Checkup > #8/20/2004# 
            and Scheduled = True )
       )
       )
left outer  
  join MedicareCheckups N 
    on (
       P.ID = N.ID 
   and N.Checkup
     = ( select max(Checkup)
           from MedicareCheckups
          where ID = P.ID
            and Checkup > #8/20/2004# 
            and Scheduled = False )
       )
 where P.Hold = False 
order 
    by P.LastName asc

rudy

http://r937.com/

SQL Consulting

 

[Genimuse]

Ah yes, that's the idea, cool.

Unfortunately I'm getting a syntax error. It specifically complains about this section:

       P.ID = S.ID
    and S.Checkup
     = ( select max(Checkup)
           from MedicareCheckups
          where ID = P.ID
            and Checkup > #8/20/2004# 
            and Scheduled = True )

but the parser is pretty stupid and it could easily be somewhere else. Can you spot a syntax error, or might it be the stupidity of Jet SQL?

 

[PHV]

You may try this:
SELECT P.ID, P.LastName, P.FirstName, P.Chart, P.BirthdayMonth, P.BirthdayDay, P.Phone, P.MCStatus, P.Hold
, S.LastScheduled, U.LastUnscheduled, 28+U.LastUnscheduled AS NextSchedule, P.Notes
FROM (MedicarePatients P
LEFT JOIN (
SELECT ID, Max(Checkup) As LastScheduled
FROM MedicareCheckups WHERE Scheduled=True AND Checkup>#2/20/2004#
GROUP BY ID
) S ON P.ID=S.ID)
LEFT JOIN (
SELECT ID, Max(Checkup) As LastUnscheduled
FROM MedicareCheckups WHERE Scheduled=False AND Checkup>#2/20/2004#
GROUP BY ID
) U ON P.ID=U.ID
WHERE P.Hold=False
ORDER BY IIF(S.LastScheduled>28+U.LastUnscheduled, S.LastScheduled, 28+U.LastUnscheduled), P.LastName

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Genimuse]

Excellent, PHV, thanks, that was very close to what I needed, fantastic! A little tweaking and I'm finally done with this query.

r937, thanks for your help, too. Unfortunately I got a syntax error with that and couldn't spot the problem.

 

[PHV]

A little tweaking and I'm finally done with this query.
Can you please explain the members how you finally achieved your goal ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Genimuse]

You bet. The tweaking I needed was just to the additional date field and the sort order. I needed them sorted by either the latest scheduled date, or if there was none, the latest actual (non-scheduled) date plus 28 days. As such I just changed the third date field and the order by, with the final result like this (blue where I tweaked):

SELECT P.ID, P.LastName, P.FirstName, P.Chart, P.BirthdayMonth, P.BirthdayDay, P.Phone, P.MCStatus,  U.LastUnscheduled, S.LastScheduled, [COLOR=blue]IIF(S.LastScheduled IS NULL, U.LastUnscheduled + 28, S.LastScheduled) AS LastOrder[/color], P.Notes 
FROM (MedicarePatients P 
LEFT JOIN (
    SELECT ID, Max(Checkup) As LastScheduled 
    FROM MedicareCheckups WHERE Scheduled=True AND Checkup>#2/20/2004#
    GROUP BY ID
) S ON P.ID=S.ID) 
LEFT JOIN  (
    SELECT ID, Max(Checkup) As LastUnscheduled 
    FROM MedicareCheckups WHERE Scheduled=False AND Checkup>#2/20/2004#
    GROUP BY ID
) U ON P.ID=U.ID 
WHERE P.Hold=False 
ORDER BY [COLOR=blue]IIF(S.LastScheduled IS NULL, U.LastUnscheduled + 28, S.LastScheduled)[/color], P.LastName, P.FirstName

 

[Genimuse]

I also had to move the WHERE date-related qualifier to the outside of the whole thing, since they weren't really providing any useful change inside the sub-selects, a la:

SELECT P.ID, P.LastName, P.FirstName, P.Chart, P.BirthdayMonth, P.BirthdayDay, P.Phone, P.MCStatus, U.LastUnscheduled, S.LastScheduled, IIF(S.LastScheduled IS NULL, U.LastUnscheduled + 28, S.LastScheduled) AS LastOrder, P.Notes 
FROM (MedicarePatients P 
LEFT JOIN (
    SELECT ID, Max(Checkup) As LastScheduled 
    FROM MedicareCheckups WHERE Scheduled=True
    GROUP BY ID
) S ON P.ID=S.ID) 
LEFT JOIN  (
    SELECT ID, Max(Checkup) As LastUnscheduled 
    FROM MedicareCheckups WHERE Scheduled=False
    GROUP BY ID
) U ON P.ID=U.ID 
WHERE P.Hold=False AND [COLOR=blue]IIF(S.LastScheduled IS NULL, U.LastUnscheduled + 28, S.LastScheduled) > #8/10/2004#[/color]
ORDER BY IIF(S.LastScheduled IS NULL, U.LastUnscheduled + 28, S.LastScheduled), P.LastName, P.FirstName