选择列的最小值和最大值以及对应的日期(另一列)

Question

我正试图获得零件的最低成本和零件的最高成本。以及它们发生的日期。我几乎得到了我想要的，除了在我的查询中，我不知道如何将最低成本与正确的日期联系起来。我明白为什么(因为这是我告诉查询要做的)，但我想知道我如何改变查询，以便在选择最低成本时，它也选择相应的日期?

数据集示例:

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

所需输出:

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

当前输出:

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

查询我当前有:

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    

Answer 1

这是我第一次尝试，但它似乎是基于你的数据工作。我不保证在更大的数据集上的性能。也许还有其他更好的方法。

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'

Answer 2

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