View the original community article here
Last tested: June 2019
The Problem
I want to set a PDT Trigger value which rebuilds twice a day. The docs only say how to build once a day! In this example, let's say I want to build at 0900 and 1700 daily.
A Solution
When approaching trigger problems, it helps to envision a table of desired values. In this case, you want to make sure that the trigger value changes at 0900, but doesn't change back at 0901. It needs to be exactly the same value from 0900 to 1659, then change at 1700, then be the same value from 1700 to 0859 the next day. In tabular form:
Time | Value |
0859 | 0 |
0900 | 1 |
0901 | 1 |
1659 | 1 |
1700 | 0 |
1701 | 0 |
We can achieve this pretty easily with a CASE WHEN!
CASE WHEN [current time is between 0900 and 1700]
THEN 1
ELSE 0
END
General Form
Generally, for n desired times:
CASE
WHEN [current time is in between time 1 and 2] THEN 1
WHEN [current time is in between time 2 and 3] THEN 2
...
WHEN [current time is in between time _n-1_ and _n_] THEN _n-1_
ELSE _n_
END
This content is subject to limited support.