I am trying to write a comparison script against my play and live database to figure out if I have certain data attributes missing from either live or test environment. IDs generally don't match. Data Attributes are from several different tables.
(Select T1.ID,T1.Description, T1.Type, T1.Environment
from
(select cast(current_timestamp as varchar(50)) as id, 'Core Data As Of' as description, '_Extract Date' as type, 'PLAY' as Environment
union
select cast(id as varchar(50))as id, first_name +' '+ last_name as description, 'Salereps' as type, 'PLAY' as Environment from zACdb3.dbo.contacts where salesrep = 'Y' and delete_flag = 'N'
union
select cast(id as varchar(50))as id, first_name +' '+ last_name as description, 'Buyers' as type, 'PLAY' as Environment from zACdb3.dbo.contacts where buyer ='Y' and delete_flag = 'N'
union
select cast(terms_id as varchar(50)) as id, terms_desc as description, 'Terms' as type, 'PLAY' as Environment from zACdb3.dbo.terms where delete_flag = 'N'
) T1 --ORDER BY T1.Type, T1.ID, T1.Description, T1.Environment ASC
union all
Select T2.ID, T2.Description, T2.Type, T2.Environment
From
(select cast(current_timestamp as varchar(50)) as id, 'Core Data As Of' as description, '_Extract Date' as type, 'LIVE' as Environment
union
select cast(id as varchar(50))as id, first_name +' '+ last_name as description, 'Salereps' as type, 'LIVE' as Environment from zACdb1.dbo.contacts where salesrep = 'Y' and delete_flag = 'N'
union
select cast(id as varchar(50))as id, first_name +' '+ last_name as description, 'Buyers' as type, 'LIVE' as Environment from zACdb1.dbo.contacts where buyer ='Y' and delete_flag = 'N'
union
select cast(terms_id as varchar(50)) as id, terms_desc as description, 'Terms' as type, 'LIVE' as Environment from zACdb1.dbo.terms where delete_flag = 'N'
) T2 --ORDER BY T2.Type, T2.ID, T2.Description, T2.Environment ASC
--Order by t1.Type, T1.Description, T1.ID, T1.Environment
)
Except
(
/*) T3
Union ALL
Select T6.ID,T6.Description, T6.Type, T6.Environment
from(*/
Select T1.ID,T1.Description, T1.Type, T1.Environment
from
(select cast(current_timestamp as varchar(50)) as id, 'Core Data As Of' as description, '_Extract Date' as type, 'PLAY' as Environment
union
select cast(id as varchar(50))as id, first_name +' '+ last_name as description, 'Salereps' as type, 'PLAY' as Environment from zACdb3.dbo.contacts where salesrep = 'Y' and delete_flag = 'N'
union
select cast(id as varchar(50))as id, first_name +' '+ last_name as description, 'Buyers' as type, 'PLAY' as Environment from zACdb3.dbo.contacts where buyer ='Y' and delete_flag = 'N'
union
select cast(terms_id as varchar(50)) as id, terms_desc as description, 'Terms' as type, 'PLAY' as Environment from zACdb3.dbo.terms where delete_flag = 'N'
) T1
INTERSECT
Select T2.ID, T2.Description, T2.Type, T2.Environment
From
(select cast(current_timestamp as varchar(50)) as id, 'Core Data As Of' as description, '_Extract Date' as type, 'LIVE' as Environment
union
select cast(id as varchar(50))as id, first_name +' '+ last_name as description, 'Salereps' as type, 'LIVE' as Environment from zACdb1.dbo.contacts where salesrep = 'Y' and delete_flag = 'N'
union
select cast(id as varchar(50))as id, first_name +' '+ last_name as description, 'Buyers' as type, 'LIVE' as Environment from zACdb1.dbo.contacts where buyer ='Y' and delete_flag = 'N'
union
select cast(terms_id as varchar(50)) as id, terms_desc as description, 'Terms' as type, 'LIVE' as Environment from zACdb1.dbo.terms where delete_flag = 'N'
) T2
) Order by t1.type, t1.description, t1.id, t1.Environment
Ideally would like to see something like this. Know Im not there but not sure how to get there when IDS don't match but the description and type should match
| ID | Type |Play | Live
| 1001 | SalesRep |Joe Buck| Missing
| 1003 | Buyer |Missing | Ty Wood