This is the input data.
Dept Company Code Payment Amt
Gardeners Sort:Insurance Carrier 100 20.00
Gardeners Sort:Insurance Carrier 100 22.00
Gardeners Sort:Insurance Carrier 100 21.00
Gardeners Sort:Insurance Carrier 100 20.00
Gardeners Sort:Insurance Carrier 100 22.00
I want to return
Sort:Insurance Carrier 100 - 22.00 and 21.00
Not 22.00 and 22.00 I fear this code is returning 22 and 22 arguably the 2 top prices paid but not really so.
I have this SQL
SELECT
[DEPT], [Sort: Procedure Code] as Code, [Sort: Insurance Carrier],
SUM(CASE WHEN num = 1 THEN [Pmt Amount] ELSE 0 END) AS [first high],
SUM(CASE WHEN num = 2 THEN [Pmt Amount] ELSE 0 END) AS [second high]
FROM
(
SELECT ROW_NUMBER() OVER(PARTITION BY
[DEPT], [Sort: Procedure Code], [Sort: Insurance Carrier]
ORDER BY [Pmt Amount] DESC) AS num,
[DEPT], [Sort: Procedure Code], [Sort: Insurance Carrier],
[Pmt Amount]
FROM
[revenuedetail$]
) AS t
WHERE num IN (1, 2)
GROUP BY [DEPT], [Sort: Procedure Code], [Sort: Insurance Carrier]