FY_Budget_W

version1:

declare @budgetIdmax int,@curBudgetId int;                                        
select @budgetIdmax=max(budget_id) from budget;
set @curBudgetId=1;
while @curBudgetId<=@budgetIdmax
begin 
INSERT INTO FY_BUDGET_W (FK_FISCAL_YEAR_ID) (select distinct fk_fiscalyear_id from EFFORT_ESTIMATION where fk_budget_id=@curBudgetId )
set @curBudgetId=@curBudgetId+1;         
end  

 

 

Version 2:
declare @budgetIdmax int,@curBudgetId int;                                        
select @budgetIdmax=max(budget_id) from budget;
set @curBudgetId=1;
while @curBudgetId<=@budgetIdmax
begin 
INSERT INTO FY_BUDGET_W (FK_FISCAL_YEAR_ID,fk_budget_id) (select distinct fk_fiscalyear_id,fk_budget_id from EFFORT_ESTIMATION where fk_budget_id=@curBudgetId )
set @curBudgetId=@curBudgetId+1;         
end  

 

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

 

Version 3 :      test passed

  

 
declare @budgetIdmax int,@curBudgetId int,@FYbudgetStatus varchar(20);                                        
select @budgetIdmax=max(budget_id) from budget;
set @curBudgetId=1;
while @curBudgetId<=@budgetIdmax
begin 
select top 1 @FYbudgetStatus=BUDGET_plan_STATUS  from budget where budget_id=@curBudgetId 
INSERT INTO FY_BUDGET_W (FK_FISCAL_YEAR_ID,fk_budget_id,FY_BUDGET_STATUS) (select distinct fk_fiscalyear_id,fk_budget_id,@FYbudgetStatus from EFFORT_ESTIMATION where fk_budget_id=@curBudgetId )
set @curBudgetId=@curBudgetId+1;         
end  

 

ALTER TABLE dbo.BUDGET ADD RECORD_FY_ID bigint NULL, 
RECORD_BUDGET_STATUS nvarchar(50) NULL; 
ALTER TABLE dbo.BUDGET ADD CONSTRAINT DF_BUDGET_RECORD_BUDGET_STATUS
DEFAULT 'DRAFT' FOR RECORD_BUDGET_STATUS;

UPDATE BUDGET
SET RECORD_FY_ID = 0, RECORD_BUDGET_STATUS = 'DRAFT';

 

  

 

   EFFORT_TRACE_ID:BudgetId_EffortEstimationId ( ProjectId_EffortEstimationId) :

 

ALTER TABLE EFFORT_ESTIMATION ADD [EFFORT_TRACE_ID] [nvarchar] (50) NULL ;

Update EFFORT_ESTIMATION set EFFORT_TRACE_ID=str(FK_BUDGET_ID)+'_'+str(EFFORT_ESTIMATION_ID);

 

 

    RECORD_BUDGET_STATUS, RECORD_FY_ID: