Search code examples
sqldatetimecasewindow-functions

Generate an new Column in SQL that displays a value from a previous cell if value gets empty

I'm trying to evaluate current data from a sample warehouse. I am particularly interested in the timestamp when a workpiece is no longer in stock.

To connect this timestamp with the ID of the workpiece I am currently looking for a SQL Query to generate a column that displays the ID of the workpiece that has occupied the storage location as soon as the ID value is empty.

At best this should only happen for the first empty value and not for the following ones.

The target result should look like the figure in the link below:

https://i.sstatic.net/ojvhs.png

Solution

  • You seem to want lag():

    select 
    t.*,
    case when id is null
        then lag(id) over(partition by location order by event_date)
    end as leaving_id
from mytable t