People - Data Analysis | GMC/GDC Numbers

Current Assumptions for TIS

Currently Held in DR

vwPerson by Status;

select status, count(*)
FROM [HEELIVE].[vwPerson]
group by status;

status	RowCount
Current	108596
Inactive	136398

With a NULL GMCNumber and a NULL or UNKNOWN GDCNumber;

select gmcnumber, gdcnumber, status, count(*)
FROM [HEELIVE].[vwPerson]
where status = 'Current'
and gmcnumber is null
group by gmcnumber, gdcnumber, status
order by 4 desc;

gmcnumber	gdcnumber	status	RowCount
NULL	NULL	Current	11180
NULL	UNKNOWN	Current	7

95 People with duplicated GMCNumbers;

SELECT GMCNUMBER, STATUS, COUNT(*) 
FROM [HEELIVE].[vwPerson] 
GROUP BY GMCNUMBER, 
STATUS HAVING COUNT(*) > 1 
ORDER BY 3 DESC;

GMCNUMBER	STATUS	RowCount
NULL	Current	13935
UNKNOWN	Inactive	12922
NULL	Inactive	6992
N/A	Current	2585
UNKNOWN	Current	2378
N/A	Inactive	1048
	Inactive	1036
	Current	604
Retired	Inactive	23
Retired	Current	19
NA	Current	15
0	Inactive	9
1234567	Inactive	5
6066499	Inactive	5
0	Inactive	4
5208874	Inactive	3
L922264	Inactive	3
L962435	Inactive	3
0	Current	3
1111111	Inactive	3
L950202	Inactive	2
2578918	Inactive	2
L933034	Inactive	2
L950915	Inactive	2
555555	Inactive	2
u	Inactive	2
L923059	Inactive	2
6071960	Inactive	2
4743116	Inactive	2
L910017	Inactive	2
888888	Inactive	2
L922242	Inactive	2
6145688	Inactive	2
3675689	Inactive	2
5193442	Inactive	2
4150394	Inactive	2
L99/940	Inactive	2
L950806	Inactive	2
L949777	Inactive	2
L912804	Inactive	2
4217028	Inactive	2
9999999	Inactive	2
5555555	Inactive	2
2818087	Inactive	2
5200431	Inactive	2
6054833	Inactive	2
4582038	Inactive	2
L980074	Inactive	2
L971362	Inactive	2
5203773	Inactive	2
6065661	Inactive	2
5200000	Inactive	2
L950644	Inactive	2
L960152	Inactive	2
6168983	Inactive	2
9876543	Current	2
7479912	Current	2
L941460	Inactive	2
L941327	Inactive	2
L970242	Inactive	2
4123901	Current	2
L952029	Inactive	2
6146557	Inactive	2
L951903	Inactive	2
L952273	Inactive	2
4	Inactive	2
L941604	Inactive	2
7072678	Inactive	2
L99/001	Inactive	2
L979407	Inactive	2
L940070	Inactive	2
8888888	Inactive	2
L920077	Inactive	2
4271945	Inactive	2
L960358	Inactive	2
L940300	Inactive	2
7293795	Inactive	2
L932379	Inactive	2
95/0516	Inactive	2
L923859	Inactive	2
4127321	Inactive	2
L962367	Inactive	2
6144659	Inactive	2
L950858	Inactive	2
4257820	Inactive	2
L962248	Inactive	2
L941738	Inactive	2
98/1208	Inactive	2
152636	Inactive	2
L971892	Inactive	2
L950934	Inactive	2
L98/127	Inactive	2
L962659	Inactive	2
L971128	Inactive	2
6091459	Inactive	2

39 People with duplicated GDCNumber;

SELECT GdCNUMBER, STATUS, COUNT(*) 
FROM [HEELIVE].[vwPerson] 
GROUP BY GdCNUMBER, 
STATUS HAVING COUNT(*) > 1 
ORDER BY 3 DESC;

GDCNUMBER	STATUS	RowCount
NULL	Inactive	111726
NULL	Current	85261
N/A	Inactive	17709
N/A	Current	11163
	Current	5572
UNKNOWN	Inactive	795
UNKNOWN	Current	738
	Inactive	413
67522	Inactive	2
49099	Inactive	2
196456	Inactive	2
67728	Inactive	2
66102	Inactive	2
83713	Inactive	2
66990	Inactive	2
65064	Inactive	2
100204	Inactive	2
211755	Inactive	2
67956	Inactive	2
54025	Inactive	2
192439	Inactive	2
172033	Inactive	2
52466	Inactive	2
68214	Inactive	2
76433	Inactive	2
154368	Inactive	2
66558	Inactive	2
58558	Inactive	2
79463	Inactive	2
81612	Inactive	2
59768	Inactive	2
258345	Inactive	2
68090	Inactive	2
84005	Inactive	2
53517	Inactive	2
63246	Inactive	2
64920	Inactive	2
84804	Inactive	2
79732	Inactive	2

People by Status, GMCNumber, GDCNumber and PublicHealthNumber

with q as (
select status, 
	   case when gmcnumber = 'UNKNOWN' THEN gmcnumber
			when gmcnumber = 'N/A' THEN gmcnumber
			when gmcnumber = 'Retired' THEN gmcnumber
			when gmcnumber IS NULL THEN gmcnumber
			else 'Valid' 
	  end gmcnumber_c, gmcnumber,
	  case when gdcnumber = 'UNKNOWN' THEN gdcnumber
			when gdcnumber = 'N/A' THEN gdcnumber
			when gdcnumber = 'Retired' THEN gdcnumber
			when gdcnumber IS NULL THEN gdcnumber
			else 'Valid' 
	  end gdcnumber_c, gdcnumber, 
	  case when publichealthnumber = 'UNKNOWN' THEN publichealthnumber
			when publichealthnumber = 'N/A' THEN publichealthnumber
			when publichealthnumber = 'Retired' THEN publichealthnumber
			when publichealthnumber IS NULL THEN publichealthnumber
			else 'Valid' 
	  end publichealthnumber_c, publichealthnumber 
from [HEELIVE].[vwPerson])
select status, gmcnumber_c, gdcnumber_c, publichealthnumber_c, count(*) as rc
from q
group by status, gmcnumber_c, gdcnumber_c, publichealthnumber_c
order by 5 desc;

