mssp
- 浏览: 50752 次
- 性别:
- 来自: 南京
-
文章分类
最新评论
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 BUDGET
SET RECORD_FY_ID = 0
WHERE (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 )) b
cross 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;
end
declare @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)
begin
Insert 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_ID
FROM EFFORT_ESTIMATION where fk_budget_id=@curBudgetId)
end
select @eeCount=count(*) from Expense_estimation where fk_budget_id=@curBudgetId;
if(@eeCount>0)
begin
insert 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_COST
FROM 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;
end
declare @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)
begin
Insert 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_ID
FROM EFFORT_ESTIMATION where fk_budget_id=@SrcBudId)
end
select @eeCount=count(*) from Expense_estimation where fk_budget_id=@curBudgetId;
if(@eeCount>0)
begin
insert 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_COST
FROM 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;
end
declare @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)
begin
Insert 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_ID
FROM EFFORT_ESTIMATION where fk_budget_id=@SrcBudId)
end
select @eeCount=count(*) from Expense_estimation where fk_budget_id=@curBudgetId;
if(@eeCount>0)
begin
insert 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_COST
FROM 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;
end
declare @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)
begin
Insert 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_ID
FROM EFFORT_ESTIMATION where fk_budget_id=@SrcBudId)
end
select @eeCount=count(*) from Expense_estimation where fk_budget_id=@curBudgetId;
if(@eeCount>0)
begin
insert 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_COST
FROM 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=1
if(@processCount>0)
begin
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
) ;
end
set @curBudgetId=@curBudgetId+1;
end
declare @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)
begin
Insert 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_ID
FROM EFFORT_ESTIMATION where fk_budget_id=@SrcBudId)
end
select @eeCount=count(*) from Expense_estimation where fk_budget_id=@curBudgetId;
if(@eeCount>0)
begin
insert 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_COST
FROM 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
分享到：
发表评论
-
PRT maintainence
2015-09-23 20:33 0jjjeeee -
Migration Prepration & Notice
2011-07-28 10:25 0Problem1: some ee=0 should d ... -
BUDGET,FY_BUDGET_W
2011-07-08 13:48 0Version1: SELECT ee.*, BUDGE ... -
FY_Budget_W
2011-07-07 15:46 627version1: declare @budgetIdmax ... -
Set IS_ALIGNED='1' for EEs of all budgets in process&Delete sw's RS
2011-04-15 13:31 641Final sql script version : ... -
Align Traffic light //Batch update EffortEstimation set IS_ALIGNED value =1 or 0
2011-04-14 15:04 0string effsAligned = (p ... -
你看错了吧？！
2011-04-13 10:12 686SELECT BUDGET.BUDGET_ID, BUDGET ... -
DataMigration script
2011-03-24 11:14 9741. not keep the budget plan his ... -
temp store
2011-03-18 15:14 0SELECT RESOURCEASSIGNMENT.RESOU ... -
temporay note
2011-03-04 16:04 752declare @projectidmax int,@curp ... -
Migrate budget table,MemberResource table
2011-03-04 15:57 695Add two column set default val ... -
Delete Script
2011-03-04 15:16 724declare @projectidmax int,@curp ... -
Delete Top n-1 from a groud of records of a table
2011-03-04 14:35 710Test successfully! decla ... -
Import data from ResourceAssignment into Resource_Assign
2011-03-04 14:29 930insert into RESOURCE_ASSIGN( MA ...
相关推荐
Programme de gestion budget
V1.0.9.29-sp3_20221110_02_YSYTH_FISCAL_BUDGET_PRO_调整调剂处理现有财政端数据的流程.sql
该压缩包包含了MATLAB编程实现的两个脚本，即"C_linkbudget_my.m"和"SL_budget.m"，以及一个详细说明文档"航天任务分析与设计.docx"。这些文件旨在帮助我们理解如何为中轨通信卫星的C波段和S/L波段的馈电链路进行...
标题 "bep_budget_public_body" 暗示我们可能在讨论一个与预算管理或公共财政相关的项目，可能是某个软件系统的一部分。"PLSQL美化"标签则指出这涉及到Oracle数据库的PL/SQL编程语言，可能涉及代码美化、格式化或者...
"Underground-communication.zip_link budget_underground_井下信道_通信链路预算"这个压缩包文件很可能包含了关于井下通信链路预算的详细资料，旨在帮助工程师们理解和优化这种通信方式。 首先，我们需要理解...
为了保证不同设备之间的兼容性和数据传输的可靠性，USB协会针对USB 3.1接口的高速模式SuperSpeed（包括Gen1和Gen2）制定了相应的信号损耗预算（Loss Budget）。损耗预算文档详细描述了在主机、电缆和设备各部分之间...
"Calculateur automatique de budget Excel 2020"指的是利用Microsoft Excel 2020的高级功能和公式来构建一个能自动计算和分析预算的电子表格工具。这个工具能够帮助用户实时跟踪收入、支出，并提供预算预测，以优化...
DBTables_BM_Prj_Budget_Lock.xml DBTables_BM_Prj_Budget_Lock.xmlDBTables_BM_Prj_Budget_Lock.xml
Configuration and the Calculation of Link Budget
在"budget_tracker_app_frontend"项目中，JavaScript负责处理用户输入、更新界面以及与后端进行数据通信。 在预算追踪应用中，前端部分通常包括以下几个关键组件： 1. 用户界面（UI）：UI设计需直观易用，允许用户...
标题“budget_tracker_homework”可能指的是一个用于个人财务管理的项目，它使用JavaScript作为主要编程语言。这个项目可能是为了帮助用户追踪他们的预算，监控收入和支出，以便更好地管理财务。 JavaScript是一种...
**Budget Planner App** 是一个基于React技术栈的应用，利用了React的Context API和Hook机制来管理应用程序的状态和行为。这个项目正处于开发阶段，主要集中在构建应用的业务逻辑和CSS样式设计，以提供用户友好的...
GPS_Power_Budget计算，计算GPS信号功率
《Python库django_budget_backend-0.2-py3-none-any.whl详解》 在Python的世界里，库扮演着至关重要的角色，它们为开发者提供了丰富的功能和便捷的工具，极大地提升了开发效率。今天我们要讨论的是一个名为`django_...
"budget_management_api" 是一个专为管理个人或企业预算的应用程序设计的后端服务。它主要使用 JavaScript 这一广泛使用的编程语言进行开发，这表明该API可能基于Node.js平台，因为Node.js是JavaScript在服务器端...
budget_bot_django：DjangoТелеграм-ботнабазе
这通常意味着用户可以通过自定义的VBA代码来自动化处理预算和日历任务，比如自动计算、提醒或生成报告。 在描述中提到的“Excel Planning Calendar Budget Daily 2020”，我们了解到这是一个2020年的每日预算和计划...
预算管理系统 功能介绍 该预算管理系统旨在帮助用户跟踪其财务流量并制定预算计划。 该系统可以跟踪用户的银行帐户余额，收入，支出并设置预算目标。 它还可以通过可视化用户的总财富，图表中的支出记录来生成财务...
T3A2预算跟踪器 为Coder Academy T3A2全栈分配创建的预算跟踪应用程序 目的 2020年是全球财务困难的一年，因此，现在比以往任何时候都更重要的是执行预算。 但是，如果没有办法跟踪预算，那就没有意义了。...
Budgent-Management-App 开发预算管理模块（适用于Odoo 12）来跟踪和管理家庭支出。 功能包括可以选择预算一个月的收入/支出。 添加/列出新的收入/费用。 将收入/费用与相应的银行帐户关联。 管理帐户之间的现金转移...