How do i write this update with if else statement

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

How do i write this update with if else statement

Author

Topic

pnasz
Posting Yak Master

101 Posts

Posted - 2010-10-30 : 08:17:45

How do i write this update with if else statement

update AttendanceResult
set EmpSIN=RIGHT('00' + CONVERT(varchar(2),FLOOR(m.first_in)),2) + ':' + RIGHT('00' + CONVERT(varchar(2),FLOOR(((m.first_in-FLOOR(m.first_in))*60))),2) + ':' + RIGHT('00' + CONVERT(varchar(2),FLOOR(((m.first_in-FLOOR(m.first_in))*60)-FLOOR(((m.first_in-FLOOR(m.first_in))*60)))*60),2),
EmpSOut=RIGHT('00' + CONVERT(varchar(2),FLOOR(m.first_out)),2) + ':' + RIGHT('00' + CONVERT(varchar(2),FLOOR(((m.first_out-FLOOR(m.first_out))*60))),2) + ':' + RIGHT('00' + CONVERT(varchar(2),FLOOR(((m.first_out-FLOOR(m.first_out))*60)-FLOOR(((m.first_out-FLOOR(m.first_out))*60)))*60),2)
from EmpTimeSchedule$ as m where AttendanceResult.EmpID=m.empno and AttendanceResult.weekdayname!='Thursday'

update AttendanceResult

set EmpSIN=RIGHT('00' + CONVERT(varchar(2),FLOOR(m.first_in1)),2) + ':' + RIGHT('00' + CONVERT(varchar(2),FLOOR(((m.first_in1-FLOOR(m.first_in1))*60))),2) + ':' + RIGHT('00' + CONVERT(varchar(2),FLOOR(((m.first_in1-FLOOR(m.first_in1))*60)-FLOOR(((m.first_in1-FLOOR(m.first_in1))*60)))*60),2),
EmpSOut=RIGHT('00' + CONVERT(varchar(2),FLOOR(m.first_out1)),2) + ':' + RIGHT('00' + CONVERT(varchar(2),FLOOR(((m.first_out1-FLOOR(m.first_out1))*60))),2) + ':' + RIGHT('00' + CONVERT(varchar(2),FLOOR(((m.first_out1-FLOOR(m.first_out1))*60)-FLOOR(((m.first_out1-FLOOR(m.first_out1))*60)))*60),2)
from EmpTimeSchedule$ as m where AttendanceResult.EmpID=m.empno and AttendanceResult.weekdayname='Thursday'

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2010-10-30 : 08:31:02

not sure what you are asking for but

if <condition>
update ...
else
update ....

can also bracket the statements

if <condition>
begin
update ...
end
else
begin
update ....
end

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

pnasz
Posting Yak Master

101 Posts

Posted - 2010-10-30 : 08:37:56

if based on weekdayname i want to write update query.

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2010-10-30 : 08:46:15

You mean update for the current day?
change the where clause

AttendanceResult.weekdayname=datename(dw,getdate())

or maybe you want the condition
if datename(dw,getdate()) <> 'Thursday'

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2010-10-30 : 09:47:52

Think this is the same question as
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=152342

But that one has a better description of what is wanted so suggest it is continued on that thread.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources