I have two tables named "Visit" and "VisitMovement" and one Visit can contain many VisitMovements.
Within my query I need to join to the latest record for the Visit in VisitMovement so I can get back the latest VisitMovement details.
To do this I have a view like this that I join to:
SELECT VisitID, max(VisitMovementID) as VisitMovementID FROM dbo.VisitMovement GROUP BY VisitID
and finish up with:
SELECT ...... FROM Visit V INNER JOIN VisitMovement VM ON V.VisitID = VM.VisitID INNER JOIN vwLatestVisitMovement LVM on VM.VisitMovementID = LVM.VisitMovementID
I can't Index this View due to the max operator, and I can see over-time this becoming a performance issue with the Index Scan operation.
I wondered if anyone had any good ideas on how to approach this? I was thinking of having a trigger and maintaining the latest VisitMovementID in the Visit table but this doesn't sit well with me.