Retrieve the first and last record available depending on dates-CodePudding

I'm trying to understand how to extract the first and last records available based on dates with the following example:

SELECT clientID, AssessmentDate, TotalScore
FROM Client.Assessments

For each of the clients (based on their clientID), I am trying to retrieve the TotalScore for their first and last assessment available (based on the AssessmentDate). I deal with lots of assessment entries, and I usually do a pre-post statistical analysis with the first assessment they have, and I compare it to the last assessment available.

CodePudding user response：

The easiest is to think in two steps. First, prepare the min/max dates for each client. Second, select for rows with these dates.

SELECT clientsMaxMin.clientID
    , ca.TotalScore
FROM 
(
    SELECT clientID
        , max(AssessmentDate) as maxDate
        , min(AssessmentDate) as minDate
    FROM Client.Assessments AS c
    GROUP BY c.clientID
) clientsMaxMin  -- prepare a smaller table with max and min dates
JOIN Client.Assessments AS ca  -- from the original table select only rows with min/max values
    ON ca.AssessmentDate = clientsMaxMin.maxDate
        OR ca.AssessmentDate = clientsMaxMin.minDate