• Статья

Выполнение скрипта переноса 7 для сравнения моментальных снимков проверки данных Project Server 2003 и Project Server 2007

 

Применимо к: Project Server 2010

Последнее изменение раздела: 2012-03-16

Скрипт переноса 7 сравнивает моментальный снимок данных Microsoft Office Project Server 2003, сделанный перед переносом (созданный скриптом 3), с моментальным снимком данных Microsoft Office Project Server 2007, сделанным после переноса (созданным скриптом 5). Скрипт 7 выполняет следующее:

  1. Составляет список проектов, не перенесенных в базу данных черновиков (проекты, существующие в Project Server 2003, но отсутствующие в базе данных опубликованных проектов Office Project Server 2007).

  2. Составляет список имен несовпадающих проектов с отличиями в определенных полях.

  3. Составляет список имен и задач несовпадающих проектов с отличиями в определенных полях.

Этот скрипт задает переменные для полей работы, длительности и стоимости проектов и задач. Переменные используются для определения различий полей, которые либо больше положительной границы ошибки, либо меньше отрицательной границы ошибки, измеренной в процентах. Эта функция позволяет пользователю игнорировать различия, лежащие в пределах ошибки, и сосредоточиться на существенных отличиях.

Предупреждение

Этот скрипт является одним из нескольких скриптов, выполняемых после переноса и включенных в виртуальную среду переноса Project Server. Выполнение скриптов не является обязательным, но это настоятельно рекомендуется делать, чтобы помочь обнаружить проблемы, которые могут помешать успешному переносу данных. Дополнительные сведения о доступных скриптах, выполняемых после переноса, см. в статье Виртуальная среда переноса Project Server: выполнение скриптов после переноса (необязательно).

Чтобы запустить скрипт 7, выполните перечисленные ниже действия.

  1. На рабочем столе виртуальной среды переноса щелкните Запустить процесс переноса. Откроется окно проводника Windows, в котором отображается содержимое диска E.

  2. В проводнике Windows дважды щелкните следующую папку:

    • При наличии одной базы данных откройте папку Migrate_Proj_2003_Single_DB.

    • Если базы данных Project Server 2003 разделены, откройте Migrate_Proj_2003_Split_DB.

  3. Откройте папку Verification Scripts и щелкните скрипт VME Script 7.sql. Это действие откроет SQL Server Management Studio и отобразит скрипт 7.

  4. Щелкните Выполнить, чтобы выполнить скрипт.

  5. Просмотрите результаты.

Важно!

В результатах выполнения скрипта могут содержаться сообщения о ложном несовпадении идентификаторов задач для задач Project Server 2003 и Office Project Server 2007. Например, при сравнении задачи, перенесенной из Project Server 2003 в Office Project Server 2007, идентификатор TASK_UID Project Server 2003 может не совпадать с идентификатором TASK_ID Project Server 2003. Это может объясняться тем, что при вставке, удалении и перемещении в рамках проекта задачи могут получить несовпадающие идентификаторы UID и ID в Project Server 2003. Если в результатах выполнения скрипта 7 присутствуют несовпадения такого типа, можно проверить правильность перенесенных значений путем сравнения значений, отображаемых в Microsoft Project профессиональный 2010 из исходной среды в среду переноса. Если значения совпадают, следовательно, расхождения, о которых сообщает скрипт 7, являются ложными, и их можно оставить без внимания.

Скрипт 7

Скрипт 7 содержит следующий код:

/*---------------------------------------------------------------------------------------
-- Script A7: Compares data validation snapshot between Pre-Migration (2003) and Post-Migration (2007) databases ----
-- This script:
-- 1. Compares the project data before and after the migration from Project Server 2003 SP3 to Project Server 2003 SP2
-- 2. List the projects not migrated to the draft database (existing in Project Server 2003 but missing in 2007)
   3. List the distinct project names with differences on specific fields
   4. List the distinct project names and tasks with differences on specific fields
   5. Adjust to parameters by using variables containing the % of difference between the Project 2003 and 2007 data
   
   This script requires you to set the database names of the Migration Validation Database.
   By default we have: USE ProjectServer_Migration_Data_Validation
   
   This script allows you to set variables for the Work, Duration and Cost fields of the projects and tasks. The variables are used 
   to expose the differences on various fields which are either greater to the positive margin of error (in %) or smaller than 
   the negative margin of error (in %). This feature allows the user to ignore the differences which are within the margin of error, 
   and focus on the significant differences.
   
-- ************************************************ BEGIN DECLARE VARIABLES ************************************************ --
*/
DECLARE @Work_Ratio decimal(18,4);-- Variable to set the Work Ratio differences in % (+ or -) which are tolerable
DECLARE @Duration_Ratio decimal(18,4);-- Variable to set the Duration Ratio differences in % (+ or -) which are tolerable
DECLARE @Cost_Ratio decimal(18,4);-- Variable to set the Cost Ratio differences in % (+ or -) which are tolerable

--------------------------------------------  END DECLARE VARIABLES  ---------------------------------------------

-- *********************************************** BEGIN SET VARIABLE VALUES *********************************************** --

SET @Work_Ratio = 0.005;-- Set the Work Ratio to a % to ignore differences (+ or -) which are tolerable
SET @Duration_Ratio = 0.005;-- Set local variable to a % to ignore differences (+ or -) which are tolerable
SET @Cost_Ratio = 0.005;-- Set local variable to a % to ignore differences (+ or -) which are tolerable

------------------------------------------  END SET VARIABLE VALUES  -----------------------------------------------
  
 ----------------------------------------------------------------------------------------------*/

USE ProjectServer_Migration_Data_Validation

--------------------------------------------------
-- List of Projects not migrated to the Project Server 2007 Draft database
--------------------------------------------------
select distinct [Project Name] AS 'List of Projects not migrated to the Project Server 2007 Draft database' 
from Migration_PS2003_Data_Validation_Snapshot 
where replace([Project Name], '.Published', '_Published') not in 
(select [Project Name] from Migration_PS2007_Data_Validation_Snapshot)
and Migration_PS2003_Data_Validation_Snapshot.[proj type] in (0,1,100)


--------------------------------------------------
-- Projects with different start date
--------------------------------------------------
select 
Migration_PS2003_Data_Validation_Snapshot.[Project Name] 
AS 'List of Projects with Project Start Dates not Matching After Migration', 
datediff(day, 
Migration_PS2003_Data_Validation_Snapshot.[Proj Start Date],
Migration_PS2007_Data_Validation_Snapshot.[Proj Start Date]) 
as Difference_in_Days
from 
Migration_PS2003_Data_Validation_Snapshot, Migration_PS2007_Data_Validation_Snapshot WITH (NOLOCK)
where
REPLACE(Migration_PS2003_Data_Validation_Snapshot.[Project Name],'.Published','_Published') 
= Migration_PS2007_Data_Validation_Snapshot.[Project Name] and 
Migration_PS2003_Data_Validation_Snapshot.[Proj Start Date] 
<> Migration_PS2007_Data_Validation_Snapshot.[Proj Start Date]
order by Difference_in_Days



--------------------------------------------------
-- Projects with different finish date
--------------------------------------------------
select distinct
Migration_PS2003_Data_Validation_Snapshot.[Project Name] 
AS 'List of Projects with Project Finish Dates not Matching After Migration', 
Migration_PS2003_Data_Validation_Snapshot.[Proj Finish Date], 
Migration_PS2007_Data_Validation_Snapshot.[Proj Finish Date],
datediff(day, 
Migration_PS2003_Data_Validation_Snapshot.[Proj Finish Date],Migration_PS2007_Data_Validation_Snapshot.[Proj Finish Date]) 
as Difference_in_Days
from 
Migration_PS2003_Data_Validation_Snapshot, Migration_PS2007_Data_Validation_Snapshot --WITH (NOLOCK)
where 
REPLACE(Migration_PS2003_Data_Validation_Snapshot.[Project Name],'.Published','_Published') 
= Migration_PS2007_Data_Validation_Snapshot.[Project Name] and 
Migration_PS2003_Data_Validation_Snapshot.[Proj Finish Date] <> Migration_PS2007_Data_Validation_Snapshot.[Proj Finish Date]
order by Difference_in_Days


--------------------------------------------------
-- Projects with different Project Work
--------------------------------------------------
select distinct
Migration_PS2003_Data_Validation_Snapshot.[Project Name]
AS 'List of Projects with Project Work not Matching After Migration'
, Migration_PS2003_Data_Validation_Snapshot.[Task Work] AS 'Project 2003 Work'
, Migration_PS2007_Data_Validation_Snapshot.[Task Work] AS 'Project 2007 Work',
(Migration_PS2003_Data_Validation_Snapshot.[Task Work] 
- Migration_PS2007_Data_Validation_Snapshot.[Task Work]) as 'Work Difference'
from 
Migration_PS2003_Data_Validation_Snapshot, Migration_PS2007_Data_Validation_Snapshot --WITH (NOLOCK)
where 

REPLACE(Migration_PS2003_Data_Validation_Snapshot.[Project Name],'.Published','_Published') 
= Migration_PS2007_Data_Validation_Snapshot.[Project Name] 
and Migration_PS2003_Data_Validation_Snapshot.[Task Work] <> Migration_PS2007_Data_Validation_Snapshot.[Task Work]
and ((Migration_PS2003_Data_Validation_Snapshot.[Task Work] 
- Migration_PS2007_Data_Validation_Snapshot.[Task Work])
> ((@Work_Ratio * Migration_PS2003_Data_Validation_Snapshot.[Task Work])/100)
or
((Migration_PS2003_Data_Validation_Snapshot.[Task Work] 
- Migration_PS2007_Data_Validation_Snapshot.[Task Work])
 < ((@Work_Ratio*-1)* Migration_PS2003_Data_Validation_Snapshot.[Task Work])/100))
and Migration_PS2003_Data_Validation_Snapshot.[Task UID]  
= Migration_PS2007_Data_Validation_Snapshot.[Task ID]
and Migration_PS2003_Data_Validation_Snapshot.[Task UID]= 0
and Migration_PS2003_Data_Validation_Snapshot.[Task Type] = 
    Migration_PS2007_Data_Validation_Snapshot.[Task Type] 
and Migration_PS2007_Data_Validation_Snapshot.[Task Type] = 1
order by 'Work Difference'


--------------------------------------------------
-- Projects with different Actual Work
--------------------------------------------------
select distinct
Migration_PS2003_Data_Validation_Snapshot.[Project Name]
AS 'List of Projects with Project Actual Work not Matching After Migration'
, Migration_PS2003_Data_Validation_Snapshot.[Task Actual Work] AS 'Project 2003 Actual Work'
, Migration_PS2007_Data_Validation_Snapshot.[Task Actual Work] AS 'Project 2007 Actual Work',
(Migration_PS2003_Data_Validation_Snapshot.[Task Actual Work] 
- Migration_PS2007_Data_Validation_Snapshot.[Task Actual Work]) as 'Actual Work Difference'
from 
Migration_PS2003_Data_Validation_Snapshot, Migration_PS2007_Data_Validation_Snapshot --WITH (NOLOCK)
where 

REPLACE(Migration_PS2003_Data_Validation_Snapshot.[Project Name],'.Published','_Published') 
= Migration_PS2007_Data_Validation_Snapshot.[Project Name] 
and Migration_PS2003_Data_Validation_Snapshot.[Task Actual Work] 
<> Migration_PS2007_Data_Validation_Snapshot.[Task Actual Work]
and ((Migration_PS2003_Data_Validation_Snapshot.[Task Actual Work] 
- Migration_PS2007_Data_Validation_Snapshot.[Task Actual Work])
> ((@Work_Ratio * Migration_PS2003_Data_Validation_Snapshot.[Task Actual Work])/100)
or
((Migration_PS2003_Data_Validation_Snapshot.[Task Actual Work] 
- Migration_PS2007_Data_Validation_Snapshot.[Task Actual Work])
 < ((@Work_Ratio*-1)* Migration_PS2003_Data_Validation_Snapshot.[Task Actual Work])/100))
and Migration_PS2003_Data_Validation_Snapshot.[Task UID]  
= Migration_PS2007_Data_Validation_Snapshot.[Task ID]
and Migration_PS2003_Data_Validation_Snapshot.[Task UID]= 0
and Migration_PS2003_Data_Validation_Snapshot.[Task Type] = 
    Migration_PS2007_Data_Validation_Snapshot.[Task Type] 
and Migration_PS2007_Data_Validation_Snapshot.[Task Type] = 1
order by 'Actual Work Difference'

--------------------------------------------------
-- Projects with different Remaining Work 
--------------------------------------------------
select distinct
Migration_PS2003_Data_Validation_Snapshot.[Project Name]
AS 'List of Projects with Project Remaining Work not Matching After Migration'
, Migration_PS2003_Data_Validation_Snapshot.[Task Rem Work] AS 'Project 2003 Rem Work'
, Migration_PS2007_Data_Validation_Snapshot.[Task Rem Work] AS 'Project 2007 Rem Work',
(Migration_PS2003_Data_Validation_Snapshot.[Task Rem Work] 
- Migration_PS2007_Data_Validation_Snapshot.[Task Rem Work]) as 'Rem Work Difference'
from 
Migration_PS2003_Data_Validation_Snapshot, Migration_PS2007_Data_Validation_Snapshot --WITH (NOLOCK)
where 

REPLACE(Migration_PS2003_Data_Validation_Snapshot.[Project Name],'.Published','_Published') 
= Migration_PS2007_Data_Validation_Snapshot.[Project Name] 
and Migration_PS2003_Data_Validation_Snapshot.[Task Rem Work] 
<> Migration_PS2007_Data_Validation_Snapshot.[Task Rem Work]
and ((Migration_PS2003_Data_Validation_Snapshot.[Task Rem Work] 
- Migration_PS2007_Data_Validation_Snapshot.[Task Rem Work])
> ((@Work_Ratio * Migration_PS2003_Data_Validation_Snapshot.[Task Rem Work])/100)
or
((Migration_PS2003_Data_Validation_Snapshot.[Task Rem Work] 
- Migration_PS2007_Data_Validation_Snapshot.[Task Rem Work])
 < ((@Work_Ratio*-1)* Migration_PS2003_Data_Validation_Snapshot.[Task Rem Work])/100))
and Migration_PS2003_Data_Validation_Snapshot.[Task UID]  
= Migration_PS2007_Data_Validation_Snapshot.[Task ID]
and Migration_PS2003_Data_Validation_Snapshot.[Task UID]= 0
and Migration_PS2003_Data_Validation_Snapshot.[Task Type] = 
    Migration_PS2007_Data_Validation_Snapshot.[Task Type] 
and Migration_PS2007_Data_Validation_Snapshot.[Task Type] = 1
order by 'Rem Work Difference'


--------------------------------------------------
-- Projects with different Project % Complete
--------------------------------------------------
select distinct
Migration_PS2003_Data_Validation_Snapshot.[Project Name]
AS 'List of Projects with Project Task % Complete not Matching After Migration'
, Migration_PS2003_Data_Validation_Snapshot.[Task %Complete] AS 'Project 2003 %Complete'
, Migration_PS2007_Data_Validation_Snapshot.[Task %Complete] AS 'Project 2007 %Complete',
(Migration_PS2003_Data_Validation_Snapshot.[Task %Complete] 
- Migration_PS2007_Data_Validation_Snapshot.[Task %Complete]) as '%Complete Difference'
from 
Migration_PS2003_Data_Validation_Snapshot, Migration_PS2007_Data_Validation_Snapshot --WITH (NOLOCK)
where 

REPLACE(Migration_PS2003_Data_Validation_Snapshot.[Project Name],'.Published','_Published') 
= Migration_PS2007_Data_Validation_Snapshot.[Project Name] 
and Migration_PS2003_Data_Validation_Snapshot.[Task %Complete] 
<> Migration_PS2007_Data_Validation_Snapshot.[Task %Complete]
and Migration_PS2003_Data_Validation_Snapshot.[Task UID]  
= Migration_PS2007_Data_Validation_Snapshot.[Task ID]
and Migration_PS2003_Data_Validation_Snapshot.[Task UID]= 0
and Migration_PS2003_Data_Validation_Snapshot.[Task Type] = 
    Migration_PS2007_Data_Validation_Snapshot.[Task Type] 
