How to write a PDT Trigger Value that rebuilds multiple times per day

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

This content is subject to limited support.                

Version history
Last update:
‎06-25-2021 03:12 PM
Updated by: