CODEWITHSUNDEEP
sqlsql-server
user2171512
user2171512

Reputation: 561

How to correct write my update query in sql server

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

Upvotes: 1

Views: 85

Answers (1)

LukStorms
LukStorms

Reputation: 29667

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]

Upvotes: 2

Related Questions