Quote:
| Originally Posted by uwila or even try the following simpler one:
SELECT V.ValuationID AS VID, VR.DBLiabFunding AS JK7, VR.MPLiabFRS17OrIAS19 AS JK32, VD.PlanLenYears AS M12, F.InputStage AS ISTAGE
FROM Valuations V
INNERJOIN (select ValuationID, max(InputStage) InputStage from Financial F group by ValuationID) F ON F.ValuationID = V.ValuationID
INNERJOIN ValuationResults VR ON VR.ValuationID = V.ValuationID AND F.InputStage = VR.InputStage
INNERJOIN ValuationDocuments VD ON VD.ValuationID = V.ValuationID AND F.InputStage = VD.InputStage
WHERE (VR.DBLiabFunding ISNOTNULL)AND(VR.MPLiabFRS17OrIAS19 ISNOTNULL)AND(VD.PlanLenYears ISNOTNULL)
The bit in red creates a temporary table which contains rows for final records and prelim records if no final record exists. Note that if a final record exists without a prelim record, then this one will be excluded.
I haven't tested the full query, but the red bit does exactly what I said 
Cheers, Chris |
Thanks Chris - that works perfectly!
I tried to get my head around the RANK stuff, but the examples made no sense in the context i was working in, and eventully ended up with a huge selection that found all the 2s which matched my criteria, and joined them to the 1s with the same criteria not in the list of 2s! I like your way much more!!!
Thanks again Chris - green incoming.
Martin