View: dbo.Consolidation_Deleted_Subsidiary_Client_View
View definition
-- drop view dbo.Consolidation_Deleted_Subsidiary_Client_View
create view dbo.Consolidation_Deleted_Subsidiary_Client_View
as
select
prior_list.consolidating__client_KEY
, prior_list.subsidiary__client_KEY
, prior_list.sequence_number
from
(
select * from dbo.Client_N_Subsidiary_Client where subsidiary_client_collection_type_KEY = 2 -- Currently Included in Master
) as prior_list
left join
(
select * from dbo.Client_N_Subsidiary_Client where subsidiary_client_collection_type_KEY = 1 -- Selected to Consolidate
) as new_list
on prior_list.consolidating__client_KEY = new_list.consolidating__client_KEY
and prior_list.subsidiary__client_KEY = new_list.subsidiary__client_KEY
and prior_list.sequence_number = new_list.sequence_number
where
new_list.client_n_subsidiary_client_KEY is null