SQL get max value of child

From Logic Wiki
Jump to: navigation, search


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