Datanory Production » Datanory - GOLDCREST

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

select * from member where member_cd = 'BRY'

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

select * from #tempcustomeruser a
inner join customer b on b.customer_code = a.customer_code and b.org_id = 'GOLDCREST'
inner join member c on c.member_cd = a.user_code and c.org_id = 'GOLDCREST'
where not exists(select 1 from customer_to_user_relation r where r.org_id = 'GOLDCREST' 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_20240529
--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', 'GOLDCREST', 'GOLDCREST', '2023-01-01 00:00:00.000', '2099-12-31 00:00:00.000'
--from #tempcustomeruser a
--inner join customer b on b.customer_code = a.customer_code and b.org_id = 'GOLDCREST'
--inner join member c on c.member_cd = a.user_code and c.org_id = 'GOLDCREST'
--where not exists(select 1 from customer_to_user_relation r where r.org_id = 'GOLDCREST' 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 = 'GOLDCREST'
group by b.customer_code, a.customer_id, user_id, start_date, end_date
having count(1)>1
order by b.customer_code

select * from customer_to_user_relation where user_id = '70fe0d960d204e86b862f88a8038fd10'