Migrate budget table,MemberResource table

Add two column
set default value for table field property
set current value

 

ALTER TABLE dbo.BUDGET ADD
	IS_IN_PROCESS bit NULL,
	BUDGET_PLAN_STATUS nvarchar(50) NULL
GO
ALTER TABLE dbo.BUDGET ADD CONSTRAINT
	DF_BUDGET_IS_IN_PROCESS DEFAULT 1 FOR IS_IN_PROCESS

 

 [code=java]

jjdkdd;

dddd;

 [/code]

fill in the budgetPlanstatus value based on its Status field' value

 

update BUDGET SET BUDGET_PLAN_STATUS=STATUS WHERE IS_IN_PROCESS=1;
-----have to perfect: add case subcondition--case status="REJECTED"  SET "DRAFT",CASE status=

"FROZEN" SET "APPROVED" ;

 



 
DELETE EFFORT_ESTIMATION WHERE FK_BUDGET_ID NOT IN (SELECT BUDGET_ID from budget

WHERE IS_IN_PROCESS=1 );


DELETE EXPENSE_ESTIMATION WHERE FK_BUDGET_ID NOT IN (SELECT BUDGET_ID from budget

WHERE IS_IN_PROCESS=1 );





       
             
  DDD----have to perfect

 

 

 

 

ALTER TABLE dbo.MEMBERRESOURCE ADD  
   [STARTDATE] [datetime] NULL 

 

UPDATE MEMBERRESOURCE SET STARTDATE='2009-9-27'

 

ALTER TABLE MEMBERRESOURCE  ALTER COLUMN STARTDATE DATETIME  NOT NULL

ALTER TABLE MEMBERRESOURCE  ALTER COLUMN CLOSEDATE DATETIME  NOT NULL

 

 

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[RESOURCE_ASSIGN]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[RESOURCE_ASSIGN]
GO

CREATE TABLE [dbo].[RESOURCE_ASSIGN] (
	[RESOURCE_ASSIGN_ID] [bigint] IDENTITY (1, 1) NOT NULL ,
	[FK_MEMBERRESOURCE_ID] [bigint] NULL ,
	[FK_EFFORT_ESTIMATION_ID] [bigint] NULL ,
	[MANMONTH_10] [real] NULL ,
	[MANMONTH_11] [real] NULL ,
	[MANMONTH_12] [real] NULL ,
	[MANMONTH_01] [real] NULL ,
	[MANMONTH_02] [real] NULL ,
	[MANMONTH_03] [real] NULL ,
	[MANMONTH_04] [real] NULL ,
	[MANMONTH_05] [real] NULL ,
	[MANMONTH_06] [real] NULL ,
	[MANMONTH_07] [real] NULL ,
	[MANMONTH_08] [real] NULL ,
	[MANMONTH_09] [real] NULL 
) ON [PRIMARY]
GO

 

 

declare @projectidmax int,@curprojectid int;   
  
select @projectidmax=max(fk_project_id) from budget;   
set @curprojectid=1;   
while @curprojectid<=@projectidmax    
begin    
declare @budgetidmax int;   
select @budgetidmax=max(budget_id) from budget where fk_project_id=@curprojectid;   
update budget set IS_IN_PROCESS=1 where ((fk_project_id=@curprojectid) and (budget_id=@budgetidmax));
update budget set IS_IN_PROCESS=0  where ((fk_project_id=@curprojectid) and (budget_id<@budgetidmax));    

insert into RESOURCE_ASSIGN( MANMONTH_10,    
      MANMONTH_11, MANMONTH_12, MANMONTH_01, MANMONTH_02, MANMONTH_03,    
      MANMONTH_04, MANMONTH_05, MANMONTH_06, MANMONTH_07, MANMONTH_08,    
      MANMONTH_09,FK_MEMBERRESOURCE_ID, FK_EFFORT_ESTIMATION_ID )   
  
