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 |
You should use lead, and case when. Could you please try this? It gave me this 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;