Sample source SQL for data driver source

A driver source SQL defines the historical series for the a driver such as Overtime, Census, or Vacant Shifts. It is used by the Forecast Engine Data Sync task to retrieve data for specific teams and date ranges.

When you are creating a source SQL for a driver source, take note of these reminders:

Data is returned in a standard three-column shape for Team, Date, and Value.
Only use a valid SELECT statement in your query.
A validator checks SQL grammar, security risks, and general structure when the query is saved, not run.
The query must contain dates as parameters. If these parameters are blank or if the query does not contain a date filter, a full load is generated from 1900-01-01 to 3000-01-01.

Sample query:

SELECT
    t.WBT_NAME          AS TEAM,
    w.WRKD_WORK_DATE    AS DATE,
    SUM(
        CASE
            WHEN w.WRKD_HOURS > w.WRKD_REG_HOURS
                 THEN (w.WRKD_HOURS - w.WRKD_REG_HOURS)
            ELSE 0
        END
    )                   AS VALUE
FROM
    WORK_DETAIL w
    JOIN WORKBRAIN_TEAM t
        ON t.WBT_ID = w.WBT_ID
WHERE
    w.WRKD_WORK_DATE = NVL(:START_DATE, DATE '1900-01-01')
    AND w.WRKD_WORK_DATE   NVL(:END_DATE,  DATE '3000-01-01')
    AND w.WRKD_HOURS  w.WRKD_REG_HOURS    -- Only include overtime
GROUP BY
    t.WBT_NAME,
    w.WRKD_WORK_DATE;

For multiple tables or views, you can use UNION or UNION ALL. Refer to this sample query:

SELECT
    TEAM,
    DATE,
    VALUE
FROM
(
    SELECT
        t.WBT_NAME                AS TEAM,
        w1.WRKD_WORK_DATE         AS DATE,
        SUM(w1.DRIVER_VALUE_1)    AS VALUE
    FROM TABLE_1 w1
    JOIN WORKBRAIN_TEAM t ON t.WBT_ID = w1.WBT_ID
    WHERE
        w1.WRKD_WORK_DATE >= NVL(:START_DATE, DATE '1900-01-01')
        AND w1.WRKD_WORK_DATE <  NVL(:END_DATE,  DATE '3000-01-01')
    GROUP BY
        t.WBT_NAME,
        w1.WRKD_WORK_DATE

    UNION ALL

    SELECT
        t.WBT_NAME                AS TEAM,
        w2.WRKD_WORK_DATE         AS DATE,
        SUM(w2.DRIVER_VALUE_2)    AS VALUE
    FROM TABLE_2 w2
    JOIN WORKBRAIN_TEAM t ON t.WBT_ID = w2.WBT_ID
    WHERE
        w2.WRKD_WORK_DATE >= NVL(:START_DATE, DATE '1900-01-01')
        AND w2.WRKD_WORK_DATE <  NVL(:END_DATE,  DATE '3000-01-01')
    GROUP BY
        t.WBT_NAME,
        w2.WRKD_WORK_DATE
);