status	gmcnumber_c	gdcnumber_c	publichealthnumber_c	rc
Inactive	Valid	NULL	NULL	97063
Current	Valid	NULL	NULL	76965
Inactive	Valid	N/A	NULL	17195
Inactive	UNKNOWN	NULL	NULL	11869
Current	NULL	NULL	NULL	11237
Current	Valid	N/A	NULL	9429
Inactive	NULL	Valid	NULL	4833
Current	NULL	Valid	NULL	3192
Inactive	NULL	NULL	NULL	2890
Current	Valid	Valid	NULL	2562
Current	UNKNOWN	NULL	NULL	2060
Current	N/A	N/A	N/A	1553
Inactive	N/A	Valid	NULL	646
Inactive	UNKNOWN	UNKNOWN	Valid	369
Current	N/A	NULL	NULL	362
Inactive	UNKNOWN	N/A	NULL	350
Current	N/A	UNKNOWN	NULL	348
Current	UNKNOWN	UNKNOWN	NULL	283
Inactive	UNKNOWN	UNKNOWN	NULL	217
Inactive	Valid	Valid	NULL	183
Inactive	N/A	NULL	NULL	156
Current	N/A	Valid	NULL	156
Inactive	NULL	NULL	Valid	153
Current	NULL	NULL	Valid	134
Inactive	NULL	UNKNOWN	NULL	129
Inactive	N/A	N/A	Valid	103
Inactive	UNKNOWN	Valid	NULL	86
Current	N/A	N/A	NULL	76
Inactive	N/A	UNKNOWN	NULL	56
Current	N/A	N/A	Valid	56
Inactive	N/A	N/A	N/A	54
Current	NULL	UNKNOWN	NULL	52
Current	Valid	UNKNOWN	NULL	48
Current	N/A	NULL	Valid	36
Inactive	N/A	NULL	Valid	23
Inactive	Retired	NULL	NULL	23
Inactive	UNKNOWN	NULL	Valid	20
Current	Retired	NULL	NULL	19
Inactive	Valid	UNKNOWN	NULL	18
Current	UNKNOWN	Valid	NULL	18
Inactive	NULL	N/A	Valid	17
Current	UNKNOWN	N/A	NULL	15
Inactive	UNKNOWN	N/A	Valid	9
Current	UNKNOWN	UNKNOWN	Valid	7
Inactive	NULL	NULL	UNKNOWN	7
Current	NULL	NULL	UNKNOWN	5
Inactive	N/A	Valid	N/A	4
Inactive	UNKNOWN	UNKNOWN	UNKNOWN	3
Inactive	Valid	N/A	n/a	3
Current	UNKNOWN	NULL	Valid	3
Inactive	N/A	N/A	unknown	3
Current	Valid	N/A	N/A	3
Current	UNKNOWN	Valid	Valid	2
Current	NULL	Valid	N/A	2
Current	N/A	Valid	N/A	2
Inactive	Valid	UNKNOWN	N/A	2
Current	NULL	N/A	Valid	1
Inactive	UNKNOWN	UNKNOWN	N/A	1
Inactive	NULL	Valid	N/A	1
Current	UNKNOWN	N/A	N/A	1
Current	NULL	Valid	Valid	1
Current	Valid	NULL	Valid	1
Current	N/A	Valid	Valid	1
Current	Valid	N/A	Valid	1
Current	Valid	UNKNOWN	N/A	1
Inactive	Valid	NULL	Valid	1
Inactive	Valid	N/A	Valid	1
Inactive	N/A	N/A	NULL	1
Inactive	N/A	UNKNOWN	n/a	1
Inactive	N/A	Valid	Valid	1
Inactive	Valid	Valid	N/A	1

193 People with a valid GMC and GDC Numbers, that are valid and different, of which 81 have a current status

with q as (
select status, 
	   case when gmcnumber = 'UNKNOWN' THEN gmcnumber
			when gmcnumber = 'N/A' THEN gmcnumber
			when gmcnumber = 'Retired' THEN gmcnumber
			when gmcnumber IS NULL THEN gmcnumber
			else 'Valid' 
	  end gmcnumber_c, gmcnumber,
	  case when gdcnumber = 'UNKNOWN' THEN gdcnumber
			when gdcnumber = 'N/A' THEN gdcnumber
			when gdcnumber = 'Retired' THEN gdcnumber
			when gdcnumber IS NULL THEN gdcnumber
			else 'Valid' 
	  end gdcnumber_c, gdcnumber, 
	  case when publichealthnumber = 'UNKNOWN' THEN publichealthnumber
			when publichealthnumber = 'N/A' THEN publichealthnumber
			when publichealthnumber = 'Retired' THEN publichealthnumber
			when publichealthnumber IS NULL THEN publichealthnumber
			else 'Valid' 
	  end publichealthnumber_c, publichealthnumber 
from [HEELIVE].[vwPerson])
select status, gmcnumber_c, gdcnumber_c, publichealthnumber_c, count(*) as rc
from q
where gmcnumber_c = 'Valid'
and gdcnumber_c = 'Valid'
and gmcnumber <> gdcnumber
group by status, gmcnumber_c, gdcnumber_c, publichealthnumber_c
order by 5 desc;

status	gmcnumber_c	gdcnumber_c	publichealthnumber_c	rc
Inactive	Valid	Valid	NULL	111
Current	Valid	Valid	NULL	81
Inactive	Valid	Valid	N/A	1

These are the 81 Current people with a GMC and GDC Numbers;

