0

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-05-01' and location = 'HS')
and date_received >= '2022-01-01' and date_received <= '2022-05-01' 
group by date_received) as t_fd,
-- 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')
and date_received >= '2022-01-01' and date_received <= '2022-05-01' 
group by date_received) as t_ld
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

2
  • 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?
    – mustaccio
    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.
    – SkylarP
    Commented May 24, 2022 at 18:42

1 Answer 1

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, 
 (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.

Fiddle.

4
  • You deserve a medal, a raise at your job, or whatever you want. I've been trying to solve this for 2 days. Thank you so much.
    – SkylarP
    Commented May 24, 2022 at 21:06
  • I do have a quick question, If I wanted to add two more columns (description and qty), would I need to add them to the last select statement (select part, min(cost) min_cost,max(cost) max_cost from part group by part) ?
    – SkylarP
    Commented May 25, 2022 at 13:55
  • I can't tell, as I have no idea where these columns are coming from
    – mustaccio
    Commented May 25, 2022 at 14:38
  • The answer was, yes. It was two more columns from the same table.
    – SkylarP
    Commented May 25, 2022 at 16:51

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.