CODEWITHSUNDEEP
sqlsql-server
Khubaib
Khubaib

Reputation: 103

Conditional calculation in SELECT SQL Server

I have Question_Data table as below:

------------------------------
QuestionID | Attempts | Wrong
------------------------------
     1     |   20     |   7
     2     |   1      |   4
     3     |   14     |   2
     4     |   30     |   5

When a request is received, I process it and it results in a table called, "Responses Table": (Sample Responses Table)

---------------------
QuestionID | Response
---------------------
     1     |   T
     2     |   F
     3     |   F
     4     |   F
     5     |   T

Now what i want is that i want to update "Question_Data" table on the basis of "Responses" table as: New "Question_Data" table:

------------------------------
QuestionID | Attempts | Wrong
------------------------------
     1     |   21     |   7
     2     |   2      |   5
     3     |   15     |   3
     4     |   31     |   6
     5     |   1      |   0

I checked QuestionID in "Question_Data" table and if it exists in "Question_Data" table I incremented it "Attempts" and if response is "F" increment "Wrong".

But if QuestionID is not present in "Question_Data" table. Insert a new row with same QuestionID and increment its "Attempts" to 1 and if response is "T" set its wrong to 0.

I am new to SQL. Any help will be appreciated.

Upvotes: 0

Views: 200

Answers (2)

pradip vaghani
pradip vaghani

Reputation: 182

UPDATE  Q
SET     Q.Attempts = Q.Attempts + 1 ,
        Q.Wrong = Q.Wrong + ( CASE WHEN R.Response = 'F' THEN 1
                                   ELSE 0
                              END )
FROM    Question_Data Q
        INNER JOIN Responses_Table R ON Q.QuestionID = R.QuestionID

After update if data is not present in Question_Data run this query

INSERT INTO Question_Data
        (QuestionID ,
          Attempts ,
          Wrong)
    SELECT  
        R.QuestionID ,
        1,
        (CASE WHEN R.Response = 'F' THEN 1
              ELSE 0
         END) Wrong
    FROM    
        Responses_Table R
    LEFT JOIN 
        Question_Data Q ON Q.QuestionID = R.QuestionID
    WHERE   
        Q.QuestionID IS NULL

Upvotes: 2

Mureinik
Mureinik

Reputation: 311228

Assuming that the QuestionID is unique in both the Questions_Data and Responses table, you could use the update-join syntax:

UPDATE qd
SET    Attempts = Attempts + 1,
       Wrong = Wrong + CASE Response WHEN 'F' THEN 1 ELSE 0 END
FROM   Questions_Data qd
JOIN   Reponse r ON qd.QuestionID = r.QuestionID

Upvotes: 0

Related Questions