Knowledge Drop
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 0END
General Form
Generally, for n desired times:
CASEWHEN [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