Workfront Data Connect 쿼리 예제

SELECT
    projectid,
    parametervalues,
    name,
    parametervalues:"DE:Business Unit"::int as BusinessUnit,
    parametervalues:"DE:Project ID"::int as ProjectID,
    parametervalues:"DE:Expanded Project Name"::text as ExpandedProjectName
FROM PROJECTS_CURRENT
WHERE ExpandedProjectName is not null

-- Calculate the begin/end effective timestamp and duration in days

SELECT

    projectid,
    name as project_name,
    prev_status as previous_status,
    status,
    status_change_date as status_begin_effective_timestamp,
    case
        when status_change_date is null then NULL
        else
            nvl(lead(status_change_date) ignore nulls over (partition by projectid order by status_change_date), current_timestamp)
    end as status_end_effective_timestamp,
    datediff('DAYS',status_change_date, nvl(lead(status_change_date) ignore nulls over (partition by projectid order by status_change_date), current_timestamp)) as status_duration_days

FROM
    ( -- Filter to just the records that have changed
     SELECT projectid,
        name,
        prev_status,
        status,
        begin_effective_timestamp as status_change_date
     FROM
        (  -- Calculate records where previous status is different
          SELECT DISTINCT
           pe.projectid,
           pe.name AS name,
           pe.STATUS,
           nvl(lag(pe.STATUS) over (partition by pe.projectid order by pe.BEGIN_EFFECTIVE_TIMESTAMP), status) prev_status,
           begin_effective_timestamp

          FROM projects_event pe
         -- Set any WHERE conditions to limit the results as needed
         --WHERE
            -- pe.PROJECTID = '5ebe…c1e1'
        )
        WHERE prev_status != status
    )
    order by status_change_date;