create table #database
(
NRIC varchar(50)
,OTHER_ID varchar(50)
,CLIENT_ID varchar(50)
)
insert into #database values ('123456015678','a555555','1234')
insert into #database values ('O32242','997654-06-9382','5678')
insert into #database values ('990424-09-8282','KSD2323','986')
insert into #database values ('987654-02-5987','K02333','1323')
insert into #database values ('NA','930233043333','404345')
insert into #database values ('90203043214','KJ82932E','945')
insert into #database values ('760423068777','NA','5454')
insert into #database values ('631123048754','NA','34934')
insert into #database values ('NA','650203099282','3535')
insert into #database values ('981312-03-9242','A923943','45545')
insert into #database values ('NA','650212-09-5444','9897')
insert into #database values ('NA','NA','5433')
insert into #database values ('K1203343','NA','3545')
insert into #database values ('907865','BJ823932','45436')
insert into #database values ('991216-04-0987','EF65342','7897')
insert into #database values ('111213096574','19132','9685')
insert into #database values ('991231-05-43333','A290232','34662')
insert into #database values ('990504-02-4343','NA','643264')
insert into #database values ('990504-02-4343','B11111','643264')
insert into #database values ('995432-06-1234','H88322','32564')
insert into #database values ('NA','NA','64564')
insert into #database values ('NA','WF2323','36346')
create table #fraudlisting
(
NRIC varchar(50)
,OTHER_ID varchar(50)
)
insert into #fraudlisting values ('123456-01-5678','a555555')
insert into #fraudlisting values ('987654-02-5987','NA')
insert into #fraudlisting values ('930233043333','NA')
insert into #fraudlisting values ('090203-04-3214','KJ82932E')
insert into #fraudlisting values ('NA','650203-09-9282')
insert into #fraudlisting values ('NA','A923943')
insert into #fraudlisting values ('907865','BJ823932')
insert into #fraudlisting values ('EF65342','NA')
insert into #fraudlisting values ('111213-09-6574','NA')
insert into #fraudlisting values ('990504024343','NA')
insert into #fraudlisting values ('995432061234','NA')
create table #results
(
NRIC varchar(50)
,OTHER_ID varchar(50)
,CLIENT_ID varchar(50)
)
insert into #results values ('123456-01-5678','a555555','1234')
insert into #results values ('987654-02-5987','K02333','1323')
insert into #results values ('930233043333','NA','404345')
insert into #results values ('090203-04-3214','KJ82932E','945')
insert into #results values ('650203-09-9282','NA','3535')
insert into #results values ('981312-03-9242','A923943','45545')
insert into #results values ('907865','BJ823932','45436')
insert into #results values ('991216-04-0987','EF65342','7897')
insert into #results values ('111213-09-6574','19132','9685')
insert into #results values ('990504024343','B11111','643264')
insert into #results values ('995432061234','H88322','32564')
SELECT d.*
FROM (
SELECT C.CLIENT_ID, [NRIC] = isnull(n.nric, ''), [OTHER_ID] = isnull(o.other_id, '')
FROM (
SELECT DISTINCT client_id
FROM #database
) c
LEFT JOIN (
SELECT DISTINCT CLIENT_ID, [NRIC] = CASE WHEN nric LIKE '%-%'
OR len(nric) > 10
OR nric = '907865' THEN nric WHEN other_id LIKE '%-%'
OR len(other_id) > 10 THEN other_id END
FROM #database
) n ON c.client_id = n.client_id
LEFT JOIN (
SELECT CLIENT_ID, [OTHER_ID] = nullif((
replace((
CASE WHEN len(other_id) < 10
AND other_id <> 'na' THEN other_id WHEN len(nric) < 10
AND other_id <> 'na'
AND nric <> '907865' THEN nric END
), 'NA', '')
), '')
FROM #database
WHERE nullif((
replace((
CASE WHEN len(other_id) < 10
AND other_id <> 'na' THEN other_id WHEN len(nric) < 10
AND other_id <> 'na'
AND nric <> '907865' THEN nric END
), 'NA', '')
), '') IS NOT NULL
) o ON c.client_id = o.client_id
) d
LEFT JOIN (
SELECT DISTINCT [nric] = CASE WHEN nric = '907865'
OR (
len(nric) > 10
AND nric <> 'na'
) THEN nric WHEN other_id = '907865'
OR (
len(other_id) > 10
AND other_id <> 'na'
) THEN other_id END
FROM #fraudlisting
) n ON replace(d.nric, '-', '') = replace(n.nric, '-', '')
LEFT JOIN (
SELECT DISTINCT [other_id] = CASE WHEN nric <> '907865'
AND len(nric) < 10
AND nric <> 'na' THEN nric WHEN other_id <> '907865'
AND len(other_id) < 10
AND other_id <> 'na' THEN other_id END
FROM #fraudlisting
) o ON d.other_id = o.other_id
WHERE n.nric IS NOT NULL
OR o.other_id IS NOT NULL
ORDER BY d.client_id
Telegram: DB developers channel