Sometimes Adding to a Date 1

[Genimuse]

Thanks to much help here, I've got a query that works. Now I have to revisit this query, because now I need something different, something that I don't think can be done in a simple query (I suspect it would require a stored procedure), but who knows, here's the challenge:

My current query looks like this, and it works fine for the old problem:

SELECT P.ID, P.LastName, P.FirstName, P.Chart, P.BirthdayMonth, P.BirthdayDay, P.Phone, P.MCStatus, P.Hold, D.LastDate, 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 > #10/01/2004# 
ORDER BY D.LastDate ASC, P.LastName ASC

Very nifty, and thanks to everyone.

However, here's my new need: If S.Scheduled is True than I want D.LastDate just the way it is, but if S.Scheduled = False then I want D.LastDate + 28 days.

I can do this in the application layer, but the data doesn't come sorted in the order I need (by the date, modified or not), so it requires an array sort and stuff that I'd like to avoid if SQL can help me.

Can it be done in a select, or am I SOL?

 

[PHV]

In the first SELECT fields list, replace this:
, D.LastDate,
By this:
, D.LastDate + Iif(S.Scheduled, 0, 28) As ScheduleDate,

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

 

[Genimuse]

I didn't realize you could do things on that side of the FROM. Man, that's great, thanks so much!

 

[Genimuse]

I had to modify the ORDER BY a bit, but it works great.

I realize now, though, that I also need to have the last date where Scheduled = False, in addition to the last date of any type (and sorting can stay as is). I couldn't figure out how to grab it but to add another INNER JOIN, but I clearly don't know how to do it right. Again. How can something like this work:

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, [COLOR=blue][b]A.LastActual,[/b][/color] P.Notes 
FROM (
    MedicarePatients P 
    INNER JOIN (
        SELECT ID, Max(Checkup) As LastDate 
        FROM MedicareCheckups 
        GROUP BY ID) D 
    ON P.ID = D.ID) 
[COLOR=blue][b]INNER JOIN (
    SELECT ID, Max(Checkup) AS LastActual
    FROM MedicareCheckups
    WHERE Selected = False
    GROUP BY ID) A
    ON P.ID = A.ID[/b][/color]
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

The blue parts are what I added, but something's wrong with it.

 

[PHV]

Why adding complication with another join ?
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, D.LastDate AS LastActual, 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

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

 

[Genimuse]

D.LastDate is the MAX date, whether Scheduled is True or not. I effectively need the MAX date where Scheduled is False and, if it exists, the MAX date where Scheduled is True.

I first tried the query you suggested, but realized that all I get is the MAX LastDate (plus one that has 28 days added in some cases). If Scheduled is True then I don't get the latest non-Scheduled date for that patient.

 

[Genimuse]

Sorry to bump this, I'm a bit desperate. Any thoughts on how to get both dates, anyone?

 

[PHV]

This issue was resolved in another forum.
Here the code to close this thread:
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 IIF(S.LastScheduled IS NULL, U.LastUnscheduled + 28, S.LastScheduled) > #8/10/2004#
ORDER BY IIF(S.LastScheduled IS NULL, U.LastUnscheduled + 28, S.LastScheduled), P.LastName, P.FirstName

 

[Genimuse]

Indeed. and thanks again, PHV.