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 |
New contributor
newbie21 is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.