Modern Dashboard | Business hours SWQL Query

Question

Hi

Can any one help me to convert mentioned query to show Todays Availability of Business Hours I  9 AM to 5 PM.

SELECT Availability, Status, Color FROM
(
SELECT concat(round(avg(Availability), 2), ' %' )as Availability, 'UP - ' + SubString(tostring(DATETRUNC('day',GETDATE())) ,0,12 ) as Status, '#30b230' As Color
FROM Orion.ResponseTime rt
WHERE (( rt.node.customproperties.ATM_Branches='ATM Branches') OR (rt.node.customproperties.ATM='Offsite ATM' ))
AND datetime > DATETRUNC('day',GETDATE())

)

yaquaholic · Answer

Version 2 - now with 9am until 5:30pm

WHERE Weekday(rt.DateTime) IN (1,2,3,4,5) 
AND ( ( Hour(rt.DateTime) = 17 AND Minute(rt.DateTime) <= 30 )            OR 
           Hour(rt.DateTime) IN (9,10,11,12,13,14,15,16)
         )

yaquaholic · Answer

Well 9 until 17:59, but hopefully you get the idea

yaquaholic · Answer

Hi MakeCrazy,

You could use the Weekday and Hour functions to limit the tr.DateTime to weekdays between 9am and 5pm as so:

AND Weekday(rt.DateTime) IN (1,2,3,4,5)
AND Hour(rt.DateTime) IN (9,10,11,12,13,14,15,16,17)

Weekday translates the rt.datetime to the day integer, where Sunday = 0 and Saturday = 6
And Hour is the 24 hour digit from the rt.datetime 
The SWQL (time) functions can be viewed here - github.com/.../SWQL-Functions

I hope it helps,
Yaquaholic