플라이트 플랜 쿼리
마지막 업데이트: 2025년 4월 25일
작성 대상:
- 사용자
이 문서의 쿼리를 사용하여 고급 Analytics의 시각화와 유사한 데이터 시각화를 만들 수 있습니다.
쿼리는 향상된 Analytics에 표시된 결과와 유사한 결과를 생성하지만 정확히 일치하지 않을 수 있습니다.
전제 조건
시작하기 전에 다음을 수행해야 합니다
-
Business Intelligence(BI) 도구와의 연결 설정:
연결을 설정한 후에는 이 문서의 쿼리를 사용하여 데이터를 추출하고 시각화할 수 있습니다.
프로젝트 계획 시간 조정(날짜 범위 내)
WITH VALID_PROJECTS AS (
SELECT
projectid,
plannedstartdate as projectplannedstartdate,
plannedcompletiondate as projectplannedcompletiondate,
actualstartdate as projectactualstartdate,
actualcompletiondate as projectactualcompletiondate,
condition as projectcondition,
name as projectname,
status,
groupid,
conditiontype,
calendardate as snapshotdate
FROM PROJECTS_DAILY_HISTORY
WHERE nvl(trim(deleted),'-1') != '1'
AND (
to_date(actualcompletiondate) > '2021-01-01'
OR actualcompletiondate is NULL
)
), VALID_CUSTOMENUMS AS (
SELECT
distinct valueasstring,
groupid,
enumclass,
equateswith,
label,
calendardate
FROM CUSTOMENUMS_DAILY_HISTORY
), VALID_TASKS AS (
SELECT
projectid,
taskid,
plannedminutes,
planneddurationminutes,
completedminutes,
completedplannedminutes,
completedplanneddurationminutes,
taskcompleted,
numberofchildren,
snapshotdate,
CASE
WHEN actualcompletiondate is not NULL or percentcomplete = '100' or status='CPL' THEN 0
ELSE plannedminutes - completedplannedminutes
END as remainingminutes,
CASE
WHEN actualcompletiondate is not NULL or percentcomplete = '100' or status='CPL' THEN 0
ELSE planneddurationminutes - completedplanneddurationminutes
END as remainingdurationminutes
FROM (
SELECT
a.projectid,a.taskid,
a.actualcompletiondate,
a.percentcomplete,
a.numberofchildren,
a.plannedminutes,
a.planneddurationminutes,
a.completedminutes,
a.completedplannedminutes,
a.completedplanneddurationminutes,
a.snapshotdate,
a.status,
CASE
WHEN a.actualcompletiondate is not NULL or a.percentcomplete = '100' or a.status='CPL' THEN 1
ELSE 0
END as taskcompleted
FROM (
SELECT
distinct
t.projectid,t.taskid,
t.actualcompletiondate,
t.percentcomplete,
t.numberofchildren,
t.workrequired as plannedminutes,
t.plannedDurationMinutes as planneddurationminutes,
t.actualwork as completedminutes,
round((t.workrequired * t.percentcomplete)/100) as completedplannedminutes,
round((t.plannedDurationMinutes * t.percentcomplete)/100) as completedplanneddurationminutes,
t.groupid,
t.status,
t.calendardate as snapshotdate
FROM TASKS_DAILY_HISTORY t
WHERE nvl(trim(t.deleted),'-1') != '1'
) a
INNER JOIN valid_projects v ON (a.projectid = v.projectid AND a.snapshotdate = v.snapshotdate)
)
), VALID_ISSUES AS (
SELECT
projectid,
optaskid,
plannedminutes,
planneddurationminutes,
completedminutes,
completedplannedminutes,
completedplanneddurationminutes,
issueresolved,
calendardate as snapshotdate,
CASE
WHEN actualcompletiondate is not NULL or percentcomplete = '100' or equateswith='CLS' or (equateswith='CLS' and status='CLS') THEN 0
ELSE plannedminutes - completedplannedminutes
END as remainingminutes,
CASE
WHEN actualcompletiondate is not NULL or percentcomplete = '100' or equateswith='CLS' or (equateswith='CLS' and status='CLS') THEN 0
ELSE planneddurationminutes - completedplanneddurationminutes END as remainingdurationminutes
FROM (
SELECT
projectid,
optaskid,
actualcompletiondate,
equateswith,
plannedminutes,
planneddurationminutes,
percentcomplete,
completedminutes,
status,
CASE
WHEN actualcompletiondate is not NULL or percentcomplete = '100' or equateswith = 'CLS' or (equateswith='CLS' and status='CLS') THEN 1
ELSE 0
END as issueresolved,
round((plannedminutes * percentcomplete)/100) as completedplannedminutes,
round((planneddurationminutes * percentcomplete)/100) as completedplanneddurationminutes,
calendardate
FROM (
SELECT
a.projectid,
a.optaskid,
a.actualcompletiondate,
ce.equateswith,
a.plannedminutes,
a.planneddurationminutes,
a.completedminutes,
a.calendardate,
a.status,
CASE
WHEN a.percentcomplete is not null THEN a.percentcomplete
WHEN a.actualcompletiondate is not NULL or a.percentcomplete = '100' or ce.equateswith='CLS' or (ce.equateswith='CLS' and a.status='CLS') THEN 100
ELSE 0
END as percentcomplete
FROM (
SELECT
distinct t.projectid,
t.optaskid,
t.actualcompletiondate,
t.workrequired as plannedminutes,
t.planneddurationminutes as planneddurationminutes,
t.actualworkrequired as completedminutes,
t.calendardate as calendardate,
t.percentcomplete,
t.status,
FROM OPTASKS_DAILY_HISTORY t
WHERE nvl(trim(t.deleted),'-1') != '1'
AND t.optasktype in ('ISU','BUG')
) a
INNER JOIN VALID_PROJECTS v ON (a.projectid=v.projectid AND a.calendardate = v.snapshotdate)
LEFT JOIN VALID_CUSTOMENUMS ce ON (ce.valueasstring = a.status AND ce.groupid = v.groupid AND a.projectid = v.projectid AND ce.enumclass='STATUS_OPTASK' AND a.calendardate = ce.calendardate)
)
)
), AGGREGATE_TASKS AS (
SELECT
projectid,
snapshotdate,
SUM(CASE WHEN numberofchildren=0 THEN plannedminutes ELSE 0 END) as plannedminutes,
SUM(CASE WHEN numberofchildren=0 THEN planneddurationminutes ELSE 0 END) as planneddurationminutes,
SUM(CASE WHEN numberofchildren=0 THEN completedminutes ELSE 0 END) as completedminutes,
SUM(CASE WHEN numberofchildren=0 THEN completedplannedminutes ELSE 0 END) as completedplannedminutes,
SUM(CASE WHEN numberofchildren=0 THEN completedplanneddurationminutes ELSE 0 END) as completedplanneddurationminutes,
SUM(CASE WHEN numberofchildren=0 THEN remainingminutes ELSE 0 END) as remainingminutes,
SUM(CASE WHEN numberofchildren=0 THEN remainingdurationminutes ELSE 0 END) as remainingdurationminutes,
count(CASE WHEN numberofchildren=0 THEN taskid ELSE NULL END) as totaltaskcount,
SUM(CASE WHEN numberofchildren=0 THEN taskcompleted ELSE 0 END) as totalcompletedtaskcount
FROM valid_tasks
GROUP BY projectid, snapshotdate
), AGGREGATE_ISSUES AS (
SELECT
projectid,
snapshotdate,
SUM(plannedminutes) as issueplannedminutes,
SUM(planneddurationminutes) as issueplanneddurationminutes,
SUM(completedminutes) as issuecompletedminutes,
SUM(completedplannedminutes) as issuecompletedplannedminutes,
SUM(completedplanneddurationminutes) as issuecompletedplanneddurationminutes,
SUM(remainingminutes) as issueremainingminutes,
SUM(remainingdurationminutes) as issueremainingdurationminutes,
count(optaskid) as totalissuecount,
SUM(issueresolved) as totalresolvedissue
FROM valid_issues
GROUP BY projectid, snapshotdate
)
SELECT
p.projectid as projectguid,
p.projectname,
p.projectplannedstartdate as projectplannedstartdate,
p.projectplannedcompletiondate as projectplannedcompletiondate,
p.projectactualstartdate as projectactualstartdate,
p.projectactualcompletiondate as projectactualcompletiondate,
p.projectcondition as projectcond,
coalesce(t.plannedminutes, 0) as plannedminutes,
coalesce(t.planneddurationminutes, 0) as planneddurationminutes,
coalesce(t.completedminutes, 0) as completedminutes,
coalesce(t.completedplannedminutes, 0) as completedplannedminutes,
coalesce(t.completedplanneddurationminutes,0) as completedplanneddurationminutes,
coalesce(t.remainingminutes,0) as remainingminutes,
coalesce(t.remainingdurationminutes, 0) as remainingdurationminutes,
coalesce(cast(t.totaltaskcount as Integer),0) - coalesce(cast(pt.totaltaskcount as Integer),0) as taskaddeddaily,
coalesce(cast(t.totalcompletedtaskcount as Integer),0) - coalesce(cast(pt.totalcompletedtaskcount as Integer),0) as taskcompleteddaily,
coalesce(t.plannedminutes,0) - coalesce(pt.plannedminutes,0) as plannedminutesdaily,
coalesce(t.planneddurationminutes,0) - coalesce(pt.planneddurationminutes,0) as planneddurationminutesdaily,
coalesce(t.completedminutes,0) - coalesce(pt.completedminutes,0) as completedminutesdaily,
coalesce(t.completedplannedminutes,0) - coalesce(pt.completedplannedminutes,0) as completedplannedminutesdaily,
coalesce(t.completedplanneddurationminutes,0) - coalesce(pt.completedplanneddurationminutes,0) as completedplanneddurationminutesdaily,
CASE
WHEN ce.equateswith='CPL' THEN TRUE
WHEN ce.equateswith is NULL and p.status='CPL' THEN TRUE
ELSE FALSE
END as iscompleted,
coalesce(cast(t.totaltaskcount as Integer),0) as totaltaskcount,
coalesce(cast(t.totalcompletedtaskcount as Integer),0) as totalcompletedtaskcount,
coalesce(i.issueplannedminutes, 0) as issueplannedminutes,
coalesce(i.issueplanneddurationminutes, 0) as issueplanneddurationminutes,
coalesce(i.issuecompletedminutes, 0) as issuecompletedminutes,
coalesce(i.issuecompletedplannedminutes, 0) as issuecompletedplannedminutes,
coalesce(i.issuecompletedplanneddurationminutes,0) as issuecompletedplanneddurationminutes,
coalesce(i.issueremainingminutes,0) as issueremainingminutes,
coalesce(i.issueremainingdurationminutes, 0) as issueremainingdurationpminutes,
coalesce(cast(i.totalissuecount as Integer),0) - coalesce(cast(pi.totalissuecount as Integer),0) as issueaddeddaily,
coalesce(cast(i.totalresolvedissue as Integer),0) - coalesce(cast(pi.totalresolvedissue as Integer),0) as issueresolveddaily,
coalesce(i.issueplannedminutes,0) - coalesce(pi.issueplannedminutes,0) as issueplannedminutesdaily,
coalesce(i.issueplanneddurationminutes,0) - coalesce(pi.issueplanneddurationminutes,0) as issueplanneddurationminutesdaily,
coalesce(i.issuecompletedminutes,0) - coalesce(pi.issuecompletedminutes,0) as issuecompletedminutesdaily,
coalesce(i.issuecompletedplannedminutes,0) - coalesce(pi.issuecompletedplannedminutes,0) as issuecompletedplannedminutesdaily,
coalesce(i.issuecompletedplanneddurationminutes,0) - coalesce(pi.issuecompletedplanneddurationminutes,0) as issuecompletedplanneddurationminutesdaily,
coalesce(cast(i.totalissuecount as Integer),0) as totalissuecount,
coalesce(cast(i.totalresolvedissue as Integer),0) as totalresolvedissue,
convert_timezone('UTC',current_timestamp)::timestamp_ntz as dl_load_ts,
ce2.equateswith as conditionequateswith,
ce2.label as conditionlabel,
p.status as status,
ce.equateswith as statusequateswith,
ce.label as statuslabel,
split_part(p.status, ':', 2) as statuspending,
p.conditiontype as projectconditiontype,
CASE
WHEN p.conditiontype = 'PG' THEN 'PROGRESS STATUS'
WHEN p.conditiontype = 'MN' THEN 'MANUAL'
END as projectconditiontypename
FROM valid_projects p
LEFT JOIN (
SELECT
customenumid,
enumclass,
calendardate,
label,
equateswith
FROM CUSTOMENUMS_DAILY_HISTORY) ce ON (ce.enumclass = 'STATUS_PROJ' AND p.status = ce.label AND ce.calendardate = p.snapshotdate)
LEFT JOIN aggregate_tasks t ON (p.snapshotdate = t.snapshotdate AND t.projectid = p.projectid)
LEFT JOIN aggregate_issues i ON (p.snapshotdate = i.snapshotdate AND i.projectid = p.projectid)
LEFT JOIN aggregate_tasks pt ON (p.snapshotdate = dateadd(days,1,pt.snapshotdate) AND p.projectid = pt.projectid)
LEFT JOIN aggregate_issues pi ON (p.snapshotdate = dateadd(days,1,pi.snapshotdate) AND p.projectid = pi.projectid)
LEFT JOIN (
SELECT
customenumid,
enumclass,
calendardate,
label,
equateswith
FROM CUSTOMENUMS_DAILY_HISTORY) ce2 ON (ce2.enumclass = 'CONDITION_PROJ' AND p.projectcondition = ce2.label AND ce2.calendardate = p.snapshotdate)