Help need in Avoiding Loop - 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 2008 Forums

Transact-SQL (2008)

Help need in Avoiding Loop

Author

Topic

sqllover
Constraint Violating Yak Guru

338 Posts

Posted - 2014-04-01 : 18:26:02

Hi,

Here is my table structure,

;with Users as 
(
select 1 as UserId, 2 as PendingAmount,10 as AvailableAmount union all
select 2 as UserId, 4 as PendingAmount,12 as AvailableAmount union all
select 3 as UserId, 3 as PendingAmount,8 as AvailableAmount union all
select 4 as UserId, 26 as PendingAmount,30 as AvailableAmount union all
select 5 as UserId, 24 as PendingAmount,20 as AvailableAmount union all
select 6 as UserId, 70 as PendingAmount,100 as AvailableAmount 
)

select * from  users;


;with users_transaction as 

( select 1 as UserId, 3 as refund, 0 as status union all
select 1 as UserId, 2 as refund, 0 as status union all
select 2 as UserId, 5 as refund, 0 as status union all
select 2 as UserId, 10 as refund, 0 as status union all
select 3 as UserId, 13 as refund, 0 as status union all
select 1 as UserId, 13 as refund, 1  )

select * from users_transaction

I need to sum the refund amount from table "users_transaction" group by UserId where status = 0 and add the amount into the "Users" table column "PendingAmount " based on the userId.

Once updated the PendingAmount then have to make the corresponding status as 1 on the
users_transaction table.

Is is possible to do without looping the users_transaction table? idf yes Please give me some sample query

Expected Output :

;with Users as 
(
select 1 as UserId, 7 as PendingAmount,10 as AvailableAmount union all
select 2 as UserId, 19 as PendingAmount,12 as AvailableAmount union all
select 3 as UserId, 16 as PendingAmount,8 as AvailableAmount union all
select 4 as UserId, 26 as PendingAmount,30 as AvailableAmount union all
select 5 as UserId, 24 as PendingAmount,20 as AvailableAmount union all
select 6 as UserId, 70 as PendingAmount,100 as AvailableAmount 
)

select * from  users;


;with users_transaction as 

( select 1 as UserId, 3 as refund, 1 as status union all
select 1 as UserId, 2 as refund,1 as status union all
select 2 as UserId, 5 as refund, 1 as status union all
select 2 as UserId, 10 as refund, 1 as status union all
select 3 as UserId, 13 as refund, 1 as status union all
select 1 as UserId, 13 as refund, 1  )

select * from users_transaction

nagino
Yak Posting Veteran

75 Posts

Posted - 2014-04-01 : 22:35:36

[code]BEGIN TRAN;

DECLARE @Work TABLE (UserId int, refund int);

INSERT INTO @Work
SELECT UserId, refund
FROM (
UPDATE users_transaction
SET [status] = 1
OUTPUT inserted.UserId, inserted.refund
WHERE [status] = 0
) Source;

UPDATE Users
SET PendingAmount = PendingAmount + SumOfRefund
FROM (
SELECT UserId, SUM(refund) SumOfRefund
FROM @Work
GROUP BY UserId) Source
WHERE Users.UserId = Source.UserId;

SELECT * FROM Users;
SELECT * FROM users_transaction;

ROLLBACK TRAN;
--COMMIT TRAN;[/code]

-------------------------------------
From Japan
Sorry, my English ability is limited.

sqllover
Constraint Violating Yak Guru

338 Posts

Posted - 2014-04-02 : 07:36:43

Thank you Nagino. the only area where i struck up is updating the status=1. now clear

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources