Question

SQL Code to return the 'previous' entry if null

  • 10 June 2020
  • 5 replies
  • 229 views

Hi !


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.


Kind regards,


Richard


5 replies

Userlevel 2

Hi WorthyWow,


The function you are looking for is called “COALESCE”. It returns the first non-null value if found and null otherwise.


There are many examples of how to use this in SQL online.

Hope this helps!

Jasper

Userlevel 6
Badge

Check out sql lag window function, coalesce won’t work across rows. You would be better just creating a mapping table to fill the gaps though if it is a consistent process.

Hi - many thanks for the suggestions - much appreciated.

Userlevel 2

Hi again,


I came across this post, I think it is exactly what you need!



Hope this helps,

Jasper

Hi Jasper - wow ! That looks just what I’m after! I’ll report back once I’ve given it a go.


Many thanks,


Richard

Reply