Access Query: Subtract last 2 values, specific to ID-CodePudding

Help appreciated! My table is setup as follows: fake data TableName = GAD7

[PatientID   Date        Value
Sam        10/21/2022    15
George     06/12/2022     7
Luke       09/03/2021    11
Sam        05/15/2020    20 
George    12/02/2017      2
George    01/01/1992      6][1]

So I have potentially multiple rows of the same patient, w/different dates. I need to create a query that subtracts the LAST 2/most recent values for each patient. So my query would show only those with 2 records. Negative values are fine/expected. My successful query would then show:

PatientID (LastScore - 2nd_toLastScore)
Sam        -5.0 
George     5.0

Luke is not shown because he only has one value

I was able to formulate a query to show only those PatientIDs with >= 2 records and last date and last value. I am not sure how to get the second from last date/value AND THEN subtract those values.

Access query

The SQL view :

SELECT GAD7.PatientID, Count(GAD7.PatientID) AS CountOfPatientID, Last(GAD7.TestDate) AS LastDate, Last(GAD7.Score) AS LastScore
FROM GAD7
GROUP BY GAD7.PatientID
HAVING (((Count(GAD7.PatientID))>=2))
ORDER BY GAD7.PatientID;

CodePudding user response：

Consider:

Query1: Score1

SELECT GAD7.*
FROM GAD7
WHERE 1=(SELECT Count(*) 1 FROM GAD7 AS G7 
         WHERE G7.PatientID=GAD7.PatientID AND G7.TestDate>GAD7.TestDate);

Query2: Score2

SELECT GAD7.*
FROM GAD7
WHERE 2=(SELECT Count(*) 1 FROM GAD7 AS G7 
         WHERE G7.PatientID=GAD7.PatientID AND G7.TestDate>GAD7.TestDate);

Query3:

SELECT Score2.PatientID, [Score2].[Score]-[Score1].[Score] AS D
FROM Score1 INNER JOIN Score2 ON Score1.PatientID = Score2.PatientID;

Could nest the SQL statements for an all-in-one query.

Or this all-in-one version using TOP N to pull previous Score:

SELECT GAD7.*, (SELECT TOP 1 Score FROM GAD7 AS Dupe 
                WHERE Dupe.PatientID = GAD7.PatientID AND Dupe.TestDate<GAD7.TestDate 
                ORDER BY Dupe.TestDate DESC) AS PrevScore 
FROM GAD7 WHERE PatientID IN
    (SELECT PatientID FROM GAD7 GROUP BY PatientID HAVING Count(*)>1) 
AND 1=(SELECT Count(*) 1 FROM GAD7 AS G7 WHERE G7.PatientID=GAD7.PatientID AND G7.TestDate>GAD7.TestDate);