and Migration_PS2007_Data_Validation_Snapshot.[Task Type] = 1
order by '%Complete Difference'


--------------------------------------------------
-- Projects with different Project %Work Complete
--------------------------------------------------
select distinct
Migration_PS2003_Data_Validation_Snapshot.[Project Name]
AS 'List of Projects with Project Task %Work Complete not Matching After Migration'
, Migration_PS2003_Data_Validation_Snapshot.[Task %Work Complete] AS 'Project 2003 %Work Complete'
, Migration_PS2007_Data_Validation_Snapshot.[Task % Work Complete] AS 'Project 2007 %Work Complete',
(Migration_PS2003_Data_Validation_Snapshot.[Task %Work Complete] 
- Migration_PS2007_Data_Validation_Snapshot.[Task % Work Complete]) as '%Work Complete Difference'
from 
Migration_PS2003_Data_Validation_Snapshot, Migration_PS2007_Data_Validation_Snapshot --WITH (NOLOCK)
where 

REPLACE(Migration_PS2003_Data_Validation_Snapshot.[Project Name],'.Published','_Published') 
= Migration_PS2007_Data_Validation_Snapshot.[Project Name] 
and Migration_PS2003_Data_Validation_Snapshot.[Task %Work Complete] 
<> Migration_PS2007_Data_Validation_Snapshot.[Task % Work Complete]
and Migration_PS2003_Data_Validation_Snapshot.[Task UID]  
= Migration_PS2007_Data_Validation_Snapshot.[Task ID]
and Migration_PS2003_Data_Validation_Snapshot.[Task UID]= 0
and Migration_PS2003_Data_Validation_Snapshot.[Task Type] = 
    Migration_PS2007_Data_Validation_Snapshot.[Task Type] 
and Migration_PS2007_Data_Validation_Snapshot.[Task Type] = 1
order by '%Work Complete Difference'


----------------------------------------------------
-- Projects with different Task % Phys Work Complete
----------------------------------------------------
select distinct
Migration_PS2003_Data_Validation_Snapshot.[Project Name]
AS 'List of Projects with Project Task % Phys Work Complete not Matching After Migration'
, Migration_PS2003_Data_Validation_Snapshot.[Task % Phys Work Complete] AS 'Project 2003 % Phys Work Complete'
, Migration_PS2007_Data_Validation_Snapshot.[Task % Phys Work Complete] AS 'Project 2007 % Phys Work Complete',
(Migration_PS2003_Data_Validation_Snapshot.[Task % Phys Work Complete]  
- Migration_PS2007_Data_Validation_Snapshot.[Task % Phys Work Complete]) as '% Phys Work Complete Difference'
from 
Migration_PS2003_Data_Validation_Snapshot, Migration_PS2007_Data_Validation_Snapshot --WITH (NOLOCK)
where 

REPLACE(Migration_PS2003_Data_Validation_Snapshot.[Project Name],'.Published','_Published') 
= Migration_PS2007_Data_Validation_Snapshot.[Project Name] 
and Migration_PS2003_Data_Validation_Snapshot.[Task % Phys Work Complete] 
<> Migration_PS2007_Data_Validation_Snapshot.[Task % Phys Work Complete]
and Migration_PS2003_Data_Validation_Snapshot.[Task UID]  
= Migration_PS2007_Data_Validation_Snapshot.[Task ID]
and Migration_PS2003_Data_Validation_Snapshot.[Task UID]= 0
and Migration_PS2003_Data_Validation_Snapshot.[Task Type] = 
    Migration_PS2007_Data_Validation_Snapshot.[Task Type] 
and Migration_PS2007_Data_Validation_Snapshot.[Task Type] = 1
order by '% Phys Work Complete Difference'



--------------------------------------------------
-- Projects with different Duration
--------------------------------------------------
select distinct
Migration_PS2003_Data_Validation_Snapshot.[Project Name]
AS 'List of Projects with Project Duration not Matching After Migration'
, Migration_PS2003_Data_Validation_Snapshot.[Task Duration] AS 'Project 2003 Duration'
, Migration_PS2007_Data_Validation_Snapshot.[Task Duration] AS 'Project 2007 Duration',
(Migration_PS2003_Data_Validation_Snapshot.[Task Duration]  
- Migration_PS2007_Data_Validation_Snapshot.[Task Duration]) as 'Duration Difference'
from 
Migration_PS2003_Data_Validation_Snapshot, Migration_PS2007_Data_Validation_Snapshot --WITH (NOLOCK)
where 

REPLACE(Migration_PS2003_Data_Validation_Snapshot.[Project Name],'.Published','_Published') 
= Migration_PS2007_Data_Validation_Snapshot.[Project Name] 
and Migration_PS2003_Data_Validation_Snapshot.[Task Duration] 
<> Migration_PS2007_Data_Validation_Snapshot.[Task Duration]
and ((Migration_PS2003_Data_Validation_Snapshot.[Task Duration] 
- Migration_PS2007_Data_Validation_Snapshot.[Task Duration])
> ((@Work_Ratio * Migration_PS2003_Data_Validation_Snapshot.[Task Duration])/100)
or
((Migration_PS2003_Data_Validation_Snapshot.[Task Duration] 
- Migration_PS2007_Data_Validation_Snapshot.[Task Duration])
 < ((@Work_Ratio*-1)* Migration_PS2003_Data_Validation_Snapshot.[Task Duration])/100))
and Migration_PS2003_Data_Validation_Snapshot.[Task UID]  
= Migration_PS2007_Data_Validation_Snapshot.[Task ID]
and Migration_PS2003_Data_Validation_Snapshot.[Task UID]= 0
and Migration_PS2003_Data_Validation_Snapshot.[Task Type] = 
    Migration_PS2007_Data_Validation_Snapshot.[Task Type] 
and Migration_PS2007_Data_Validation_Snapshot.[Task Type] = 1
order by 'Duration Difference'

--------------------------------------------------
-- Projects with different Remaining Duration
--------------------------------------------------
select distinct
Migration_PS2003_Data_Validation_Snapshot.[Project Name]
AS 'List of Projects with Remaining Duration not Matching After Migration'
, Migration_PS2003_Data_Validation_Snapshot.[Task Rem Duration]  AS 'Project 2003 Rem Duration'
, Migration_PS2007_Data_Validation_Snapshot.[Task Rem Duration] AS 'Project 2007 Rem Duration',
(Migration_PS2003_Data_Validation_Snapshot.[Task Rem Duration]  
- Migration_PS2007_Data_Validation_Snapshot.[Task Rem Duration]) as 'Rem Duration Difference'
from 
Migration_PS2003_Data_Validation_Snapshot, Migration_PS2007_Data_Validation_Snapshot --WITH (NOLOCK)
where 

REPLACE(Migration_PS2003_Data_Validation_Snapshot.[Project Name],'.Published','_Published') 
= Migration_PS2007_Data_Validation_Snapshot.[Project Name] 
and Migration_PS2003_Data_Validation_Snapshot.[Task Rem Duration] 
<> Migration_PS2007_Data_Validation_Snapshot.[Task Rem Duration]
and ((Migration_PS2003_Data_Validation_Snapshot.[Task Rem Duration] 
- Migration_PS2007_Data_Validation_Snapshot.[Task Rem Duration])
> ((@Work_Ratio * Migration_PS2003_Data_Validation_Snapshot.[Task Rem Duration])/100)
or
((Migration_PS2003_Data_Validation_Snapshot.[Task Rem Duration] 
- Migration_PS2007_Data_Validation_Snapshot.[Task Rem Duration])
 < ((@Work_Ratio*-1)* Migration_PS2003_Data_Validation_Snapshot.[Task Rem Duration])/100))
and Migration_PS2003_Data_Validation_Snapshot.[Task UID]  
= Migration_PS2007_Data_Validation_Snapshot.[Task ID]
and Migration_PS2003_Data_Validation_Snapshot.[Task UID]= 0
and Migration_PS2003_Data_Validation_Snapshot.[Task Type] = 
    Migration_PS2007_Data_Validation_Snapshot.[Task Type] 
and Migration_PS2007_Data_Validation_Snapshot.[Task Type] = 1
order by 'Rem Duration Difference'

