-2

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

GMB
  • 216,147
  • 25
  • 84
  • 135
Temitayo
  • 39
  • 8
  • How are you defining "previous"? I could guess it's one or both of `Date` and `Id` (although obviously `Date` alone gives us some ambiguity) but would be very helpful if you could identify what you want to use. – Damien_The_Unbeliever Nov 13 '20 at 09:29
  • Is the `Date` column an actual date of a varchar that holds those strings? – Liam Nov 13 '20 at 09:30
  • What does "write the query" mean? What kind of query? SQL, Linq, Entity Framework, Dapper, nHibernate, etc. etc.? – Liam Nov 13 '20 at 09:31
  • @Liam the the date is Date column in sql server – Temitayo Nov 13 '20 at 09:35

2 Answers2

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'
GMB
  • 216,147
  • 25
  • 84
  • 135
0
DECLARE @Suppliers table
(
    ID integer PRIMARY KEY CLUSTERED,
    Supplier char(6) NOT NULL,
    Supplier_Due smallmoney NOT NULL,
    [Date] date NOT NULL
);

INSERT @Suppliers
    (ID, Supplier, Supplier_Due, [Date])
VALUES
    (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');

SELECT
    S.ID, 
    S.Supplier, 
    S.Supplier_Due, 
    S.[Date],
    [Previous Due] =
        LAG(S.Supplier_Due, 1, 0) OVER (
            PARTITION BY S.Supplier
            ORDER BY S.[Date] ASC)
FROM @Suppliers AS S
WHERE
    S.[Date] = CONVERT(date, '2020-11-13', 121);

db<>fiddle demo

Documentation: LAG (Transact-SQL)

user14570231
  • 119
  • 4