TSQL -> Data selection that depends on what time is it now

Suppose that we have service providers that are available:
(onduty_code)
1 - during day time
2 - at night hours
3 - 24 hours 7 days a week
And we need to return to client application list of service providers that are active now, while checking if current time is day hours or night. And we will add to this list those who works 24/7. Pay attention that we need to write Query as one select statement, that can be used as Data View, and we can not use procedural constructs like [If .. Then .. Else ].

Table data can look like this :
idnameonduty_codephone
1Best Tire1123-000-000-001
2Automotive One2123-000-000-002
3Automotive Two1123-000-000-003
4Roadside Assistance 24/73123-000-000-004
5All in One Auto Repair2123-000-000-005

-- service providers table
CREATE TABLE dbo.service_shops
(
  [id] [int] Identity(1,1) NOT NULL,
  [name] [varchar] (50) NOT NULL,
  [onduty_code] [tinyint] NOT NULL,
  [phone] [varchar] (50)
) ON [PRIMARY];

-- this select returns 0 - if nothing found, 1 - if condition is met
select count(*)
from (select DATEPART(hour, GETDATE()) as h) as a
where a.h BETWEEN 8 AND 20;-- time between 8:00 and 20:00

-- select providers that works at this time + those that always available

select t1.name, t1.phone
from dbo.service_shops AS t1 inner join
(-- day time
  select count(*) * 1 as code
  from (select DATEPART(hour,getdate()) as h) as a
  where a.h BETWEEN 8 AND 20
) as t2 on t1.onduty_code = t2.code
    UNION ALL
select t1.name, t1.phone
from dbo.service_shops AS t1 inner join
(-- night hours
  select count(*) * 2 as code
  from (select DATEPART(hour,getdate()) as h) as a
  where (a.h BETWEEN 1 AND 7) OR (a.h BETWEEN 21 AND 24)
) as t2 on t1.onduty_code = t2.code
    UNION ALL
-- 24 hours 7 days a week
select name, phone from dbo.service_shops where onduty_code = 3;





sqlexamples.info