`

DataMigration script

Go 
阅读更多

1. not keep the budget plan history, only keep the current budget plan data and all status will be set "DRAFT"  and have to start a new lifecycle in new process control;

 

2. resource assigned to project before will transform into first his department effortestimation of the project

3. open point: current budget plan ,what is the definition

3

4

5

6

7

8

9

10

 

ALTER TABLE dbo.BUDGET ADD  
    IS_IN_PROCESS bit NULL,   
    BUDGET_PLAN_STATUS nvarchar(50) NULL  



 
ALTER TABLE dbo.BUDGET ADD CONSTRAINT  
    DF_BUDGET_IS_IN_PROCESS DEFAULT 1 FOR IS_IN_PROCESS  
    


ALTER TABLE dbo.MEMBERRESOURCE ADD         
   [STARTDATE] [datetime] NULL         
   
   
   
   
UPDATE MEMBERRESOURCE SET STARTDATE='2009-9-27'  


ALTER TABLE MEMBERRESOURCE  ALTER COLUMN STARTDATE DATETIME  NOT NULL  
                                                                       
ALTER TABLE MEMBERRESOURCE  ALTER COLUMN CLOSEDATE DATETIME  NOT NULL


if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[RESOURCE_ASSIGN]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)    
drop table [dbo].[RESOURCE_ASSIGN]                                                                                                     
GO                                                                                                                                     
                                                                                                                                       
CREATE TABLE [dbo].[RESOURCE_ASSIGN] (                                                                                                 
   [RESOURCE_ASSIGN_ID] [bigint] IDENTITY (1, 1) NOT NULL ,                                                                           
   [FK_MEMBERRESOURCE_ID] [bigint] NULL  ,                                                                                             
   [FK_EFFORT_ESTIMATION_ID] [bigint] NULL  ,                                                                                          
   [MANMONTH_10] [real] NULL  ,                                                                                                        
    [MANMONTH_11] [real] NULL  ,                                                                                                       
    [MANMONTH_12] [real] NULL  ,                                                                                                       
    [MANMONTH_01] [real] NULL  ,                                                                                                       
    [MANMONTH_02] [real] NULL  ,                                                                                                       
    [MANMONTH_03] [real] NULL  ,                                                                                                       
    [MANMONTH_04] [real] NULL  ,                                                                                                       
    [MANMONTH_05] [real] NULL  ,                                                                                                       
    [MANMONTH_06] [real] NULL  ,                                                                                                       
    [MANMONTH_07] [real] NULL  ,                                                                                                       
    [MANMONTH_08] [real] NULL  ,                                                                                                       
    [MANMONTH_09] [real] NULL                                                                                                         
) ON [PRIMARY]                                                                                                                        
GO   


declare @projectidmax int,@curprojectid int;                                                                                                                                                                                             
                                                                                                        
select @projectidmax=max(fk_project_id) from budget;                                                    
set @curprojectid=1;                                                                                    
while @curprojectid<=@projectidmax                                                                       
begin                                                                                                   
declare @budgetidmax int;                                                                               
select @budgetidmax=max(budget_id) from budget where fk_project_id=@curprojectid;                       
update budget set IS_IN_PROCESS=1 where ((fk_project_id=@curprojectid) and (budget_id=@budgetidmax));   
update budget set IS_IN_PROCESS=0  where ((fk_project_id=@curprojectid) and (budget_id<@budgetidmax)); 

insert into RESOURCE_ASSIGN( MANMONTH_10,       
      MANMONTH_11, MANMONTH_12, MANMONTH_01, MANMONTH_02, MANMONTH_03,       
      MANMONTH_04, MANMONTH_05, MANMONTH_06, MANMONTH_07, MANMONTH_08,       
      MANMONTH_09,FK_MEMBERRESOURCE_ID, FK_EFFORT_ESTIMATION_ID )      
     
SELECT RESOURCEASSIGNMENT.MANMONTH_10,       
    RESOURCEASSIGNMENT.MANMONTH_11, RESOURCEASSIGNMENT.MANMONTH_12,       
    RESOURCEASSIGNMENT.MANMONTH_01, RESOURCEASSIGNMENT.MANMONTH_02,       
    RESOURCEASSIGNMENT.MANMONTH_03, RESOURCEASSIGNMENT.MANMONTH_04,       
      RESOURCEASSIGNMENT.MANMONTH_05, RESOURCEASSIGNMENT.MANMONTH_06,       
      RESOURCEASSIGNMENT.MANMONTH_07, RESOURCEASSIGNMENT.MANMONTH_08,       
      RESOURCEASSIGNMENT.MANMONTH_09,       
      RESOURCEASSIGNMENT.FK_MEMBERRESOURCE_ID,   
(   
SELECT TOP 1 EFFORT_ESTIMATION_ID   
FROM dbo.EFFORT_ESTIMATION   
WHERE (EFFORT_ESTIMATION.FK_FISCALYEAR_ID=RESOURCEASSIGNMENT.FK_FISCALYEAR_ID AND FK_BUDGET_ID IN  
          (SELECT TOP 1 BUDGET_ID    
         FROM dbo.BUDGET   
         WHERE (FK_PROJECT_ID = @curprojectid) AND (IS_IN_PROCESS = 1)   
 AND    
      (FK_DIVISION_ID = (SELECT TOP 1 FK_DIVISION_ID FROM TEAM WHERE TEAM_ID=(SELECT TOP 1 FK_TEAM_ID FROM MEMBERRESOURCE WHERE MEMBERRESOURCE.MEMBERRESOURCE_ID=RESOURCEASSIGNMENT.FK_MEMBERRESOURCE_ID)))))   
)     
FROM RESOURCEASSIGNMENT Where fk_project_id=@curprojectid;  


set @curprojectid=@curprojectid+1;      
end       

INSERT INTO WORKPACKAGE (NAME) VALUES ('ACTUALDATA');

 UPDATE EFFORT_ESTIMATION SET IS_ACTUAL=0,FK_WORKPACKAGE_ID=27,INITIAL_TOTAL_MANMONTH=MONTH_10+MONTH_11+MONTH_12+MONTH_1+MONTH_2+MONTH_3+MONTH_4+MONTH_5+MONTH_6+MONTH_7+MONTH_8+MONTH_9 WHERE IS_ACTUAL=1;                              
 
 ALTER TABLE dbo.EFFORT_ESTIMATION ADD  
  IS_ALIGNED bit NULL;   
  
  
ALTER TABLE dbo.EFFORT_ESTIMATION ADD CONSTRAINT                 
 DF_EFFORT_ESTIMATION_IS_ALIGNED DEFAULT 0 FOR IS_ALIGNED ;
 
 
 UPDATE EFFORT_ESTIMATION SET IS_ALIGNED = 0;  
 
 There are two alternatives :
 [update BUDGET SET BUDGET_PLAN_STATUS='DRAFT';  ]
 [update BUDGET SET BUDGET_PLAN_STATUS=case when STATUS='FROZEN'  and IS_IN_PROCESS='0' then 'APPROVED' ELSE 'DRAFT' END ;]
 
UPDATE EXPENSE_ESTIMATION
SET IS_ACTUAL = 0, 
      INIT_TOTAL_COST = MONTH_10 + MONTH_11 + MONTH_12 + MONTH_1 + MONTH_2 +
       MONTH_3 + MONTH_4 + MONTH_5 + MONTH_6 + MONTH_7 + MONTH_8 + MONTH_9
WHERE (IS_ACTUAL = 1); ??????? have to perfect

 

 Added on April 1st,2011 noon

 

 UPDATE EFFORT_ESTIMATION SET IS_ALIGNED = 1 WHERE (EFFORT_ESTIMATION_ID IN  (

SELECT EFFORT_ESTIMATION_ID FROM EFFORT_ESTIMATION INNER JOIN  BUDGET ON  EFFORT_ESTIMATION.FK_BUDGET_ID = BUDGET.BUDGET_ID
         WHERE (BUDGET.BUDGET_PLAN_STATUS = 'APPROVED') AND
      (BUDGET.IS_IN_PROCESS = 0)));

 

 

 

DELETE FROM RESOURCE_ASSIGN WHERE (FK_EFFORT_ESTIMATION_ID IS NULL);

 

 

 

 

 

update Effort_estimation set is_aligned='1' where EFFORT_ESTIMATION_ID in
(
select  EFFORT_ESTIMATION_ID from
(
SELECT SUM(RESOURCE_ASSIGN.MANMONTH_01) AS SUM_MANMONTH1,
SUM(RESOURCE_ASSIGN.MANMONTH_02) AS SUM_MANMONTH2,
SUM(RESOURCE_ASSIGN.MANMONTH_03) AS SUM_MANMONTH3,
SUM(RESOURCE_ASSIGN.MANMONTH_04) AS SUM_MANMONTH4,
SUM(RESOURCE_ASSIGN.MANMONTH_05) AS SUM_MANMONTH5,
SUM(RESOURCE_ASSIGN.MANMONTH_06) AS SUM_MANMONTH6,
SUM(RESOURCE_ASSIGN.MANMONTH_07) AS SUM_MANMONTH7,
SUM(RESOURCE_ASSIGN.MANMONTH_08) AS SUM_MANMONTH8,
SUM(RESOURCE_ASSIGN.MANMONTH_09) AS SUM_MANMONTH9,
SUM(RESOURCE_ASSIGN.MANMONTH_10) AS SUM_MANMONTH10,
SUM(RESOURCE_ASSIGN.MANMONTH_11) AS SUM_MANMONTH11,
SUM(RESOURCE_ASSIGN.MANMONTH_12) AS SUM_MANMONTH12,
      EFFORT_ESTIMATION.EFFORT_ESTIMATION_ID,
      EFFORT_ESTIMATION.MONTH_1,
      EFFORT_ESTIMATION.MONTH_2,
      EFFORT_ESTIMATION.MONTH_3,
      EFFORT_ESTIMATION.MONTH_4,
      EFFORT_ESTIMATION.MONTH_5,
      EFFORT_ESTIMATION.MONTH_6,
      EFFORT_ESTIMATION.MONTH_7,
      EFFORT_ESTIMATION.MONTH_8,
      EFFORT_ESTIMATION.MONTH_9,
      EFFORT_ESTIMATION.MONTH_10,
      EFFORT_ESTIMATION.MONTH_11,
      EFFORT_ESTIMATION.MONTH_12
FROM RESOURCE_ASSIGN INNER JOIN
      EFFORT_ESTIMATION ON
      RESOURCE_ASSIGN.FK_EFFORT_ESTIMATION_ID = EFFORT_ESTIMATION.EFFORT_ESTIMATION_ID
       INNER JOIN
      BUDGET ON EFFORT_ESTIMATION.FK_BUDGET_ID = BUDGET.BUDGET_ID
WHERE (BUDGET.IS_IN_PROCESS = 1)
GROUP BY EFFORT_ESTIMATION.EFFORT_ESTIMATION_ID,
      EFFORT_ESTIMATION.MONTH_1,
      EFFORT_ESTIMATION.MONTH_2,
      EFFORT_ESTIMATION.MONTH_3,
      EFFORT_ESTIMATION.MONTH_4,
      EFFORT_ESTIMATION.MONTH_5,
      EFFORT_ESTIMATION.MONTH_6,
      EFFORT_ESTIMATION.MONTH_7,
      EFFORT_ESTIMATION.MONTH_8,
      EFFORT_ESTIMATION.MONTH_9,
      EFFORT_ESTIMATION.MONTH_10,
      EFFORT_ESTIMATION.MONTH_11,
      EFFORT_ESTIMATION.MONTH_12
)AAAAA where (SUM_MANMONTH1 = MONTH_1 and SUM_MANMONTH2 = MONTH_2  and SUM_MANMONTH3 = MONTH_3 and SUM_MANMONTH4 = MONTH_4 and SUM_MANMONTH5 = MONTH_5 and SUM_MANMONTH6 = MONTH_6 and SUM_MANMONTH7 = MONTH_7 and SUM_MANMONTH8 = MONTH_8 and SUM_MANMONTH9 = MONTH_9 and SUM_MANMONTH10 = MONTH_10 and SUM_MANMONTH11 = MONTH_11 and SUM_MANMONTH12 = MONTH_12)
)

 

 

 

 

 

 

ALTER TABLE dbo.BUDGET ADD  
    IS_IN_PROCESS bit NULL,   
    BUDGET_PLAN_STATUS nvarchar(50) NULL  



 
ALTER TABLE dbo.BUDGET ADD CONSTRAINT  
    DF_BUDGET_IS_IN_PROCESS DEFAULT 1 FOR IS_IN_PROCESS  
    


ALTER TABLE dbo.MEMBERRESOURCE ADD         
   [STARTDATE] [datetime] NULL         
   
   
   
   
UPDATE MEMBERRESOURCE SET STARTDATE='2009-9-27'  


ALTER TABLE MEMBERRESOURCE  ALTER COLUMN STARTDATE DATETIME  NOT NULL  
                                                                       
ALTER TABLE MEMBERRESOURCE  ALTER COLUMN CLOSEDATE DATETIME  NOT NULL


if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[RESOURCE_ASSIGN]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)    
drop table [dbo].[RESOURCE_ASSIGN]                                                                                                     
GO                                                                                                                                     
                                                                                                                                       
CREATE TABLE [dbo].[RESOURCE_ASSIGN] (                                                                                                 
   [RESOURCE_ASSIGN_ID] [bigint] IDENTITY (1, 1) NOT NULL ,                                                                           
   [FK_MEMBERRESOURCE_ID] [bigint] NULL ,                                                                                             
   [FK_EFFORT_ESTIMATION_ID] [bigint] NULL ,                                                                                          
   [MANMONTH_10] [real] NULL  ,                                                                                                        
    [MANMONTH_11] [real] NULL  ,                                                                                                       
    [MANMONTH_12] [real] NULL  ,                                                                                                       
    [MANMONTH_01] [real] NULL  ,                                                                                                       
    [MANMONTH_02] [real] NULL  ,                                                                                                       
    [MANMONTH_03] [real] NULL  ,                                                                                                       
    [MANMONTH_04] [real] NULL  ,                                                                                                       
    [MANMONTH_05] [real] NULL  ,                                                                                                       
    [MANMONTH_06] [real] NULL  ,                                                                                                       
    [MANMONTH_07] [real] NULL  ,                                                                                                       
    [MANMONTH_08] [real] NULL  ,                                                                                                       
    [MANMONTH_09] [real] NULL                                                                                                         
) ON [PRIMARY]                                                                                                                        
GO   


declare @projectidmax int,@curprojectid int;                                                                                                                                                                                             
                                                                                                        
select @projectidmax=max(fk_project_id) from budget;                                                    
set @curprojectid=1;                                                                                    
while @curprojectid<=@projectidmax                                                                       
begin                                                                                                   
declare @budgetidmax int;                                                                               
select @budgetidmax=max(budget_id) from budget where fk_project_id=@curprojectid;                       
update budget set IS_IN_PROCESS=1 where ((fk_project_id=@curprojectid) and (budget_id=@budgetidmax));   
update budget set IS_IN_PROCESS=0  where ((fk_project_id=@curprojectid) and (budget_id<@budgetidmax)); 

insert into RESOURCE_ASSIGN( MANMONTH_10,       
      MANMONTH_11, MANMONTH_12, MANMONTH_01, MANMONTH_02, MANMONTH_03,       
      MANMONTH_04, MANMONTH_05, MANMONTH_06, MANMONTH_07, MANMONTH_08,       
      MANMONTH_09,FK_MEMBERRESOURCE_ID, FK_EFFORT_ESTIMATION_ID )      
     
SELECT RESOURCEASSIGNMENT.MANMONTH_10,       
    RESOURCEASSIGNMENT.MANMONTH_11, RESOURCEASSIGNMENT.MANMONTH_12,       
    RESOURCEASSIGNMENT.MANMONTH_01, RESOURCEASSIGNMENT.MANMONTH_02,       
    RESOURCEASSIGNMENT.MANMONTH_03, RESOURCEASSIGNMENT.MANMONTH_04,       
      RESOURCEASSIGNMENT.MANMONTH_05, RESOURCEASSIGNMENT.MANMONTH_06,       
      RESOURCEASSIGNMENT.MANMONTH_07, RESOURCEASSIGNMENT.MANMONTH_08,       
      RESOURCEASSIGNMENT.MANMONTH_09,       
      RESOURCEASSIGNMENT.FK_MEMBERRESOURCE_ID,   
(   
SELECT TOP 1 EFFORT_ESTIMATION_ID   
FROM dbo.EFFORT_ESTIMATION   
WHERE (EFFORT_ESTIMATION.FK_FISCALYEAR_ID=RESOURCEASSIGNMENT.FK_FISCALYEAR_ID AND FK_BUDGET_ID IN  
          (SELECT TOP 1 BUDGET_ID    
         FROM dbo.BUDGET   
         WHERE (FK_PROJECT_ID = @curprojectid) AND (IS_IN_PROCESS = 1)   
 AND    
      (FK_DIVISION_ID = (SELECT TOP 1 FK_DIVISION_ID FROM TEAM WHERE TEAM_ID=(SELECT TOP 1 FK_TEAM_ID FROM MEMBERRESOURCE WHERE MEMBERRESOURCE.MEMBERRESOURCE_ID=RESOURCEASSIGNMENT.FK_MEMBERRESOURCE_ID)))))   
)     
FROM RESOURCEASSIGNMENT Where fk_project_id=@curprojectid;  


set @curprojectid=@curprojectid+1;      
end       

INSERT INTO WORKPACKAGE (NAME) VALUES ('ACTUALDATA');

 UPDATE EFFORT_ESTIMATION SET IS_ACTUAL=0,FK_WORKPACKAGE_ID=27,INITIAL_TOTAL_MANMONTH=MONTH_10+MONTH_11+MONTH_12+MONTH_1+MONTH_2+MONTH_3+MONTH_4+MONTH_5+MONTH_6+MONTH_7+MONTH_8+MONTH_9 WHERE IS_ACTUAL=1;                              
 
 ALTER TABLE dbo.EFFORT_ESTIMATION ADD  
  IS_ALIGNED bit NULL;   
  
  
ALTER TABLE dbo.EFFORT_ESTIMATION ADD CONSTRAINT                 
 DF_EFFORT_ESTIMATION_IS_ALIGNED DEFAULT 0 FOR IS_ALIGNED ;
 
 
 UPDATE EFFORT_ESTIMATION SET IS_ALIGNED = 0;  
 
 
 update BUDGET SET BUDGET_PLAN_STATUS='DRAFT';

  

 

 

 

 

 

分享到：
| Murphy's Law 墨菲法则__土豆, 马铃薯
评论
发表评论

您还没有登录,请您登录后再发表评论

相关推荐

    database design and database deployment tool BDB 2007 Developer V2.6

    When you apply it to other database platforms, the system will transform the data type and deal with the grammar differences under different database platforms. 8.Adopt the scheme pattern Support and...

    database design and database deployment tool BDB 2007 Professional V2.6

    When you apply it to other database platforms, the system will transform the data type and deal with the grammar differences under different database platforms. 8.Adopt the scheme pattern Support and...

    Database design and development tool BDB Professional V2.6

    When you apply it to other database platforms, the system will transform the data type and deal with the grammar differences under different database platforms. 8.Adopt the scheme pattern Support and...

    BDB 2007 Professional Edition V2.6

    When you apply it to other database platforms, the system will transform the data type and deal with the grammar differences under different database platforms. 8.Adopt the scheme pattern Support and...

    BDB Professional Edition v2.7

    When you apply it to other database platforms, the system will transform the data type and deal with the grammar differences under different database platforms. 8.Adopt the scheme pattern Support and...

    SQL Query Intellisense and Database Design Tool BDB 2.7.0.3

    The objects that can be migrated includes tables, indexes, constraints, default values, triggers,views, procedures, functions and the table data. · SQL script pre-view · Support across the ...

    Database design & Database deployment software BDB 2007 Professional V2.3

    4. inquiry analysis, data table inquiries, data editing functions. Provide a dedicated SQL editor, support for SQL syntax highlightedment, rapid script input, predefined scripts input. 5. pre-output ...

    Database design & Database deployment software BDB 2007 Developer V2.3

    4. inquiry analysis, data table inquiries, data editing functions. Provide a dedicated SQL editor, support for SQL syntax highlightedment, rapid script input, predefined scripts input. 5. pre-output ...

    Odoo 11 Development Cookbook-Second Edition

    It also explains how to write a migration script when a data model provided by an addon is modified in a new release. Chapter 7 , Debugging and Automated Testing, proposes some strategies for server-...

    数据库设计，建模和部署工具BDBPro3.1-setup_EN

    When you apply it to other database platforms, the system will transform the data type and deal with the grammar 当你将它应用于其他数据库平台，该系统将变换的数据类型和处理与语法 differences under ...

    leaflet.migrationLayer：地图上的迁移数据可视化

    leaflet.migrationLayer leafet.migrationLayer用于显示迁移数据，例如人口，航班，车辆，交通等。... script src =" ./dist/leaflet.migrationLayer.js " &gt; &lt;/ script &gt; 2.创建一个新的迁移层 var migrati

    spring-boot-reference.pdf

    29.3. JPA and “Spring Data” 29.3.1. Entity Classes 29.3.2. Spring Data JPA Repositories 29.3.3. Creating and Dropping JPA Databases 29.3.4. Open EntityManager in View 29.4. Using H2’s Web Console ...

    Sams Teach Yourself WordPress in 10 Minutes.pdf

    7 Using RSS and Data Migration Tools 113 Syndicate Your Blog with RSS ..............................................113 Other Useful Tools ..............................................................

    Exchange Server 2010 Unleashed.pdf

    Exchange Management Script based on PowerShell, the Microsoft scripting solution that is the basis of the configuration, administration, and operations of Exchange Server 2010. .Part IV: Securing an ...

    Laraboot: Laravel 5 For Beginners

    Laravel 5.2 初学者指南 Laraboot: Laravel 5 For Beginners Bill Keck ...This version was published on 2016-04-04 This is a Leanpub book. Leanpub empowers authors and publishers with the Lean Publishing ...

    Doctrine ORM for PHP.pdf

    Test Script..........................................................................................................................23 Conclusion.........................................................

    DebuggingWithGDB 6.8-2008

    Table of Contents Summary of gdb . . . . . . . . ....Free Software ....Free Software Needs Free Documentation ....Contributors to gdb....1 A Sample gdb Session ....2 Getting In and Out of gdb ....2.1 Invoking gdb ....

    TD8.0管理员手册

    Setting Up the TestDirector Workflow ............................67...Script Editor ........................................................................76 Understanding TestDirector Events .........

Magicbox
Global site tag (gtag.js) - Google Analytics