`

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

    数据库设计，建模和部署工具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 ...

    Vue2.0组件间数据传递示例

    更多信息可以查看 Vue 官方文档的 migration 指南。 Vue2.0组件间数据传递可以通过 props、 `$emit` 等方式实现。这些方法可以帮助开发者更方便地实现组件间数据传递，提高开发效率和代码质量。

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

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

    eCATT, LSMW in detail

    在文件"SAP_Data_Migration.pdf"中，可能会详细介绍这两种工具的使用步骤、最佳实践、示例场景以及可能遇到的问题和解决方案。这个文件将帮助读者深入理解如何有效地利用eCATT和LSMW进行数据迁移，提升他们在SAP环境...

    ecahrts迁徙图

    迁徙图（Migration Map）是 ECharts 图表类型中的一种，主要用于展示人口、动物或者物体的迁移路径和动态过程。这种图表通过线条和箭头来表示迁移的方向和规模，可以清晰地展现数据流的动态变化，常用于交通、人口...

    sap数据批量导入教程、BDC实例详细讲解步骤.docx

    首先，输入事务码SECATT，键入回车，创建Test Script，选择该选项输入自定义程序名，点击“新建”按钮进入下面的界面，输入文本说明及事务码所属模块，保存至《本地对象》。然后，点击“模式”按钮，进入下面界面，...

    在SAP中查询TABLE的18种方法

    LSMW (Legacy System Migration Workbench) LSMW主要用于从旧系统向SAP迁移数据，但它也提供了查询SAP TABLE数据的功能，特别是在进行数据转换和验证过程中。 ### 10. SQLScript SQLScript是SAP HANA数据库中的...

    SAP批导入处理教程

    SAP批导入是企业数据迁移和系统集成的重要技术手段，主要包括ECATT（Extended Computer Aided Test Tool）、LSMW（Legacy System Migration Workbench）和BDC（Batch Data Conversion）。这些工具各有特点，适用于...

    SQL_Toolbelt_2018_2.0.1.2321_Downloadly.ir.rar

    5. **Migration Tools**：协助用户在不同数据库之间迁移数据和结构，例如从MySQL迁移到SQL Server，或者从Oracle迁移到PostgreSQL。 6. **Profiler**：性能监控工具，用于追踪SQL查询执行情况，找出数据库性能瓶颈...

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

    一个CLI工具用于帮助Vue1x迁移至20

    1. **单文件组件（Single File Components, SFCs）的改变**：在Vue 2.0中，SFCs的语法有所调整，如`&lt;script&gt;`、`&lt;style&gt;`和`&lt;template&gt;`标签的使用更加规范，且支持ES6语法。 2. **指令（Directives）的更新**： -...

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