By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE Part
(Part int,cost numeric(20,6),date_received date);
INSERT INTO Part
VALUES
(846060,28.373,'1/5/2022'),
(846060,27.588,'3/8/2022'),
(846060,29.143,'4/25/2022'),
(846060,29.143,'2/28/2022'),
(70/1300/100,176.500,'1/7/2022'),
(70/1300/100,195.000,'3/19/2022'),
(80/800/75,77.846,'2/1/2022'),
(80/800/75,76.688,'4/19/2022'),
(80/800/75,76.602,'4/13/2022'),
(800372,0.9925,'1/1/2022'),
(800372,0.9925,'1/19/2022'),
(800372,0.9925,'4/1/2022'),
(800372,0.9925,'3/10/2022');
13 rows affected
select distinct part, t_fd.First_Date, t_fd.lowest_cost, t_ld.Last_Date, t_ld.highest_cost from
Part,
--date for lowest cost
(select top 1 date_received as First_Date, min(cost) as lowest_cost from Part where
cost = (select min(cost) from Part where part not like '*%' and date_received >= '2022-01-01' and date_received <= '2022-05-01') group by date_received) as t_fd,
-- date for highest cost
(select top 1 date_received Last_Date, max(cost) as highest_cost from Part where
cost = (select max(cost) from Part where part not like '*%' and date_received >= '2022-01-01' and date_received <= '2022-05-01') group by date_received) as t_ld
where
part not like '*%' and date_received >= '2022-01-01' and date_received <= '2022-05-01'
| part | First_Date | lowest_cost | Last_Date | highest_cost |
|---|---|---|---|---|
| 0 | 2022-01-01 | 0.992500 | 2022-03-19 | 195.000000 |
| 800372 | 2022-01-01 | 0.992500 | 2022-03-19 | 195.000000 |
| 846060 | 2022-01-01 | 0.992500 | 2022-03-19 | 195.000000 |