SQL statement to update field with Case

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

SQL Server 2005 Forums

Transact-SQL (2005)

SQL statement to update field with Case

Author

Topic

liangtp@hotmail.com
Starting Member

27 Posts

Posted - 2008-08-09 : 01:58:13

Hi,

How shall I write a SQL statement to update a "Status" field. For example, if I have a #temp table with following fields.

RecNo, Appointment Time, Arrival Time, Status
1, 1899-12-30 09:48:00.000, 1899-12-30 10:00:00.000, ''
2, 1899-12-30 10:25:00.000, 1899-12-30 10:20:00.000, ''
3, 1899-12-30 10:35:00.000, 1899-12-30 10:10:00.000, ''

If Arrival Time - Appointment Time > 20, update Status = "Late"
If Arrival Time - Appointment Time > -10, update Status = "On Time"
If Arrival Time - Appointment Time < -10, update Status = "Early"

Thanks.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-09 : 02:23:06

[code]UPDATE #Temp
SET Status=CASE WHEN Arrival Time - Appointment Time > 20 THEN 'Late'
WHEN Arrival Time - Appointment Time > -10 THEN 'On Time'
WHEN Arrival Time - Appointment Time < -10 THEN 'Early'
END[/code]