Datanory Production » Datanory - OROMEO

--create table #TempTableCustUser (
-- customer_code varchar(50),
-- user_code varchar(50)
--)

select * from #TempTableCustUser
select * from #TempTableCustUser where customer_code not in (select customer_code from customer where org_id = 'oromeo')
select * from #TempTableCustUser where user_code not in (select member_cd from member where org_id = 'oromeo')

select * from #TempTableCustUser a
inner join customer b on b.customer_code = a.customer_code and b.org_id = 'oromeo'
inner join member c on c.member_cd = a.user_code and c.org_id = 'oromeo'
where not exists(select 1 from customer_to_user_relation r where r.org_id = 'oromeo' and b.customer_id = r.customer_id and c.member_id = r.user_id)

--select * into DMS_Ecosystem_Backup_Table.dbo._customer_to_user_relation_20240512
--from DMS_EcoSystem.dbo.customer_to_user_relation

--insert into customer_to_user_relation
--(customer_to_user_relation_id, customer_id, user_id, created_date, created_by, updated_date, updated_by, org_id, tenant_id, start_date, end_date)
--select newid(), b.customer_id, c.member_id, getdate(), 'Support', getdate(), 'Support', 'OROMEO', 'OROMEO', '2024-01-01 00:00:00.000', '2099-12-31 00:00:00.000'
--from #TempTableCustUser a
--inner join customer b on b.customer_code = a.customer_code and b.org_id = 'oromeo'
--inner join member c on c.member_cd = a.user_code and c.org_id = 'oromeo'
--where not exists(select 1 from customer_to_user_relation r where r.org_id = 'oromeo' and b.customer_id = r.customer_id and c.member_id = r.user_id)

select b.customer_code, a.customer_id, user_id, start_date, end_date, count(1) from customer_to_user_relation a
inner join customer b on b.customer_id = a.customer_id
where a.org_id = 'oromeo'
group by b.customer_code, a.customer_id, user_id, start_date, end_date
having count(1)>1
order by b.customer_code