51黑料不打烊

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)