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

## All we need is an easy explanation of the problem, so here it is.

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

## How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

### Method 1

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.