SELECT RESOURCEASSIGNMENT.MANMONTH_10,    
     RESOURCEASSIGNMENT.MANMONTH_11, RESOURCEASSIGNMENT.MANMONTH_12,    
     RESOURCEASSIGNMENT.MANMONTH_01, RESOURCEASSIGNMENT.MANMONTH_02,    
     RESOURCEASSIGNMENT.MANMONTH_03, RESOURCEASSIGNMENT.MANMONTH_04,    
      RESOURCEASSIGNMENT.MANMONTH_05, RESOURCEASSIGNMENT.MANMONTH_06,    
      RESOURCEASSIGNMENT.MANMONTH_07, RESOURCEASSIGNMENT.MANMONTH_08,    
      RESOURCEASSIGNMENT.MANMONTH_09,    
      RESOURCEASSIGNMENT.FK_MEMBERRESOURCE_ID,
(
SELECT TOP 1 EFFORT_ESTIMATION_ID
FROM dbo.EFFORT_ESTIMATION
WHERE (EFFORT_ESTIMATION.FK_FISCALYEAR_ID=RESOURCEASSIGNMENT.FK_FISCALYEAR_ID AND FK_BUDGET_ID IN
          (SELECT TOP 1 BUDGET_ID 
         FROM dbo.BUDGET
         WHERE (FK_PROJECT_ID = @curprojectid) AND (IS_IN_PROCESS = 1)
 AND 
      (FK_DIVISION_ID = (SELECT TOP 1 FK_DIVISION_ID FROM TEAM WHERE TEAM_ID=(SELECT TOP 1 FK_TEAM_ID FROM MEMBERRESOURCE WHERE MEMBERRESOURCE.MEMBERRESOURCE_ID=RESOURCEASSIGNMENT.FK_MEMBERRESOURCE_ID)))))
)  
FROM RESOURCEASSIGNMENT Where fk_project_id=@curprojectid;

set @curprojectid=@curprojectid+1;   
end -----have to perfect 

----Resource_Assign: some FK_EFFORT_ESTIMATION_ID IS NULL
FIND OUT THE REASON
-----perhaps : forecast? 

 UPDATE EFFORT_ESTIMATION SET IS_ACTUAL=0,INITIAL_TOTAL_MANMONTH=MONTH_10+MONTH_11+MONTH_12+MONTH_1+MONTH_2+MONTH_3+MONTH_4+MONTH_5+MONTH_6+MONTH_7+MONTH_8+MONTH_9 WHERE IS_ACTUAL=1;                                                    UPDATE EFFORT_ESTIMATION SET FK_WORKPACKAGE_ID=27 WHERE FK_WORKPACKAGE_ID IS NULL;(In fact, can merge into first sql)

ALTER TABLE dbo.EFFORT_ESTIMATION ADD
 IS_ALIGNED bit NULL;

ALTER TABLE dbo.EFFORT_ESTIMATION ADD CONSTRAINT
 DF_EFFORT_ESTIMATION_IS_ALIGNED DEFAULT 0 FOR IS_ALIGNED

UPDATE EFFORT_ESTIMATION SET IS_ALIGNED = 0;

update BUDGET SET BUDGET_PLAN_STATUS=case when STATUS='FROZEN' then 'APPROVED' ELSE 'DRAFT' END

 

after ResourceAssignment--->Resource_Assign,

if Fk_effort_estimation_id=null,delete the Resource_Assign;

 

delete all IS_IN_PROCESSING=0 from Budget(also effort and expense)---no dirty data can avoid some hidden trouble or danger due to incomplete and unprecise condition control;

 

 

 

 

 

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

 

SELECT RESOURCEASSIGNMENT.MANMONTH_10, 
      RESOURCEASSIGNMENT.MANMONTH_11, RESOURCEASSIGNMENT.MANMONTH_12, 
      RESOURCEASSIGNMENT.MANMONTH_01, RESOURCEASSIGNMENT.MANMONTH_02, 
      RESOURCEASSIGNMENT.MANMONTH_03, RESOURCEASSIGNMENT.MANMONTH_04, 
      RESOURCEASSIGNMENT.MANMONTH_05, RESOURCEASSIGNMENT.MANMONTH_06, 
      RESOURCEASSIGNMENT.MANMONTH_07, RESOURCEASSIGNMENT.MANMONTH_08, 
      RESOURCEASSIGNMENT.MANMONTH_09, 
      RESOURCEASSIGNMENT.FK_MEMBERRESOURCE_ID, 
      EFFORT_ESTIMATION.EFFORT_ESTIMATION_ID
