PDT Trigger Value: Rebuild multiple times per day

If you want to set a PDT trigger value that rebuilds twice a day, for example at 0900 and 1700 daily, you can do this with a CASE WHEN statement.

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:

You can turn the table above into a CASE WHEN statement to trigger the PDT rebuilds when the value changes between 0 and 1:

sql_trigger:
  CASE WHEN [current time is between 0900 and 1700]
    THEN 1 
    ELSE 0
  END ;;

The general form you can use for n desired times is shown below:

sql_trigger:
  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 ;;

For more information about sql_trigger values, check out the Caching Queries and Rebuilding PDTs with Datagroups and sql_trigger_value parameter documentation pages.