Whilst I'm still waiting to be sent on a SQL training course, I'm still having to SQL that appears to get harder!
Anyway, I am doing some selections for a charting application, and the data selected contains two records for the same ID, one is a 'preliminary' record and the other is a 'final' one. For some IDs there is only a preliminary record, for others there is both. My selection includes only records where each of the required fields are not NULL, but what i also need to do is select the final record (if present, otherwise the preliminary) for each ID. I tried to update my selection using the MAX function around the preliminary/final marker, and this works for most cases, except where the data for the preliminary record and the final record are different.
Do any of you SQL experts out ther know if there is a way to perform this selection?
Here is what i have so far:
Code:
SELECT V.ValuationID AS VID, VR.DBLiabFunding AS JK7, VR.MPLiabFRS17OrIAS19 AS JK32, VD.PlanLenYears AS M12, MAX(F.InputStage) AS ISTAGE
FROM Valuations V
INNER JOIN Financial F ON F.ValuationID = V.ValuationID
INNER JOIN ValuationResults VR ON VR.ValuationID = V.ValuationID AND F.InputStage = VR.InputStage
INNER JOIN ValuationDocuments VD ON VD.ValuationID = V.ValuationID AND F.InputStage = VD.InputStage
WHERE (F.InputStage = 1 OR F.InputStage = 2)
AND (VR.DBLiabFunding IS NOT NULL) AND (VR.MPLiabFRS17OrIAS19 IS NOT NULL) AND (VD.PlanLenYears IS NOT NULL)
GROUP BY V.ValuationID, VR.DBLiabFunding, VR.MPLiabFRS17OrIAS19, VD.PlanLenYears
Thanks in advance
Martin