April 1, 2014 at 4:24 pm
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 upadted 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
April 1, 2014 at 7:50 pm
Let's turn this into a thinking lesson on "Divide'n'Conquer" methods. To do that, you have to think in columns instead of rows and you have to think about accomplishing just one task at a time.
Forget everything about the UPDATE for right now. What's the first thing that you need to do with the User_Transaction table? Answer in the form of a query.
--Jeff Moden
Change is inevitable... Change for the better is not.
April 1, 2014 at 8:11 pm
Jeff Moden (4/1/2014)
Let's turn this into a thinking lesson on "Divide'n'Conquer" methods. To do that, you have to think in columns instead of rows and you have to think about accomplishing just one task at a time.Forget everything about the UPDATE for right now. What's the first thing that you need to do with the User_Transaction table? Answer in the form of a query.
Jeff: My compliments on your approach. Definitely in the vein of teaching one to fish...
April 2, 2014 at 5:27 am
Hi Jeff,
Yes you correct. i always start to write algorithm before i write logic.
Here is my algo,
1. Get the records from users_transaction table group by user id where status = 0 and make it as result set
2. join the result set with users table with userid as matching condition.
The only area where i am thinking about is how to set status = 1 when i complete the above two steps.
below is my algorithm result of my first and second step,
;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
),
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 U.UserId,(U.PendingAmount + isnull(UT.Refund,0)) as PendingAmount from (
select UserId,sum(refund) as Refund from users_transaction where status = 0 group by UserId) UT
right join Users U on(UT.UserId = U.UserId)
Any suggestions or improvisations please, also how can i make the status=1 after the accomplishment.
[also am sure i have to use transaction. that is later part i will handle that]
April 2, 2014 at 6:36 am
born2achieve (4/2/2014)
...Any suggestions or improvisations please, also how can i make the status=1 after the accomplishment....
In the users_transaction table or the users table? Assuming the former, because we know it contains a "status" column (horrible column name - status of what?) then you will have to run a second update statement. Probably the best way to do this is to capture the keys of the users_transaction table in the OUTPUT clause of the first update (of users), and use them to filter the second update (of users_transaction).
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
April 2, 2014 at 7:37 am
born2achieve (4/2/2014)
Hi Jeff,Yes you correct. i always start to write algorithm before i write logic.
Here is my algo,
1. Get the records from users_transaction table group by user id where status = 0 and make it as result set
2. join the result set with users table with userid as matching condition.
The only area where i am thinking about is how to set status = 1 when i complete the above two steps.
below is my algorithm result of my first and second step,
;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
),
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 U.UserId,(U.PendingAmount + isnull(UT.Refund,0)) as PendingAmount from (
select UserId,sum(refund) as Refund from users_transaction where status = 0 group by UserId) UT
right join Users U on(UT.UserId = U.UserId)
Any suggestions or improvisations please, also how can i make the status=1 after the accomplishment.
[also am sure i have to use transaction. that is later part i will handle that]
The next thing to realize while you're writing code is that all code should be treated as if it were final code because it usually is. You need to adopt a formatting style that's easy to read and stick with it at all times because you'll never go back to fix it.
You also need to learn not to jump ahead of someone trying to teach you something. 😉
Here's what I suggest for the solution to your whole problem. I was going to try to lead you into it so that you'd understand it one important piece at a time including easier ways of providing test data while increasing readability. I stress that because it's bloody important especially when you're probably the next person that will actually have to read it.
Slow down. Write good clean code. Peel one potato at a time. Like this...
DROP TABLE #Users, #Users_Transaction, #Updated;
SELECT d.*
INTO #Users
FROM (
SELECT 1, 2, 10 UNION ALL
SELECT 2, 4, 12 UNION ALL
SELECT 3, 3, 8 UNION ALL
SELECT 4, 26, 30 UNION ALL
SELECT 5, 24, 20 UNION ALL
SELECT 6, 70, 100
) d (UserID, PendingAmount, AvailableAmount)
;
SELECT d.*
INTO #Users_Transaction
FROM (
SELECT 1, 3, 0 UNION ALL
SELECT 1, 2, 0 UNION ALL
SELECT 2, 5, 0 UNION ALL
SELECT 2, 10, 0 UNION ALL
SELECT 3, 13, 0 UNION ALL
SELECT 1, 13, 1
) d (UserID, Refund, Status)
;
--------------------------------------------------------------------------------------------
--===== Set it so if the first UPDATE fails,
-- we won't even start the second update.
-- This really says "If we're in a transaction
-- and something fails, stop processing the
-- transaction and do a rollback if we can".
SET XACT_ABORT ON
;
--===== Create a place to remember the rows we updated.
CREATE TABLE #Updated (UserID INT)
;
--===== Start a transaction because both of these UPDATEs
-- must succeed together or fail together
BEGIN TRANSACTION
;
WITH
ctePreAgg AS
( --=== Preaggregate the refunds by UserID so that we
-- don't have to mess with it in the UPDATE.
SELECT UserID
,TotalRefund = SUM(Refund)
FROM #Users_Transaction
WHERE [Status] = 0
GROUP BY UserID
) --=== Do the update and capture the UserIDs that we updated.
UPDATE u
SET u.PendingAmount = u.PendingAmount + pa.TotalRefund
OUTPUT INSERTED.UserID INTO #Updated (UserID)
FROM #Users u
JOIN ctePreAgg pa
ON u.UserID = pa.UserID
;
--===== Using the captured UserIDs, update the status to
-- identify that the rows have been used.
UPDATE t
SET t.Status = 1
FROM #Users_Transaction t
WHERE t.UserID IN (SELECT UserID FROM #Updated)
AND [Status] = 0
;
--===== If we made it to here without an error,
-- we're good to go. Commit the UPDATEs
-- on both tables.
COMMIT TRANSACTION
;
--Jeff Moden
Change is inevitable... Change for the better is not.
April 2, 2014 at 8:03 am
Thank you Jeff for the great explanation. Before seeing you thread this is what i tried.
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;
After seeing your sample i tweaked some area based on your explanation. Very helpful and i will ensure that going forward i will post good DDL for easy readability.
thank you very much.
April 2, 2014 at 7:21 pm
Thanks for the feedback. What did your final code end up looking like? I ask only because I want to make sure that it won't be a problem for you in the future.
--Jeff Moden
Change is inevitable... Change for the better is not.
April 2, 2014 at 7:44 pm
Hi Jeff,
I am using the code what you have provided. I didn't change anything on yours and tested with more data and it's working great. thanks for your wonderful time on this post.
April 2, 2014 at 9:48 pm
I sure do appreciate the positive feedback. Glad it's working for you.
--Jeff Moden
Change is inevitable... Change for the better is not.
April 4, 2014 at 7:58 am
+1000 on code formatting!!!!! I am a firm believer of having formatted code! It just makes for easier debugging in the future. It is also far easier to read!
Kurt
born2achieve (4/2/2014)
Thank you Jeff for the great explanation. Before seeing you thread this is what i tried.
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;
After seeing your sample i tweaked some area based on your explanation. Very helpful and i will ensure that going forward i will post good DDL for easy readability.
thank you very much.
Kurt W. Zimmerman
SR DBA
Lefrak Organization
New York, NY
http://www.linkedin.com/in/kurtwzimmerman
April 7, 2014 at 1:11 pm
Hi Jeff,
Quick Query about if i have 60000 + records to process with this logic will it break anywhere with timeout issue. could you please suggest me
Also it is possible to generate 60000 + dynamic data for testing with the schema i provided? any help please
April 7, 2014 at 1:26 pm
born2achieve (4/7/2014)
Hi Jeff,Quick Query about if i have 60000 + records to process with this logic will it break anywhere with timeout issue. could you please suggest me
It should run in a flash. But, let's ask the most important question... have you got Point-in-Time backups running on this database? If you don't, stop what you're doing and set that up right NOW! 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
April 7, 2014 at 1:28 pm
Hi Jeff,
thanks for the reply,
Also it is possible to generate 60000 + dynamic data for testing with the schema i provided? any help please
What is point in time database backup? any clue please
April 7, 2014 at 1:31 pm
Database using full recovery model, full backup + transaction log backups at scheduled intervals.
Viewing 15 posts - 1 through 15 (of 29 total)
You must be logged in to reply to this topic. Login to reply