Search code examples
sql

Start Date to End Date owing the part

I have the following data in my table.

id part_num modify_date order_number vendor_num
637 pn23053 3/18/24 15:03 on-542 13
637 pn23053 2/8/24 5:10 on-542 13
637 pn23053 2/8/24 5:00 on-542 13
637 pn23053 2/8/24 5:00 on-542 1
637 pn23053 1/8/24 21:56 on-542 1
637 pn23053 1/8/24 21:56 on-542 13
637 pn23053 11/4/23 18:4 on-542 13
637 pn23053 11/4/23 18:4 on-542 13
637 pn23053 11/4/23 18:4 on-542 20
895 pn95345 6/8/24 17:20 on-691 14
895 pn95345 3/17/24 16:30 on-691 14
895 pn95345 3/8/24 13:50 on-691 14
895 pn95345 3/8/24 13:50 on-691 5
895 pn95345 2/6/24 21:57 on-691 5
895 pn95345 2/6/24 21:57 on-691 21
895 pn95345 2/3/24 18:57 on-691 21
895 pn95345 1/8/24 18:57 on-691 21

I have tried using ROW_NUMBER() OVER (PARTITION BY modify_date, owner_id ORDER BY modify_date ASC) AS row_num, but the data turned out to be funky. There are two unique part numbers in the table, but many vendors are associated with each part. So, I need to know when the vendor starts and ends owning the part. I expect the result to be...

id part_num modify_date order_number vendor_num End-modify_date
637 pn23053 2/8/24 5:00 on-542 13 current Date
637 pn23053 1/8/24 21:56 on-542 1 2/8/24 5:00
637 pn23053 11/4/23 18:4 on-542 13 1/8/24 21:56
637 pn23053 11/4/23 18:4 on-542 20 11/4/23 18:4
895 pn95345 3/8/24 13:50 on-691 14 current Date
895 pn95345 2/6/24 21:57 on-691 5 3/8/24 13:50
895 pn95345 1/8/24 18:57 on-691 21 2/6/24 21:57
Solution

  • You should use lead, and case when. Could you please try this? It gave me this output.

    output

    SELECT
    id,
    part_num,
    order_number,
    vendor_num,
    MIN(modify_date) AS modify_date,
    COALESCE(MAX(modify_date), NOW()) AS end_modify_date
FROM sys.visits
GROUP BY
    id,
    part_num,
    order_number,
    vendor_num
ORDER BY
    id,
    part_num,
    order_number,
    vendor_num,
    modify_date;