with q as (
select status, 
	   case when gmcnumber = 'UNKNOWN' THEN gmcnumber
			when gmcnumber = 'N/A' THEN gmcnumber
			when gmcnumber = 'Retired' THEN gmcnumber
			when gmcnumber IS NULL THEN gmcnumber
			else 'Valid' 
	  end gmcnumber_c, gmcnumber,
	  case when gdcnumber = 'UNKNOWN' THEN gdcnumber
			when gdcnumber = 'N/A' THEN gdcnumber
			when gdcnumber = 'Retired' THEN gdcnumber
			when gdcnumber IS NULL THEN gdcnumber
			else 'Valid' 
	  end gdcnumber_c, gdcnumber, 
	  case when publichealthnumber = 'UNKNOWN' THEN publichealthnumber
			when publichealthnumber = 'N/A' THEN publichealthnumber
			when publichealthnumber = 'Retired' THEN publichealthnumber
			when publichealthnumber IS NULL THEN publichealthnumber
			else 'Valid' 
	  end publichealthnumber_c, publichealthnumber 
from [HEELIVE].[vwPerson])
select status, gmcnumber, gdcnumber, publichealthnumber
from q
where gmcnumber_c = 'Valid'
and gdcnumber_c = 'Valid'
and gmcnumber <> gdcnumber
and status = 'Current'

status	gmcnumber	gdcnumber	publichealthnumber
Current	6050333	243960	NULL
Current	7082028	170339	NULL
Current	6075667	71819	NULL
Current	6145565	77929	NULL
Current	6155629	79672	NULL
Current	7271465	85966	NULL
Current	6054062	71259	NULL
Current	7038887	109982	NULL
Current	7558397	191759	NULL
Current	3130485	D 53723	NULL
Current	4502245	66539	NULL
Current	3284834	D 55386	NULL
Current	7071773	145892	NULL
Current	7014396	79337	NULL
Current	4295833	61623	NULL
Current	3493946	56181	NULL
Current	6029427	71139	NULL
Current	7271903	83886	NULL
Current	7088837	102512	NULL
Current	7078864	81287	NULL
Current	6165405	111822	NULL
Current	6103403	69486	NULL
Current	6039304	68421	NULL
Current	4092353	60427	NULL
Current	6099851	72293	NULL
Current	7043463	76527	NULL
Current	7085101	82297	NULL
Current	6151754	116606	NULL
Current	6026637	193169	NULL
Current	6147232	78999	NULL
Current	6105159	83951	NULL
Current	2523080	63471	NULL
Current	3587544	81487	NULL
Current	4703417	77987	NULL
Current	2324463	44960	NULL
Current	4680682	75154	NULL
Current	6031313	71020	NULL
Current	7138363	85446	NULL
Current	6160757	259246	NULL
Current	3545599	58622	NULL
Current	3324392	55633	NULL
Current	7014395	80550	NULL
Current	4193962	61530	NULL
Current	7038884	104928	NULL
Current	4014696	60496	NULL
Current	7484406	249390	NULL
Current	4618193	66578	NULL
Current	6025146	69339	NULL
Current	3196579	D 57053	NULL
Current	4545150	66926	NULL
Current	4202033	D 60023	NULL
Current	7482436	232108	NULL
Current	6166842	82361	NULL
Current	7047254	160410	NULL
Current	2565712	D 47694	NULL
Current	7050088	81221	NULL
Current	6073258	73270	NULL
Current	6102810	228938	NULL
Current	3001602	50644	NULL
Current	4200471	61635	NULL
Current	6129007	101076	NULL
Current	4362306	72715	NULL
Current	6054956	98321	NULL
Current	4575924	64886	NULL
Current	4257514	62606	NULL
Current	116198	116198	NULL
Current	6053146	245106	NULL
Current	6159134	245416	NULL
Current	7083853	70719	NULL
Current	3273764	54270	NULL
Current	7134164	83151	NULL
Current	7263521	170119	NULL
Current	7014367	76259	NULL
Current	3240117	50840	NULL
Current	7461832	208506	NULL
Current	4365127	64484	NULL
Current	4735290	69055	NULL
Current	4745266	69686	NULL
Current	7271855	83252	NULL
Current	6054983	211655	NULL
Current	6162303	244971	NULL

People with a valid Public Health number and GMC or GDC Number;

