drop view [V_PLANTA_CUMU_DT827];
GO

CREATE VIEW [dbo].[V_PLANTA_CUMU_DT827] (
  [PROJECT_ID], [REPORT_ID], [MONTH], [YEAR], [COSTS_TOTAL], [EFFORT_TOTAL], [REVENUES_TOTAL], 
  [COSTS_CUMULATIVE], [COSTS_ACTUAL_CUMULATIVE], [COSTS_REMAIN_CUMULATIVE], [EFFORT_CUMULATIVE], [REVENUES_CUMULATIVE])
AS 
  SELECT 
    PROJECTID,
    REPORTID,
    TIMESLICE [MONTH],
    DATEADD(yy, DATEDIFF(yy,0,TIMESLICE),0) [YEAR],
    TC COSTS_TOTAL,
    AE+RE EFFORT_TOTAL,
    AR+RR REVENUES_TOTAL,
    CML_TC COSTS_CUMULATIVE,
    CML_AC COSTS_ACTUAL_CUMULATIVE,
    CML_RC COSTS_REMAIN_CUMULATIVE,
    CML_AE+CML_RE EFFORT_CUMULATIVE,
    CML_AR+CML_RR REVENUES_CUMULATIVE
  FROM DT827_CONDENSED
  WHERE DENSITYRATIO = 3 -- = PR (project level)
UNION ALL
SELECT 
    PROJECTID,
    (SELECT MAX(DI041716) FROM DT823 WHERE DI008416 = REPORTID) REPORTID,
    TIMESLICE "MONTH",
    DATEADD(yy, DATEDIFF(yy,0,TIMESLICE),0) [YEAR],
    PC COSTS_TOTAL,
    0 EFFORT_TOTAL,
    0 REVENUES_TOTAL,
    CML_PC COSTS_CUMULATIVE,
    0 COSTS_ACTUAL_CUMULATIVE,
    0 COSTS_REMAIN_CUMULATIVE,
    0 EFFORT_CUMULATIVE,
    0 REVENUES_CUMULATIVE
  FROM DT827_CONDENSED
  WHERE DENSITYRATIO = 3 -- = PR (project level)
;
