Contact US

Question

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

profoundhypnotic (Programmer)

(OP)

11 May 22 19:45

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 (Programmer) · Answer 1 · 2022-05-12 11:58:23

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

Come Join Us!

Posting Guidelines

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

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

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

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

CODE

Red Flag This Post

Red Flag Submitted

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Join Tek-Tips^® Today!

Contact US

Come Join Us!

Posting Guidelines

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

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

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

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

CODE

Red Flag This Post

Red Flag Submitted

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Join Tek-Tips® Today!

Join Tek-Tips^® Today!