Reference Data - Trust Data Consolidation

For Discussion

#		Comment	Owner
1	Should TrustName and Number be unique ?	Yes.	Alistair Pringle (Unlicensed)

Break down of TrustNumber and TrustName NULL or NOT NULL values;

select CASE WHEN TrustName IS NULL THEN 'NULL' ELSE 'NOT NULL' END TrustName, 
	   CASE WHEN trustNumber IS NULL THEN 'NULL' ELSE 'NOT NULL' END trustNumber, status, count(*) 
from [HEELIVE].[vwTrust]
group by CASE WHEN TrustName IS NULL THEN 'NULL' ELSE 'NOT NULL' END, CASE WHEN trustNumber IS NULL THEN 'NULL' ELSE 'NOT NULL' END, status

TrustName	trustNumber	status	RowCount
NOT NULL	NOT NULL	Current	824
NULL	NULL	Current	3
NULL	NOT NULL	Current	19
NOT NULL	NULL	Current	15
NOT NULL	NULL	Inactive	107
NULL	NULL	Inactive	6
NULL	NOT NULL	Inactive	22
NOT NULL	NOT NULL	Inactive	1273
		Total	2269

There are 19 Trust rows that are duplicated

select TrustName, trustNumber, status, count(*) 
from [HEELIVE].[vwTrust]
group by TrustName, trustNumber, status
having count(*) > 1
order by 3, 4 desc

TrustName	trustNumber	status	RowCount
NULL	NULL	Current	3
NULL	BATH	Current	2
NULL	BRIS	Current	2
NULL	GLOS	Current	2
NULL	QD6	Current	2
NULL	QDX	Current	2
NULL	SOM	Current	2
NULL	SWI	Current	2
Undefined	UND	Current	2
Wiltshire Council	817	Current	2
Worcestershire PCT	NULL	Current	2
NULL	NULL	Inactive	6
NULL	ZZZZZ	Inactive	3
NULL	RECRU	Inactive	3
NULL	RT5EJ	Inactive	2
NULL	MAT	Inactive	2
John Coupland Hospital	NULL	Inactive	2
ZZZ John Coupland Hospital	NULL	Inactive	2
NULL	??RWM	Inactive	2

Trust Name and Number duplicates

select lo.LocalOfficeName, t.*
from [HEELIVE].[vwTrust] as t
left join [HEELIVE].[vwLocalOffice] as lo ON (lo.itemtype = 'Trust' and lo.UniqueID = t.TrustID)
where status = 'Current'
and ISNULL(TrustName, -1) + ISNULL(trustNumber,-2) in (select ISNULL(TrustName, -1) + ISNULL(trustNumber,-2)
													   from [HEELIVE].[vwTrust]
													   where status = 'Current'
													   group by TrustName, trustNumber
													   having count(*) > 1)
order by trustName, TrustNumber

LocalOfficeName	RecordOrigin	STATUS	TrustName	TrustKnownAs	TrustNumber	DeaneryLETB	TrustID
HESW	HEELIVE	Current	NULL	Bath VTS	BATH	Severn Local Office - HESW	36090056
NULL	HEELIVE	Current	NULL	Bath VTS	BATH	Severn Local Office - HESW	32948533
HESW	HEELIVE	Current	NULL	Bristol VTS	BRIS	Severn Local Office - HESW	36090050
NULL	HEELIVE	Current	NULL	Bristol VTS	BRIS	Severn Local Office - HESW	32948605
HESW	HEELIVE	Current	NULL	Gloucestershire VTS	GLOS	Severn Local Office - HESW	36090059
NULL	HEELIVE	Current	NULL	Gloucestershire VTS	GLOS	Severn Local Office - HESW	32948528
HENE	HEELIVE	Current	NULL	Health Education South West	NULL	NULL	29034540
HESW	HEELIVE	Current	NULL	Deferred Trainee	NULL	Severn Local Office - HESW	36090055
NULL	HEELIVE	Current	NULL	Deferred Trainee	NULL	Severn Local Office - HESW	33643819
HESW	HEELIVE	Current	NULL	S & W Devon Health Authority	QD6	Peninsula Local Office - HESW	36090058
NULL	HEELIVE	Current	NULL	S & W Devon Health Authority	QD6	Peninsula Local Office - HESW	33643809
HESW	HEELIVE	Current	NULL	North and East Devon Health Authority	QDX	Peninsula Local Office - HESW	36090054
NULL	HEELIVE	Current	NULL	North and East Devon Health Authority	QDX	Peninsula Local Office - HESW	32948568
HESW	HEELIVE	Current	NULL	Somerset VTS	SOM	Severn Local Office - HESW	36090057
NULL	HEELIVE	Current	NULL	Somerset VTS	SOM	Severn Local Office - HESW	32948661
HESW	HEELIVE	Current	NULL	Swindon VTS	SWI	Severn Local Office - HESW	36090052
NULL	HEELIVE	Current	NULL	Swindon VTS	SWI	Severn Local Office - HESW	32948634
HEEM	HEELIVE	Current	Undefined	Undefined	UND	Health Education England West Midlands	275385639
HEEOE	HEELIVE	Current	Undefined	Undefined	UND	Health Education England West Midlands	275385639
HELASE	HEELIVE	Current	Undefined	Undefined	UND	Health Education England West Midlands	275385639
HESW	HEELIVE	Current	Undefined	Undefined	UND	Health Education England West Midlands	275385639
HEWM	HEELIVE	Current	Undefined	Undefined	UND	Health Education England West Midlands	275385639
HEWTV	HEELIVE	Current	Undefined	Undefined	UND	NULL	275385640
HESW	HEELIVE	Current	Wiltshire Council	Wiltshire Council	817	Severn Local Office - HESW	32948539
HEWTV	HEELIVE	Current	Wiltshire Council	Wiltshire Council (817 / 1-AHNGU3)	817	Health Education England Wessex	276017384
HESW	HEELIVE	Current	Worcestershire PCT	Worcestershire PCT	NULL	NULL	36093376
NULL	HEELIVE	Current	Worcestershire PCT	Worcestershire PCT	NULL	NULL	32948547

Red rows above signify that the LocalOffice field has not been set for these trust rows, and probably being done to aid the filtering out of these duplicated Trusts