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
Advertisement
Answer
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'