Custom Query builder range in IF

Hi all

 

Bit stuck with this one. I'm trying to create a report where data would only be returned for any transaction if the hour of the day is higher than 6 and lower than 23.

I've already managed to convert the time_stamp field to the minute of the day by using this, which returns the minute of any given day for UTC 2:

(((time_stamp-(FLOOR(time_stamp/60/60/24))*60*60*24)/60) 120)

 

My challenge now is figuring out how to use this in an IF statement, specifically a lower and upper limit. If I use something like this where only a lower limit is used, it works as expected:

IF((((time_stamp-(FLOOR(time_stamp/60/60/24))*60*60*24)/60) 120),>=,360,(SUM(Tot_ok_Hits Tot_critical_Hits Tot_minor_Hits)/(COUNT(Tot_ok_Hits))*100),999999)

Likewise if I only use an upper limit:

IF((((time_stamp-(FLOOR(time_stamp/60/60/24))*60*60*24)/60) 120),<=,1380,(SUM(Tot_ok_Hits Tot_critical_Hits Tot_minor_Hits)/(COUNT(Tot_ok_Hits))*100),999999)

 

From what I could see by googling around, it seems that I need to use an IF statement within an IF statement (which appears and is cumbersome), but what I've tried so far does not work:

IF(IF((((time_stamp-(FLOOR(time_stamp/60/60/24))*60*60*24)/60) 120),>=,360,(SUM(Tot_ok_Hits Tot_critical_Hits Tot_minor_Hits)/(COUNT(Tot_ok_Hits))*100),(((time_stamp-(FLOOR(time_stamp/60/60/24))*60*60*24)/60) 120)),<=,1380,(SUM(Tot_ok_Hits Tot_critical_Hits Tot_minor_Hits)/(COUNT(Tot_ok_Hits))*100),999999)

IF((((time_stamp-(FLOOR(time_stamp/60/60/24))*60*60*24)/60) 120),>=,360,(SUM(Tot_ok_Hits Tot_critical_Hits Tot_minor_Hits)/(COUNT(Tot_ok_Hits))*100),IF((((time_stamp-(FLOOR(time_stamp/60/60/24))*60*60*24)/60) 120),<=,1380,(SUM(Tot_ok_Hits Tot_critical_Hits Tot_minor_Hits)/(COUNT(Tot_ok_Hits))*100),999999))

 

I suspect that the problem is that only the first criteria (i.e. minute of the day being bigger than 360) gets processed, with the second criteria (minute of the day less than 1380) being discarded, or rather never getting processed as BSM regards any value after the first criteria as already being processed.

 

Is there a way to use some other comparator here, like <> or IN or something like that, and if so, how?

 

EDIT: Just to elaborate, I know this seems like a very arduous way of achieving something I could do with filtering, but certain transactions are unavailable during certain times.

The idea is to process all BPM transactions, except mark certain ones as 100% available if the time they ran is between 23:00 and 06:00.

If SLA reporting is the way to go here, I'll accept that and rather focus on that instead.