# 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-05-01' and location = 'HS')
-- date for highest cost
(select top 1 date_received Last_Date, max(cost) as highest_cost from v_po_history where
cost = (select max(cost) from v_po_history where part not like '*%' and date_received >= '2022-01-01' and date_received <= '2022-05-01' and location = 'HS')
where part not like '*%' and date_received >= '2022-01-01' and date_received <= '2022-05-01'
and location = 'HS'
``````

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 fiddle

• In your fiddle example (not very helpful, by the way, because it only shows a single part number) certain cost is reported on two different dates. Which of these dates do you want reported? Commented May 24, 2022 at 18:10
• it should show the latest date that the cost was recorded. so in that example, it should report the 4/25/2022. I'll update the fiddle with more data. Commented May 24, 2022 at 18:42

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,
By the way, things like `70/1300/100` don't look like integer values.