--------------------------------------------------
-- Projects with different Actual Duration
--------------------------------------------------
select distinct
Migration_PS2003_Data_Validation_Snapshot.[Project Name]
AS 'List of Projects with Project Actual Duration not Matching After Migration'
, Migration_PS2003_Data_Validation_Snapshot.[Task Actual Duration]  AS 'Project 2003 Actual Duration'
, Migration_PS2007_Data_Validation_Snapshot.[Task Actual Duration] AS 'Project 2007 Actual Duration',
(Migration_PS2003_Data_Validation_Snapshot.[Task Actual Duration]  
- Migration_PS2007_Data_Validation_Snapshot.[Task Actual Duration]) as 'Actual Duration Difference'
from 
Migration_PS2003_Data_Validation_Snapshot, Migration_PS2007_Data_Validation_Snapshot --WITH (NOLOCK)
where 

REPLACE(Migration_PS2003_Data_Validation_Snapshot.[Project Name],'.Published','_Published') 
= Migration_PS2007_Data_Validation_Snapshot.[Project Name] 
and Migration_PS2003_Data_Validation_Snapshot.[Task Actual Duration] 
<> Migration_PS2007_Data_Validation_Snapshot.[Task Actual Duration]
and ((Migration_PS2003_Data_Validation_Snapshot.[Task Actual Duration] 
- Migration_PS2007_Data_Validation_Snapshot.[Task Actual Duration])
> ((@Work_Ratio * Migration_PS2003_Data_Validation_Snapshot.[Task Actual Duration])/100)
or
((Migration_PS2003_Data_Validation_Snapshot.[Task Actual Duration] 
- Migration_PS2007_Data_Validation_Snapshot.[Task Actual Duration])
 < ((@Work_Ratio*-1)* Migration_PS2003_Data_Validation_Snapshot.[Task Actual Duration])/100))
and (Migration_PS2003_Data_Validation_Snapshot.[Task Actual Duration] 
- Migration_PS2007_Data_Validation_Snapshot.[Task Actual Duration]) != -1
and Migration_PS2003_Data_Validation_Snapshot.[Task UID]  
= Migration_PS2007_Data_Validation_Snapshot.[Task ID]
and Migration_PS2003_Data_Validation_Snapshot.[Task UID]= 0
and Migration_PS2003_Data_Validation_Snapshot.[Task Type] = 
    Migration_PS2007_Data_Validation_Snapshot.[Task Type] 
and Migration_PS2007_Data_Validation_Snapshot.[Task Type] = 1
order by 'Actual Duration Difference'


--------------------------------------------------
-- Projects with different Cost
--------------------------------------------------
select distinct
Migration_PS2003_Data_Validation_Snapshot.[Project Name]
AS 'List of Projects with Project Cost not Matching After Migration'
, Migration_PS2003_Data_Validation_Snapshot.[Task Cost]  AS 'Project 2003 Cost'
, Migration_PS2007_Data_Validation_Snapshot.[Task Cost] AS 'Project 2007 Cost',
(Migration_PS2003_Data_Validation_Snapshot.[Task Cost]  
- Migration_PS2007_Data_Validation_Snapshot.[Task Cost]) as 'Cost Difference'
from 
Migration_PS2003_Data_Validation_Snapshot, Migration_PS2007_Data_Validation_Snapshot --WITH (NOLOCK)
where 

REPLACE(Migration_PS2003_Data_Validation_Snapshot.[Project Name],'.Published','_Published') 
= Migration_PS2007_Data_Validation_Snapshot.[Project Name] 
and Migration_PS2003_Data_Validation_Snapshot.[Task Cost] 
<> Migration_PS2007_Data_Validation_Snapshot.[Task Cost]
and ((Migration_PS2003_Data_Validation_Snapshot.[Task Cost] 
- Migration_PS2007_Data_Validation_Snapshot.[Task Cost])
> ((@Work_Ratio * Migration_PS2003_Data_Validation_Snapshot.[Task Cost])/100)
or
((Migration_PS2003_Data_Validation_Snapshot.[Task Cost] 
- Migration_PS2007_Data_Validation_Snapshot.[Task Cost])
 < ((@Work_Ratio*-1)* Migration_PS2003_Data_Validation_Snapshot.[Task Cost])/100))
and Migration_PS2003_Data_Validation_Snapshot.[Task UID]  
= Migration_PS2007_Data_Validation_Snapshot.[Task ID]
and Migration_PS2003_Data_Validation_Snapshot.[Task UID]= 0
and Migration_PS2003_Data_Validation_Snapshot.[Task Type] = 
    Migration_PS2007_Data_Validation_Snapshot.[Task Type] 
and Migration_PS2007_Data_Validation_Snapshot.[Task Type] = 1
order by 'Cost Difference'

--------------------------------------------------
-- Projects with different Fixed Cost
--------------------------------------------------
select distinct
Migration_PS2003_Data_Validation_Snapshot.[Project Name]
AS 'List of Projects with Project Fixed Cost not Matching After Migration'
, Migration_PS2003_Data_Validation_Snapshot.[Task Fixed Cost]  AS 'Project 2003 Fixed Cost'
, Migration_PS2007_Data_Validation_Snapshot.[Task Fixed Cost] AS 'Project 2007 Fixed Cost',
(Migration_PS2003_Data_Validation_Snapshot.[Task Fixed Cost]  
- Migration_PS2007_Data_Validation_Snapshot.[Task Fixed Cost]) as 'Fixed Cost Difference'
from 
Migration_PS2003_Data_Validation_Snapshot, Migration_PS2007_Data_Validation_Snapshot --WITH (NOLOCK)
where 

REPLACE(Migration_PS2003_Data_Validation_Snapshot.[Project Name],'.Published','_Published') 
= Migration_PS2007_Data_Validation_Snapshot.[Project Name] 
and Migration_PS2003_Data_Validation_Snapshot.[Task Fixed Cost] 
<> Migration_PS2007_Data_Validation_Snapshot.[Task Fixed Cost]
and ((Migration_PS2003_Data_Validation_Snapshot.[Task Fixed Cost] 
- Migration_PS2007_Data_Validation_Snapshot.[Task Fixed Cost])
> ((@Work_Ratio * Migration_PS2003_Data_Validation_Snapshot.[Task Fixed Cost])/100)
or
((Migration_PS2003_Data_Validation_Snapshot.[Task Fixed Cost] 
- Migration_PS2007_Data_Validation_Snapshot.[Task Fixed Cost])
 < ((@Work_Ratio*-1)* Migration_PS2003_Data_Validation_Snapshot.[Task Fixed Cost])/100))
and Migration_PS2003_Data_Validation_Snapshot.[Task UID]  
= Migration_PS2007_Data_Validation_Snapshot.[Task ID]
and Migration_PS2003_Data_Validation_Snapshot.[Task UID]= 0
and Migration_PS2003_Data_Validation_Snapshot.[Task Type] = 
    Migration_PS2007_Data_Validation_Snapshot.[Task Type] 
and Migration_PS2007_Data_Validation_Snapshot.[Task Type] = 1
order by 'Fixed Cost Difference'

--------------------------------------------------
-- Projects with different Actual Cost
--------------------------------------------------
select distinct
Migration_PS2003_Data_Validation_Snapshot.[Project Name]
AS 'List of Projects with Project Actual Cost not Matching After Migration'
, Migration_PS2003_Data_Validation_Snapshot.[Task Actual Cost]  AS 'Project 2003 Actual Cost'
, Migration_PS2007_Data_Validation_Snapshot.[Task Actual Cost] AS 'Project 2007 Actual Cost',
(Migration_PS2003_Data_Validation_Snapshot.[Task Actual Cost]  
- Migration_PS2007_Data_Validation_Snapshot.[Task Actual Cost]) as 'Actual Cost Difference'
from 
Migration_PS2003_Data_Validation_Snapshot, Migration_PS2007_Data_Validation_Snapshot --WITH (NOLOCK)
where 

