CODEWITHSUNDEEP
pervasive-sql
SkylarP
SkylarP

Reputation: 41

select min and max value of a column and corresponding date (another column)

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  |    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

Upvotes: 0

Views: 1066

Answers (2)

SkylarP
SkylarP

Reputation: 41

select g.part, min_cost, (select min(p1.date_received) from v_po_history p1 where p1.part = g.part and p1.cost = g.min_cost and part not like '*%' and DATE_RECEIVED >= '2022-01-01' and date_received <= '2022-05-01' and location = 'HS') as min_cost_date, 
max_cost, (select max(p2.date_received) from v_po_history p2 where p2.part = g.part and p2.cost = g.max_cost and part not like '*%' and DATE_RECEIVED >= '2022-01-01' and date_received <= '2022-05-01' and location = 'HS') as max_cost_date 
from (select part, min(cost) min_cost, max(cost) 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'
    group by part) g

Upvotes: 0

mirtheil
mirtheil

Reputation: 9192

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.

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'

Upvotes: 1

Related Questions