SQL Code to return the 'previous' entry if null

worthywow
Participant II

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

0 5 1,646
5 REPLIES 5

blue1
Participant III

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

IanT
Participant V

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.

worthywow
Participant II

Hi - many thanks for the suggestions - much appreciated.

blue1
Participant III

Hi again,

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

Hi, Can someone please help: I am trying to replace consecutive null values in a pivot by a value from the row above null. I tried using offset function but it fills up only for the next immediate value and skips the rest. Any help is appreciated. Thanks

Hope this helps,
Jasper

worthywow
Participant II

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

Many thanks,

Richard

Top Labels in this Space
Top Solution Authors