Updating maximum amounts for totals

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)

Updating maximum amounts for totals

Author

Topic

midavis
Starting Member

23 Posts

Posted - 2011-03-17 : 16:11:19

I have a summary of data inside table Data. There are 3 different RecordTypes ('G','L','I'). I will need to update any records for each fk_id and a RecordType of 'I' with the maximum amount from either the 'I' record or the sum of 'G' and 'L'. I need to do this for amount1, amount2, amount3, etc. separately. I will explain the example below.

Table Data is what the initial data looks like. Looking at the sum of 'G' and 'L' for fk_id of '011AL' and amount1 I get 117.99 which is greater than the amount for 'I' (which is 0) so I need to update the amount1 for the 'L' record type to 117.99. For amount3 the sum for '011AL' is 106.53 which is not greater than amount3 for a RecordType of 'I' so the 117.74 amount should not be changed.

I hope this makes sense. I am just trying to find a solid way to do this that is fast because I will dealing with about 1 million records of data.

Thanks in advance.

CREATE TABLE Data
(
id int IDENTITY(1,1) NOT NULL,
fk_id varchar(20) NOT NULL,
record_type char(1) NOT NULL,
amount1 decimal(10,2) NOT NULL,
amount2 decimal(10,2) NOT NULL,
amount3 decimal(10,2) NOT NULL
)

CREATE TABLE Data2
(
id int IDENTITY(1,1) NOT NULL,
fk_id varchar(20) NOT NULL,
record_type char(1) NOT NULL,
amount1 decimal(10,2) NOT NULL,
amount2 decimal(10,2) NOT NULL,
amount3 decimal(10,2) NOT NULL
)

INSERT INTO Data
Values
( '011AL', 'G', 117.74 , 117.74, 94.23 ),
( '011AL', 'L', 0.25 , 0 , 12.30 ),
( '011AL', 'I', 0 , 0, 117.74 )

INSERT INTO Data2
Values
( '011AL', 'G', 117.74 , 117.74, 117.74 ),
( '011AL', 'L', 0.25 , 0 , 0 ),
( '011AL', 'I', 117.99 , 117.74, 117.74 )

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2011-03-17 : 19:05:49

I'm not clear what the second table represents. Is it just a sample output or is it supposed to be involved in the logic? In either case...[CODE]update i
set amount1 = case
when i.amount1 >= grpGL.amount1
then i.amount1
else grpGL.amount1
end,
amount2 = case
when i.amount2 >= grpGL.amount2
then i.amount2
else grpGL.amount2
end,
amount3 = case
when i.amount3 >= grpGL.amount3
then i.amount3
else grpGL.amount3
end
from
#Data i
inner join
(
select
fk_id,
sum(amount1) amount1,
sum(amount2) amount2,
sum(amount3) amount3
from
#Data
where
record_type in ('G', 'L')
group by
fk_id
) grpGL
on
i.fk_id = grpGL.fk_id
where
i.record_type = 'I'[/CODE]HTH

=======================================
Elitism is the slur directed at merit by mediocrity. -Sydney J. Harris, journalist (1917-1986)

midavis
Starting Member

23 Posts

Posted - 2011-03-18 : 08:07:48

I think this will work fine. I did figure out a solution but yours is simpler. Thanks

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2011-03-18 : 11:50:29

Mi gusto!

=======================================
Elitism is the slur directed at merit by mediocrity. -Sydney J. Harris, journalist (1917-1986)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources