`

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 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

 

分享到:
评论

相关推荐

    Budget.zip_BUDGET_gestion_zip

    Programme de gestion budget

    V1.0.9.29-sp3_20221110_02_YSYTH_FISCAL_BUDGET_PRO_调整调剂处理现有财政端数据的流程.sql

    V1.0.9.29-sp3_20221110_02_YSYTH_FISCAL_BUDGET_PRO_调整调剂处理现有财政端数据的流程.sql

    LINKBUDGET.zip_link budget matlab_uplink budget_卫星_卫星通信_通信卫星 馈电

    该压缩包包含了MATLAB编程实现的两个脚本,即"C_linkbudget_my.m"和"SL_budget.m",以及一个详细说明文档"航天任务分析与设计.docx"。这些文件旨在帮助我们理解如何为中轨通信卫星的C波段和S/L波段的馈电链路进行...

    bep_budget_public_body

    标题 "bep_budget_public_body" 暗示我们可能在讨论一个与预算管理或公共财政相关的项目,可能是某个软件系统的一部分。"PLSQL美化"标签则指出这涉及到Oracle数据库的PL/SQL编程语言,可能涉及代码美化、格式化或者...

    Underground-communication.zip_link budget_underground_井下信道_通信链路预

    "Underground-communication.zip_link budget_underground_井下信道_通信链路预算"这个压缩包文件很可能包含了关于井下通信链路预算的详细资料,旨在帮助工程师们理解和优化这种通信方式。 首先,我们需要理解...

    USB_3.1_Loss_Budget_Rev_1.0_-_2015-03-02.pdf

    为了保证不同设备之间的兼容性和数据传输的可靠性,USB协会针对USB 3.1接口的高速模式SuperSpeed(包括Gen1和Gen2)制定了相应的信号损耗预算(Loss Budget)。损耗预算文档详细描述了在主机、电缆和设备各部分之间...

    calculateur-automatique-budget_excel_

    "Calculateur automatique de budget Excel 2020"指的是利用Microsoft Excel 2020的高级功能和公式来构建一个能自动计算和分析预算的电子表格工具。这个工具能够帮助用户实时跟踪收入、支出,并提供预算预测,以优化...

    DBTables_BM_Prj_Budget_Lock.xml

    DBTables_BM_Prj_Budget_Lock.xml DBTables_BM_Prj_Budget_Lock.xmlDBTables_BM_Prj_Budget_Lock.xml

    Configuration-and-the-Calculation-of-Link-Budget._link budget_li

    Configuration and the Calculation of Link Budget

    budget_tracker_app_frontend

    在"budget_tracker_app_frontend"项目中,JavaScript负责处理用户输入、更新界面以及与后端进行数据通信。 在预算追踪应用中,前端部分通常包括以下几个关键组件: 1. 用户界面(UI):UI设计需直观易用,允许用户...

    budget_tracker_homework

    标题“budget_tracker_homework”可能指的是一个用于个人财务管理的项目,它使用JavaScript作为主要编程语言。这个项目可能是为了帮助用户追踪他们的预算,监控收入和支出,以便更好地管理财务。 JavaScript是一种...

    Budget_Planner_App

    **Budget Planner App** 是一个基于React技术栈的应用,利用了React的Context API和Hook机制来管理应用程序的状态和行为。这个项目正处于开发阶段,主要集中在构建应用的业务逻辑和CSS样式设计,以提供用户友好的...

    GPS_Power_Budget

    GPS_Power_Budget计算,计算GPS信号功率

    Python库 | django_budget_backend-0.2-py3-none-any.whl

    《Python库django_budget_backend-0.2-py3-none-any.whl详解》 在Python的世界里,库扮演着至关重要的角色,它们为开发者提供了丰富的功能和便捷的工具,极大地提升了开发效率。今天我们要讨论的是一个名为`django_...

    budget_management_api:用于管理我的预算的应用程序的后端

    "budget_management_api" 是一个专为管理个人或企业预算的应用程序设计的后端服务。它主要使用 JavaScript 这一广泛使用的编程语言进行开发,这表明该API可能基于Node.js平台,因为Node.js是JavaScript在服务器端...

    budget_bot_django:DjangoТелеграм-ботнабазе

    budget_bot_django:DjangoТелеграм-ботнабазе

    budget-calendar-daily_Office_excel_calendar_VBa_planning_

    这通常意味着用户可以通过自定义的VBA代码来自动化处理预算和日历任务,比如自动计算、提醒或生成报告。 在描述中提到的“Excel Planning Calendar Budget Daily 2020”,我们了解到这是一个2020年的每日预算和计划...

    Budget_Management_System

    预算管理系统 功能介绍 该预算管理系统旨在帮助用户跟踪其财务流量并制定预算计划。 该系统可以跟踪用户的银行帐户余额,收入,支出并设置预算目标。 它还可以通过可视化用户的总财富,图表中的支出记录来生成财务...

    budget_tracker_app_backend

    T3A2预算跟踪器 为Coder Academy T3A2全栈分配创建的预算跟踪应用程序 目的 2020年是全球财务困难的一年,因此,现在比以往任何时候都更重要的是执行预算。 但是,如果没有办法跟踪预算,那就没有意义了。...

    budget_expense_management

    Budgent-Management-App 开发预算管理模块(适用于Odoo 12)来跟踪和管理家庭支出。 功能包括可以选择预算一个月的收入/支出。 添加/列出新的收入/费用。 将收入/费用与相应的银行帐户关联。 管理帐户之间的现金转移...

Global site tag (gtag.js) - Google Analytics