Conditional aggregation may help:
select
product,
orderedQty,
cast(sum(m.PickedQty) as decimal(18,1)) as PickedQty,
MAX (CASE WHEN Rn = 1 THEN Operator END) AS Operator
from (
SELECT
*,
ROW_NUMBER() OVER (PARTITION BY product, orderedQty ORDER BY TimeFinised DESC) AS Rn
FROM (VALUES
(1, 1234, 60, 40, 'Joe', 125546),
(2, 1234, 60, 18, 'Benny', 120025)
) v (LineId, Product, orderedQty, PickedQty, Operator, TimeFinised)
) m
group by
product, orderedQty
Result:
product orderedQty PickedQty Operator
1234 60 58.0 Joe
与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…