How to correct write my update query in sql server

Question

how can i write my sql server query to update correct amount. Here is what i have at the moment, but it's not the correct solution.

I am trying to calculate sum of REdeemedBonusAmount by PlayerId and PlayerBonusId, but i need to use DBT.ExternalWAllet flag to take correct Amount from db

UPDATE R SET          
        [RedeemedBonusAmount] = AGGR.[RedeemedBonusAmount]
    FROM #Results R
    JOIN 
    (
        SELECT  Aggr.[PlayerId],
                aggr.[PlayerBonusId],
                    CASE 
                    WHEN DBT.ExternalWallet=1 THEN SUM(Aggr.Amount1)
                    ELSE SUM(Aggr.[Amount2]) 
                END AS [RedeemedBonusAmount]
        FROM table_1 Aggr
        JOIN table_2 DPB ON Aggr.[PlayerId] = DPB.[PlayerId]
        JOIN table_3 DB ON DPB.BonusId = DB.Id
        JOIN table_4 DBT ON DB.BonusTypeId= DBT.Id
        WHERE  Aggr.[TimeId] BETWEEN 2018110100 AND 2018122123 and aggr.playerid=362920
        GROUP BY Aggr.[PlayerId],aggr.[PlayerBonusId],DBT.ExternalWallet
    ) AS AGGR ON R.playerid=Aggr.playerid and R.PlayerBonusId=Aggr.[PlayerBonusId]

Here is the table what i get if i just run select:

PlayerId    PlayerBonusId   RedeemedBonusAmount
362920      619622          380.000000
362920      619624          19.000000
362920      619622          0.000000
362920      619624          0.000000

what are you trying to do? please provide Minimal, Complete, and Verifiable example — Derviş Kayımbaşıoğlu
– Derviş Kayımbaşıoğlu, Commented Dec 20, 2018 at 9:56
I am trying to calculate sum of REdeemedBonusAmount by PlayerId and PlayerBonusId, but i need to use DBT.ExternalWAllet flag to take correct Amount from db — user2171512
– user2171512, Commented Dec 20, 2018 at 9:59
For the sake of Good, add more context and some test data for trying to help you. — Geovanny Hernandez
– Geovanny Hernandez, Commented Dec 20, 2018 at 10:02

LukStorms · Accepted Answer · 2018-12-20 10:02:48Z

2

Instead of CASEing the SUM's, SUM the CASE.

Then you don't also need to GROUP on DBT.ExternalWallet, which isn't used in the join of the outer query.

UPDATE R 
SET [RedeemedBonusAmount] = AGGR.[RedeemedBonusAmount]
FROM #Results R
JOIN 
(
  SELECT  Aggr.[PlayerId],
          Aggr.[PlayerBonusId],
          SUM(CASE 
              WHEN DBT.ExternalWallet=1 THEN Aggr.Amount1
              ELSE Aggr.[Amount2]
              END) AS [RedeemedBonusAmount]
   FROM table_1 Aggr
   JOIN table_2 DPB ON Aggr.[PlayerId] = DPB.[PlayerId]
   JOIN table_3 DB ON DPB.BonusId = DB.Id
   JOIN table_4 DBT ON DB.BonusTypeId= DBT.Id
   WHERE  Aggr.[TimeId] BETWEEN 2018110100 AND 2018122123 and aggr.playerid=362920
   GROUP BY Aggr.[PlayerId],aggr.[PlayerBonusId]
) AS AGGR ON R.playerid=Aggr.playerid and R.PlayerBonusId=Aggr.[PlayerBonusId]

answered Dec 20, 2018 at 10:02

LukStorms

29.8k5 gold badges36 silver badges49 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to correct write my update query in sql server

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related