January 20, 2015 at 12:39 pm
Below are my table Structure,
declare @tblCompanyProess table (IdProcess int identity(1,1), UserId int,UserCompProcessId int, Amount money, IsProcessed bit default 0);
declare @UserBalance table(BalanceId int identity(1,1), UserId int, UserAmountBalance money);
insert into @tblCompanyProess(UserId,UserCompProcessId,Amount,IsProcessed)
select 1 as UserId,300 as UserCompProcessId,20 as Amount,0 as IsProcessed union all
select 2 as UserId,300 as UserCompProcessId,30 as Amount,0 as IsProcessed union all
select 3 as UserId,300 as UserCompProcessId,50 as Amount,0 as IsProcessed union all
select 4 as UserId,300 as UserCompProcessId,60 as Amount,0 as IsProcessed union all
select 1 as UserId,400 as UserCompProcessId,100 as Amount,0 as IsProcessed union all
select 2 as UserId,400 as UserCompProcessId,70 as Amount,0 as IsProcessed union all
select 1 as UserId,100 as UserCompProcessId,100 as Amount,1 as IsProcessed union all
select 2 as UserId,100 as UserCompProcessId,80 as Amount,1 as IsProcessed ;
insert into @UserBalance(UserId,UserAmountBalance)
select 1 as UserId, 2000 as UserAmountBalance union all
select 2 as UserId, 3000 as UserAmountBalance union all
select 3 as UserId, 4000 as UserAmountBalance union all
select 4 as UserId, 5000 as UserAmountBalance
select * from @tblCompanyProess;
select * from @UserBalance;
Requirement:
I should not use loop. Basically i need to avoid RBAR.I need to take the records from @tblCompanyProess where IsProcessed = 0 based on UserId,UserCompProcessId
and get the Amount and update into @UserBalance table on UserAmountBalance column. Once updated the amount then i need to make the isProcessed =1 on the table @tblCompanyProess
After doing this, the table @UserBalance will look like below,
With output as (
select 1 as UserId, 2120 as UserAmountBalance union all
select 2 as UserId, 3100 as UserAmountBalance union all
select 3 as UserId, 4050 as UserAmountBalance union all
select 4 as UserId, 5060 as UserAmountBalance )
select * from output;
the table @tblCompanyProess will look like below,
select 1 as UserId,300 as UserCompProcessId,20 as Amount,1 as IsProcessed union all
select 2 as UserId,300 as UserCompProcessId,30 as Amount,1 as IsProcessed union all
select 3 as UserId,300 as UserCompProcessId,50 as Amount,1 as IsProcessed union all
select 4 as UserId,300 as UserCompProcessId,60 as Amount,1 as IsProcessed union all
select 1 as UserId,400 as UserCompProcessId,100 as Amount,1 as IsProcessed union all
select 2 as UserId,400 as UserCompProcessId,70 as Amount,1 as IsProcessed union all
select 1 as UserId,100 as UserCompProcessId,100 as Amount,1 as IsProcessed union all
select 2 as UserId,100 as UserCompProcessId,80 as Amount,1 as IsProcessed ;
Is it possible to achieve without looping.
January 20, 2015 at 12:42 pm
Awesome job with ddl and sample data. The problem here is that your desired output is exactly the same as the data you start with.
_______________________________________________________________
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/
January 20, 2015 at 12:59 pm
Hi Sean,
thanks for the reply and my bad. updated the data on my original post two process happening
1. Updating the balance on the table @UserBalance
2. Making the IsProcessed = 1 on the table @tblCompanyProess once the update over.
any solution please
January 20, 2015 at 1:06 pm
KGJ-Dev (1/20/2015)
Hi Sean,thanks for the reply and my bad. updated the data on my original post two process happening
1. Updating the balance on the table @UserBalance
2. Making the IsProcessed = 1 on the table @tblCompanyProess once the update over.
any solution please
That makes more sense. So what have you tried? This is a pretty basic update using SUM.
_______________________________________________________________
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/
January 20, 2015 at 1:21 pm
trying it
January 20, 2015 at 1:24 pm
Below is my try,
DECLARE @Affected TABLE (UserId INT);
WITH data
AS (
SELECT UserId
,ProcessingAmout = SUM(COALESCE(Amount, 0))
FROM @tblCompanyProess
WHERE IsProcessed = 0
)
UPDATE @UserBalance
SET UserAmountBalance = COALESCE(B.UserAmountBalance, 0) + D.ProcessingAmout
OUTPUT INSERTED.IdMember
INTO @Affected(IDMember)
FROM @UserBalance UB
INNER JOIN data D ON UB.UserId = D.UserId;
UPDATE @tblCompanyProess
SET IsProcessed = 1
WHERE UserId IN (
SELECT userid
FROM @Affected
)
AND IsProcessed = 0;
Any suggestion please
January 20, 2015 at 1:55 pm
KGJ-Dev (1/20/2015)
Below is my try,
DECLARE @Affected TABLE (UserId INT);WITH data
AS (
SELECT UserId
,ProcessingAmout = SUM(COALESCE(Amount, 0))
FROM @tblCompanyProess
WHERE IsProcessed = 0
)
UPDATE @UserBalance
SET UserAmountBalance = COALESCE(B.UserAmountBalance, 0) + D.ProcessingAmout
OUTPUT INSERTED.IdMember
INTO @Affected(IDMember)
FROM @UserBalance UB
INNER JOIN data D ON UB.UserId = D.UserId;
UPDATE @tblCompanyProess
SET IsProcessed = 1
WHERE UserId IN (
SELECT userid
FROM @Affected
)
AND IsProcessed = 1;
Your solution is pretty close to what I came up with.
;with cte as
(
select userid, SUM(Amount) sumAmount
from @tblCompanyProess
where isProcessed = 0
group by userid
)
update ub set
ub.UserAmountBalance = ub.UserAmountBalance + c.sumAmount
OUTPUT DELETED.UserId
INTO @Affected(UserID)
from @UserBalance ub
inner join cte c on ub.UserId = c.UserId;
UPDATE tc
SET IsProcessed = 1
FROM @tblCompanyProess tc
INNER JOIN @Affected a on a.UserID = tc.UserId
WHERE tc.IsProcessed = 0;
The big difference is the where clause in the second update. I think you wanted to process only those ID's that are 0. Also, the IN clause is not as efficient as a join.
Hope that helps out.
__________________________________________________________________________________________________________
How to Post to get the most: http://www.sqlservercentral.com/articles/Best+Practices/61537/
January 20, 2015 at 2:06 pm
Added a transaction. These two update statements should always be grouped in an explicit transaction.
SET XACT_ABORT ON;
BEGIN TRAN;
with cte as
(
select userid, SUM(Amount) sumAmount
from @tblCompanyProess
where isProcessed = 0
group by userid
)
update ub set
ub.UserAmountBalance = ub.UserAmountBalance + c.sumAmount
OUTPUT DELETED.UserId
INTO @Affected(UserID)
from @UserBalance ub
inner join cte c on ub.UserId = c.UserId;
UPDATE tc
SET IsProcessed = 1
FROM @tblCompanyProess tc
INNER JOIN @Affected a on a.UserID = tc.UserId
WHERE tc.IsProcessed = 0;
COMMIT;
January 20, 2015 at 2:18 pm
Thanks guys for the response. Have a question about,
will OUTPUT Inserted.UserId doesn't fit for this requirement? In my previous post, i am using this.
January 20, 2015 at 2:19 pm
Stephanie Giovannini (1/20/2015)
Added a transaction. These two update statements should always be grouped in an explicit transaction.
SET XACT_ABORT ON;
BEGIN TRAN;
with cte as
(
select userid, SUM(Amount) sumAmount
from @tblCompanyProess
where isProcessed = 0
group by userid
)
update ub set
ub.UserAmountBalance = ub.UserAmountBalance + c.sumAmount
OUTPUT DELETED.UserId
INTO @Affected(UserID)
from @UserBalance ub
inner join cte c on ub.UserId = c.UserId;
UPDATE tc
SET IsProcessed = 1
FROM @tblCompanyProess tc
INNER JOIN @Affected a on a.UserID = tc.UserId
WHERE tc.IsProcessed = 0;
COMMIT;
Throw a try/catch around this and we have a winner.
_______________________________________________________________
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/
January 20, 2015 at 2:24 pm
In my post i didn't mention about transaction and having try catch block. I am already having that. Didn't include inmy post. So no worries on that piece.
One more question, is there any chance i can get the "UserCompProcessId" into @Affected along with "UserId'? any solution for this please
January 20, 2015 at 2:29 pm
KGJ-Dev (1/20/2015)
In my post i didn't mention about transaction and having try catch block. I am already having that. Didn't include inmy post. So no worries on that piece.One more question, is there any chance i can get the "UserCompProcessId" into @Affected along with "UserId'? any solution for this please
You could...but that negates the aggregate for the sum which is the whole point of what you are doing. What would be the benefit of that piece of data at this point. You are updating what should be a computed column but you have it as a separate table. Your update is doing it in batches which contains multiple values for the UserCompProcessId. I would probably just have an AccountBalance column that is a computed column instead of multiple tables and trying to iterate through all this.
_______________________________________________________________
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/
January 20, 2015 at 2:32 pm
Understood and thanks for your suggestion. What would happen If i use OUTPUT.inserted UserId instead of OUTPUT.deleted UserId
January 20, 2015 at 2:36 pm
KGJ-Dev (1/20/2015)
Understood and thanks for your suggestion. What would happen If i use OUTPUT.inserted UserId instead of OUTPUT.deleted UserId
In an UPDATE or DELETE statement you would get the value prior to updating. In an INSERT you would get NULL.
_______________________________________________________________
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/
January 20, 2015 at 2:45 pm
Thanks sean, in that case i am updating the amount. can both the syntax below results same?
OUTPUT INSERTED.UserId
OUTPUT Deleted.UserId
Viewing 15 posts - 1 through 15 (of 21 total)
You must be logged in to reply to this topic. Login to reply