Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 123»»»

Help need in Avoiding Loop Expand / Collapse
Author
Message
Posted Tuesday, April 1, 2014 4:24 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, April 9, 2014 11:31 AM
Points: 175, Visits: 351
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

Post #1557262
Posted Tuesday, April 1, 2014 7:50 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 9:13 PM
Points: 36,995, Visits: 31,514
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
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1557292
Posted Tuesday, April 1, 2014 8:11 PM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: 2 days ago @ 1:00 PM
Points: 4,196, Visits: 3,235
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...



Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions
Post #1557294
Posted Wednesday, April 2, 2014 5:27 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, April 9, 2014 11:31 AM
Points: 175, Visits: 351
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]
Post #1557409
Posted Wednesday, April 2, 2014 6:36 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 9:46 AM
Points: 7,224, Visits: 13,696
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).



“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

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
Exploring Recursive CTEs by Example Dwain Camps
Post #1557439
Posted Wednesday, April 2, 2014 7:37 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 9:13 PM
Points: 36,995, Visits: 31,514
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
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1557487
Posted Wednesday, April 2, 2014 8:03 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, April 9, 2014 11:31 AM
Points: 175, Visits: 351
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.
Post #1557503
Posted Wednesday, April 2, 2014 7:21 PM This worked for the OP Answer marked as solution


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 9:13 PM
Points: 36,995, Visits: 31,514
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
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1557773
Posted Wednesday, April 2, 2014 7:44 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, April 9, 2014 11:31 AM
Points: 175, Visits: 351
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.
Post #1557778
Posted Wednesday, April 2, 2014 9:48 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 9:13 PM
Points: 36,995, Visits: 31,514
I sure do appreciate the positive feedback. Glad it's working for you.

--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1557796
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse