Sample Data
Formula
Get value larger than recalculate average value. For the min value that less than average value of n iteration, replace the value into zero.
Query
;WITH Catalogue AS
(
SELECT
Cat
, Qty
, rn = ROW_NUMBER() OVER (ORDER BY Qty)
FROM (VALUES
('Cat A', 1.0),
('Cat B', 2),
('Cat C', 3),
('Cat D', 4),
('Cat E', 5),
('Cat F', 6),
('Cat G', 7),
('Cat H', 8),
('Cat I', 9),
('Cat J', 10),
('Cat K', 11),
('Cat L', 12),
('Cat M', 13),
('Cat N', 14),
('Cat O', 15),
('Cat P', 16),
('Cat Q', 17)
) Catalogue(Cat, Qty)
), numbers AS(
SELECT n = 1
UNION ALL
SELECT n = n + 1
FROM numbers
WHERE n < 17
)
SELECT
iteration = n
, ave_qty = AVG(CASE WHEN c.rn >= n.n THEN Qty ELSE 0 END)
, min_qty = MIN(NULLIF(CASE WHEN c.rn >= n.n THEN Qty ELSE 0 END, 0))
FROM Catalogue c
CROSS JOIN numbers n
GROUP BY
n
HAVING AVG(CASE WHEN c.rn >= n.n THEN Qty ELSE 0 END) <= MIN(NULLIF(CASE WHEN c.rn >= n.n THEN Qty ELSE 0 END, 0))
|
Desired Output
No comments:
Post a Comment