using date and value to choose return value

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 2000 Forums

Transact-SQL (2000)

using date and value to choose return value

Author

Topic

dooglex
Starting Member

1 Post

Posted - 2007-10-22 : 05:16:07

Hi, sorry is i make a huge mistake, im pretty new to this.
Basically i want to change the value returned from a select dependant on the time of day. If the select returns a zero and its during office hours, i want to return the value 'a' instead.
So in sudocode:
If availableStaff = '0' AND (curtime() > 17.00.00 OR curtime < 09.00.00) then return '0'
If availableStaff = '0' AND NOT (curtime() > 17.00.00 OR curtime < 09.00.00) then return 'a'
Else return availableStaff

The code i wrote used nested case statements to try this but since the only software i can use (its T-sql as part of a piece of software) is not a proper IDE its very difficult to understand the problem let alone the soloution.

SELECT
RTrim(csqSum.CSQName) as CSQ,
loggedInAgents,
case when availableAgents = '0' then
case when curtime() > 170000 Then availableAgents = '0'
else availableAgents = 'a'
else availableAgents as availableAgents,
unavailableAgents,

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-10-22 : 05:39:08

[code]Select
....,
case when availableAgents = '0' then
case when convert(datetime, convert(varchar(10), getdate(), 108)) > '17:00:00' or convert(datetime, convert(varchar(10), getdate(), 108)) < '09:00:00' then '0'
else 'a' end
else
availableAgents end as availableAgents,
...[/code]

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-10-22 : 09:11:36

[code]SELECT *,
CASE
WHEN AvailableStaff = '0' AND HOUR(CURRENT_TIMESTAMP) NOT BETWEEN 9 AND 17 THEN '0'
WHEN AvailableStaff = '0' AND HOUR(CURRENT_TIMESTAMP) BETWEEN 9 AND 17 THEN 'a'
ELSE AvailableStaff
END
FROM Table1[/code]

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources