hi
hope this helps
create sample data script
-- Sample data
CREATE TABLE #Summary (
FY VARCHAR(10),
Provider VARCHAR(50),
DistinctConsumers INT,
Service VARCHAR(50),
PaidAmt DECIMAL(18,2)
);
INSERT INTO #Summary (FY, Provider, DistinctConsumers, Service, PaidAmt)
VALUES
-- Provider A: has data in FY21, FY22, no consumers in FY23
('FY21','Provider A', 101,'Dental', 27976.16),
('FY22','Provider A', 31,'Dental', 11516.68),
('FY23','Provider A', NULL,'Dental', 0.00),
-- Provider B: consistent across all years
('FY21','Provider B', 251,'Vision', 82575.36),
('FY22','Provider B', 257,'Vision', 109256.13),
('FY23','Provider B', 246,'Vision', 99974.29),
('FY24','Provider B', 260,'Vision', 120000.00),
('FY25','Provider B', 270,'Vision', 125500.00),
-- Provider K: small counts, varying PaidAmt
('FY21','Provider K', 3,'Medical', 3959.64),
('FY22','Provider K', 5,'Medical', 9705.00),
('FY23','Provider K', 1,'Medical', 3027.96),
-- Provider Q: moderate counts, all years
('FY21','Provider Q', 52,'Dental', 20183.32),
('FY22','Provider Q', 31,'Dental', 17843.82),
('FY23','Provider Q', 26,'Dental', 20084.61),
('FY24','Provider Q', 40,'Dental', 25000.00),
('FY25','Provider Q', 45,'Dental', 30000.00);
t-sql
SELECT
Provider,
-- FY21
COUNT(DISTINCT CASE WHEN FY = 'FY21' THEN DistinctConsumers END) AS FY21_consumers,
SUM(CASE WHEN FY = 'FY21' THEN PaidAmt END) AS FY21_PaidAmt,
-- FY22
COUNT(DISTINCT CASE WHEN FY = 'FY22' THEN DistinctConsumers END) AS FY22_consumers,
SUM(CASE WHEN FY = 'FY22' THEN PaidAmt END) AS FY22_PaidAmt,
-- FY23
COUNT(DISTINCT CASE WHEN FY = 'FY23' THEN DistinctConsumers END) AS FY23_consumers,
SUM(CASE WHEN FY = 'FY23' THEN PaidAmt END) AS FY23_PaidAmt,
-- FY24
COUNT(DISTINCT CASE WHEN FY = 'FY24' THEN DistinctConsumers END) AS FY24_consumers,
SUM(CASE WHEN FY = 'FY24' THEN PaidAmt END) AS FY24_PaidAmt,
-- FY25
COUNT(DISTINCT CASE WHEN FY = 'FY25' THEN DistinctConsumers END) AS FY25_consumers,
SUM(CASE WHEN FY = 'FY25' THEN PaidAmt END) AS FY25_PaidAmt
FROM #Summary
GROUP BY Provider
ORDER BY Provider;
Output
