I have a recruitment table that for a particular job maps job stages to milestones. The below is an example
For example if the candidate is in the “Application Review” stage they are at the “Application” milestone
However if they are in the “Second Stage” for example the milestone is blank and I need to return the previous milestone entry based on the job stages order. So in this example I need to return the milestone as “Assessment”. The table is linked on “Jobs Stages Job ID” and “Jobs Stages Stages ID”
I’m struggling to work out how to do this in SQL so any help/advice would be much appreciated.