Hello! I have a notification working for UserRequests that come in afterhours and weekends:
SELECT UserRequest AS t WHERE 1=1
AND (TIME(start_date) > '17:00:00' OR TIME(start_date)<'08:00:00' OR DATE_FORMAT(start_date, '%w') IN ('0','6'))
That's returning results as expected. I want to either incorporate a clause for Holidays or create a separate notification rule for Holidays but I'm having problems with that OQL. As a test I ran
SELECT UserRequest AS u
WHERE DATE_FORMAT(start_date,'%Y-%m-%d')='2021-07-05'
which returns three tickets that were opened on that date. But when I try this one:
SELECT UserRequest AS u
WHERE DATE_FORMAT(start_date,'%Y-%m-%d') IN (SELECT Holiday)
I get no results. I'm at a loss and would appreciate any advice.
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
The subqueries in OQL like SELECT ... IN (SELECT ...) can only be made on primary/foreign keys.
The Holiday class is brought by the combodo-sla-computation module that is part of either the "Approval process automation" or "SLA considering business hours" extensions. For now they are used only in PHP...
One solution would be to write a dedicated trigger implementation, that does the computation in PHP.
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
Hello! I have a notification working for UserRequests that come in afterhours and weekends:
SELECT UserRequest AS t WHERE 1=1
AND (TIME(start_date) > '17:00:00' OR TIME(start_date)<'08:00:00' OR DATE_FORMAT(start_date, '%w') IN ('0','6'))
That's returning results as expected. I want to either incorporate a clause for Holidays or create a separate notification rule for Holidays but I'm having problems with that OQL. As a test I ran
SELECT UserRequest AS u
WHERE DATE_FORMAT(start_date,'%Y-%m-%d')='2021-07-05'
which returns three tickets that were opened on that date. But when I try this one:
SELECT UserRequest AS u
WHERE DATE_FORMAT(start_date,'%Y-%m-%d') IN (SELECT Holiday)
I get no results. I'm at a loss and would appreciate any advice.
<bump></bump>
Hello,
This is an old thread...
The subqueries in OQL like
SELECT ... IN (SELECT ...)can only be made on primary/foreign keys.The Holiday class is brought by the combodo-sla-computation module that is part of either the "Approval process automation" or "SLA considering business hours" extensions. For now they are used only in PHP...
One solution would be to write a dedicated trigger implementation, that does the computation in PHP.