REPLACE(Migration_PS2003_Data_Validation_Snapshot.[Project Name],'.Published','_Published') 
= Migration_PS2007_Data_Validation_Snapshot.[Project Name] 
and Migration_PS2003_Data_Validation_Snapshot.[Task Actual Cost] 
<> Migration_PS2007_Data_Validation_Snapshot.[Task Actual Cost]
and ((Migration_PS2003_Data_Validation_Snapshot.[Task Actual Cost] 
- Migration_PS2007_Data_Validation_Snapshot.[Task Actual Cost])
> ((@Work_Ratio * Migration_PS2003_Data_Validation_Snapshot.[Task Actual Cost])/100)
or
((Migration_PS2003_Data_Validation_Snapshot.[Task Actual Cost] 
- Migration_PS2007_Data_Validation_Snapshot.[Task Actual Cost])
 < ((@Work_Ratio*-1)* Migration_PS2003_Data_Validation_Snapshot.[Task Actual Cost])/100))
and Migration_PS2003_Data_Validation_Snapshot.[Task UID]  
= Migration_PS2007_Data_Validation_Snapshot.[Task ID]
and Migration_PS2003_Data_Validation_Snapshot.[Task UID]= 0
and Migration_PS2003_Data_Validation_Snapshot.[Task Type] = 
    Migration_PS2007_Data_Validation_Snapshot.[Task Type] 
and Migration_PS2007_Data_Validation_Snapshot.[Task Type] = 1
order by 'Actual Cost Difference' 

--------------------------------------------------
-- Projects with different Remaining Cost
--------------------------------------------------
select distinct
Migration_PS2003_Data_Validation_Snapshot.[Project Name]
AS 'List of Projects with Project Remaining Cost not Matching After Migration'
, Migration_PS2003_Data_Validation_Snapshot.[Task Rem Cost]  AS 'Project 2003 Rem Cost'
, Migration_PS2007_Data_Validation_Snapshot.[Task Rem Cost] AS 'Project 2007 Rem Cost',
(Migration_PS2003_Data_Validation_Snapshot.[Task Rem Cost]  
- Migration_PS2007_Data_Validation_Snapshot.[Task Rem Cost]) as 'Rem Cost Difference'
from 
Migration_PS2003_Data_Validation_Snapshot, Migration_PS2007_Data_Validation_Snapshot --WITH (NOLOCK)
where 

REPLACE(Migration_PS2003_Data_Validation_Snapshot.[Project Name],'.Published','_Published') 
= Migration_PS2007_Data_Validation_Snapshot.[Project Name] 
and Migration_PS2003_Data_Validation_Snapshot.[Task Rem Cost] 
<> Migration_PS2007_Data_Validation_Snapshot.[Task Rem Cost]
and ((Migration_PS2003_Data_Validation_Snapshot.[Task Rem Cost] 
- Migration_PS2007_Data_Validation_Snapshot.[Task Rem Cost])
> ((@Work_Ratio * Migration_PS2003_Data_Validation_Snapshot.[Task Rem Cost])/100)
or
((Migration_PS2003_Data_Validation_Snapshot.[Task Rem Cost] 
- Migration_PS2007_Data_Validation_Snapshot.[Task Rem Cost])
 < ((@Work_Ratio*-1)* Migration_PS2003_Data_Validation_Snapshot.[Task Rem Cost])/100))
and Migration_PS2003_Data_Validation_Snapshot.[Task UID]  
= Migration_PS2007_Data_Validation_Snapshot.[Task ID]
and Migration_PS2003_Data_Validation_Snapshot.[Task UID]= 0
and Migration_PS2003_Data_Validation_Snapshot.[Task Type] = 
    Migration_PS2007_Data_Validation_Snapshot.[Task Type] 
and Migration_PS2007_Data_Validation_Snapshot.[Task Type] = 1
order by 'Rem Cost Difference'

--------------------------------------------------
-- Projects with different Overtime Cost
--------------------------------------------------
select distinct
Migration_PS2003_Data_Validation_Snapshot.[Project Name]
AS 'List of Projects with Project Overtime Cost not Matching After Migration'
, Migration_PS2003_Data_Validation_Snapshot.[Task Ovt Cost]  AS 'Project 2003 Ovt Cost'
, Migration_PS2007_Data_Validation_Snapshot.[Task Ovt Cost] AS 'Project 2007 Ovt Cost',
(Migration_PS2003_Data_Validation_Snapshot.[Task Ovt Cost]  
- Migration_PS2007_Data_Validation_Snapshot.[Task Ovt Cost]) as 'Ovt Cost Difference'
from 
Migration_PS2003_Data_Validation_Snapshot, Migration_PS2007_Data_Validation_Snapshot 
where 

REPLACE(Migration_PS2003_Data_Validation_Snapshot.[Project Name],'.Published','_Published') 
= Migration_PS2007_Data_Validation_Snapshot.[Project Name] 
and Migration_PS2003_Data_Validation_Snapshot.[Task Ovt Cost] 
<> Migration_PS2007_Data_Validation_Snapshot.[Task Ovt Cost]
and ((Migration_PS2003_Data_Validation_Snapshot.[Task Ovt Cost] 
- Migration_PS2007_Data_Validation_Snapshot.[Task Ovt Cost])
> ((@Work_Ratio * Migration_PS2003_Data_Validation_Snapshot.[Task Ovt Cost])/100)
or
((Migration_PS2003_Data_Validation_Snapshot.[Task Ovt Cost] 
- Migration_PS2007_Data_Validation_Snapshot.[Task Ovt Cost])
 < ((@Work_Ratio*-1)* Migration_PS2003_Data_Validation_Snapshot.[Task Ovt Cost])/100))
and Migration_PS2003_Data_Validation_Snapshot.[Task UID]  
= Migration_PS2007_Data_Validation_Snapshot.[Task ID]
and Migration_PS2003_Data_Validation_Snapshot.[Task UID]= 0
and Migration_PS2003_Data_Validation_Snapshot.[Task Type] = 
    Migration_PS2007_Data_Validation_Snapshot.[Task Type] 
and Migration_PS2007_Data_Validation_Snapshot.[Task Type] = 1
order by 'Ovt Cost Difference'

----------------------------------------------------------------------
/* Distinct Projects with one or more mismatch 
---------------------------------------------------------------------*/
select Migration_PS2003_Data_Validation_Snapshot.[Project Name]
AS 'List of Projects with at least one mismatch After Migration',
datediff(day, 
Migration_PS2003_Data_Validation_Snapshot.[Proj Start Date],
Migration_PS2007_Data_Validation_Snapshot.[Proj Start Date]) as Start_Date_Diff_in_Days,
datediff(day, 
Migration_PS2003_Data_Validation_Snapshot.[Proj Finish Date],
Migration_PS2007_Data_Validation_Snapshot.[Proj Finish Date]) as Finish_Date_Diff_in_Days,
(Migration_PS2003_Data_Validation_Snapshot.[Task Work] 
- Migration_PS2007_Data_Validation_Snapshot.[Task Work]) as 'Work Diff',
(Migration_PS2003_Data_Validation_Snapshot.[Task Actual Work] 
- Migration_PS2007_Data_Validation_Snapshot.[Task Actual Work]) as 'Actual Work Diff',
(Migration_PS2003_Data_Validation_Snapshot.[Task Rem Work] 
- Migration_PS2007_Data_Validation_Snapshot.[Task Rem Work]) as 'Rem Work Diff',
(Migration_PS2003_Data_Validation_Snapshot.[Task %Complete] 
- Migration_PS2007_Data_Validation_Snapshot.[Task %Complete]) as '%Complete Diff',
(Migration_PS2003_Data_Validation_Snapshot.[Task %Work Complete] 
- Migration_PS2007_Data_Validation_Snapshot.[Task % Work Complete]) as '%Work Complete Diff',
(Migration_PS2003_Data_Validation_Snapshot.[Task % Phys Work Complete]  
- Migration_PS2007_Data_Validation_Snapshot.[Task % Phys Work Complete]) as '% Phys Work Complete Diff',
(Migration_PS2003_Data_Validation_Snapshot.[Task Duration]  
- Migration_PS2007_Data_Validation_Snapshot.[Task Duration]) as 'Duration Diff',
(Migration_PS2003_Data_Validation_Snapshot.[Task Rem Duration]  
- Migration_PS2007_Data_Validation_Snapshot.[Task Rem Duration]) as 'Rem Duration Diff',
(Migration_PS2003_Data_Validation_Snapshot.[Task Actual Duration]  
- Migration_PS2007_Data_Validation_Snapshot.[Task Actual Duration]) as 'Actual Duration Diff',
(Migration_PS2003_Data_Validation_Snapshot.[Task Cost]  
- Migration_PS2007_Data_Validation_Snapshot.[Task Cost]) as 'Cost Diff',
(Migration_PS2003_Data_Validation_Snapshot.[Task Fixed Cost]  
- Migration_PS2007_Data_Validation_Snapshot.[Task Fixed Cost]) as 'Fixed Cost Diff',
(Migration_PS2003_Data_Validation_Snapshot.[Task Actual Cost]  
- Migration_PS2007_Data_Validation_Snapshot.[Task Actual Cost]) as 'Actual Cost Diff',
(Migration_PS2003_Data_Validation_Snapshot.[Task Rem Cost]  
- Migration_PS2007_Data_Validation_Snapshot.[Task Rem Cost]) as 'Rem Cost Diff',
(Migration_PS2003_Data_Validation_Snapshot.[Task Ovt Cost]  
- Migration_PS2007_Data_Validation_Snapshot.[Task Ovt Cost]) as 'Ovt Cost Diff'