with q as (
select status, 
	   case when gmcnumber = 'UNKNOWN' THEN gmcnumber
			when gmcnumber = 'N/A' THEN gmcnumber
			when gmcnumber = 'Retired' THEN gmcnumber
			when gmcnumber IS NULL THEN gmcnumber
			else 'Valid' 
	  end gmcnumber_c, gmcnumber,
	  case when gdcnumber = 'UNKNOWN' THEN gdcnumber
			when gdcnumber = 'N/A' THEN gdcnumber
			when gdcnumber = 'Retired' THEN gdcnumber
			when gdcnumber IS NULL THEN gdcnumber
			else 'Valid' 
	  end gdcnumber_c, gdcnumber, 
	  case when publichealthnumber = 'UNKNOWN' THEN publichealthnumber
			when publichealthnumber = 'N/A' THEN publichealthnumber
			when publichealthnumber = 'Retired' THEN publichealthnumber
			when publichealthnumber IS NULL THEN publichealthnumber
			else 'Valid' 
	  end publichealthnumber_c, publichealthnumber 
from [HEELIVE].[vwPerson])
select status, gmcnumber_c, gdcnumber_c, publichealthnumber_c, count(*) as rc
from q
where publichealthnumber_c = 'Valid'
group by status, gmcnumber_c, gdcnumber_c, publichealthnumber_c
order by 5 desc;

There are only 940 people rows with a valid Public Health Number, of which only 2 Current Person rows have a valid GMC Number

status	gmcnumber_c	gdcnumber_c	publichealthnumber_c	rc
Inactive	UNKNOWN	UNKNOWN	Valid	369
Inactive	NULL	NULL	Valid	153
Current	NULL	NULL	Valid	134
Inactive	N/A	N/A	Valid	103
Current	N/A	N/A	Valid	56
Current	N/A	NULL	Valid	36
Inactive	N/A	NULL	Valid	23
Inactive	UNKNOWN	NULL	Valid	20
Inactive	NULL	N/A	Valid	17
Inactive	UNKNOWN	N/A	Valid	9
Current	UNKNOWN	UNKNOWN	Valid	7
Current	UNKNOWN	NULL	Valid	3
Current	UNKNOWN	Valid	Valid	2
Current	Valid	NULL	Valid	1
Current	Valid	N/A	Valid	1
Current	N/A	Valid	Valid	1
Current	NULL	N/A	Valid	1
Current	NULL	Valid	Valid	1
Inactive	N/A	Valid	Valid	1
Inactive	Valid	NULL	Valid	1
Inactive	Valid	N/A	Valid	1

These are the 2 current people rows with a GMC and Public Health Number

with q as (
select status, 
	   case when gmcnumber = 'UNKNOWN' THEN gmcnumber
			when gmcnumber = 'N/A' THEN gmcnumber
			when gmcnumber = 'Retired' THEN gmcnumber
			when gmcnumber IS NULL THEN gmcnumber
			else 'Valid' 
	  end gmcnumber_c, gmcnumber,
	  case when gdcnumber = 'UNKNOWN' THEN gdcnumber
			when gdcnumber = 'N/A' THEN gdcnumber
			when gdcnumber = 'Retired' THEN gdcnumber
			when gdcnumber IS NULL THEN gdcnumber
			else 'Valid' 
	  end gdcnumber_c, gdcnumber, 
	  case when publichealthnumber = 'UNKNOWN' THEN publichealthnumber
			when publichealthnumber = 'N/A' THEN publichealthnumber
			when publichealthnumber = 'Retired' THEN publichealthnumber
			when publichealthnumber IS NULL THEN publichealthnumber
			else 'Valid' 
	  end publichealthnumber_c, publichealthnumber 
from [HEELIVE].[vwPerson])
select status, gmcnumber, gdcnumber_c, publichealthnumber
from q
where publichealthnumber_c = 'Valid'
and gmcnumber_c = 'Valid'
and status = 'Current'

status	gmcnumber	gdcnumber_c	publichealthnumber
Current	4399768	N/A	54190
Current	7503361	NULL	7145047578

For Discussion

#		Comment	Owner
1	Do we need to flag trainees who are missing data in any way in TIS?		Joanne Watson (Unlicensed)
2