Update Query Using Min(Date)

[reginaldmerritt]

Ok I think I have it working. I swapped EventDate for DaysToReview as this is a field that lists difference in days between EventDate and Today. Not sure why that made a difference.

UPDATE TBSchemeOfWork AS tSW INNER JOIN (SELECT [SchemeOfWorkID], [DaysToReview] 
FROM TBSchemeOfWorkEvent AS tSWEo 
WHERE ([DaysToReview] In (                
SELECT Min([DaysToReview])               
FROM   [TBSchemeOfWorkEvent]  AS tSWEi              
WHERE  (tSWEi.SchemeOfWorkID = tSWEo.SchemeOfWorkID)               
AND    ([IsAReview] = True) 
AND    ([ActualReviewDate] Is Null) 
GROUP BY [SchemeOfWorkID])))  AS sSWE 
ON tSW.SchemeOfWorkID = sSWE.SchemeOfWorkID SET tSW.DaysTillNextReview = sSWE.DaysToReview;

[NeoPa]

Reginald Merritt:
The link between TBSchemeOfWork and TBSchemeOfWorkE vent is the PK in TBSchemeOfWork which is SchemeOfWorkID.

I got this from your post #3. I only know your data from what you tell me ;-)

Also, I notice you have changed the layout of the SQL when you post it. This makes no sense to me as it is much clearer in the way I posted it (That's why I did it that way after all). It makes checking for changes so much more difficult. I'm happy to continue working with you on this, but please try not to make it more difficult than necessary.

Reginald Merritt:
The Update SQL sees to work but only captures the Maximum DaysToReview

I have no idea why this might be, but I see you have attached an example database for me to look at, so I will. For now, I would suggest you make just that one change for the PK into my SQL and see where that gets you.

I'm off for a while now, but when I get back I'll look into it myself in your example database.

PS. Scratch that. I cannot read your database. Please check Attach Database (or other work) and attach it again if you would. I'll look for it again when I get back.

[reginaldmerritt]

I got this from your post #3. I only know your data from what you tell me ;-)

True, I obviously thought you where a mind reader ;)

Sorry the database isn't working I've reattached it but everything seems to be working ok now anyway. Just can't work out why changing MIN to MAX doesn't make any difference?. I probably just did'nt do a refresh. After I changed the key group field to DaysToReview instead of EventDate I got the data I needed but that's probably not what made the difference.

Thanks again Adrian, Have a great Christmas

[NeoPa]

Reginald Merritt:
I swapped EventDate for DaysToReview as this is a field that lists difference in days between EventDate and Today. Not sure why that made a difference.

That leads me to think that maybe [EventDate] is not stored as a DateTime field (as it probably should be). I'll look at the attachment anyway to see if I can explain the Min/Max issue.

Looking into it now. The SQL isn't perfect. What you're seeing is not what you think, but it's certainly complicated so I'm looking deeper. All records that match the minimum valid date are being returned - even if they don't follow the other filter properties (Essentially the SQL is fatally flawed - though somewhat close). I'll post back later when I have a better grip on how best to handle it.

PS. Good job with the attached database.

[NeoPa]

It seems that both subquery levels needed their WHERE clauses fully specified. I'll include the SELECT version of the SQL here as well as the update so that you can see more easily what is happening :

SELECT *
FROM   [TBSchemeOfWork] AS [tSW]
       INNER JOIN
       (
    SELECT   [SchemeOfWorkID]
           , [DaysToReview]
           , [EventDate]
    FROM     [TBSchemeOfWorkEvent] AS tSWEo
    WHERE    (tSWEo.EventDate In (
        SELECT   Min([EventDate])
        FROM     [TBSchemeOfWorkEvent] AS tSWEi
        WHERE    (tSWEi.SchemeOfWorkID = tSWEo.SchemeOfWorkID)
          AND    ([IsAReview])
          AND    ([ActualReviewDate] Is Null)))
      AND    ([IsAReview])
      AND    ([ActualReviewDate] Is Null)
       ) AS [sSWE]
    ON tSW.SchemeOfWorkID = sSWE.SchemeOfWorkID

The UPDATE version should then be :

UPDATE [TBSchemeOfWork] AS [tSW]
       INNER JOIN
       (
    SELECT   [SchemeOfWorkID]
           , [DaysToReview]
           , [EventDate]
    FROM     [TBSchemeOfWorkEvent] AS tSWEo
    WHERE    (tSWEo.EventDate In (
        SELECT   Min([EventDate])
        FROM     [TBSchemeOfWorkEvent] AS tSWEi
        WHERE    (tSWEi.SchemeOfWorkID = tSWEo.SchemeOfWorkID)
          AND    ([IsAReview])
          AND    ([ActualReviewDate] Is Null)))
      AND    ([IsAReview])
      AND    ([ActualReviewDate] Is Null)
       ) AS [sSWE]
    ON tSW.SchemeOfWorkID = sSWE.SchemeOfWorkID
SET tSW.DaysTillNextReview = sSWE.DaysToReview