Set IS_ALIGNED='1' for EEs of all budgets in process&Delete sw's RS

Final sql script version :

 

update Effort_estimation set is_aligned='1' where EFFORT_ESTIMATION_ID in 
(
select  EFFORT_ESTIMATION_ID from
(
SELECT SUM(RESOURCE_ASSIGN.MANMONTH_01) AS SUM_MANMONTH1, 
SUM(RESOURCE_ASSIGN.MANMONTH_02) AS SUM_MANMONTH2, 
SUM(RESOURCE_ASSIGN.MANMONTH_03) AS SUM_MANMONTH3, 
SUM(RESOURCE_ASSIGN.MANMONTH_04) AS SUM_MANMONTH4, 
SUM(RESOURCE_ASSIGN.MANMONTH_05) AS SUM_MANMONTH5, 
SUM(RESOURCE_ASSIGN.MANMONTH_06) AS SUM_MANMONTH6, 
SUM(RESOURCE_ASSIGN.MANMONTH_07) AS SUM_MANMONTH7, 
SUM(RESOURCE_ASSIGN.MANMONTH_08) AS SUM_MANMONTH8, 
SUM(RESOURCE_ASSIGN.MANMONTH_09) AS SUM_MANMONTH9, 
SUM(RESOURCE_ASSIGN.MANMONTH_10) AS SUM_MANMONTH10, 
SUM(RESOURCE_ASSIGN.MANMONTH_11) AS SUM_MANMONTH11, 
SUM(RESOURCE_ASSIGN.MANMONTH_12) AS SUM_MANMONTH12, 
      EFFORT_ESTIMATION.EFFORT_ESTIMATION_ID, 
      EFFORT_ESTIMATION.MONTH_1, 
      EFFORT_ESTIMATION.MONTH_2, 
      EFFORT_ESTIMATION.MONTH_3, 
      EFFORT_ESTIMATION.MONTH_4, 
      EFFORT_ESTIMATION.MONTH_5, 
      EFFORT_ESTIMATION.MONTH_6, 
      EFFORT_ESTIMATION.MONTH_7, 
      EFFORT_ESTIMATION.MONTH_8, 
      EFFORT_ESTIMATION.MONTH_9, 
      EFFORT_ESTIMATION.MONTH_10, 
      EFFORT_ESTIMATION.MONTH_11, 
      EFFORT_ESTIMATION.MONTH_12 
FROM RESOURCE_ASSIGN INNER JOIN
      EFFORT_ESTIMATION ON 
      RESOURCE_ASSIGN.FK_EFFORT_ESTIMATION_ID = EFFORT_ESTIMATION.EFFORT_ESTIMATION_ID
       INNER JOIN
      BUDGET ON EFFORT_ESTIMATION.FK_BUDGET_ID = BUDGET.BUDGET_ID
WHERE (BUDGET.IS_IN_PROCESS = 1)
GROUP BY EFFORT_ESTIMATION.EFFORT_ESTIMATION_ID, 
      EFFORT_ESTIMATION.MONTH_1,
      EFFORT_ESTIMATION.MONTH_2, 
      EFFORT_ESTIMATION.MONTH_3, 
      EFFORT_ESTIMATION.MONTH_4, 
      EFFORT_ESTIMATION.MONTH_5, 
      EFFORT_ESTIMATION.MONTH_6, 
      EFFORT_ESTIMATION.MONTH_7, 
      EFFORT_ESTIMATION.MONTH_8, 
      EFFORT_ESTIMATION.MONTH_9, 
      EFFORT_ESTIMATION.MONTH_10, 
      EFFORT_ESTIMATION.MONTH_11, 
      EFFORT_ESTIMATION.MONTH_12 
)AAAAA where (SUM_MANMONTH1 = MONTH_1 and SUM_MANMONTH2 = MONTH_2  and SUM_MANMONTH3 = MONTH_3 and SUM_MANMONTH4 = MONTH_4 and SUM_MANMONTH5 = MONTH_5 and SUM_MANMONTH6 = MONTH_6 and SUM_MANMONTH7 = MONTH_7 and SUM_MANMONTH8 = MONTH_8 and SUM_MANMONTH9 = MONTH_9 and SUM_MANMONTH10 = MONTH_10 and SUM_MANMONTH11 = MONTH_11 and SUM_MANMONTH12 = MONTH_12)
)

 

 

Preparation&Draft:

 

1.

