how to select min and max value and the date for each

profoundhypnotic · May 11, 2022

I am trying to get the lowest cost for a part and the highest cost for a part. along with the dates that they occurred. I almost have what I want except that in my query I don't know how to associate the lowest cost with the correct date. I understand why (because that's what I told the query to do) but I am wondering how can I alter the query so that when the lowest cost is selected, it selects the corresponding date as well?

Data set example:
|Part |lowest_cost|date_received|
|------|-----------|-------------|
|846060| 28.373265 | 1/5/2022 |
|846060| 29.143835 | 2/28/2022 |
|846060| 27.588483 | 3/8/2022 |
|846060| 29.143835 | 4/25/2022 |

Desired output:
|Part |lowest_cost|date_received|highest_cost|last_date_received|difference|
|------|-----------|-------------|------------|------------------|----------|
|846060| 27.588483 | 3/8/2022 | 29.143835 | 4/25/2022 | 1.555405 |

current output:
|Part |lowest_cost|date_received|highest_cost|last_date_received|difference|
|------|-----------|-------------|------------|------------------|----------|
|846060| 27.588483 | 1/5/2022 | 29.143835 | 4/25/2022 | 1.555405 |

query I have currently:
select part,min(cost) as Lowest_Cost,max(cost) as Highest_Cost,
min(date_received) as First_date,
max(date_received) as Last_Date, (max(cost) - min(cost)) as Difference
from v_po_history
where part not like '*%' and date_received >= '2022-01-01' and date_received <= '2022-05-01' and location = 'HS' and part = '846060'
group by part

mirtheil · May 12, 2022

As posted in another forum:
This is my first attempt at it but it seems to work based on your data. I make no guarantees on performance on a larger data set. There may be other better ways too.

Code:

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 part = '846060') 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 part = '846060') 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' and part = '846060'

Mirtheil

http://www.mirtheil.com

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

how to select min and max value and the date for each

profoundhypnotic

Programmer

mirtheil

Programmer

Similar threads

Part and Inventory Search

Sponsor