Compairing Hours - SQL Server Forums

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

Compairing Hours

Author

Topic

binto
Yak Posting Veteran

59 Posts

Posted - 2010-02-09 : 06:30:38

Hi All
I have following table

Declare @T table(W_Name varchar(50),W_Hours varchar(10),W_Colour varchar(10))
INSERT INTO @T (W_Name,W_Hours)
SELECT 'John','35:20'
UNION ALL 'Mathew','29:40'
UNION ALL 'Steve','55:35'
UNION ALL 'Dan','19:15'
UNION ALL 'Dale','32:05'
UNION ALL 'Prince','38:25'

I have 'W_Hours' as Working Hours.I have to update 'W_Colour'
in such a way that if W_Hours greater than 30:00 then 'W_Colour' should be 'yellow'.if W_Hours greater than 50:00 then 'W_Colour' should be 'red'.How can I compare the hours with this requirement.Please help me.

Thanks & Regards
Binto Thomas

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-02-09 : 06:55:17

[code]
update @T
set W_Colour = case
when W_Hours >= '50:00' then 'red'
when W_Hours >= '30:00' then 'yellow'
end
[/code]

KH
[spoiler]Time is always against us[/spoiler]

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-02-09 : 07:00:25

[code]Declare @T table(W_Name varchar(50),W_Hours varchar(10),W_Colour varchar(10))
INSERT INTO @T (W_Name,W_Hours)
SELECT 'John','35:20'
UNION ALL SELECT 'Mathew','29:40'
UNION ALL SELECT 'Steve','55:35'
UNION ALL SELECT 'Dan','19:15'
UNION ALL SELECT 'Dale','32:05'
UNION ALL SELECT 'Prince','38:25'

select * from @T

update @T
set W_Colour=
CASE
WHEN convert(int,replace(W_Hours,':','')) > 5000 then 'red'
WHEN convert(int,replace(W_Hours,':','')) > 3000 then 'yellow'
ELSE W_Colour
END
where convert(int,replace(W_Hours,':','')) > 3000

select * from @T[/code]

No, you're never too old to Yak'n'Roll if you're too young to die.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-02-09 : 07:01:50