assign a row's data among other rows

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

assign a row's data among other rows

Author

Topic

lappin
Posting Yak Master

182 Posts

Posted - 2011-10-03 : 06:21:59

Hi I have a table which has area data for a number of groupcodes. I need to spread the area of one of the groups (Group 2) amongst the other groups, as if group 2 never existed. The amount each other group gets should be in proportion to it's percentage of the Non-Group2 total area.

My query is fairly complicated so here is a simplified example:
CREATE TABLE [dbo].[Test1](
GroupCode [varchar](10) NULL,
[Area] [numeric](38, 6) NULL,
[PCofTotalArea] [numeric](38, 6) NULL
) ON [PRIMARY]

GO
----------------------------------------------
--demo data

INSERT INTO [Test1]
(GroupCode
,[Area]
,[PCofTotalArea])
VALUES
('1'
,'6.0'
,'0.285500');
INSERT INTO [Test1]
(GroupCode
,[Area]
,[PCofTotalArea])
VALUES
('2'
,'459.4'
,'21.8668')
INSERT INTO [Test1]
(GroupCode
,[Area]
,[PCofTotalArea])
VALUES
('3'
,'1635.500000'
,'77.847500')

In the example above group 2 has an area of '459.4' which is 21.8668 % of the total. This needs spread out between group 1 and group 3. With group 3 getting most in proportion to it's percentage of the total area.

lappin
Posting Yak Master

182 Posts

Posted - 2011-10-03 : 06:36:49

More info:
In my actual query generating this data I am getting the total area using SUM(Area) Over Partiton By -- which gives the total area including Group 2. I think I need to get the total Non-Group 2 Area. So how can I put a WHERE statement inside the Over Partition By, to only SUM the Non-Group2 area?

lionofdezert
Aged Yak Warrior

885 Posts

Posted - 2011-10-03 : 06:38:59

use CASE statement
like SUM (CASE GroupCode WHEN 1 THEN 0 ELSE Area END)

--------------------------
http://connectsql.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-03 : 06:42:48

[code]
DECLARE @Grp2Area [numeric](38, 6)
SELECT @Grp2Area =[Area]
FROM [Test1]
WHERE GroupCode='2'

UPDATE t
SET t.[Area]=t.[Area] + (@Grp2Area *1.0 * [Area]/SUM(CASE WHEN GroupCode in (1,3) THEN Area ELSE 0 END) OVER ())
FROM Table t
WHERE t.GroupCode in (1,3)

[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

lappin
Posting Yak Master

182 Posts

Posted - 2011-10-03 : 07:38:48

thanks for all your help

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-03 : 07:57:12

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources