complex update/insert - SQL Server Forums

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 2005 Forums

Transact-SQL (2005)

complex update/insert

Author

Topic

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2009-11-30 : 05:37:18

/*
The following data is in a table called tblMain.
Looking at this table you see that I have grouped the data.
The qoal is to update tblMain as follows:
for each group, add a new record to the same table as follows:
sum up the fx values,
if the sign is positive use 3, if sign is negative use 4

The grouped records are also updated to show the ID of the new recorded added.
See the RESULT section below.

QUESTION:
How is it possible to produce the RESULT in sql.
I am trying to avoid using cursors.
*/
create table #tblMain
(ID int primary key, IDCode varchar(20), Bcode int, pID tinyint, fx decimal(15, 2),
Processed bit, ProcessedID int)

insert into #tblMain (ID, IDCode, Bcode, pID, fx, Processed, ProcessedID)
select 1, 'X', 937, 3, 638991.36, NULL, NULL
union
select 2, 'X',937,4,-853834.06, NULL, NULL
union
select 3,'X',937,4,-37847.78, NULL, NULL
union
select 4,'X',937,4,-6420.21, NULL, NULL
union
select 5,'D',759,4,-32582.23, NULL, NULL
union
select 6,'D',759,4,-21536.57, NULL, NULL
union
select 7,'W',671,4,-193898.53, NULL, NULL
union
select 8,'W',671,4,-66412.44, NULL, NULL
union
select 9,'Q',671,3,49833.14, NULL, NULL
union
select 10,'Q',671,4,-332.06, NULL, NULL

select * from #tblMain

truncate table #tblMain

--RESULT
insert into #tblMain (ID, IDCode, Bcode, pID, fx, Processed, ProcessedID)
select 1, 'X', 937, 3, 638991.36, 1, 50
union
select 2, 'X',937,4,-853834.06, 1, 50
union
select 3,'X',937,4,-37847.78, 1, 50
union
select 4,'X',937,4,-6420.21, 1, 50
union
select 5,'D',759,4,-32582.23, 1, 32
union
select 6,'D',759,4,-21536.57, 1, 32
union
select 7,'W',671,4,-193898.53, 1, 13
union
select 8,'W',671,4,-66412.44, 1, 13
union
select 9,'Q',671,3,49833.14, 1, 43
union
select 10,'Q',671,4,-332.06, 1, 43

UNION
select 50, 'X', 937, 4, -259110.69, 1, NULL --638991.36+(-853834.06)+(-37847.78)+(-6420.21)
UNION
select 32, 'D', 759, 4, -54118.80, 1, NULL ---32582.23+(-21536.57)
UNION
select 13, 'W', 671, 4, -260310.97, 1, NULL ---193898.53+(-66412.44)
UNION
select 43, 'Q', 671, 3, 49501.08, 1, NULL -- 49833.14+(-332.06)

select * from #tblMain

drop table #tblMain

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-11-30 : 09:54:39

[code]Insert into table
select IDCode,case when sum(fx) > 0 then 3 else 4 end,sum(fx),1,NULL
from table
group by IDCode
[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources