Results 1 to 6 of 6
  1. #1
    Join Date
    Apr 2011
    Posts
    27

    Unanswered: Help need in Avoiding Loop

    Hi,

    Here is my table structure:
    Code:
    ;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 :
    Code:
    ;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

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    No.you can't update a column from a select statement.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    Jan 2013
    Posts
    354
    Provided Answers: 1
    >> Here is my table structure: <<

    NO, it is not! Structure means DDL, so that people do not have to guess what the keys, constraints, Declarative Referential Integrity, data types, etc. in your schema are. Learn how to follow ISO-11179 data element naming conventions and formatting rules. Temporal data should use ISO-8601 formats. Code should be in Standard SQL as much as possible and not local dialect.

    The narrative you posted is wrong in so many ways. Identifiers are not integers; you do not do math on them! Money amounts are DECIMAL because you do math on them. A status is a state of being of something; but you have a vague generic “status” in your world. Where are the (start_date, end_date) for the existence of this state of being? What this looks like is a bit flag; we would not use bit flags in SQL. That was assembly language programming.

    CREATE TABLE Users
    (user_id CHAR(10) NOT NULL PRIMARY KEY,
    -- pending_amt DECIMAL (12,2) NOT NULL, -- no! Computed column
    available_amt DECIMAL (12,2) NOT NULL);

    Here is what you posted: it is not a table because it has no key.

    CREATE TABLE Users_Transactions --- non-table crap
    (user_id CHAR(10) NOT NULL
    REFERENCES Users(user_id), -- do you know what this is?
    refund_amt DECIMAL (12,2) NOT NULL,
    foobar_flg SMALLINT DEFAULT 0 NOT NULL
    CHECK(foobar_flg IN (0,1)) -– assembly language.
    );

    >> I need to sum the refund amount from table "Users_Transactions" group by user_id where status = 0 and add the amount into the "Users" table column "pending_amt" based on the user_id. <<

    NO! In RDBMS, we can have virtual columns; this is not punch card EDP. We do not store computed columns. Now, let's guess at a key and fix this mess.

    CREATE TABLE Users_Transactions --- corrected
    (user_id CHAR(10) NOT NULL
    REFERENCES Users(user_id),
    trans_date DATE DEFAULT CURRENT_TIMESTAMP NOT NULL,
    PRIMARY KEY (user_id, trans_date), ---guess
    refund_amt DECIMAL (12,2) NOT NULL);

    CREATE VIEW User_Balances
    AS
    SELECT user_id, SUM(refund_amt) AS refund_amt_tot
    FROM Users_Transactions
    GROUP BY user_id;

    >> Once updated the “pending_amt” then have to make the corresponding foobar_flg as 1 on the Users_Transactions table. <<

    WHY? Let me answer: this is how we would do it with punch cards that had been run thru an EAM. We would punch the processed cards so we could exclude them in the next report.

    I think all you need is a VIEW:

    CREATE VIEW User_Balances
    AS
    SELECT T.user_id, U.available_amt,
    SUM(T.refund_amt) AS refund_amt_tot
    FROM Users_Transactions AS T, Users AS U
    WHERE U.user_id = T.user_id
    GROUP BY T.user_id, U.available_amt;

    I have been teaching SQL for decades, so when I estimate that you are 3-4 years away from using SQL and RDBMS correctly, listen to me. Your mindset is wrong, your terminology is wrong and you do not know the syntax.

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Celko:

    I'm pretty sure that the OP (born2acheive) is trying to obfuscate a few details, and that leads to some of the problems that you cited. Not everyone has your experience, and you set a VERY high standard. You're right, born2acheive has a long way to go to be at your level, but that doesn't mean that they can't learn and become able to do practical SQL in months instead of years.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  5. #5
    Join Date
    Apr 2011
    Posts
    27
    Hi Celko,

    Wow, Nice explanation. I agree. I just gave sample data how my actual table will be. Going forward i will post the DDL instead of posting CTE. If we work for service based companies, learning one technology is cumbersome because past three years i worked in oracle and currently i am working in sqlserverR2. before past 3 years i was working in mysql and db2. Also i am not Database developer. I am a frontend developer and doing backend coding also. so life is full of learning process. i will keep on learning from matured guys as like you and PAT.

    Last but not least i understood your explanation. Thank you.

    Hi Pat,

    Thanks for considering me and this thread.

  6. #6
    Join Date
    Apr 2011
    Posts
    27
    This is my try.

    Inside the transaction

    Code:
    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;
    Any comments or suggestions please.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •