sql - Update a table with data from a second table passing a condition from a third

I having some difficulty to update a table column based on the following conditions and scenario.

Condition Table:

EmpID   Consulting-A    Consulting-B    Consulting-C
A1      0               10              20
A2      50              30              0
A3      0               0               70

Data Table:

EmpID   Consulting-A    Consulting-B    Consulting-C
A1      10000           20000           30000
A2      0               35000           15000
A3      0               8000            0

Input Table:

EmpID   [Total Valid Consulting]   
A1      20000 + 30000 = 50000          
A2      0 + 35000 = 35000               
A3      0               

At the end of the day, I want to have the Input Table Total Valid Consulting having the sum of Data Table when it has value greater than zero in the Condition Table.

For example, the Total Valid Consulting for EmpId A1 is only Consulting-B + Consulting-C (from Data Table) since in the Condition Table the EmpID A1 only have Consulting-B and Consulting-C value of greater than zero.

So I want to add only the columns data that has the value from the other table that are greater than zero. Is there a way to do so in SQL Server? This is for my update statement that I'm having issue to come up with.

Appreciate any help and advice. Thanks!

1 Answer

  1. Chris- Reply

    2020-01-24

    You can use CASE statement

    INSERT INTO Input
        SELECT  EmpID,
                CASE WHEN C.[Consulting-A] > 0 THEN CT.[Consulting-A] ELSE 0 END +
                CASE WHEN C.[Consulting-B] > 0 THEN CT.[Consulting-B] ELSE 0 END +
                CASE WHEN C.[Consulting-C] > 0 THEN CT.[Consulting-C] ELSE 0 END AS f
        FROM Condition AS C
        INNER JOIN Data AS CT ON C.EmpID = CT.EmpID
    

Leave a Reply

Your email address will not be published. Required fields are marked *

You can use these HTML tags and attributes <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>