mssp
- 浏览: 49108 次
- 性别:
- 来自: 南京
-
文章分类
最新评论
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 608version1: declare @budgetIdmax ... -
Set IS_ALIGNED='1' for EEs of all budgets in process&Delete sw's RS
2011-04-15 13:31 608Final 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 665SELECT BUDGET.BUDGET_ID, BUDGET ... -
DataMigration script
2011-03-24 11:14 9561. not keep the budget plan his ... -
temp store
2011-03-18 15:14 0SELECT RESOURCEASSIGNMENT.RESOU ... -
temporay note
2011-03-04 16:04 731declare @projectidmax int,@curp ... -
Migrate budget table,MemberResource table
2011-03-04 15:57 675Add two column set default val ... -
Delete Script
2011-03-04 15:16 703declare @projectidmax int,@curp ... -
Delete Top n-1 from a groud of records of a table
2011-03-04 14:35 692Test successfully! decla ... -
Import data from ResourceAssignment into Resource_Assign
2011-03-04 14:29 903insert into RESOURCE_ASSIGN( MA ...
相关推荐
Programme de gestion budget
V1.0.9.29-sp3_20221110_02_YSYTH_FISCAL_BUDGET_PRO_调整调剂处理现有财政端数据的流程.sql
本程序包含中轨通信卫星C波段馈电链路上下行预算，以及S,L波段业务链路上下行链路预算，并附有word文档说明
NULL 博文链接：https://nickys.iteye.com/blog/2176360
USB协会针对SuperSpeed中Host-cable-device各段的信号传输损耗给出了建议，可以根据该文档对各段的损耗限制作为设计要求
井下通信时的链路预算，包括误码率和信道容量的仿真。
calculatuer outomatiqer ficher excel 2020
DBTables_BM_Prj_Budget_Lock.xml DBTables_BM_Prj_Budget_Lock.xmlDBTables_BM_Prj_Budget_Lock.xml
budget_tracker_app_frontend
Configuration and the Calculation of Link Budget
budget_tracker_homework
budget_management_api：用于管理我的预算的应用程序的后端
margin_Budget_卫星通信链路预算_卫星通信_卫星参数MARGIN_matlab_源码.zip
margin_Budget_卫星通信链路预算_卫星通信_卫星参数MARGIN_matlab.zip
GPS_Power_Budget计算，计算GPS信号功率
budget_bot_django：DjangoТелеграм-ботнабазе
使用React Context和hook构建的Budget Planner App： 状态：正在建立应用程式逻辑和CSS样式。
预算管理系统 功能介绍 该预算管理系统旨在帮助用户跟踪其财务流量并制定预算计划。 该系统可以跟踪用户的银行帐户余额，收入，支出并设置预算目标。 它还可以通过可视化用户的总财富，图表中的支出记录来生成财务...
T3A2预算跟踪器 为Coder Academy T3A2全栈分配创建的预算跟踪应用程序 目的 2020年是全球财务困难的一年，因此，现在比以往任何时候都更重要的是执行预算。 但是，如果没有办法跟踪预算，那就没有意义了。...
Budgent-Management-App 开发预算管理模块（适用于Odoo 12）来跟踪和管理家庭支出。 功能包括可以选择预算一个月的收入/支出。 添加/列出新的收入/费用。 将收入/费用与相应的银行帐户关联。 管理帐户之间的现金转移...