--------------------------------------------------------
--  DDL for Procedure P_RunningSum_827
--------------------------------------------------------
IF  EXISTS (SELECT * FROM sys.procedures WHERE object_id = OBJECT_ID(N'[dbo].[P_RunningSum_827]'))
	DROP PROCEDURE [dbo].[P_RunningSum_827];
GO
CREATE PROCEDURE P_RunningSum_827 
	@DoComplete INT, 
	@ThisProjectID VARCHAR(18), 
	@ThisReportID VARCHAR(10)
AS
  -- Declarations:
BEGIN
	SET NOCOUNT ON;
	DECLARE
	  @Proceeds VARCHAR(2) = '8',
	  @level INTEGER = 0,
	  @i INTEGER = 0;

  -- initialize table DT827_CONDENSED
  IF @DoComplete = 1
    BEGIN
      TRUNCATE TABLE DT827_CONDENSED;
    END;
  ELSE
    BEGIN
      DELETE FROM DT827_CONDENSED WHERE MAINPROJECT = @ThisProjectID AND REPORTID = @ThisReportID;
    END;
  -- 
  -- TIMESLICE: at the moment only on a monthly basis, that means, unit is month - the "first of a month"-date represents the whole month (of the respective year).
  -- 
  IF @DoComplete = 1
    BEGIN
      -- Insert VGR values FROM DT827 into table - costs and efforts per resource, revenues per proceeds
      INSERT INTO DT827_CONDENSED (DENSITYRATIO,MAINPROJECT,PROJECTLEVEL,PARENTPROJECT,PARENTTASK,PROJECTID,REPORTID,TASK,TRESOURCE,TIMESLICE,PC,PC_BL,AC,RC,TC,PE,PE_BL,AE,RE,PR,PR_BL,AR,RR)
      SELECT 1 DENSITYRATIO, -- VGR
        MAINPROJECT,PROJECTLEVEL,PARENTPROJECT,PARENTTASK,PROJECTID,REPORTID,TASK,TRESOURCE,TIMESLICE,
        SUM(COALESCE(PC*Costs,0)) PC,SUM(COALESCE(PC_BL*Costs,0)) PC_BL,SUM(COALESCE(AC*Costs,0)) AC,SUM(COALESCE(RC*Costs,0)) RC,SUM(COALESCE(TC*Costs,0)) TC,
        SUM(COALESCE(PE*Costs,0)) PE,SUM(COALESCE(PE_BL*Costs,0)) PE_BL,SUM(COALESCE(AE*Costs,0)) AE,SUM(COALESCE(RE*Costs,0)) RE,
        SUM(COALESCE(PR*Proceeds,0)) PR,SUM(COALESCE(PR_BL*Proceeds,0)) PR_BL,SUM(COALESCE(AR*Proceeds,0)) AR,SUM(COALESCE(RR*Proceeds,0)) RR
      FROM (
        SELECT p.DI001052 MAINPROJECT, pr.DI008603 PROJECTLEVEL, p.DI001003 PARENTPROJECT, p.DI001004 PARENTTASK, p.DI001001 PROJECTID, pr.DI008416 REPORTID,
          s.DI008517 TASK, s.DI008518 TRESOURCE, DATEADD(mm, DATEDIFF(mm,0,DI008523),0) TIMESLICE,
          s.DI008661 PC,0.0 PC_BL,s.DI008663 AC,s.DI008665 RC,s.DI008666 TC,s.DI008660*r.DI001242 PE,0.0 PE_BL,s.DI008662*r.DI001242 AE,s.DI008664*r.DI001242 RE,
          s.DI008661 PR,0.0 PR_BL,s.DI008663 AR,s.DI008665 RR,
          CASE WHEN r.DI003414 = @Proceeds THEN 0.0 ELSE 1.0 END Costs,CASE WHEN r.DI003414 = @Proceeds THEN 1.0 ELSE 0.0 END Proceeds
        FROM DT461 p INNER JOIN DT823 pr ON p.DI001001 = pr.DI008413
          INNER JOIN DT827 s ON pr.DI008413 = s.DI008516 AND pr.DI008416 = s.DI008535 INNER JOIN DT467 r ON s.DI008518 = r.DI001218
        WHERE  pr.DI028505 = CONVERT(datetime,'01.01.1970',104) AND pr.DI008821 > CONVERT(datetime,'01.01.1970',104)
        UNION ALL
        SELECT p.DI001052 MAINPROJECT, pr.DI008603 PROJECTLEVEL, p.DI001003 PARENTPROJECT, p.DI001004 PARENTTASK, p.DI001001 PROJECTID, pr.DI008416 REPORTID,
          s.DI008517 TASK, s.DI008518 TRESOURCE, DATEADD(mm, DATEDIFF(mm,0,DI008523),0) TIMESLICE,
          0.0 PC,s.DI008661 PC_BL,0.0 AC,0.0 RC,0.0 TC,0.0 PE,s.DI008660*r.DI001242 PE_BL,0.0 AE,0.0 RE,0.0 PR,s.DI008661 PR_BL,0.0 AR,0.0 RR,
          CASE WHEN r.DI003414 = @Proceeds THEN 0.0 ELSE 1.0 END Costs,CASE WHEN r.DI003414 = @Proceeds THEN 1.0 ELSE 0.0 END Proceeds
        FROM DT461 p INNER JOIN DT823 pr ON p.DI001001 = pr.DI008413 INNER JOIN DT823 bl ON pr.DI008578 = bl.DI008413 AND pr.DI008416 = bl.DI008416
          INNER JOIN DT827 s ON p.DI001001 = s.DI008516 AND bl.DI041716 = s.DI008535 INNER JOIN DT467 r ON s.DI008518 = r.DI001218
        WHERE  pr.DI028505 = CONVERT(datetime,'01.01.1970',104) AND pr.DI008821 > CONVERT(datetime,'01.01.1970',104)
      ) t
      GROUP BY MAINPROJECT,PROJECTLEVEL,PARENTPROJECT,PARENTTASK,PROJECTID,REPORTID,TASK,TRESOURCE,TIMESLICE
      ;
      -- Insert VG values into table
      INSERT INTO DT827_CONDENSED (DENSITYRATIO,MAINPROJECT,PROJECTLEVEL,PARENTPROJECT,PARENTTASK,PROJECTID,REPORTID,TASK,TRESOURCE,TIMESLICE,PC,PC_BL,AC,RC,TC,PE,PE_BL,AE,RE,PR,PR_BL,AR,RR)
      SELECT 2 DENSITYRATIO, -- VG
        MAINPROJECT,PROJECTLEVEL,PARENTPROJECT,PARENTTASK,PROJECTID,REPORTID,TASK,'-' TRESOURCE,TIMESLICE,
        SUM(PC) PC,SUM(PC_BL) PC_BL,SUM(AC) AC,SUM(RC) RC,SUM(TC) TC,SUM(PE) PE,SUM(PE_BL) PE_BL,SUM(AE) AE,SUM(RE) RE,SUM(PR) PR,SUM(PR_BL) PR_BL,SUM(AR) AR,SUM(RR) RR
      FROM DT827_CONDENSED
      GROUP BY MAINPROJECT,PROJECTLEVEL,PARENTPROJECT,PARENTTASK,PROJECTID,REPORTID,TASK,TIMESLICE
      ;
      -- Insert aggregated subproject values mapped to parent task into table
      SELECT @level = MAX(COALESCE(PROJECTLEVEL,0)) FROM DT827_CONDENSED WHERE DENSITYRATIO = 2;
	    WHILE @level > 0
	      BEGIN
        INSERT INTO DT827_CONDENSED (DENSITYRATIO,MAINPROJECT,PROJECTLEVEL,PARENTPROJECT,PARENTTASK,PROJECTID,REPORTID,TASK,TRESOURCE,TIMESLICE,PC,PC_BL,AC,RC,TC,PE,PE_BL,AE,RE,PR,PR_BL,AR,RR)
        SELECT 2 DENSITYRATIO, -- VG
          t.MAINPROJECT,s.PROJECTLEVEL-1 PROJECTLEVEL,t.PARENTPROJECT,t.PARENTTASK,t.PROJECTID,s.REPORTID,s.PARENTTASK TASK,s.TRESOURCE,s.TIMESLICE,
          s.PC,s.PC_BL,s.AC,s.RC,s.TC,s.PE,s.PE_BL,s.AE,s.RE,s.PR,s.PR_BL,s.AR,s.RR
        FROM (
          SELECT MAINPROJECT,PROJECTLEVEL,PARENTPROJECT,PARENTTASK,PROJECTID,REPORTID,'-' TASK,'-' TRESOURCE,TIMESLICE,
            SUM(PC) PC,SUM(PC_BL) PC_BL,SUM(AC) AC,SUM(RC) RC,SUM(TC) TC,SUM(PE) PE,SUM(PE_BL) PE_BL,SUM(AE) AE,SUM(RE) RE,SUM(PR) PR,SUM(PR_BL) PR_BL,SUM(AR) AR,SUM(RR) RR
            FROM DT827_CONDENSED
            WHERE DENSITYRATIO = 2 AND PROJECTLEVEL = @level AND PARENTPROJECT IS NOT NULL AND PARENTTASK IS NOT NULL
          GROUP BY MAINPROJECT,PROJECTLEVEL,PARENTPROJECT,PARENTTASK,PROJECTID,REPORTID,TIMESLICE
          ) s INNER JOIN (
            SELECT DI001052 MAINPROJECT,DI001003 PARENTPROJECT,DI001004 PARENTTASK,DI001001 PROJECTID
            FROM DT461
          ) t ON s.PARENTPROJECT = t.PROJECTID
          ;
 		      SET @level = @level - 1;
        END; -- WHILE
      -- Insert PR values into table
      INSERT INTO DT827_CONDENSED (DENSITYRATIO,MAINPROJECT,PROJECTLEVEL,PARENTPROJECT,PARENTTASK,PROJECTID,REPORTID,TASK,TRESOURCE,TIMESLICE,PC,PC_BL,AC,RC,TC,PE,PE_BL,AE,RE,PR,PR_BL,AR,RR)
      SELECT 3 DENSITYRATIO, -- PR
        MAINPROJECT,PROJECTLEVEL,PARENTPROJECT,PARENTTASK,PROJECTID,REPORTID,'-' TASK,'-' TRESOURCE,TIMESLICE,
        SUM(PC) PC,SUM(PC_BL) PC_BL,SUM(AC) AC,SUM(RC) RC,SUM(TC) TC,SUM(PE) PE,SUM(PE_BL) PE_BL,SUM(AE) AE,SUM(RE) RE,SUM(PR) PR,SUM(PR_BL) PR_BL,SUM(AR) AR,SUM(RR) RR
      FROM DT827_CONDENSED
      WHERE DENSITYRATIO = 2
      GROUP BY MAINPROJECT,PROJECTLEVEL,PARENTPROJECT,PARENTTASK,PROJECTID,REPORTID,TIMESLICE
      ;
      -- Compute the cumulated values
      WITH RunningSums (
        s_DENSITYRATIO,s_PROJECTID,s_REPORTID,s_TASK,s_TRESOURCE,s_TIMESLICE,s_CML_PC,s_CML_PC_BL,s_CML_AC,s_CML_RC,s_CML_TC,
        s_CML_PE,s_CML_PE_BL,s_CML_AE,s_CML_RE,s_CML_PR,s_CML_PR_BL,s_CML_AR,s_CML_RR)
      AS
      (
        SELECT 
          t.DENSITYRATIO s_DENSITYRATIO,t.PROJECTID s_PROJECTID,t.REPORTID s_REPORTID,
          t.TASK s_TASK,t.TRESOURCE s_TRESOURCE,t.TIMESLICE s_TIMESLICE,
          SUM(s.PC) s_CML_PC,SUM(s.PC_BL) s_CML_PC_BL,SUM(s.AC) s_CML_AC,SUM(s.RC) s_CML_RC,SUM(s.TC) s_CML_TC,
          SUM(s.PE) s_CML_PE,SUM(s.PE_BL) s_CML_PE_BL,SUM(s.AE) s_CML_AE,SUM(s.RE) s_CML_RE,
          SUM(s.PR) s_CML_PR,SUM(s.PR_BL) s_CML_PR_BL,SUM(s.AR) s_CML_AR,SUM(s.RR) s_CML_RR
        FROM DT827_CONDENSED t, DT827_CONDENSED s
        WHERE s.DENSITYRATIO = t.DENSITYRATIO AND s.PROJECTID = t.PROJECTID AND s.REPORTID = t.REPORTID AND 
          s.TASK = t.TASK AND s.TRESOURCE = t.TRESOURCE AND s.TIMESLICE <= t.TIMESLICE
        GROUP BY t.DENSITYRATIO,t.PROJECTID,t.REPORTID,t.TASK,t.TRESOURCE,t.TIMESLICE
      )
      UPDATE DT827_CONDENSED SET 
        CML_PC = r.s_CML_PC, CML_PC_BL = r.s_CML_PC_BL, CML_AC = r.s_CML_AC, CML_RC = r.s_CML_RC, CML_TC = r.s_CML_TC, 
        CML_PE = r.s_CML_PE, CML_PE_BL = r.s_CML_PE_BL, CML_AE = r.s_CML_AE, CML_RE = r.s_CML_RE, 
        CML_PR = r.s_CML_PR, CML_PR_BL = r.s_CML_PR_BL, CML_AR = r.s_CML_AR, CML_RR = r.s_CML_RR
      FROM RunningSums r
      WHERE DENSITYRATIO = r.s_DENSITYRATIO AND PROJECTID = r.s_PROJECTID AND REPORTID = r.s_REPORTID AND 
        TASK = r.s_TASK AND TRESOURCE = r.s_TRESOURCE AND TIMESLICE = r.s_TIMESLICE
      ;
      -- Compute EVA values
      WITH EarnedValues (
        s_DENSITYRATIO,s_PROJECTID,s_REPORTID,s_TASK,s_TRESOURCE,s_TIMESLICE,s_PROGRESS,s_EV)
      AS
      (
        SELECT 
          t.DENSITYRATIO s_DENSITYRATIO,t.PROJECTID s_PROJECTID,t.REPORTID s_REPORTID,t.TASK s_TASK,t.TRESOURCE s_TRESOURCE,t.TIMESLICE s_TIMESLICE,
          dbo.PLANTA_CALC_PRORESS(s.MAX_TC,t.CML_AC)*100 s_PROGRESS,dbo.PLANTA_CALC_PRORESS(s.MAX_TC,t.CML_AC)*s.MAX_PC_BL s_EV
        FROM DT827_CONDENSED t, (
          SELECT DENSITYRATIO,PROJECTID,REPORTID,TASK,TRESOURCE,MAX(CML_PC_BL) MAX_PC_BL,MAX(CML_TC) MAX_TC FROM DT827_CONDENSED
          GROUP BY DENSITYRATIO,PROJECTID,REPORTID,TASK,TRESOURCE
          ) s
        WHERE s.DENSITYRATIO = t.DENSITYRATIO AND s.PROJECTID = t.PROJECTID AND s.REPORTID = t.REPORTID AND 
          s.TASK = t.TASK AND s.TRESOURCE = t.TRESOURCE
      )
      UPDATE DT827_CONDENSED SET 
        PROGRESS = e.s_PROGRESS, EV = e.s_EV
      FROM EarnedValues e
      WHERE DENSITYRATIO = e.s_DENSITYRATIO AND PROJECTID = e.s_PROJECTID AND REPORTID = e.s_REPORTID AND 
        TASK = e.s_TASK AND TRESOURCE = e.s_TRESOURCE AND TIMESLICE = e.s_TIMESLICE
      ;
      UPDATE DT827_CONDENSED SET 
        SV = EV - CML_PC_BL,
        CV = EV - CML_AC,
        SPI = CASE WHEN CML_PC_BL = 0 THEN 0 ELSE EV / CML_PC_BL END,
        CPI = CASE WHEN CML_AC = 0 THEN 0 ELSE EV / CML_AC END
      ;
    END;
  ELSE
    BEGIN
      -- Insert VGR values FROM DT827 into table - costs and efforts per resource, revenues per proceeds
      INSERT INTO DT827_CONDENSED (DENSITYRATIO,MAINPROJECT,PROJECTLEVEL,PARENTPROJECT,PARENTTASK,PROJECTID,REPORTID,TASK,TRESOURCE,TIMESLICE,PC,PC_BL,AC,RC,TC,PE,PE_BL,AE,RE,PR,PR_BL,AR,RR)
      SELECT 1 DENSITYRATIO, -- VGR
        MAINPROJECT,PROJECTLEVEL,PARENTPROJECT,PARENTTASK,PROJECTID,REPORTID,TASK,TRESOURCE,TIMESLICE,
        SUM(COALESCE(PC*Costs,0)) PC,SUM(COALESCE(PC_BL*Costs,0)) PC_BL,SUM(COALESCE(AC*Costs,0)) AC,SUM(COALESCE(RC*Costs,0)) RC,SUM(COALESCE(TC*Costs,0)) TC,
        SUM(COALESCE(PE*Costs,0)) PE,SUM(COALESCE(PE_BL*Costs,0)) PE_BL,SUM(COALESCE(AE*Costs,0)) AE,SUM(COALESCE(RE*Costs,0)) RE,
        SUM(COALESCE(PR*Proceeds,0)) PR,SUM(COALESCE(PR_BL*Proceeds,0)) PR_BL,SUM(COALESCE(AR*Proceeds,0)) AR,SUM(COALESCE(RR*Proceeds,0)) RR
      FROM (
        SELECT p.DI001052 MAINPROJECT, pr.DI008603 PROJECTLEVEL, p.DI001003 PARENTPROJECT, p.DI001004 PARENTTASK, p.DI001001 PROJECTID, pr.DI008416 REPORTID,
          s.DI008517 TASK, s.DI008518 TRESOURCE, DATEADD(mm, DATEDIFF(mm,0,DI008523),0) TIMESLICE,
          s.DI008661 PC,0.0 PC_BL,s.DI008663 AC,s.DI008665 RC,s.DI008666 TC,s.DI008660*r.DI001242 PE,0.0 PE_BL,s.DI008662*r.DI001242 AE,s.DI008664*r.DI001242 RE,
          s.DI008661 PR,0.0 PR_BL,s.DI008663 AR,s.DI008665 RR,
          CASE WHEN r.DI003414 = @Proceeds THEN 0.0 ELSE 1.0 END Costs,CASE WHEN r.DI003414 = @Proceeds THEN 1.0 ELSE 0.0 END Proceeds
        FROM DT461 p INNER JOIN DT823 pr ON p.DI001001 = pr.DI008413
          INNER JOIN DT827 s ON pr.DI008413 = s.DI008516 AND pr.DI008416 = s.DI008535 INNER JOIN DT467 r ON s.DI008518 = r.DI001218
        WHERE p.DI001052 = @ThisProjectID AND pr.DI008416 = @ThisReportID AND pr.DI028505 = CONVERT(datetime,'01.01.1970',104) AND pr.DI008821 > CONVERT(datetime,'01.01.1970',104)
        UNION ALL
        SELECT p.DI001052 MAINPROJECT, pr.DI008603 PROJECTLEVEL, p.DI001003 PARENTPROJECT, p.DI001004 PARENTTASK, p.DI001001 PROJECTID, pr.DI008416 REPORTID,
          s.DI008517 TASK, s.DI008518 TRESOURCE, DATEADD(mm, DATEDIFF(mm,0,DI008523),0) TIMESLICE,
          0.0 PC,s.DI008661 PC_BL,0.0 AC,0.0 RC,0.0 TC,0.0 PE,s.DI008660*r.DI001242 PE_BL,0.0 AE,0.0 RE,0.0 PR,s.DI008661 PR_BL,0.0 AR,0.0 RR,
          CASE WHEN r.DI003414 = @Proceeds THEN 0.0 ELSE 1.0 END Costs,CASE WHEN r.DI003414 = @Proceeds THEN 1.0 ELSE 0.0 END Proceeds
        FROM DT461 p INNER JOIN DT823 pr ON p.DI001001 = pr.DI008413 INNER JOIN DT823 bl ON pr.DI008578 = bl.DI008413 AND pr.DI008416 = bl.DI008416
          INNER JOIN DT827 s ON p.DI001001 = s.DI008516 AND bl.DI041716 = s.DI008535 INNER JOIN DT467 r ON s.DI008518 = r.DI001218
        WHERE p.DI001052 = @ThisProjectID AND pr.DI008416 = @ThisReportID AND pr.DI028505 = CONVERT(datetime,'01.01.1970',104) AND pr.DI008821 > CONVERT(datetime,'01.01.1970',104)
      ) t
      GROUP BY MAINPROJECT,PROJECTLEVEL,PARENTPROJECT,PARENTTASK,PROJECTID,REPORTID,TASK,TRESOURCE,TIMESLICE
      ;
      -- Insert VG values into table
      INSERT INTO DT827_CONDENSED (DENSITYRATIO,MAINPROJECT,PROJECTLEVEL,PARENTPROJECT,PARENTTASK,PROJECTID,REPORTID,TASK,TRESOURCE,TIMESLICE,PC,PC_BL,AC,RC,TC,PE,PE_BL,AE,RE,PR,PR_BL,AR,RR)
      SELECT 2 DENSITYRATIO, -- VG
        MAINPROJECT,PROJECTLEVEL,PARENTPROJECT,PARENTTASK,PROJECTID,REPORTID,TASK,'-' TRESOURCE,TIMESLICE,
        SUM(PC) PC,SUM(PC_BL) PC_BL,SUM(AC) AC,SUM(RC) RC,SUM(TC) TC,SUM(PE) PE,SUM(PE_BL) PE_BL,SUM(AE) AE,SUM(RE) RE,SUM(PR) PR,SUM(PR_BL) PR_BL,SUM(AR) AR,SUM(RR) RR
      FROM DT827_CONDENSED
      WHERE MAINPROJECT = @ThisProjectID AND REPORTID = @ThisReportID
      GROUP BY MAINPROJECT,PROJECTLEVEL,PARENTPROJECT,PARENTTASK,PROJECTID,REPORTID,TASK,TIMESLICE
      ;
      -- Insert aggregated subproject values mapped to parent task into table
      SELECT @level = MAX(COALESCE(PROJECTLEVEL,0)) FROM DT827_CONDENSED WHERE DENSITYRATIO = 2 AND MAINPROJECT = @ThisProjectID AND REPORTID = @ThisReportID;
	    WHILE @level > 0
	      BEGIN
        INSERT INTO DT827_CONDENSED (DENSITYRATIO,MAINPROJECT,PROJECTLEVEL,PARENTPROJECT,PARENTTASK,PROJECTID,REPORTID,TASK,TRESOURCE,TIMESLICE,PC,PC_BL,AC,RC,TC,PE,PE_BL,AE,RE,PR,PR_BL,AR,RR)
        SELECT 2 DENSITYRATIO, -- VG
          t.MAINPROJECT,s.PROJECTLEVEL-1 PROJECTLEVEL,t.PARENTPROJECT,t.PARENTTASK,t.PROJECTID,s.REPORTID,s.PARENTTASK TASK,s.TRESOURCE,s.TIMESLICE,
          s.PC,s.PC_BL,s.AC,s.RC,s.TC,s.PE,s.PE_BL,s.AE,s.RE,s.PR,s.PR_BL,s.AR,s.RR
        FROM (
          SELECT MAINPROJECT,PROJECTLEVEL,PARENTPROJECT,PARENTTASK,PROJECTID,REPORTID,'-' TASK,'-' TRESOURCE,TIMESLICE,
            SUM(PC) PC,SUM(PC_BL) PC_BL,SUM(AC) AC,SUM(RC) RC,SUM(TC) TC,SUM(PE) PE,SUM(PE_BL) PE_BL,SUM(AE) AE,SUM(RE) RE,SUM(PR) PR,SUM(PR_BL) PR_BL,SUM(AR) AR,SUM(RR) RR
            FROM DT827_CONDENSED
            WHERE DENSITYRATIO = 2 AND PROJECTLEVEL = @level AND PARENTPROJECT IS NOT NULL AND PARENTTASK IS NOT NULL
              AND MAINPROJECT = @ThisProjectID AND REPORTID = @ThisReportID
          GROUP BY MAINPROJECT,PROJECTLEVEL,PARENTPROJECT,PARENTTASK,PROJECTID,REPORTID,TIMESLICE
          ) s INNER JOIN (
            SELECT DI001052 MAINPROJECT,DI001003 PARENTPROJECT,DI001004 PARENTTASK,DI001001 PROJECTID
            FROM DT461
            WHERE DI001052 = @ThisProjectID
          ) t ON s.PARENTPROJECT = t.PROJECTID
          ;
 		      SET @level = @level - 1;
        END; -- WHILE
      -- Insert PR values into table
      INSERT INTO DT827_CONDENSED (DENSITYRATIO,MAINPROJECT,PROJECTLEVEL,PARENTPROJECT,PARENTTASK,PROJECTID,REPORTID,TASK,TRESOURCE,TIMESLICE,PC,PC_BL,AC,RC,TC,PE,PE_BL,AE,RE,PR,PR_BL,AR,RR)
      SELECT 3 DENSITYRATIO, -- PR
        MAINPROJECT,PROJECTLEVEL,PARENTPROJECT,PARENTTASK,PROJECTID,REPORTID,'-' TASK,'-' TRESOURCE,TIMESLICE,
        SUM(PC) PC,SUM(PC_BL) PC_BL,SUM(AC) AC,SUM(RC) RC,SUM(TC) TC,SUM(PE) PE,SUM(PE_BL) PE_BL,SUM(AE) AE,SUM(RE) RE,SUM(PR) PR,SUM(PR_BL) PR_BL,SUM(AR) AR,SUM(RR) RR
      FROM DT827_CONDENSED
      WHERE DENSITYRATIO = 2 AND MAINPROJECT = @ThisProjectID AND REPORTID = @ThisReportID
      GROUP BY MAINPROJECT,PROJECTLEVEL,PARENTPROJECT,PARENTTASK,PROJECTID,REPORTID,TIMESLICE
      ;
      -- Compute the cumulated values
      WITH RunningSums (
        s_DENSITYRATIO,s_PROJECTID,s_REPORTID,s_TASK,s_TRESOURCE,s_TIMESLICE,s_CML_PC,s_CML_PC_BL,s_CML_AC,s_CML_RC,s_CML_TC,
        s_CML_PE,s_CML_PE_BL,s_CML_AE,s_CML_RE,s_CML_PR,s_CML_PR_BL,s_CML_AR,s_CML_RR)
      AS
      (
        SELECT 
          t.DENSITYRATIO s_DENSITYRATIO,t.PROJECTID s_PROJECTID,t.REPORTID s_REPORTID,
          t.TASK s_TASK,t.TRESOURCE s_TRESOURCE,t.TIMESLICE s_TIMESLICE,
          SUM(s.PC) s_CML_PC,SUM(s.PC_BL) s_CML_PC_BL,SUM(s.AC) s_CML_AC,SUM(s.RC) s_CML_RC,SUM(s.TC) s_CML_TC,
          SUM(s.PE) s_CML_PE,SUM(s.PE_BL) s_CML_PE_BL,SUM(s.AE) s_CML_AE,SUM(s.RE) s_CML_RE,
          SUM(s.PR) s_CML_PR,SUM(s.PR_BL) s_CML_PR_BL,SUM(s.AR) s_CML_AR,SUM(s.RR) s_CML_RR
        FROM DT827_CONDENSED t, DT827_CONDENSED s
        WHERE t.MAINPROJECT = @ThisProjectID AND t.REPORTID = @ThisReportID AND
          s.DENSITYRATIO = t.DENSITYRATIO AND s.PROJECTID = t.PROJECTID AND s.REPORTID = t.REPORTID AND 
          s.TASK = t.TASK AND s.TRESOURCE = t.TRESOURCE AND s.TIMESLICE <= t.TIMESLICE
        GROUP BY t.DENSITYRATIO,t.PROJECTID,t.REPORTID,t.TASK,t.TRESOURCE,t.TIMESLICE
      )
      UPDATE DT827_CONDENSED SET 
        CML_PC = r.s_CML_PC, CML_PC_BL = r.s_CML_PC_BL, CML_AC = r.s_CML_AC, CML_RC = r.s_CML_RC, CML_TC = r.s_CML_TC, 
        CML_PE = r.s_CML_PE, CML_PE_BL = r.s_CML_PE_BL, CML_AE = r.s_CML_AE, CML_RE = r.s_CML_RE, 
        CML_PR = r.s_CML_PR, CML_PR_BL = r.s_CML_PR_BL, CML_AR = r.s_CML_AR, CML_RR = r.s_CML_RR
      FROM RunningSums r
      WHERE MAINPROJECT = @ThisProjectID AND REPORTID = @ThisReportID AND
        DENSITYRATIO = r.s_DENSITYRATIO AND PROJECTID = r.s_PROJECTID AND REPORTID = r.s_REPORTID AND 
        TASK = r.s_TASK AND TRESOURCE = r.s_TRESOURCE AND TIMESLICE = r.s_TIMESLICE
      ;
      -- Compute EVA values
      WITH EarnedValues (
        s_DENSITYRATIO,s_PROJECTID,s_REPORTID,s_TASK,s_TRESOURCE,s_TIMESLICE,s_PROGRESS,s_EV)
      AS
      (
        SELECT 
          t.DENSITYRATIO s_DENSITYRATIO,t.PROJECTID s_PROJECTID,t.REPORTID s_REPORTID,t.TASK s_TASK,t.TRESOURCE s_TRESOURCE,t.TIMESLICE s_TIMESLICE,
          dbo.PLANTA_CALC_PRORESS(s.MAX_TC,t.CML_AC)*100 s_PROGRESS,dbo.PLANTA_CALC_PRORESS(s.MAX_TC,t.CML_AC)*s.MAX_PC_BL s_EV
        FROM DT827_CONDENSED t, (
          SELECT DENSITYRATIO,PROJECTID,REPORTID,TASK,TRESOURCE,MAX(CML_PC_BL) MAX_PC_BL,MAX(CML_TC) MAX_TC FROM DT827_CONDENSED
          GROUP BY DENSITYRATIO,PROJECTID,REPORTID,TASK,TRESOURCE
          ) s
        WHERE t.MAINPROJECT = @ThisProjectID AND t.REPORTID = @ThisReportID AND
          s.DENSITYRATIO = t.DENSITYRATIO AND s.PROJECTID = t.PROJECTID AND s.REPORTID = t.REPORTID AND 
          s.TASK = t.TASK AND s.TRESOURCE = t.TRESOURCE
      )
      UPDATE DT827_CONDENSED SET 
        PROGRESS = e.s_PROGRESS, EV = e.s_EV
      FROM EarnedValues e
      WHERE MAINPROJECT = @ThisProjectID AND REPORTID = @ThisReportID AND
        DENSITYRATIO = e.s_DENSITYRATIO AND PROJECTID = e.s_PROJECTID AND REPORTID = e.s_REPORTID AND 
        TASK = e.s_TASK AND TRESOURCE = e.s_TRESOURCE AND TIMESLICE = e.s_TIMESLICE
      ;
      UPDATE DT827_CONDENSED SET 
        SV = EV - CML_PC_BL,
        CV = EV - CML_AC,
        SPI = CASE WHEN CML_PC_BL = 0 THEN 0 ELSE EV / CML_PC_BL END,
        CPI = CASE WHEN CML_AC = 0 THEN 0 ELSE EV / CML_AC END
      WHERE MAINPROJECT = @ThisProjectID AND REPORTID = @ThisReportID
      ;
    END;
  -- DEBUG: SELECT * from DT827_CONDENSED ORDER BY DENSITYRATIO,PROJECTLEVEL,MAINPROJECT,PARENTPROJECT,PARENTTASK,PROJECTID,REPORTID,TIMESLICE;
END;
GO
