SQL get max value of child
From Logic Wiki
Here in this example I have 18209 unique placeRefs and in tht_vwSP_BuildingSafetyData I have multiple docs for each placerefs with multiple dates. I want to return the latest Fire Risk Assessment document
WITH z AS (
SELECT
DISTINCT p.PlaceRef
,row=ROW_NUMBER() OVER(PARTITION BY p.PlaceRef ORDER BY D.ExpiryDate DESC)
,D.ExpiryDate
FROM
tblOHProperties P
LEFT JOIN tht_vwSP_BuildingSafetyData D ON P.PlaceRef = D.PlaceRef AND
D.DocumentType = 'Fire Risk Assessment Document'
)
SELECT COUNT(*) AS Total,
CASE WHEN ExpiryDate > getdate() THEN 1 ELSE 0 END AS IsValid
FROM z WHERE row = 1
GROUP BY CASE WHEN ExpiryDate > getdate() THEN 1 ELSE 0 END