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

• 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
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

• 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,
where p1.part = g.part and p1.cost = g.min_cost) min_cost_date,
max_cost,
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 .

2

2

2

2

2

2

2

2

2

2

2

2

2

2

2