Subquery Join with Max Date
The query below is used when you have a table with multiple rows per person and you want to select the MAX Date. First you will select the fields you want to display in the query, then you will create a subquery to select the Max Date, then you will join the query and subquery on the ID and the Max Date.
SELECT ind.studentid
,Convert(Datetime,StatusEffectiveDate) AS EDStartDate
,economicdisadvantagecode
,Isnull(ind.economicdisadvantagecode + '-' + lku3.codedesc, '') AS EconomicDisadvCode
INTO #sle3
FROM GetColumnsTable ind
LEFT OUTER JOIN LookUpTable lku3
ON lku3.code = ind.economicdisadvantagecode
AND lku3.type = 'ECO'
----This Part grabs the Max Effective Date for the ID
Inner Join
(
Select studentid, Max(Convert(Datetime,StatusEffectiveDate)) As StartDate
From GetColumnsTable ind
Group by StudentID
) ind2
----Here you join the first Query to the SubQuery and set your Criteria
on ind.StudentID = ind2.StudentID
and Convert(Datetime,ind.StatusEffectiveDate) = Convert(Datetime,ind2.StartDate)
No comments:
Post a Comment