×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

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

(OP)
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

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

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

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

Click Here to join Tek-Tips and talk with other members! Already a Member? Login


Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close