Excude a record(s), apply its percentage proportionally to remaining records

Question

Post reply

Excude a record(s), apply its percentage proportionally to remaining records

Chrissy321

SSCoach

Points: 15750
More actions
November 8, 2013 at 1:40 pm

#279006

This is a fun one I think. I am not even sure what my expected results should be since I am not sure what the calculation should be.
I want to exclude one or more records from my result set. I want to take the percentage of the excluded records and apply it proportionally to the remaining records. In affect the percentage of the remaining records would be increased proportionally while still summing to 100.
I assume i might have rounding issues....
CREATE TABLE #Components
(
ComponentID int,
Percentage decimal (4,2)
)
INSERT INTO #Components (ComponentID,Percentage)Values (1,8.54)
INSERT INTO #Components (ComponentID,Percentage)Values (2,9.85)
INSERT INTO #Components (ComponentID,Percentage)Values (3,11.57)
INSERT INTO #Components (ComponentID,Percentage)Values (4,11.52)
INSERT INTO #Components (ComponentID,Percentage)Values (5,8.63)
INSERT INTO #Components (ComponentID,Percentage)Values (6,10.20)
INSERT INTO #Components (ComponentID,Percentage)Values (7,8.57)
INSERT INTO #Components (ComponentID,Percentage)Values (8,12.25)
INSERT INTO #Components (ComponentID,Percentage)Values (9,6.50)
INSERT INTO #Components (ComponentID,Percentage)Values (10,12.37)
SELECT * FROM #Components
SELECT SUM(Percentage) FROM #Components
SELECT * FROM #Components WHERE ComponentID <> 1
SELECT SUM(Percentage) FROM #Components WHERE ComponentID <> 1
DROP TABLE #Components
Forum Etiquette: How to post Reporting Services problems

[/url]

Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
[/url]

How to Post Performance Problems - by Gail Shaw
[/url]

Sean Lange SSC Guru Points: 286573 More actions · Answer 1

Not exactly sure what you are trying to do here.

I am not even sure what my expected results should be since I am not sure what the calculation should be.

Not sure you are exactly sure what you are trying to do here either.

Probably need a little bit more information in order to help.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Chrissy321 SSCoach Points: 15750 More actions · Answer 2

Sorry for being unclear. Let me add to the narrative now and then see if I can work out the desired results.

If I exclude record 1 then my total is 91.46. I want to exclude record 1 but still total to 100.

Record 1's percentage is 8.54. I want to take this 8.54 percent and distribute it to records 2-10 so records 2-10 add to 100. Sure I can take 8.54 and add it to record 2 but I want to distribute this proportionally among the remaining records.

Let me see if I can work this out in Excel...

Chrissy321 SSCoach Points: 15750 More actions · Answer 3

I guess this is why you guys say show us what you have tried. This demonstrates the results I am looking for. I'd like to get this in one select statement without the @ExcludePercentage variable...

DECLARE @Components TABLE

(

ComponentID int,

Percentage decimal (4,2)

)

INSERT INTO @Components (ComponentID,Percentage)Values (1,8.54)

INSERT INTO @Components (ComponentID,Percentage)Values (2,9.85)

INSERT INTO @Components (ComponentID,Percentage)Values (3,11.57)

INSERT INTO @Components (ComponentID,Percentage)Values (4,11.52)

INSERT INTO @Components (ComponentID,Percentage)Values (5,8.63)

INSERT INTO @Components (ComponentID,Percentage)Values (6,10.20)

INSERT INTO @Components (ComponentID,Percentage)Values (7,8.57)

INSERT INTO @Components (ComponentID,Percentage)Values (8,12.25)

INSERT INTO @Components (ComponentID,Percentage)Values (9,6.50)

INSERT INTO @Components (ComponentID,Percentage)Values (10,12.37)

--SELECT * FROM @Components

--SELECT SUM(Percentage) FROM @Components

DECLARE @ExcludeRecords TABLE

(

ComponentID int

)

INSERT INTO @ExcludeRecords (ComponentID) VALUES (1)

DECLARE @ExcludePercentage decimal (4,2)

SET @ExcludePercentage =

(

SELECT SUM(Percentage) FROM @Components WHERE ComponentID NOT IN (SELECT ComponentID FROM @ExcludeRecords)

)

PRINT @ExcludePercentage

SELECT

ComponentID,

Percentage,

Percentage/@ExcludePercentage * 100 AS NewPercentage

FROM @Components WHERE ComponentID NOT IN (SELECT ComponentID FROM @ExcludeRecords)

SELECT

SUM

(

Percentage/@ExcludePercentage * 100

)

AS NewSumPercentage

FROM @Components WHERE ComponentID NOT IN (SELECT ComponentID FROM @ExcludeRecords)

mister.magoo SSC-Forever Points: 47068 More actions · Answer 4

Chrissy321 (11/8/2013)
I guess this is why you guys say show us what you have tried. This demonstrates the results I am looking for. I'd like to get this in one select statement without the @ExcludePercentage variable...
DECLARE @Components TABLE
(
ComponentID int,
Percentage decimal (4,2)
)
INSERT INTO @Components (ComponentID,Percentage)Values (1,8.54)
INSERT INTO @Components (ComponentID,Percentage)Values (2,9.85)
INSERT INTO @Components (ComponentID,Percentage)Values (3,11.57)
INSERT INTO @Components (ComponentID,Percentage)Values (4,11.52)
INSERT INTO @Components (ComponentID,Percentage)Values (5,8.63)
INSERT INTO @Components (ComponentID,Percentage)Values (6,10.20)
INSERT INTO @Components (ComponentID,Percentage)Values (7,8.57)
INSERT INTO @Components (ComponentID,Percentage)Values (8,12.25)
INSERT INTO @Components (ComponentID,Percentage)Values (9,6.50)
INSERT INTO @Components (ComponentID,Percentage)Values (10,12.37)
--SELECT * FROM @Components
--SELECT SUM(Percentage) FROM @Components
DECLARE @ExcludeRecords TABLE
(
ComponentID int
)
INSERT INTO @ExcludeRecords (ComponentID) VALUES (1)
DECLARE @ExcludePercentage decimal (4,2)
SET @ExcludePercentage =
(
SELECT SUM(Percentage) FROM @Components WHERE ComponentID NOT IN (SELECT ComponentID FROM @ExcludeRecords)
)
PRINT @ExcludePercentage
SELECT
ComponentID,
Percentage,
Percentage/@ExcludePercentage * 100 AS NewPercentage
FROM @Components WHERE ComponentID NOT IN (SELECT ComponentID FROM @ExcludeRecords)
SELECT
SUM
(
Percentage/@ExcludePercentage * 100
)
AS NewSumPercentage
FROM @Components WHERE ComponentID NOT IN (SELECT ComponentID FROM @ExcludeRecords)

That makes it much easier:

SELECT

ComponentID,

Percentage,

Percentage/sum(Percentage) over() * 100 AS NewPercentage

FROM @Components WHERE ComponentID NOT IN (SELECT ComponentID FROM @ExcludeRecords)

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);