Site - Data Consolidation

For Discussion

#		Comment	Owner
1	Should Site Number & Name be unique ?		Alistair Pringle (Unlicensed)

Break down of SiteNumber and SiteName NULL or NOT NULL values;

select CASE WHEN SiteName IS NULL THEN 'NULL' ELSE 'NOT NULL' END SiteName, 
	   CASE WHEN SiteNumber IS NULL THEN 'NULL' ELSE 'NOT NULL' END SiteNumber, status, count(*) 
from [HEELIVE].[vwSite] 
group by CASE WHEN SiteName IS NULL THEN 'NULL' ELSE 'NOT NULL' END, 
	   CASE WHEN SiteNumber IS NULL THEN 'NULL' ELSE 'NOT NULL' END, status

SiteName	SiteNumber	status	RowCount
NOT NULL	NOT NULL	Current	7419
NOT NULL	NULL	Current	817
NOT NULL	NULL	Inactive	243
NOT NULL	NOT NULL	Inactive	5265
		Total	13744

There are 12 SiteNames that have been duplicated, of which 5 have a Current Status;

select SiteName, SiteNumber, status, count(*) 
from [HEELIVE].[vwSite]
group by SiteName, SiteNumber, status
having count(*) > 1
order by 3, 4 desc

SiteName	SiteNumber	status	RowCount
Dental Surgery	NULL	Current	7
Health Centre	NULL	Current	2
Overseas Hospital	NULL	Current	2
Park Surgery	NULL	Current	2
The Medical Centre	NULL	Current	2
Kings Park Hospital	NULL	Inactive	3
15a Derby road	NULL	Inactive	2
1A Welbeck Road	NULL	Inactive	2
Doctors Surgery	NULL	Inactive	2
Ex	NULL	Inactive	2
Gloucestershire Royal Hospital	RTE03	Inactive	2
Poole General Hospital (Dorset HC)	NULL	Inactive	2

Further details on the 5 duplicates;

select lo.LocalOfficeName, s.*
from [HEELIVE].[vwSite] as s
left join [HEELIVE].[vwLocalOffice] as lo ON (lo.itemtype = 'Site' and lo.UniqueID = s.SiteID)
where status = 'Current'
and ISNULL(SiteName, -1) + ISNULL(SiteNumber,-2) in (select ISNULL(SiteName, -1) + ISNULL(SiteNumber,-2)
													   from [HEELIVE].[vwSite] 
													   where status = 'Current'
													   group by SiteName, SiteNumber
													   having count(*) > 1)
order by SiteName, SiteNumber

LocalOfficeName	RecordOrigin	Status	SiteName	SiteKnownAs	SiteNumber	Trust	SiteID	TrustID	AddressLine1	AddressLine2	AddressLine3	AddressLine4	AddressPostCode	Ward
HENE	HEELIVE	Current	Dental Surgery	DC Daniels Dental Practice	NULL	Durham, Darlington and Tees Area Team	10975970	19741907	61 Roman Road	Linthorpe	NULL	Middlesbrough	TS5 5PH	NULL
HENE	HEELIVE	Current	Dental Surgery	Ryton Dental Clinic	NULL	Cumbria, Northumberland, Tyne and Wear Area Team	12933411	14635626	29 Dean Terrace	Ryton	Newcastle upon Tyne	Tyne and Wear	NE40 3HQ	NULL
HENE	HEELIVE	Current	Dental Surgery	Sunderland Road Dental Practice	NULL	Cumbria, Northumberland, Tyne and Wear Area Team	16263019	14635626	265 Sunderland Road	South Shields	NULL	Tyne and Wear	NE34 6AL	NULL
HENE	HEELIVE	Current	Dental Surgery	Grange Dental Centre	NULL	Cumbria, Northumberland, Tyne and Wear Area Team	16997209	14635626	59-61 Great North Road	Gosforth	Newcastle upon Tyne	Tyne and Wear	NE3 2DQ	NULL
HENE	HEELIVE	Current	Dental Surgery	Stanley Dental Practice	NULL	Durham, Darlington and Tees Area Team	19283844	19741907	13 Front Street	Stanley	NULL	County Durham	DH9 0JE	NULL
HENE	HEELIVE	Current	Dental Surgery	Chandlers Lane Dental Practice	NULL	Cumbria, Northumberland, Tyne and Wear Area Team	21356014	14635626	52 Front Street	Brampton	NULL	NULL	CA8 1NT	NULL
HENE	HEELIVE	Current	Dental Surgery	Southview Dental Care	NULL	Cumbria, Northumberland, Tyne and Wear Area Team	22165835	14635626	46 Southview	East Denton	Newcastle upon Tyne	Tyne and Wear	NE5 2BP	NULL
HENW	HEELIVE	Current	Health Centre	Health Centre	NULL	NULL	246651307	NULL	College Street	Leigh	NULL	NULL	NULL	NULL
HENW	HEELIVE	Current	Health Centre	Health Centre	NULL	NULL	246651406	NULL	London Street	Fleetwood	NULL	NULL	FY7 6WD	NULL
NULL	HEELIVE	Current	Overseas Hospital	Overseas	NULL	NULL	33319953	NULL	NULL	NULL	NULL	NULL	NULL	NULL
HESW	HEELIVE	Current	Overseas Hospital	Overseas	NULL	NULL	36090048	NULL	NULL	NULL	NULL	NULL	NULL	NULL
HENW	HEELIVE	Current	Park Surgery	Park Surgery	NULL	NULL	246651460	NULL	8 Alexandra Road South	Whalley Range	Manchester	NULL	M16 8ER	NULL
HENW	HEELIVE	Current	Park Surgery	Park Surgery	NULL	NULL	246651316	NULL	434 Altrincham Road	Baguley	Wythenshawe	Manchester	M23 9AB	NULL
HENW	HEELIVE	Current	The Medical Centre	The Medical Centre	NULL	NULL	246651687	NULL	25 South King Street	Blackpool	NULL	NULL	FY1 4NF	NULL
HENW	HEELIVE	Current	The Medical Centre	The Medical Centre	NULL	NULL	246651251	NULL	Market Street	Whitworth	Rochdale	Lancs	OL12 8QS	NULL

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