how can i check if value exists before the date specified in sql server

Question

I have the data below in a sql table,

ID | supplier | Supplier_Due | Date      |
1  | S-0003   | 14850        |2020-11-09
2  | S-0003   | 850          |2020-11-09
3  | S-0003   | 21750        |2020-11-13
4  | S-0003   | 975          |2020-11-15
5  | S-0003   | 75           |2020-11-17

let assume the user wants to get data of 2020-11-13 which is

3  | S-0003   | 21750        |2020-11-13

but i'd like to get the previous supplier due as well before the date specified which is

850

along with

3  | S-0003   | 21750        |2020-11-13

so the actual query i wanna get is this

ID | supplier | Supplier_Due | Date      | Previous Due
3  | S-0003   | 21750        |2020-11-13 | 850

and if there is no previous due i wanna return

ID | supplier | Supplier_Due | Date      | Previous Due
3  | S-0003   | 21750        |2020-11-13 | 0.00

i couldn't even figure out how to write the query because i dont understand how to go about it

Answer 1

You can use window functions. Assuming that date can be used to consistently order the records of each supplier:

select *
from (
    select t.*, 
        lag(supplier_due, 1, 0) over(partition by supplier order by date) as previous_due
    from mytable t
) t
where date = '2020-11-13' and supplier = 'S-0003'

A typical alternative is a subquery, or a lateral join:

select t.*, coalesce(t1.supplier_due, 0) as previous_due
from mytable t
outer apply (
    select top (1) supplier_due
    from mytable t1
    where t1.supplier = t.supplier and t1.date < t.date
    order by t1.date desc
) t1
where date = '2020-11-13' and supplier = 'S-0003'