FROM RESOURCEASSIGNMENT LEFT OUTER JOIN
      BUDGET ON 
      BUDGET.FK_PROJECT_ID = RESOURCEASSIGNMENT.FK_PROJECT_ID LEFT OUTER JOIN
      EFFORT_ESTIMATION ON 
      EFFORT_ESTIMATION.FK_BUDGET_ID = BUDGET.BUDGET_ID ------have to correct1

SELECT BUDGET.NAME, PROJECT.NAME AS PROJECT_NAME, 
PROJECT.INTERNAL_ORDER_NUMBER, PROJECT.PROJECT_ID
FROM BUDGET left  JOIN
      PROJECT ON BUDGET.FK_PROJECT_ID = PROJECT.PROJECT_ID
WHERE (PROJECT.STATUS = 'IN_PROCESS') AND (BUDGET.IS_IN_PROCESS = 1)
GROUP BY BUDGET.NAME, PROJECT.NAME, PROJECT.INTERNAL_ORDER_NUMBER, 
      PROJECT.PROJECT_ID
ORDER BY PROJECT.PROJECT_ID------have to correct1

 

SELECT *, BUDGET.IS_IN_PROCESS AS Expr1, 
      EFFORT_ESTIMATION.EFFORT_ESTIMATION_ID AS Expr2, 
      RESOURCEASSIGNMENT.FK_MEMBERRESOURCE_ID AS Expr3
FROM RESOURCEASSIGNMENT INNER JOIN
      BUDGET ON 
      RESOURCEASSIGNMENT.FK_PROJECT_ID = BUDGET.FK_PROJECT_ID INNER JOIN
      EFFORT_ESTIMATION ON 
      BUDGET.BUDGET_ID = EFFORT_ESTIMATION.FK_BUDGET_ID 
      AND RESOURCEASSIGNMENT.FK_FISCALYEAR_ID = EFFORT_ESTIMATION.FK_FISCALYEAR_ID
WHERE (BUDGET.IS_IN_PROCESS = 1)


SELECT *, BUDGET.IS_IN_PROCESS AS Expr1, 
      EFFORT_ESTIMATION.EFFORT_ESTIMATION_ID AS Expr2, 
      RESOURCEASSIGNMENT.FK_MEMBERRESOURCE_ID AS Expr3, 
      TEAM.FK_DIVISION_ID AS Expr4, 
      EFFORT_ESTIMATION.FK_DIVISION_ID AS Expr5
FROM RESOURCEASSIGNMENT INNER JOIN
      BUDGET ON 
      RESOURCEASSIGNMENT.FK_PROJECT_ID = BUDGET.FK_PROJECT_ID INNER JOIN
      EFFORT_ESTIMATION ON 
      BUDGET.BUDGET_ID = EFFORT_ESTIMATION.FK_BUDGET_ID AND 
      RESOURCEASSIGNMENT.FK_FISCALYEAR_ID = EFFORT_ESTIMATION.FK_FISCALYEAR_ID
       INNER JOIN
      MEMBERRESOURCE ON 
      RESOURCEASSIGNMENT.FK_MEMBERRESOURCE_ID = MEMBERRESOURCE.MEMBERRESOURCE_ID
       INNER JOIN
      TEAM ON MEMBERRESOURCE.FK_TEAM_ID = TEAM.TEAM_ID AND 
      EFFORT_ESTIMATION.FK_DIVISION_ID = TEAM.FK_DIVISION_ID
WHERE (BUDGET.IS_IN_PROCESS = 1)

 

SELECT TOP 1 EFFORT_ESTIMATION_ID
FROM dbo.EFFORT_ESTIMATION
WHERE (FK_BUDGET_ID IN
          (SELECT BUDGET_ID AS Expr1
         FROM dbo.BUDGET
         WHERE (FK_PROJECT_ID = 108) AND (IS_IN_PROCESS = 1))) AND 
      (FK_DIVISION_ID = 5)