CODEWITHSUNDEEP
sqlsql-serversql-server-2000
user279521
user279521

Reputation: 4807

Yet another complex SQL 2000 query issue

I have two tables that I want to use in my query, tblEmployee and tblEmpPerformance

The requirement is:

Using tblEmployee.EmpID, tblEmployee.ManagerID, go thru tblEmpPerformance and look at tblEmpPerformance.SalaryRaise and tblEmpPerformance.CommissionRaise, and update tblEmployee accordingly.

Only one of these two fields will have a non-zero value for every record in tblEmpPerformance

if tblEmployee.EmpID = tblEmpPerformance.EmpID then 
If tblEmpPerformance.Salary > 0 then
    update tblEmployee
    Set CompensationType = 'Salary'
    Where tblEmployee.EmpID = tblEmpPerformance.EmpID 
    AND tblEmployee.ManagerID = tblEmpPerformance.ManagerID   

    update tblEmployee
    Set SalaryRaise = tblEmpPerformance.SalaryRaise
    Where tblEmployee.EmpID = tblEmpPerformance.EmpID 
    AND tblEmployee.ManagerID = tblEmpPerformance.ManagerID 

If tblEmpPerformance.Commission > 0 then
    update tblEmployee
    Set CompensationType = 'Commission'
    Where tblEmployee.EmpID = tblEmpPerformance.EmpID 
    AND tblEmployee.ManagerID = tblEmpPerformance.ManagerID 

    update tblEmployee
    Set CommissionRaise = tblEmpPerformance.CommissionRaise
    Where tblEmployee.EmpID = tblEmpPerformance.EmpID 
    AND tblEmployee.ManagerID = tblEmpPerformance.ManagerID

If no matching EmpID's found, then look in tblContractorPerformance

if tblEmployee.EmpID = tblContractorPerformance.EmpID then 
    If tblContractorPerformance.Salary > 0 then
    update tblEmployee
    Set CompensationType = 'Salary'
    Where tblEmployee.EmpID = tblContractorPerformance.EmpID 
    AND tblEmployee.ManagerID = tblContractorPerformance.ManagerID 
    ....
    ....
    ....
    ....

Next tblEmployee.EmpID

Update: DOH!!.. What would be the best way to write this SQL query. Stored Proc or cursor? Any suggesstion on how to design the query would be a great help as well

Upvotes: 1

Views: 100

Answers (2)

Thomas
Thomas

Reputation: 64645

As far as I can tell, you can encapsulate this into a single query:

Update tblEmployee
Set CompensationType = Case
                        When EP.Commission > 0 Then 'Commission'
                        When EP.Salary > 0 Then 'Salary'
                        When CP.Salary > 0 Then 'Salary'
                        Else E.CompensationType -- leave value as is
                        End
    , CommissionRaise = Case
                        When EP.Commission > 0 Then EP.CommissionRaise
                        Else E.CommissionRaise -- leave value as is
                        End
    , SalaryRaise = Case
                        When EP.Commission > 0 Then E.SalaryRaise
                        When EP.Salary > 0 Then EP.SalaryRaise
                        When CP.Salary > 0 Then CP.SalaryRaise
                        Else E.SalaryRaise -- leave value as is
                        End
From tblEmployee As E
    Left Join tblEmployeePerformance As EP
        On EP.EmpID = E.EmpID
            And EP.ManagerId = E.ManagerId
    Left Join tblContractorPerformance As CP
        On CP.EmpID = E.EmpID
            And CP.ManagerId = E.ManagerId

Upvotes: 3

Nathan DeWitt
Nathan DeWitt

Reputation: 6601

Please avoid cursors. Think set-based in dealing with your data. So execute on your first set of data, where tblEmpPerformance.Salary > 0. Do your updates, then do the second operation on the next set of data where tblEmpPerformance.Commission > 0. Lastly, update all the data where the tblContractorPerformance.Salary > 0.

Put these operations in a stored procedure and I'm confident you'll see better performance than the equivalent using cursors.

Upvotes: 2

Related Questions