SELECT SUM(RESOURCE_ASSIGN.MANMONTH_01) AS SUM_MANMONTH1, 
      SUM(RESOURCE_ASSIGN.MANMONTH_02) AS SUM_MANMONTH2, 
      SUM(RESOURCE_ASSIGN.MANMONTH_03) AS SUM_MANMONTH3, 
      SUM(RESOURCE_ASSIGN.MANMONTH_04) AS SUM_MANMONTH4, 
      SUM(RESOURCE_ASSIGN.MANMONTH_05) AS SUM_MANMONTH5, 
      SUM(RESOURCE_ASSIGN.MANMONTH_06) AS SUM_MANMONTH6, 
      SUM(RESOURCE_ASSIGN.MANMONTH_07) AS SUM_MANMONTH7, 
      SUM(RESOURCE_ASSIGN.MANMONTH_08) AS SUM_MANMONTH8, 
      SUM(RESOURCE_ASSIGN.MANMONTH_09) AS SUM_MANMONTH10, 
      SUM(RESOURCE_ASSIGN.MANMONTH_10) AS SUM_MANMONTH10, 
      SUM(RESOURCE_ASSIGN.MANMONTH_11) AS SUM_MANMONTH11, 
      SUM(RESOURCE_ASSIGN.MANMONTH_12) AS SUM_MANMONTH12, 
      EFFORT_ESTIMATION.EFFORT_ESTIMATION_ID, EFFORT_ESTIMATION.MONTH_1, 
      EFFORT_ESTIMATION.MONTH_2, EFFORT_ESTIMATION.MONTH_3, 
      EFFORT_ESTIMATION.MONTH_4, EFFORT_ESTIMATION.MONTH_5, 
      EFFORT_ESTIMATION.MONTH_6, EFFORT_ESTIMATION.MONTH_7, 
      EFFORT_ESTIMATION.MONTH_8, EFFORT_ESTIMATION.MONTH_9, 
      EFFORT_ESTIMATION.MONTH_10, EFFORT_ESTIMATION.MONTH_11, 
      EFFORT_ESTIMATION.MONTH_12
FROM RESOURCE_ASSIGN INNER JOIN
      EFFORT_ESTIMATION ON 
      RESOURCE_ASSIGN.FK_EFFORT_ESTIMATION_ID = EFFORT_ESTIMATION.EFFORT_ESTIMATION_ID
       INNER JOIN
      BUDGET ON EFFORT_ESTIMATION.FK_BUDGET_ID = BUDGET.BUDGET_ID
WHERE (BUDGET.IS_IN_PROCESS = 1)
GROUP BY EFFORT_ESTIMATION.EFFORT_ESTIMATION_ID, 
      EFFORT_ESTIMATION.MONTH_1, EFFORT_ESTIMATION.MONTH_2, 
      EFFORT_ESTIMATION.MONTH_3, EFFORT_ESTIMATION.MONTH_4, 
      EFFORT_ESTIMATION.MONTH_5, EFFORT_ESTIMATION.MONTH_6, 
      EFFORT_ESTIMATION.MONTH_7, EFFORT_ESTIMATION.MONTH_8, 
      EFFORT_ESTIMATION.MONTH_9, EFFORT_ESTIMATION.MONTH_10, 
      EFFORT_ESTIMATION.MONTH_11, EFFORT_ESTIMATION.MONTH_12

 

2.

SELECT COUNT(*) AS Expr1
FROM (SELECT EFFORT_ESTIMATION.*
        FROM EFFORT_ESTIMATION
        WHERE (FK_BUDGET_ID IN
                  (SELECT budget_id
                 FROM budget
                 WHERE is_in_process = 1))) EffortEstimatonbak----1774



SELECT COUNT(*) AS Expr1
FROM (SELECT *
        FROM RESOURCE_ASSIGN
        WHERE (FK_EFFORT_ESTIMATION_ID IN
                  (SELECT EFFORT_ESTIMATION_ID
                 FROM EFFORT_ESTIMATION
                 WHERE (FK_BUDGET_ID IN
                           (SELECT budget_id
                          FROM budget
                          WHERE is_in_process = 1))))) r_aBAK---------1014



SELECT COUNT(*) AS Expr1
FROM (SELECT SUM(RESOURCE_ASSIGN.MANMONTH_01) AS SUM_MANMONTH1, 
              EFFORT_ESTIMATION.EFFORT_ESTIMATION_ID
        FROM RESOURCE_ASSIGN INNER JOIN
              EFFORT_ESTIMATION ON 
              RESOURCE_ASSIGN.FK_EFFORT_ESTIMATION_ID = EFFORT_ESTIMATION.EFFORT_ESTIMATION_ID
               INNER JOIN
              BUDGET ON 
              EFFORT_ESTIMATION.FK_BUDGET_ID = BUDGET.BUDGET_ID
        WHERE (BUDGET.IS_IN_PROCESS = 1)
        GROUP BY EFFORT_ESTIMATION.EFFORT_ESTIMATION_ID) aaaa----441

 

 

----------------------------

SELECT     RESOURCE_ASSIGN_ID, FK_MEMBERRESOURCE_ID, FK_EFFORT_ESTIMATION_ID, MANMONTH_10, MANMONTH_11, MANMONTH_12,
                      MANMONTH_01, MANMONTH_02, MANMONTH_03, MANMONTH_04, MANMONTH_05, MANMONTH_06, MANMONTH_07, MANMONTH_08,
                      MANMONTH_09
FROM         RESOURCE_ASSIGN
WHERE     (FK_EFFORT_ESTIMATION_ID IN
                          (SELECT     EFFORT_ESTIMATION_ID
                            FROM          EFFORT_ESTIMATION
                            WHERE      (FK_BUDGET_ID = 605) AND (FK_FISCALYEAR_ID = 4) AND (FK_DIVISION_ID = 8)))