from Migration_PS2003_Data_Validation_Snapshot, Migration_PS2007_Data_Validation_Snapshot
where 
replace(Migration_PS2003_Data_Validation_Snapshot.[Project Name], '.Published', '_Published') 
= Migration_PS2007_Data_Validation_Snapshot.[Project Name] and
(
Migration_PS2003_Data_Validation_Snapshot.[Proj Start Date] <> Migration_PS2007_Data_Validation_Snapshot.[Proj Start Date] or
Migration_PS2003_Data_Validation_Snapshot.[Proj Finish Date] <> Migration_PS2007_Data_Validation_Snapshot.[Proj Finish Date] 
or (Migration_PS2003_Data_Validation_Snapshot.[Task Work] <> Migration_PS2007_Data_Validation_Snapshot.[Task Work]
   and ((Migration_PS2003_Data_Validation_Snapshot.[Task Work]- Migration_PS2007_Data_Validation_Snapshot.[Task Work])
> ((@Work_Ratio * Migration_PS2003_Data_Validation_Snapshot.[Task Work])/100)
or ((Migration_PS2003_Data_Validation_Snapshot.[Task Work] - Migration_PS2007_Data_Validation_Snapshot.[Task Work])
 < ((@Work_Ratio*-1)* Migration_PS2003_Data_Validation_Snapshot.[Task Work])/100))) 
or (Migration_PS2003_Data_Validation_Snapshot.[Task Actual Work] <> Migration_PS2007_Data_Validation_Snapshot.[Task Actual Work]
    and ((Migration_PS2003_Data_Validation_Snapshot.[Task Actual Work]- Migration_PS2007_Data_Validation_Snapshot.[Task Actual Work])
> ((@Work_Ratio * Migration_PS2003_Data_Validation_Snapshot.[Task Actual Work])/100)
or ((Migration_PS2003_Data_Validation_Snapshot.[Task Actual Work] - Migration_PS2007_Data_Validation_Snapshot.[Task Actual Work])
 < ((@Work_Ratio*-1)* Migration_PS2003_Data_Validation_Snapshot.[Task Actual Work])/100))) 
or (Migration_PS2003_Data_Validation_Snapshot.[Task Rem Work] <> Migration_PS2007_Data_Validation_Snapshot.[Task Rem Work]
    and ((Migration_PS2003_Data_Validation_Snapshot.[Task Rem Work]- Migration_PS2007_Data_Validation_Snapshot.[Task Rem Work])
> ((@Work_Ratio * Migration_PS2003_Data_Validation_Snapshot.[Task Rem Work])/100)
or ((Migration_PS2003_Data_Validation_Snapshot.[Task Rem Work] - Migration_PS2007_Data_Validation_Snapshot.[Task Rem Work])
 < ((@Work_Ratio*-1)* Migration_PS2003_Data_Validation_Snapshot.[Task Rem Work])/100))) 
or Migration_PS2003_Data_Validation_Snapshot.[Task %Complete] <> Migration_PS2007_Data_Validation_Snapshot.[Task %Complete] 
or Migration_PS2003_Data_Validation_Snapshot.[Task %Work Complete]<> Migration_PS2007_Data_Validation_Snapshot.[Task % Work Complete]
or Migration_PS2003_Data_Validation_Snapshot.[Task % Phys Work Complete] <> Migration_PS2007_Data_Validation_Snapshot.[Task % Phys Work Complete]
or (Migration_PS2003_Data_Validation_Snapshot.[Task Duration] <> Migration_PS2007_Data_Validation_Snapshot.[Task Duration]
    and ((Migration_PS2003_Data_Validation_Snapshot.[Task Duration]- Migration_PS2007_Data_Validation_Snapshot.[Task Duration])
> ((@Work_Ratio * Migration_PS2003_Data_Validation_Snapshot.[Task Duration])/100)
or ((Migration_PS2003_Data_Validation_Snapshot.[Task Duration] - Migration_PS2007_Data_Validation_Snapshot.[Task Duration])
 < ((@Work_Ratio*-1)* Migration_PS2003_Data_Validation_Snapshot.[Task Duration])/100))) 
or (Migration_PS2003_Data_Validation_Snapshot.[Task Rem Duration] <> Migration_PS2007_Data_Validation_Snapshot.[Task Rem Duration]
    and ((Migration_PS2003_Data_Validation_Snapshot.[Task Rem Duration]- Migration_PS2007_Data_Validation_Snapshot.[Task Rem Duration])
> ((@Work_Ratio * Migration_PS2003_Data_Validation_Snapshot.[Task Rem Duration])/100)
or ((Migration_PS2003_Data_Validation_Snapshot.[Task Rem Duration] - Migration_PS2007_Data_Validation_Snapshot.[Task Rem Duration])
 < ((@Work_Ratio*-1)* Migration_PS2003_Data_Validation_Snapshot.[Task Rem Duration])/100))) 
or (Migration_PS2003_Data_Validation_Snapshot.[Task Actual Duration] <> Migration_PS2007_Data_Validation_Snapshot.[Task Actual Duration]
    and ((Migration_PS2003_Data_Validation_Snapshot.[Task Actual Duration]- Migration_PS2007_Data_Validation_Snapshot.[Task Actual Duration])
> ((@Work_Ratio * Migration_PS2003_Data_Validation_Snapshot.[Task Actual Duration])/100)
or ((Migration_PS2003_Data_Validation_Snapshot.[Task Actual Duration] - Migration_PS2007_Data_Validation_Snapshot.[Task Actual Duration])
 < ((@Work_Ratio*-1)* Migration_PS2003_Data_Validation_Snapshot.[Task Actual Duration])/100)))
or (Migration_PS2003_Data_Validation_Snapshot.[Task Cost] <> Migration_PS2007_Data_Validation_Snapshot.[Task Cost]
    and ((Migration_PS2003_Data_Validation_Snapshot.[Task Cost]- Migration_PS2007_Data_Validation_Snapshot.[Task Cost])
> ((@Work_Ratio * Migration_PS2003_Data_Validation_Snapshot.[Task Cost])/100)
or ((Migration_PS2003_Data_Validation_Snapshot.[Task Cost] - Migration_PS2007_Data_Validation_Snapshot.[Task Cost])
 < ((@Work_Ratio*-1)* Migration_PS2003_Data_Validation_Snapshot.[Task Cost])/100))) 
or (Migration_PS2003_Data_Validation_Snapshot.[Task Fixed Cost]<> Migration_PS2007_Data_Validation_Snapshot.[Task Fixed Cost]
    and ((Migration_PS2003_Data_Validation_Snapshot.[Task Fixed Cost]- Migration_PS2007_Data_Validation_Snapshot.[Task Fixed Cost])
> ((@Work_Ratio * Migration_PS2003_Data_Validation_Snapshot.[Task Fixed Cost])/100)
or ((Migration_PS2003_Data_Validation_Snapshot.[Task Fixed Cost] - Migration_PS2007_Data_Validation_Snapshot.[Task Fixed Cost])
 < ((@Work_Ratio*-1)* Migration_PS2003_Data_Validation_Snapshot.[Task Fixed Cost])/100)))
or (Migration_PS2003_Data_Validation_Snapshot.[Task Actual Cost] <> Migration_PS2007_Data_Validation_Snapshot.[Task Actual Cost]
    and ((Migration_PS2003_Data_Validation_Snapshot.[Task Actual Cost]- Migration_PS2007_Data_Validation_Snapshot.[Task Actual Cost])
> ((@Work_Ratio * Migration_PS2003_Data_Validation_Snapshot.[Task Actual Cost])/100)
or ((Migration_PS2003_Data_Validation_Snapshot.[Task Actual Cost] - Migration_PS2007_Data_Validation_Snapshot.[Task Actual Cost])
 < ((@Work_Ratio*-1)* Migration_PS2003_Data_Validation_Snapshot.[Task Actual Cost])/100)))
or (Migration_PS2003_Data_Validation_Snapshot.[Task Rem Cost] <> Migration_PS2007_Data_Validation_Snapshot.[Task Rem Cost]
    and ((Migration_PS2003_Data_Validation_Snapshot.[Task Rem Cost]- Migration_PS2007_Data_Validation_Snapshot.[Task Rem Cost])
> ((@Work_Ratio * Migration_PS2003_Data_Validation_Snapshot.[Task Rem Cost])/100)
or ((Migration_PS2003_Data_Validation_Snapshot.[Task Rem Cost] - Migration_PS2007_Data_Validation_Snapshot.[Task Rem Cost])
 < ((@Work_Ratio*-1)* Migration_PS2003_Data_Validation_Snapshot.[Task Rem Cost])/100)))
or (Migration_PS2003_Data_Validation_Snapshot.[Task Ovt Cost] <> Migration_PS2007_Data_Validation_Snapshot.[Task Ovt Cost]
and ((Migration_PS2003_Data_Validation_Snapshot.[Task Ovt Cost]- Migration_PS2007_Data_Validation_Snapshot.[Task Ovt Cost])
> ((@Work_Ratio * Migration_PS2003_Data_Validation_Snapshot.[Task Ovt Cost])/100)
or ((Migration_PS2003_Data_Validation_Snapshot.[Task Ovt Cost] - Migration_PS2007_Data_Validation_Snapshot.[Task Ovt Cost])
 < ((@Work_Ratio*-1)* Migration_PS2003_Data_Validation_Snapshot.[Task Ovt Cost])/100)))
)
and Migration_PS2003_Data_Validation_Snapshot.[Task UID]  
= Migration_PS2007_Data_Validation_Snapshot.[Task ID]
and Migration_PS2003_Data_Validation_Snapshot.[Task UID]= 0
and Migration_PS2003_Data_Validation_Snapshot.[Task Type] = 
    Migration_PS2007_Data_Validation_Snapshot.[Task Type] 
