BUDGET & FY_BUDGET_W 一起处理
Version 1
declare @budgetIdmax int,@curBudgetId int,@FYbudgetStatus varchar(20); select @budgetIdmax=max(budget_id) from budget; set @curBudgetId=1; while @curBudgetId<=@budgetIdmax begin INSERT INTO BUDGET (NAME,FK_PROJECT_ID,IS_IN_PROCESS,BUDGET_PLAN_STATUS,RECORD_FY_ID,RECORD_BUDGET_STATUS) (SELECT BUDGET.NAME,FK_PROJECT_ID,0,BUDGET_PLAN_STATUS, ee.FK_FISCALYEAR_ID, BUDGET.BUDGET_PLAN_STATUS FROM (SELECT DISTINCT FK_FISCALYEAR_ID, FK_BUDGET_ID FROM EFFORT_ESTIMATION) ee INNER JOIN BUDGET ON ee.FK_BUDGET_ID = BUDGET.BUDGET_ID WHERE ((BUDGET.BUDGET_PLAN_STATUS = 'ALIGNED') OR (BUDGET.BUDGET_PLAN_STATUS = 'APPROVED')) and ee.FK_BUDGET_ID=@curBudgetId) ;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 from EFFORT_ESTIMATION where fk_budget_id=@curBudgetId ) ee cross join (SELECT DISTINCT BUDGET_ID,BUDGET_PLAN_STATUS FROM BUDGET WHERE RECORD_FY_ID>0 AND FK_PROJECT_ID IN (SELECT FK_PROJECT_ID FROM BUDGET WHERE BUDGET_ID=@curBudgetId )) b)set @curBudgetId=@curBudgetId+1; end
?
Version 2:declare @budgetIdmax int,@curBudgetId int,@FYbudgetStatus varchar(20); select @budgetIdmax=max(budget_id) from budget; set @curBudgetId=1; while @curBudgetId<=@budgetIdmax begin INSERT INTO BUDGET (NAME,FK_PROJECT_ID,IS_IN_PROCESS,BUDGET_PLAN_STATUS,RECORD_FY_ID,RECORD_BUDGET_STATUS) (SELECT BUDGET.NAME,FK_PROJECT_ID,0,BUDGET_PLAN_STATUS, ee.FK_FISCALYEAR_ID, BUDGET.BUDGET_PLAN_STATUS FROM (SELECT DISTINCT FK_FISCALYEAR_ID, FK_BUDGET_ID FROM EFFORT_ESTIMATION) ee INNER JOIN BUDGET ON ee.FK_BUDGET_ID = BUDGET.BUDGET_ID WHERE ((BUDGET.BUDGET_PLAN_STATUS = 'ALIGNED') OR (BUDGET.BUDGET_PLAN_STATUS = 'APPROVED')) and ee.FK_BUDGET_ID=@curBudgetId) ; 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 b.record_fy_id,b.budget_id,b.record_budget_status from (select distinct fk_fiscalyear_id,fk_budget_id from effort_estimation where fk_budget_id=@curBudgetId ) ee inner join (SELECT DISTINCT BUDGET_ID,record_fy_id,record_budget_status FROM BUDGET WHERE RECORD_FY_ID>0 AND FK_PROJECT_ID IN (SELECT FK_PROJECT_ID FROM BUDGET WHERE BUDGET_ID=@curBudgetId )) b on b.record_fy_id=ee.fk_fiscalYear_id and ee.fk_budget_id=b.budget_id) set @curBudgetId=@curBudgetId+1; end
?
not necessary code:
UPDATE BUDGETSET RECORD_FY_ID = 0WHERE (IS_IN_PROCESS = 1) AND (BUDGET_PLAN_STATUS <> 'APPROVED') AND (BUDGET_PLAN_STATUS <> 'ALIGNED')
?
Version 3:
declare @budgetIdmax int,@curBudgetId int,@FYbudgetStatus varchar(20); select @budgetIdmax=max(budget_id) from budget; set @curBudgetId=96; INSERT INTO BUDGET (NAME,FK_PROJECT_ID,IS_IN_PROCESS,BUDGET_PLAN_STATUS,RECORD_FY_ID,RECORD_BUDGET_STATUS) (SELECT BUDGET.NAME,FK_PROJECT_ID,0,BUDGET_PLAN_STATUS, ee.FK_FISCALYEAR_ID, BUDGET.BUDGET_PLAN_STATUS FROM (SELECT DISTINCT FK_FISCALYEAR_ID, FK_BUDGET_ID FROM EFFORT_ESTIMATION) ee INNER JOIN BUDGET ON ee.FK_BUDGET_ID = BUDGET.BUDGET_ID WHERE ((BUDGET.BUDGET_PLAN_STATUS = 'ALIGNED') OR (BUDGET.BUDGET_PLAN_STATUS = 'APPROVED')or budget.is_in_process=1) and ee.FK_BUDGET_ID=@curBudgetId) ; 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 b.record_fy_id,b.budget_id,b.record_budget_status from (select distinct fk_fiscalyear_id,fk_budget_id from effort_estimation where fk_budget_id=@curBudgetId ) ee cross join (SELECT DISTINCT BUDGET_ID,record_fy_id,record_budget_status FROM BUDGET WHERE RECORD_FY_ID>0 AND FK_PROJECT_ID IN (SELECT FK_PROJECT_ID FROM BUDGET WHERE BUDGET_ID=@curBudgetId )) b) ;
?
FY_BUDGET_W:
CREATE TABLE [dbo].[FY_BUDGET_W] ([FY_BUDGET_ID] [bigint] IDENTITY (1, 1) NOT NULL ,[FK_BUDGET_ID] [bigint] NULL ,[FY_BUDGET_STATUS] [nvarchar] (50) NULL ,[FK_FISCAL_YEAR_ID] [bigint] NULL ,[FK_EMPLOYEE$SUBMITOR_ID] [bigint] NULL ,[UPDATE_REASON] [nvarchar] (255) NULL ,[SUBMIT_DATE] [datetime] NULL ,[FK_EMPLOYEE$INSPECTOR_ID] [bigint] NULL ,[COMMENT] [nvarchar] (255) NULL ,[INSPECT_DATE] [datetime] NULL ) ON [PRIMARY]
?
?
?
?
?
Version 4:
declare @budgetIdmax int,@curBudgetId int,@FYbudgetStatus varchar(20); select @budgetIdmax=max(budget_id) from budget; set @curBudgetId=96; INSERT INTO BUDGET (NAME,FK_PROJECT_ID,IS_IN_PROCESS,BUDGET_PLAN_STATUS,RECORD_FY_ID,RECORD_BUDGET_STATUS) (SELECT BUDGET.NAME,FK_PROJECT_ID,0,BUDGET_PLAN_STATUS, ee.FK_FISCALYEAR_ID, BUDGET.BUDGET_PLAN_STATUS FROM (SELECT DISTINCT FK_FISCALYEAR_ID, FK_BUDGET_ID FROM EFFORT_ESTIMATION) ee INNER JOIN BUDGET ON ee.FK_BUDGET_ID = BUDGET.BUDGET_ID WHERE ((BUDGET.BUDGET_PLAN_STATUS = 'ALIGNED') OR (BUDGET.BUDGET_PLAN_STATUS = 'APPROVED')) and ee.FK_BUDGET_ID=@curBudgetId) ; 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 b.record_fy_id,b.budget_id,b.record_budget_status from (select distinct fk_fiscalyear_id,fk_budget_id from effort_estimation where fk_budget_id=@curBudgetId ) ee cross join (SELECT DISTINCT BUDGET_ID,record_fy_id,record_budget_status FROM BUDGET WHERE (RECORD_FY_ID>0 or is_in_process=1 )AND FK_PROJECT_ID IN (SELECT FK_PROJECT_ID FROM BUDGET WHERE BUDGET_ID=@curBudgetId )) b) ;
?
Version 5: test passed
?
declare @budgetIdmax int,@curBudgetId int,@FYbudgetStatus varchar(20); select @budgetIdmax=max(budget_id) from budget; set @curBudgetId=96; INSERT INTO BUDGET (NAME,FK_PROJECT_ID,IS_IN_PROCESS,BUDGET_PLAN_STATUS,RECORD_FY_ID,RECORD_BUDGET_STATUS) (SELECT BUDGET.NAME,FK_PROJECT_ID,0,BUDGET_PLAN_STATUS, ee.FK_FISCALYEAR_ID, BUDGET.BUDGET_PLAN_STATUS FROM (SELECT DISTINCT FK_FISCALYEAR_ID, FK_BUDGET_ID FROM EFFORT_ESTIMATION) ee INNER JOIN BUDGET ON ee.FK_BUDGET_ID = BUDGET.BUDGET_ID WHERE ((BUDGET.BUDGET_PLAN_STATUS = 'ALIGNED') OR (BUDGET.BUDGET_PLAN_STATUS = 'APPROVED')) and ee.FK_BUDGET_ID=@curBudgetId) ; 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 ee.fk_fiscalyear_id,b.budget_id,b.record_budget_status from (SELECT DISTINCT BUDGET_ID,record_fy_id,record_budget_status FROM BUDGET WHERE (RECORD_FY_ID>0 or is_in_process=1 )AND FK_PROJECT_ID IN (SELECT FK_PROJECT_ID FROM BUDGET WHERE BUDGET_ID=@curBudgetId )) bcross join (select distinct fk_fiscalyear_id,fk_budget_id from effort_estimation where fk_budget_id=@curBudgetId ) ee ) ;
?
Version 6:
?
declare @budgetIdmax int,@curBudgetId int,@FYbudgetStatus varchar(20); select @budgetIdmax=max(budget_id) from budget; set @curBudgetId=1; while @curBudgetId<=@budgetIdmax begin INSERT INTO BUDGET (NAME,FK_PROJECT_ID,IS_IN_PROCESS,BUDGET_PLAN_STATUS,RECORD_FY_ID,RECORD_BUDGET_STATUS,SrcBudgetId) (SELECT BUDGET.NAME,FK_PROJECT_ID,0,BUDGET_PLAN_STATUS, ee.FK_FISCALYEAR_ID, BUDGET.BUDGET_PLAN_STATUS,ee.fk_budget_id FROM (SELECT DISTINCT FK_FISCALYEAR_ID, FK_BUDGET_ID FROM EFFORT_ESTIMATION) ee INNER JOIN BUDGET ON ee.FK_BUDGET_ID = BUDGET.BUDGET_ID WHERE ((BUDGET.BUDGET_PLAN_STATUS = 'ALIGNED') OR (BUDGET.BUDGET_PLAN_STATUS = 'APPROVED')) and ee.FK_BUDGET_ID=@curBudgetId) ; set @curBudgetId=@curBudgetId+1; enddeclare @newBudgetIdMax int,@eeCount int; select @newBudgetIdMax=max(budget_id) from budget;set @curBudgetId=@budgetIdmax+1; while @curBudgetId<=@newBudgetIdMax begin select @eeCount=count(*) from Effort_estimation where fk_budget_id=@curBudgetId;if(@eeCount>0)beginInsert into Effort_estimation (TOPIC, MONTH_10, MONTH_11, MONTH_12, MONTH_1, MONTH_2, MONTH_3, MONTH_4, MONTH_5, MONTH_6, MONTH_7, MONTH_8, MONTH_9, FK_FISCALYEAR_ID, FK_BUDGET_ID, FK_DIVISION_ID, FK_WORKPACKAGE_ID, INITIAL_TOTAL_MANMONTH, IS_ALIGNED, EFFORT_TRACE_ID)( SELECT TOPIC, MONTH_10, MONTH_11, MONTH_12, MONTH_1, MONTH_2, MONTH_3, MONTH_4, MONTH_5, MONTH_6, MONTH_7, MONTH_8, MONTH_9, FK_FISCALYEAR_ID, @curBudgetId, FK_DIVISION_ID, FK_WORKPACKAGE_ID, INITIAL_TOTAL_MANMONTH, IS_ALIGNED, EFFORT_TRACE_IDFROM EFFORT_ESTIMATION where fk_budget_id=@curBudgetId)endselect @eeCount=count(*) from Expense_estimation where fk_budget_id=@curBudgetId;if(@eeCount>0)begininsert into EXPENSE_ESTIMATION(TOPIC, MONTH_10, MONTH_11, MONTH_12, MONTH_1, MONTH_2, MONTH_3, MONTH_4, MONTH_5, MONTH_6, MONTH_7, MONTH_8, MONTH_9, FK_FISCALYEAR_ID, FK_BUDGET_ID, FK_COSTTYPE_ID, INIT_TOTAL_COST)(SELECT TOPIC, MONTH_10, MONTH_11, MONTH_12, MONTH_1, MONTH_2, MONTH_3, MONTH_4, MONTH_5, MONTH_6, MONTH_7, MONTH_8, MONTH_9, FK_FISCALYEAR_ID, @curBudgetId, FK_COSTTYPE_ID, INIT_TOTAL_COSTFROM EXPENSE_ESTIMATION where fk_budget_id=@curBudgetId)end INSERT INTO FY_BUDGET_W (FK_FISCAL_YEAR_ID,fk_budget_id,FY_BUDGET_STATUS) ( select b.record_fy_id,b.BUDGET_ID,b.record_budget_status from(select distinct fk_fiscalyear_id,fk_budget_id from effort_estimation where fk_budget_id=@curBudgetId ) ee left join (SELECT record_fy_id,BUDGET_ID,record_budget_status FROM BUDGET WHERE BUDGET_ID=@curBudgetId) b on ee.fk_budget_id=b.budget_id) ; set @curBudgetId=@curBudgetId+1; end
?
?
Version 7:
?
declare @budgetIdmax int,@curBudgetId int,@FYbudgetStatus varchar(20); select @budgetIdmax=max(budget_id) from budget; set @curBudgetId=1; while @curBudgetId<=@budgetIdmax begin INSERT INTO BUDGET (NAME,FK_PROJECT_ID,IS_IN_PROCESS,BUDGET_PLAN_STATUS,RECORD_FY_ID,RECORD_BUDGET_STATUS,SrcBudgetId) (SELECT BUDGET.NAME,FK_PROJECT_ID,0,BUDGET_PLAN_STATUS, ee.FK_FISCALYEAR_ID, BUDGET.BUDGET_PLAN_STATUS,ee.fk_budget_id FROM (SELECT DISTINCT FK_FISCALYEAR_ID, FK_BUDGET_ID FROM EFFORT_ESTIMATION) ee INNER JOIN BUDGET ON ee.FK_BUDGET_ID = BUDGET.BUDGET_ID WHERE ((BUDGET.BUDGET_PLAN_STATUS = 'ALIGNED') OR (BUDGET.BUDGET_PLAN_STATUS = 'APPROVED')) and ee.FK_BUDGET_ID=@curBudgetId) ; set @curBudgetId=@curBudgetId+1; enddeclare @newBudgetIdMax int,@eeCount int,@SrcBudId bigint; select @newBudgetIdMax=max(budget_id) from budget;set @curBudgetId=@budgetIdmax+1; while @curBudgetId<=@newBudgetIdMax begin select @SrcBudId=SrcBudgetId from budget where budget_id=@curBudgetId;select @eeCount=count(*) from Effort_estimation where fk_budget_id=@SrcBudId;if(@eeCount>0)beginInsert into Effort_estimation (TOPIC, MONTH_10, MONTH_11, MONTH_12, MONTH_1, MONTH_2, MONTH_3, MONTH_4, MONTH_5, MONTH_6, MONTH_7, MONTH_8, MONTH_9, FK_FISCALYEAR_ID, FK_BUDGET_ID, FK_DIVISION_ID, FK_WORKPACKAGE_ID, INITIAL_TOTAL_MANMONTH, IS_ALIGNED, EFFORT_TRACE_ID)( SELECT TOPIC, MONTH_10, MONTH_11, MONTH_12, MONTH_1, MONTH_2, MONTH_3, MONTH_4, MONTH_5, MONTH_6, MONTH_7, MONTH_8, MONTH_9, FK_FISCALYEAR_ID, @curBudgetId, FK_DIVISION_ID, FK_WORKPACKAGE_ID, INITIAL_TOTAL_MANMONTH, IS_ALIGNED, EFFORT_TRACE_IDFROM EFFORT_ESTIMATION where fk_budget_id=@SrcBudId)endselect @eeCount=count(*) from Expense_estimation where fk_budget_id=@curBudgetId;if(@eeCount>0)begininsert into EXPENSE_ESTIMATION(TOPIC, MONTH_10, MONTH_11, MONTH_12, MONTH_1, MONTH_2, MONTH_3, MONTH_4, MONTH_5, MONTH_6, MONTH_7, MONTH_8, MONTH_9, FK_FISCALYEAR_ID, FK_BUDGET_ID, FK_COSTTYPE_ID, INIT_TOTAL_COST)(SELECT TOPIC, MONTH_10, MONTH_11, MONTH_12, MONTH_1, MONTH_2, MONTH_3, MONTH_4, MONTH_5, MONTH_6, MONTH_7, MONTH_8, MONTH_9, FK_FISCALYEAR_ID, @curBudgetId, FK_COSTTYPE_ID, INIT_TOTAL_COSTFROM EXPENSE_ESTIMATION where fk_budget_id=@SrcBudId)end INSERT INTO FY_BUDGET_W (FK_FISCAL_YEAR_ID,fk_budget_id,FY_BUDGET_STATUS) ( select ee.fk_fiscalyear_id,b.BUDGET_ID,b.record_budget_status from(select distinct fk_fiscalyear_id,fk_budget_id from effort_estimation where fk_budget_id=@curBudgetId ) ee inner join (SELECT * FROM BUDGET WHERE BUDGET_ID=@curBudgetId) b on ee.fk_budget_id=b.SrcBudgetid) ; set @curBudgetId=@curBudgetId+1; end
?
Version 8: test passed
?
declare @budgetIdmax int,@curBudgetId int,@FYbudgetStatus varchar(20); select @budgetIdmax=max(budget_id) from budget; set @curBudgetId=1; while @curBudgetId<=@budgetIdmax begin INSERT INTO BUDGET (NAME,FK_PROJECT_ID,IS_IN_PROCESS,BUDGET_PLAN_STATUS,RECORD_FY_ID,RECORD_BUDGET_STATUS,SrcBudgetId) (SELECT BUDGET.NAME,FK_PROJECT_ID,0,BUDGET_PLAN_STATUS, ee.FK_FISCALYEAR_ID, BUDGET.BUDGET_PLAN_STATUS,ee.fk_budget_id FROM (SELECT DISTINCT FK_FISCALYEAR_ID, FK_BUDGET_ID FROM EFFORT_ESTIMATION) ee INNER JOIN BUDGET ON ee.FK_BUDGET_ID = BUDGET.BUDGET_ID WHERE ((BUDGET.BUDGET_PLAN_STATUS = 'ALIGNED') OR (BUDGET.BUDGET_PLAN_STATUS = 'APPROVED')) and ee.FK_BUDGET_ID=@curBudgetId) ; set @curBudgetId=@curBudgetId+1; enddeclare @newBudgetIdMax int,@eeCount int,@SrcBudId bigint; select @newBudgetIdMax=max(budget_id) from budget;set @curBudgetId=@budgetIdmax+1; while @curBudgetId<=@newBudgetIdMax begin select @SrcBudId=SrcBudgetId from budget where budget_id=@curBudgetId;select @eeCount=count(*) from Effort_estimation where fk_budget_id=@SrcBudId;if(@eeCount>0)beginInsert into Effort_estimation (TOPIC, MONTH_10, MONTH_11, MONTH_12, MONTH_1, MONTH_2, MONTH_3, MONTH_4, MONTH_5, MONTH_6, MONTH_7, MONTH_8, MONTH_9, FK_FISCALYEAR_ID, FK_BUDGET_ID, FK_DIVISION_ID, FK_WORKPACKAGE_ID, INITIAL_TOTAL_MANMONTH, IS_ALIGNED, EFFORT_TRACE_ID)( SELECT TOPIC, MONTH_10, MONTH_11, MONTH_12, MONTH_1, MONTH_2, MONTH_3, MONTH_4, MONTH_5, MONTH_6, MONTH_7, MONTH_8, MONTH_9, FK_FISCALYEAR_ID, @curBudgetId, FK_DIVISION_ID, FK_WORKPACKAGE_ID, INITIAL_TOTAL_MANMONTH, IS_ALIGNED, EFFORT_TRACE_IDFROM EFFORT_ESTIMATION where fk_budget_id=@SrcBudId)endselect @eeCount=count(*) from Expense_estimation where fk_budget_id=@curBudgetId;if(@eeCount>0)begininsert into EXPENSE_ESTIMATION(TOPIC, MONTH_10, MONTH_11, MONTH_12, MONTH_1, MONTH_2, MONTH_3, MONTH_4, MONTH_5, MONTH_6, MONTH_7, MONTH_8, MONTH_9, FK_FISCALYEAR_ID, FK_BUDGET_ID, FK_COSTTYPE_ID, INIT_TOTAL_COST)(SELECT TOPIC, MONTH_10, MONTH_11, MONTH_12, MONTH_1, MONTH_2, MONTH_3, MONTH_4, MONTH_5, MONTH_6, MONTH_7, MONTH_8, MONTH_9, FK_FISCALYEAR_ID, @curBudgetId, FK_COSTTYPE_ID, INIT_TOTAL_COSTFROM EXPENSE_ESTIMATION where fk_budget_id=@SrcBudId)end INSERT INTO FY_BUDGET_W (FK_FISCAL_YEAR_ID,fk_budget_id,FY_BUDGET_STATUS) ( select ee.fk_fiscalyear_id,b.BUDGET_ID,b.record_budget_status from(select distinct fk_fiscalyear_id,fk_budget_id from effort_estimation where fk_budget_id=@curBudgetId ) ee cross join (SELECT * FROM BUDGET WHERE BUDGET_ID=@curBudgetId) b ) ; set @curBudgetId=@curBudgetId+1; end
?
Version 9:
?
declare @budgetIdmax int,@curBudgetId int,@FYbudgetStatus varchar(20); select @budgetIdmax=max(budget_id) from budget; set @curBudgetId=1; while @curBudgetId<=@budgetIdmax begin INSERT INTO BUDGET (NAME,CREATE_DATE,FK_PROJECT_ID,IS_IN_PROCESS,BUDGET_PLAN_STATUS,RECORD_FY_ID,RECORD_BUDGET_STATUS,SrcBudgetId) (SELECT BUDGET.NAME,CREATE_DATE,FK_PROJECT_ID,0,BUDGET_PLAN_STATUS, ee.FK_FISCALYEAR_ID, BUDGET.BUDGET_PLAN_STATUS,ee.fk_budget_id FROM (SELECT DISTINCT FK_FISCALYEAR_ID, FK_BUDGET_ID FROM EFFORT_ESTIMATION) ee INNER JOIN BUDGET ON ee.FK_BUDGET_ID = BUDGET.BUDGET_ID WHERE ((BUDGET.BUDGET_PLAN_STATUS = 'ALIGNED') OR (BUDGET.BUDGET_PLAN_STATUS = 'APPROVED')) and ee.FK_BUDGET_ID=@curBudgetId) ; set @curBudgetId=@curBudgetId+1; enddeclare @newBudgetIdMax int,@eeCount int,@SrcBudId bigint; select @newBudgetIdMax=max(budget_id) from budget;set @curBudgetId=@budgetIdmax+1; while @curBudgetId<=@newBudgetIdMax begin select @SrcBudId=SrcBudgetId from budget where budget_id=@curBudgetId;select @eeCount=count(*) from Effort_estimation where fk_budget_id=@SrcBudId;if(@eeCount>0)beginInsert into Effort_estimation (TOPIC, MONTH_10, MONTH_11, MONTH_12, MONTH_1, MONTH_2, MONTH_3, MONTH_4, MONTH_5, MONTH_6, MONTH_7, MONTH_8, MONTH_9, FK_FISCALYEAR_ID, FK_BUDGET_ID, FK_DIVISION_ID, FK_WORKPACKAGE_ID, INITIAL_TOTAL_MANMONTH, IS_ALIGNED, EFFORT_TRACE_ID)( SELECT TOPIC, MONTH_10, MONTH_11, MONTH_12, MONTH_1, MONTH_2, MONTH_3, MONTH_4, MONTH_5, MONTH_6, MONTH_7, MONTH_8, MONTH_9, FK_FISCALYEAR_ID, @curBudgetId, FK_DIVISION_ID, FK_WORKPACKAGE_ID, INITIAL_TOTAL_MANMONTH, IS_ALIGNED, EFFORT_TRACE_IDFROM EFFORT_ESTIMATION where fk_budget_id=@SrcBudId)endselect @eeCount=count(*) from Expense_estimation where fk_budget_id=@curBudgetId;if(@eeCount>0)begininsert into EXPENSE_ESTIMATION(TOPIC, MONTH_10, MONTH_11, MONTH_12, MONTH_1, MONTH_2, MONTH_3, MONTH_4, MONTH_5, MONTH_6, MONTH_7, MONTH_8, MONTH_9, FK_FISCALYEAR_ID, FK_BUDGET_ID, FK_COSTTYPE_ID, INIT_TOTAL_COST)(SELECT TOPIC, MONTH_10, MONTH_11, MONTH_12, MONTH_1, MONTH_2, MONTH_3, MONTH_4, MONTH_5, MONTH_6, MONTH_7, MONTH_8, MONTH_9, FK_FISCALYEAR_ID, @curBudgetId, FK_COSTTYPE_ID, INIT_TOTAL_COSTFROM EXPENSE_ESTIMATION where fk_budget_id=@SrcBudId)end INSERT INTO FY_BUDGET_W (FK_FISCAL_YEAR_ID,fk_budget_id,FY_BUDGET_STATUS) ( select ee.fk_fiscalyear_id,b.BUDGET_ID,b.record_budget_status from(select distinct fk_fiscalyear_id,fk_budget_id from effort_estimation where fk_budget_id=@curBudgetId ) ee cross join (SELECT * FROM BUDGET WHERE BUDGET_ID=@curBudgetId) b ) ; set @curBudgetId=@curBudgetId+1; end
?
Version 10:
?
declare @budgetIdmax int,@curBudgetId int,@FYbudgetStatus varchar(20),@processCount int; select @budgetIdmax=max(budget_id) from budget; set @curBudgetId=1; while @curBudgetId<=@budgetIdmax begin INSERT INTO BUDGET (NAME,CREATE_DATE,FK_PROJECT_ID,IS_IN_PROCESS,BUDGET_PLAN_STATUS,RECORD_FY_ID,RECORD_BUDGET_STATUS,SrcBudgetId) (SELECT BUDGET.NAME,CREATE_DATE,FK_PROJECT_ID,0,BUDGET_PLAN_STATUS, ee.FK_FISCALYEAR_ID, BUDGET.BUDGET_PLAN_STATUS,ee.fk_budget_id FROM (SELECT DISTINCT FK_FISCALYEAR_ID, FK_BUDGET_ID FROM EFFORT_ESTIMATION) ee INNER JOIN BUDGET ON ee.FK_BUDGET_ID = BUDGET.BUDGET_ID WHERE ((BUDGET.BUDGET_PLAN_STATUS = 'ALIGNED') OR (BUDGET.BUDGET_PLAN_STATUS = 'APPROVED')) and ee.FK_BUDGET_ID=@curBudgetId) ; select @processCount=count(*) from budget where budget_id=@curBudgetId and is_in_process=1if(@processCount>0)beginINSERT INTO FY_BUDGET_W (FK_FISCAL_YEAR_ID,fk_budget_id,FY_BUDGET_STATUS) ( select ee.fk_fiscalyear_id,b.BUDGET_ID,b.record_budget_status from(select distinct fk_fiscalyear_id,fk_budget_id from effort_estimation where fk_budget_id=@curBudgetId ) ee cross join (SELECT * FROM BUDGET WHERE BUDGET_ID=@curBudgetId) b ) ; endset @curBudgetId=@curBudgetId+1; enddeclare @newBudgetIdMax int,@eeCount int,@SrcBudId bigint; select @newBudgetIdMax=max(budget_id) from budget;set @curBudgetId=@budgetIdmax+1; while @curBudgetId<=@newBudgetIdMax begin select @SrcBudId=SrcBudgetId from budget where budget_id=@curBudgetId;select @eeCount=count(*) from Effort_estimation where fk_budget_id=@SrcBudId;if(@eeCount>0)beginInsert into Effort_estimation (TOPIC, MONTH_10, MONTH_11, MONTH_12, MONTH_1, MONTH_2, MONTH_3, MONTH_4, MONTH_5, MONTH_6, MONTH_7, MONTH_8, MONTH_9, FK_FISCALYEAR_ID, FK_BUDGET_ID, FK_DIVISION_ID, FK_WORKPACKAGE_ID, INITIAL_TOTAL_MANMONTH, IS_ALIGNED, EFFORT_TRACE_ID)( SELECT TOPIC, MONTH_10, MONTH_11, MONTH_12, MONTH_1, MONTH_2, MONTH_3, MONTH_4, MONTH_5, MONTH_6, MONTH_7, MONTH_8, MONTH_9, FK_FISCALYEAR_ID, @curBudgetId, FK_DIVISION_ID, FK_WORKPACKAGE_ID, INITIAL_TOTAL_MANMONTH, IS_ALIGNED, EFFORT_TRACE_IDFROM EFFORT_ESTIMATION where fk_budget_id=@SrcBudId)endselect @eeCount=count(*) from Expense_estimation where fk_budget_id=@curBudgetId;if(@eeCount>0)begininsert into EXPENSE_ESTIMATION(TOPIC, MONTH_10, MONTH_11, MONTH_12, MONTH_1, MONTH_2, MONTH_3, MONTH_4, MONTH_5, MONTH_6, MONTH_7, MONTH_8, MONTH_9, FK_FISCALYEAR_ID, FK_BUDGET_ID, FK_COSTTYPE_ID, INIT_TOTAL_COST)(SELECT TOPIC, MONTH_10, MONTH_11, MONTH_12, MONTH_1, MONTH_2, MONTH_3, MONTH_4, MONTH_5, MONTH_6, MONTH_7, MONTH_8, MONTH_9, FK_FISCALYEAR_ID, @curBudgetId, FK_COSTTYPE_ID, INIT_TOTAL_COSTFROM EXPENSE_ESTIMATION where fk_budget_id=@SrcBudId)end INSERT INTO FY_BUDGET_W (FK_FISCAL_YEAR_ID,fk_budget_id,FY_BUDGET_STATUS) ( select ee.fk_fiscalyear_id,b.BUDGET_ID,b.record_budget_status from(select distinct fk_fiscalyear_id,fk_budget_id from effort_estimation where fk_budget_id=@curBudgetId ) ee cross join (SELECT * FROM BUDGET WHERE BUDGET_ID=@curBudgetId) b ) ; set @curBudgetId=@curBudgetId+1; end
?
