Conditional updates on sql query

Question

In my code I have written an update query according to the date. That means, I have created an application for payroll, in that monthly they want to add month leave as two. If it is a new joinee that will separate two. This is the process I have done through my code. Now they want to change the model, that is, if the new joinee date of join has been greater than 15 days it should add one day leave. Please help me to do this. And this is my code befor I used:

UPDATE tbl_emploeedetails
SET    elbal = elbal - 2
WHERE  employeestatus = 'L'
       AND ( Month(doj) = Month(Getdate()) - 1
             AND Year(doj) = Year(Getdate())
             AND Day(doj) > 25 )
        OR ( Month(doj) = Month(Getdate())
             AND Year(doj) = Year(Getdate()) )

and this is month leave add query :

update tbl_emploeedetails 
    set elbal = elbal + 2 where employeestatus = 'L'

Prasanna · Accepted Answer · 2012-09-20 08:10:18Z

1

You can use something like this

UPDATE YourTable
SET UpdateColumn =
(CASE
     WHEN <Condition1> THEN <Expression1> 
     WHEN <Condition2> THEN <Expression2> 
     ELSE <Expression3> 
END)

Example:

UPDATE YourTable
SET UpdateColumn =
(CASE
     WHEN A>B THEN D * 2
     WHEN A>C THEN D * 3
     ELSE D * 4
END)

answered Sep 20, 2012 at 8:10

Prasanna

4,7432 gold badges25 silver badges29 bronze badges

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

Collectives™ on Stack Overflow

Conditional updates on sql query

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