and Migration_PS2007_Data_Validation_Snapshot.[Task Type] = 1

----------------------------------------------------------------------
/* Distinct Projects and Tasks with one or more mismatch 
---------------------------------------------------------------------*/
select distinct Migration_PS2003_Data_Validation_Snapshot.[Project Name]
AS 'List of Projects and tasks with at least one mismatch after Migration',
Migration_PS2007_Data_Validation_Snapshot.[Task Name],
Migration_PS2007_Data_Validation_Snapshot.[Task ID],
Migration_PS2007_Data_Validation_Snapshot.[Task Type],
datediff(day, 
Migration_PS2003_Data_Validation_Snapshot.[Proj Start Date],
Migration_PS2007_Data_Validation_Snapshot.[Proj Start Date]) as Start_Date_Diff_in_Days,
datediff(day, 
Migration_PS2003_Data_Validation_Snapshot.[Proj Finish Date],
Migration_PS2007_Data_Validation_Snapshot.[Proj Finish Date]) as Finish_Date_Diff_in_Days,
(Migration_PS2003_Data_Validation_Snapshot.[Task Work] 
- Migration_PS2007_Data_Validation_Snapshot.[Task Work]) as 'Work Diff',
(Migration_PS2003_Data_Validation_Snapshot.[Task Actual Work] 
- Migration_PS2007_Data_Validation_Snapshot.[Task Actual Work]) as 'Actual Work Diff',
(Migration_PS2003_Data_Validation_Snapshot.[Task Rem Work] 
- Migration_PS2007_Data_Validation_Snapshot.[Task Rem Work]) as 'Rem Work Diff',
(Migration_PS2003_Data_Validation_Snapshot.[Task %Complete] 
- Migration_PS2007_Data_Validation_Snapshot.[Task %Complete]) as '%Complete Diff',
(Migration_PS2003_Data_Validation_Snapshot.[Task %Work Complete] 
- Migration_PS2007_Data_Validation_Snapshot.[Task % Work Complete]) as '%Work Complete Diff',
(Migration_PS2003_Data_Validation_Snapshot.[Task % Phys Work Complete]  
- Migration_PS2007_Data_Validation_Snapshot.[Task % Phys Work Complete]) as '% Phys Work Complete Diff',
(Migration_PS2003_Data_Validation_Snapshot.[Task Duration]  
- Migration_PS2007_Data_Validation_Snapshot.[Task Duration]) as 'Duration Diff',
(Migration_PS2003_Data_Validation_Snapshot.[Task Rem Duration]  
- Migration_PS2007_Data_Validation_Snapshot.[Task Rem Duration]) as 'Rem Duration Diff',
(Migration_PS2003_Data_Validation_Snapshot.[Task Actual Duration]  
- Migration_PS2007_Data_Validation_Snapshot.[Task Actual Duration]) as 'Actual Duration Diff',
(Migration_PS2003_Data_Validation_Snapshot.[Task Cost]  
- Migration_PS2007_Data_Validation_Snapshot.[Task Cost]) as 'Cost Diff',
(Migration_PS2003_Data_Validation_Snapshot.[Task Fixed Cost]  
- Migration_PS2007_Data_Validation_Snapshot.[Task Fixed Cost]) as 'Fixed Cost Diff',
(Migration_PS2003_Data_Validation_Snapshot.[Task Actual Cost]  
- Migration_PS2007_Data_Validation_Snapshot.[Task Actual Cost]) as 'Actual Cost Diff',
(Migration_PS2003_Data_Validation_Snapshot.[Task Rem Cost]  
- Migration_PS2007_Data_Validation_Snapshot.[Task Rem Cost]) as 'Rem Cost Diff',
(Migration_PS2003_Data_Validation_Snapshot.[Task Ovt Cost]  
- Migration_PS2007_Data_Validation_Snapshot.[Task Ovt Cost]) as 'Ovt Cost Diff'


