How to get the highest (max) cost, lowest (min) cost, and the date that each occurred?

[Amritpal]

I need to find the highest cost & lowest cost for each part in a table (There are many parts) in a given date range. And output the date that each cost was recorded.

Example Data:

CREATE TABLE v_po_history
(Part int,cost numeric(20,6),date_received date);
INSERT INTO v_po_history
VALUES

(846060,28.373,'1/5/2022'),

(846060,27.588,'3/8/2022'),

(846060,29.143,'4/25/2022'),

(846060,29.143,'2/28/2022'),

(70/1300/100,176.500,'1/7/2022'),

(70/1300/100,195.000,'3/19/2022'),

(80/800/75,77.846,'2/1/2022'),

(80/800/75,76.688,'4/19/2022'),

(80/800/75,76.602,'4/13/2022'),

(800372,0.9925,'1/1/2022'),

(800372,0.9925,'1/19/2022'),

(800372,0.9925,'4/1/2022'),

(800372,0.9925,'3/10/2022');

I need my output to look something like:

|  Part  |  Lowest Cost  |  Date  |  Highest Cost  |  Date  |
|--------|---------------|--------|----------------|--------|
| 846060 |    27.588     | 3/8/22 |     29.143     | 4/25/22|
|70/13...|    176.500    | 1/7/22 |     195.000    | 3/19/22|
|80/80...|    76.602     | 4/13/22|     77.846     | 2/1/22 |
| 800372 |    0.9925     | 1/1/22 |     0.9925     | 4/1/22 |

Query I've pieced together from other forums:

select distinct part, description, location, t_fd.First_Date, t_fd.lowest_cost, t_ld.Last_Date, t_ld.highest_cost from
v_po_history, 
--date for lowest cost
(select top 1 date_received as First_Date, min(cost) as lowest_cost from v_po_history where 
cost = (select min(cost) from v_po_history where part not like '*%' and date_received >= '2022-01-01' and date_received = '2022-01-01' and date_received = '2022-01-01' and date_received = '2022-01-01' and date_received = '2022-01-01' and date_received 

Output:

|  Part  |  Lowest Cost  |  Date  |  Highest Cost  |  Date  |
|--------|---------------|--------|----------------|--------|
| 846060 |    0.9925     | 1/1/22 |     195.000    | 4/25/22|
|70/13...|    0.9925     | 1/1/22 |     195.000    | 4/25/22|
|80/80...|    0.9925     | 1/1/22 |     195.000    | 4/25/22|
| 800372 |    0.9925     | 1/1/22 |     195.000    | 4/25/22|

I understand why I get these results. The query is only selecting the lowest cost and the highest cost but not for each part, just the lowest and highest values in the table. My question how can I tweak this so I get my desired results? Or do I need to scrap this query and go about this in a different way? I am using Pervasive SQL btw. Here is a https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=bf5fd0f9750e3ed1a77b0b4efd8b592d

[yajahira]

To obtain results per part, you obviously need to group by part somewhere, and I don't see that in your attempt.

Something like this might work for you:

select 
 g.part, 
 min_cost, 
 (select max(p1.date_received) from part p1 
  where p1.part = g.part and p1.cost = g.min_cost) min_cost_date,
 max_cost, 
 (select max(p2.date_received) from part p2 
  where p2.part = g.part and p2.cost = g.max_cost) max_cost_date
from (select part, min(cost) min_cost, max(cost) max_cost
from part group by part) g

By the way, things like 70/1300/100 don't look like integer values.

https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=af758528512301f3445e02674a6f834e .