Select detail records making up percentage of total balance per group 1

[JoeZim]

I have the following data:

Region Balance
NewYork 300
NewYork 400
NewYork 500
NewYork 600
NewYork 700
NewYork 800
NewYork 900
Boston 100
Boston 200
Boston 300
Boston 400
Boston 500
Boston 600
Boston 700

I'm looking to display the records that make up at least 80% (go over 80% if necessary, but not under) of the total balance per region. Therefore, based on the above example, it would display:

Region Balance
NewYork 500
NewYork 600
NewYork 700
NewYork 800
NewYork 900
Boston 300
Boston 400
Boston 500
Boston 600

The results display detail records which make up 80% or greater of total balance per Region.

Hope this makes sense.

 

[RiverGuy]

Here's an example. This should work if you're using either SQL 2000 or SQL 2005 and up. Obviously, the top part is filling a sample table variable with your data, and you would have to replace the table variable name with your actual table name in the select statement.

DECLARE @Data TABLE (Region VARCHAR(100), Balance INT)

INSERT INTO @Data SELECT 'NewYork',				300
INSERT INTO @Data SELECT 'NewYork',				400
INSERT INTO @Data SELECT 'NewYork',				500
INSERT INTO @Data SELECT 'NewYork',				600
INSERT INTO @Data SELECT 'NewYork',				700
INSERT INTO @Data SELECT 'NewYork',				800
INSERT INTO @Data SELECT 'NewYork',				900
INSERT INTO @Data SELECT 'Boston',				100
INSERT INTO @Data SELECT 'Boston',				200
INSERT INTO @Data SELECT 'Boston',				300
INSERT INTO @Data SELECT 'Boston',				400
INSERT INTO @Data SELECT 'Boston',				500
INSERT INTO @Data SELECT 'Boston',				600
INSERT INTO @Data SELECT 'Boston',				700




SELECT * FROM
	(SELECT a.Region, Balance, 
	(SELECT SUM(Balance) FROM @Data b WHERE a.Region = b.Region AND a.Balance <= b.Balance) AS RunningTotal,
	ISNULL((SELECT SUM(Balance) FROM @Data b WHERE a.Region = b.Region AND a.Balance < b.Balance), 0) AS PreviousRunningTotal, 
	c.EightyPercent
	FROM @Data a
	INNER JOIN
		(SELECT Region, SUM(Balance) * .80 AS EightyPercent FROM @Data GROUP BY Region) c
	ON a.Region = c.Region) d
WHERE (RunningTotal <= EightyPercent)
OR (RunningTotal > EightyPercent AND PreviousRunningTotal <= EightyPercent) 
ORDER BY Region, Balance DESC

 

[JoeZim]

Fantastic, this is exactly what I was looking for! Thanks RiverGuy.