from Migration_PS2003_Data_Validation_Snapshot, Migration_PS2007_Data_Validation_Snapshot
where 
replace(Migration_PS2003_Data_Validation_Snapshot.[Project Name], '.Published', '_Published') 
= Migration_PS2007_Data_Validation_Snapshot.[Project Name] and
(
Migration_PS2003_Data_Validation_Snapshot.[Proj Start Date] <> Migration_PS2007_Data_Validation_Snapshot.[Proj Start Date] or
Migration_PS2003_Data_Validation_Snapshot.[Proj Finish Date] <> Migration_PS2007_Data_Validation_Snapshot.[Proj Finish Date] 
or (Migration_PS2003_Data_Validation_Snapshot.[Task Work] <> Migration_PS2007_Data_Validation_Snapshot.[Task Work]
   and ((Migration_PS2003_Data_Validation_Snapshot.[Task Work]- Migration_PS2007_Data_Validation_Snapshot.[Task Work])
> ((@Work_Ratio * Migration_PS2003_Data_Validation_Snapshot.[Task Work])/100)
or ((Migration_PS2003_Data_Validation_Snapshot.[Task Work] - Migration_PS2007_Data_Validation_Snapshot.[Task Work])
 < ((@Work_Ratio*-1)* Migration_PS2003_Data_Validation_Snapshot.[Task Work])/100))) 
or (Migration_PS2003_Data_Validation_Snapshot.[Task Actual Work] <> Migration_PS2007_Data_Validation_Snapshot.[Task Actual Work]
    and ((Migration_PS2003_Data_Validation_Snapshot.[Task Actual Work]- Migration_PS2007_Data_Validation_Snapshot.[Task Actual Work])
> ((@Work_Ratio * Migration_PS2003_Data_Validation_Snapshot.[Task Actual Work])/100)
or ((Migration_PS2003_Data_Validation_Snapshot.[Task Actual Work] - Migration_PS2007_Data_Validation_Snapshot.[Task Actual Work])
 < ((@Work_Ratio*-1)* Migration_PS2003_Data_Validation_Snapshot.[Task Actual Work])/100))) 
or (Migration_PS2003_Data_Validation_Snapshot.[Task Rem Work] <> Migration_PS2007_Data_Validation_Snapshot.[Task Rem Work]
    and ((Migration_PS2003_Data_Validation_Snapshot.[Task Rem Work]- Migration_PS2007_Data_Validation_Snapshot.[Task Rem Work])
> ((@Work_Ratio * Migration_PS2003_Data_Validation_Snapshot.[Task Rem Work])/100)
or ((Migration_PS2003_Data_Validation_Snapshot.[Task Rem Work] - Migration_PS2007_Data_Validation_Snapshot.[Task Rem Work])
 < ((@Work_Ratio*-1)* Migration_PS2003_Data_Validation_Snapshot.[Task Rem Work])/100))) 
or Migration_PS2003_Data_Validation_Snapshot.[Task %Complete] <> Migration_PS2007_Data_Validation_Snapshot.[Task %Complete] 
or Migration_PS2003_Data_Validation_Snapshot.[Task %Work Complete]<> Migration_PS2007_Data_Validation_Snapshot.[Task % Work Complete]
or Migration_PS2003_Data_Validation_Snapshot.[Task % Phys Work Complete] <> Migration_PS2007_Data_Validation_Snapshot.[Task % Phys Work Complete]
or (Migration_PS2003_Data_Validation_Snapshot.[Task Duration] <> Migration_PS2007_Data_Validation_Snapshot.[Task Duration]
    and ((Migration_PS2003_Data_Validation_Snapshot.[Task Duration]- Migration_PS2007_Data_Validation_Snapshot.[Task Duration])
> ((@Work_Ratio * Migration_PS2003_Data_Validation_Snapshot.[Task Duration])/100)
or ((Migration_PS2003_Data_Validation_Snapshot.[Task Duration] - Migration_PS2007_Data_Validation_Snapshot.[Task Duration])
 < ((@Work_Ratio*-1)* Migration_PS2003_Data_Validation_Snapshot.[Task Duration])/100))) 
or (Migration_PS2003_Data_Validation_Snapshot.[Task Rem Duration] <> Migration_PS2007_Data_Validation_Snapshot.[Task Rem Duration]
    and ((Migration_PS2003_Data_Validation_Snapshot.[Task Rem Duration]- Migration_PS2007_Data_Validation_Snapshot.[Task Rem Duration])
> ((@Work_Ratio * Migration_PS2003_Data_Validation_Snapshot.[Task Rem Duration])/100)
or ((Migration_PS2003_Data_Validation_Snapshot.[Task Rem Duration] - Migration_PS2007_Data_Validation_Snapshot.[Task Rem Duration])
 < ((@Work_Ratio*-1)* Migration_PS2003_Data_Validation_Snapshot.[Task Rem Duration])/100))) 
or (Migration_PS2003_Data_Validation_Snapshot.[Task Actual Duration] <> Migration_PS2007_Data_Validation_Snapshot.[Task Actual Duration]
    and ((Migration_PS2003_Data_Validation_Snapshot.[Task Actual Duration]- Migration_PS2007_Data_Validation_Snapshot.[Task Actual Duration])
> ((@Work_Ratio * Migration_PS2003_Data_Validation_Snapshot.[Task Actual Duration])/100)
or ((Migration_PS2003_Data_Validation_Snapshot.[Task Actual Duration] - Migration_PS2007_Data_Validation_Snapshot.[Task Actual Duration])
 < ((@Work_Ratio*-1)* Migration_PS2003_Data_Validation_Snapshot.[Task Actual Duration])/100)))
or (Migration_PS2003_Data_Validation_Snapshot.[Task Cost] <> Migration_PS2007_Data_Validation_Snapshot.[Task Cost]
    and ((Migration_PS2003_Data_Validation_Snapshot.[Task Cost]- Migration_PS2007_Data_Validation_Snapshot.[Task Cost])
> ((@Work_Ratio * Migration_PS2003_Data_Validation_Snapshot.[Task Cost])/100)
or ((Migration_PS2003_Data_Validation_Snapshot.[Task Cost] - Migration_PS2007_Data_Validation_Snapshot.[Task Cost])
 < ((@Work_Ratio*-1)* Migration_PS2003_Data_Validation_Snapshot.[Task Cost])/100))) 
or (Migration_PS2003_Data_Validation_Snapshot.[Task Fixed Cost]<> Migration_PS2007_Data_Validation_Snapshot.[Task Fixed Cost]
    and ((Migration_PS2003_Data_Validation_Snapshot.[Task Fixed Cost]- Migration_PS2007_Data_Validation_Snapshot.[Task Fixed Cost])
> ((@Work_Ratio * Migration_PS2003_Data_Validation_Snapshot.[Task Fixed Cost])/100)
or ((Migration_PS2003_Data_Validation_Snapshot.[Task Fixed Cost] - Migration_PS2007_Data_Validation_Snapshot.[Task Fixed Cost])
 < ((@Work_Ratio*-1)* Migration_PS2003_Data_Validation_Snapshot.[Task Fixed Cost])/100)))
or (Migration_PS2003_Data_Validation_Snapshot.[Task Actual Cost] <> Migration_PS2007_Data_Validation_Snapshot.[Task Actual Cost]
    and ((Migration_PS2003_Data_Validation_Snapshot.[Task Actual Cost]- Migration_PS2007_Data_Validation_Snapshot.[Task Actual Cost])
> ((@Work_Ratio * Migration_PS2003_Data_Validation_Snapshot.[Task Actual Cost])/100)
or ((Migration_PS2003_Data_Validation_Snapshot.[Task Actual Cost] - Migration_PS2007_Data_Validation_Snapshot.[Task Actual Cost])
 < ((@Work_Ratio*-1)* Migration_PS2003_Data_Validation_Snapshot.[Task Actual Cost])/100)))
or (Migration_PS2003_Data_Validation_Snapshot.[Task Rem Cost] <> Migration_PS2007_Data_Validation_Snapshot.[Task Rem Cost]
    and ((Migration_PS2003_Data_Validation_Snapshot.[Task Rem Cost]- Migration_PS2007_Data_Validation_Snapshot.[Task Rem Cost])
> ((@Work_Ratio * Migration_PS2003_Data_Validation_Snapshot.[Task Rem Cost])/100)
or ((Migration_PS2003_Data_Validation_Snapshot.[Task Rem Cost] - Migration_PS2007_Data_Validation_Snapshot.[Task Rem Cost])
 < ((@Work_Ratio*-1)* Migration_PS2003_Data_Validation_Snapshot.[Task Rem Cost])/100)))
or (Migration_PS2003_Data_Validation_Snapshot.[Task Ovt Cost] <> Migration_PS2007_Data_Validation_Snapshot.[Task Ovt Cost]
and ((Migration_PS2003_Data_Validation_Snapshot.[Task Ovt Cost]- Migration_PS2007_Data_Validation_Snapshot.[Task Ovt Cost])
> ((@Work_Ratio * Migration_PS2003_Data_Validation_Snapshot.[Task Ovt Cost])/100)
or ((Migration_PS2003_Data_Validation_Snapshot.[Task Ovt Cost] - Migration_PS2007_Data_Validation_Snapshot.[Task Ovt Cost])
 < ((@Work_Ratio*-1)* Migration_PS2003_Data_Validation_Snapshot.[Task Ovt Cost])/100)))
)
and Migration_PS2003_Data_Validation_Snapshot.[Task UID]  
= Migration_PS2007_Data_Validation_Snapshot.[Task ID]
and Migration_PS2003_Data_Validation_Snapshot.[Task Type] = 
    Migration_PS2007_Data_Validation_Snapshot.[Task Type] 
order by Migration_PS2003_Data_Validation_Snapshot.[Project Name],
Migration_PS2007_Data_Validation_Snapshot.[Task ID]