select p.Status, recordtype, count(*) as rc
from [HEELIVE].[vwPerson] as p
group by p.Status, recordtype
order by p.Status, recordtype

select p.Status, recordtype, ISNULL(lo.LocalOfficeName, 'NULL') as LocalOfficeName, count(*) as rc
from [HEELIVE].[vwPerson] as p
left join [HEELIVE].[vwLocalOffice] as lo ON (lo.UniqueID = p.PersonID and lo.ItemType = 'Person')
group by lo.LocalOfficeName, p.Status, recordtype
order by p.Status, recordtype, LocalOfficeName

select p.Status, recordtype, ISNULL(lo.LocalOfficeName, 'NULL') as LocalOfficeName, count(*) as rc
from [HEELIVE].[vwPerson] as p
left join [HEELIVE].[vwLocalOffice] as lo ON (lo.UniqueID = p.PersonID and lo.ItemType = 'Person')
where p.PersonID in (select UniqueID
                    from [HEELIVE].[vwLocalOffice]
                    where ItemType = 'Person'
                    group by UniqueID
                    having count(*) > 1)
group by lo.LocalOfficeName, p.Status, recordtype
order by p.Status, recordtype, LocalOfficeName

select p.Status, recordtype, 
	  case when plc.rc > 0 THEN 'EXISTS' ELSE 'NONE' END as placement_rows, 
	  case when pms.rc > 0 THEN 'EXISTS' ELSE 'NONE' END as programme_rows, count(*) as rc
from [HEELIVE].[vwPerson] as p
left join [HEELIVE].[vwLocalOffice] as lo ON (lo.UniqueID = p.PersonID and lo.ItemType = 'Person')
left join (select personid, count(*) as rc 
		   from [HEELIVE].[vwPlacement] as pl
		   group by PersonID) as plc on (plc.PersonID = p.PersonID)
left join (select personid, count(*) as rc 
		   from [HEELIVE].[vwProgrammeMembership] as pm
		   group by PersonID) as pms on (pms.personid = p.PersonID)
where lo.LocalOfficeName is null
and p.Status = 'Current'
group by p.Status, recordtype,
case when plc.rc > 0 THEN 'EXISTS' ELSE 'NONE' END,
	  case when pms.rc > 0 THEN 'EXISTS' ELSE 'NONE' END
order by p.Status, recordtype, 3,4

select p.recordtype, case when p.role IS NULL THEN 'NO Role' ELSE 'Role Exists' END role, count(*) rc
from [HEELIVE].[vwPerson] as p
left join [HEELIVE].[vwLocalOffice] as lo ON (lo.UniqueID = p.PersonID and lo.ItemType = 'Person')
left join (select personid, count(*) as rc 
		   from [HEELIVE].[vwPlacement] as pl
		   group by PersonID) as plc on (plc.PersonID = p.PersonID)
left join (select personid, count(*) as rc 
		   from [HEELIVE].[vwProgrammeMembership] as pm
		   group by PersonID) as pms on (pms.personid = p.PersonID)
where lo.LocalOfficeName is null
and p.Status = 'Current'
and ISNULL(plc.rc,0) = 0
and ISNULL(pms.rc ,0) = 0
group by p.recordtype, case when p.role IS NULL THEN 'NO Role' ELSE 'Role Exists' END;

case  when pl.GradeName in ('Academic Clinical Fellow','Academic Clinical Fellow Year 1','Academic Clinical Fellow Year 2','Academic Clinical Fellow Year 3','Academic Clinical Fellow Year 4','Academic Clinical Fellow Year 5','Academic Clinical Fellow Year 6','Academic Clinical Fellow Year 7','Academic Clinical Fellow Year 8','Academic Clinical Lecturer','Academic Clinical Lecturer (POST)','Academic Clinical Lecturer Year 1','Academic Clinical Lecturer Year 2','Academic Clinical Lecturer Year 3','Academic Clinical Lecturer Year 4','Academic Clinical Lecturer Year 5','Academic Clinical Lecturer Year 6','Academic Clinical Lecturer Year 7','Academic Clinical Lecturer Year 8','Academic Foundation Training','ACF1','ACF2','ACF3','ACF4','ACL1','ACL2','ACL3','ACL4','ACL5','ACL6','AF1','AF2','AF3','AF4','AF5','AF6','AL1','AL2','AL3','AL4','AL5','AL6','AL7','AL8','Broad Based Training Year 1','Broad Based Training Year 2','Clinical Lecturer Year 1','Clinical Lecturer Year 2','Clinical Lecturer Year 3','Clinical Lecturer Year 4','Clinical Lecturer Year 5','Clinical Lecturer Year 6','Clinical Lecturer Year 7','Clinical Lecturer Year 8','Core Training','Core Training MOD','Core Training OR GPST (Shared Post)','Core Training temp','Core Training Year 1','Core Training Year 1 MOD','Core Training Year 2','Core Training Year 2 MOD','Core Training Year 3','Core Training Year 3 MOD','DCT','DCT1','DCT2','DCT3','Dental Core Training','Dental Core Training Year 1','Dental Core Training Year 2','Dental Core Training Year 3','Dental Foundation 2','Dental Foundation Training','Dental Foundation Training 1','Dental Foundation Year 1','Dental Foundation Year 2','Dental Officer','DF2','FEL','Fixed Term Specialist Training Appointee','Fixed Term Specialty Registrar Year 1','Fixed Term Specialty Registrar Year 2','Fixed Term Specialty Registrar Year 3','Fixed Term Specialty Training Appt 1','Fixed Term Specialty Training Appt 2','Fixed Term Specialty Training Appt 3','Foundation Programme Year2  Academic LNR','Foundation Year 1','Foundation Year 2','FT1','FT2','FT3','FT4','FT9','FTT','FTTA','FY1','FY2','GP ACF Year 1','GP ACF Year 2','GP ACF Year 3','GP ACF Year 4','GP Registrar','GP Returner','GP Specialty Training','GP Specialty Training 1/2','GP Specialty Training Registrar','GP Specialty Training Year 2','GP Specialty Training Year 3','GP Specialty Training Year 4','Gp Specialty Training Year 5','GP STR Year 1','GP STR Year 1 Extension','GP STR Year 2','GP STR Year 2 Extension','GP STR Year 3','GP STR Year 3 Extension','GP Trainee 5','GP Trainee 6','GPR','GPRef np','GPRef p','GPST','GPST1','GPST2','GPST3','GPStR','GPSTR1','GPSTR2','GPSTR3','GPSTR4','Higher Training','Higher Training','HO','House Officer','Innovative GPR - HENE','IPT','LA1','LA2','LA3','LA4','LA5','LA6','LA7','LACF1','LACF2','LACF3','LAF','LAF1','LAF2','LAF3','LAF4','LAF5','LAL','LAL3','LAL4','LAL5','LAL6','LAL7','LAR','LAR1','LAR2','LAR3','LAS','LAT','LAT Core Training','LAT Core Training Year 1','LAT Core Training Year 2','LAT Core Training Year 3','LAT Specialty Training','LAT Specialty Training Year 1','LAT Specialty Training Year 2','LAT Specialty Training Year 3','LAT Specialty Training Year 4','LAT Specialty Training Year 5','LAT Specialty Training Year 6','LAT Specialty Training Year 7','LAT Specialty Training Year 8','LAT StR','LAT1','LAT2','LAT3','LAT4','LAT5','LAT6','LAT7','LAT8','LC1','LC2','LC3','LCL1','LCL2','LCL3','LCL4','LCL5','LEC','LL1','LL2','LL3','LL4','LL5','LL6','Local Academic Clinical Fellow','Local Clinical Lecturer','Locum appointed Trainee','Lower Training','Lower Training','MED','MTP','MTP1','MTP2','MTP3','NG-StR5','Out Of Programme','PACCS','PCMT','PCPT','PCST','PDent','PGSTR','PGSTR3','PH Associate','Post CCST Training','Pre Registration House Officer','PreReg House Officer','Pre-registration House Officer','PRHO','PSTR','PSTR3+','Registrar','Retainee','Senior House Officer MOD','Senior Registrar','SHO','SHO for OMFS only','SHO VTS','SHO Year 2/3/4','Specialist Registrar','Specialist Registrar MOD','Specialist Registrar Year 1-OLD','Specialist Registrar Year 2 - OLD','Specialist Registrar Year 3 - OLD','Specialist Registrar Year 4','Specialist Registrar Year 4 - OLD','Specialist Registrar Year 5','Specialist Registrar Year 5 - OLD','Specialist Registrar Year 6','Specialist Registrar Year 6 - OLD','Specialist Registrar Year 7','Specialist Registrar Year 8','Specialist Trainee','Specialty Registrar','Specialty Registrar - HENE','Specialty Training','Specialty Training - HESW','Specialty Training MOD','Specialty Training Year 1','Specialty Training Year 1 MOD','Specialty Training Year 2','Specialty Training Year 2 MOD','Specialty Training Year 3','Specialty Training Year 3 MOD','Specialty Training Year 4','Specialty Training Year 4 MOD','Specialty Training Year 5','Specialty Training Year 5 MOD','Specialty Training Year 6','Specialty Training Year 6 MOD','Specialty Training Year 7','Specialty Training Year 7 MOD','Specialty Training Year 8','Specialty Training Year 8 MOD','Specialty Training Year 9','SpR','SpR3','SpR4','SpR5','SpR6','SpR7','SpR8','ST9','StR','StR1','StR2','StR3','Transition posts - DOH','Trust Converted Post','VIS','Yorkshire Deanery ST','Z1 GP Registrar','Z1 Pre-Reg House Officer','Z1 Registrar','Z1 Senior Registrar','Z2 Community Post (Not','Z2 Registrar','Z2 Senior Registrar','zzGP Registrar','zzSenior House Officer','zzTrust Converted Post','ZZZ Clinical Assistant','ZZZ Consultant','ZZZ Dental House Officer','ZZZ Flexible SHO','ZZZ General Practitioner Post CCT','ZZZ GP Registrar','ZZZ Hospital Practitione','ZZZ House Officer','ZZZ Locum for Training','ZZZ Post General Practice','ZZZ Pre Registration House Officer','ZZZ Public Health Assistant','ZZZ Registrar','ZZZ Senior House Office (F2)','ZZZ Senior House Officer','ZZZ Senior Registrar','ZZZ SHO Foundation','ZZZ SHO VTS','ZZZ SpR Clinical Lecturer','ZZZ SpR Unfunded','ZZZ Trainee in Dental Public Health')
	then 'TrainingGrade'
	when pl.GradeName in ('Anterior Segment','Associate Specialist','Associate Specialist Trust','BASMU Medical Officer','Career Development Post','Clinical Assistant','Clinical Assistant Locum','Clinical Attachment','Clinical Fellow','Clinical Fellow (SpR)','Clinical Fellow (SSHO)','Clinical Fellow SHO','Clinical Fellow Staff Grade','Clinical Fellow(Hon)','Clinical Medical Officer','Clinical Psychologist','Clinical Research Fellow','Clinical Teaching and Research Fellows','Clinical Teaching Fellow','Consultant','Consultant Honorary Other Hospital','Consultant Honorary University','Consultant Locum','Consultant MOD','Consultant Split Contract','Dental Clinical Lecturer/Tutor','Dental Core Training 1','Dental Core Training 2','Dental Foundation 1','Dental Foundation 3','Dental house officer','Dental SHO','DF1','Education Research Fellow','Fellow','Fix Term Training Appointment','Fixed Term Specialty Training Year 1','Fixed Term Specialty Training Year 2','Fixed Term Specialty Training Year 3','Fixed Term Training','Flexible Careers Scheme','Foundation YR1 Standalone','Foundation YR2 Standalone','FT5','FTSTA','FTSTA Year 1','FTSTA Year 2','FTSTA Year 3','FTSTA1','FTSTA2','FTSTA3','FTSTA4','General Medical Practitioner','General Practice Registrar','General Professional Trainee','GP Practice Retainee','GP Retainer','GP Specialty Training Year 1','GP STR Year 4','GP with Special Interest','GPST4','GPSTR - Y&H','GStR1','GStR2','GStR3','GStR4','Honorary Clinical Fellow','Honorary Consultant','Hospital Orthodontic Practitioner','Hospital Practitioner','Junior Clinical Fellow','LFY2','LL7','Locum Appointment Service','Locum SHO','Locum Speciality Dentist','Locum Specialty Doctor','Locum Staff Grade','Longitudinal Year 1','Longitudinal Year 2','Medical Training Initiative','National Grid - SpR','National Grid - ST','NG-SpR4','NG-SpR5','NG-SpR6','NG-SpR7','NG-SpR8','PH Specialist','Post CCT','PRE GPSTR','Pre-GP','Public Health Assistant','Public Health Post','Research Fellow','Research Registrar','Resident Medical Officer','Resident Medical Officers','Salaried General Practitioner','SAS','Senior Clinical Fellow','Senior Clinical Medical Officer','Senior House Officer','Senior House Officer - Dental','Senior House Officer Locum','Senior House Officer Trust','Senior SHO','Simulated Patient','SOTC Consultant','Speciaity Doctor - Trust Locum','Specialist Registrar Honorary','Specialist Registrar Locum Service','Specialist Registrar Research Trust','Specialist Registrar Trust','Specialist Training Reg (lvl 3r) – HEWMD','Speciality Dentist','Specialty Doctor','Specialty Doctor - Agency Locum','Staff','Staff Grade','Staff Grade (SG)','Staff Grade Locum','Staff Grade Practioner (SG)','StRHigher','StRLower','TCP','Teaching and Research Fellows','Top Up Trainee','Trainee in Dental Public Health','Transport Fellow','Trust Foundation Grade','Trust Grade','Trust Grade Doctor - Career Grade Level','Trust Grade Foundation 1','Trust Grade Medical Retinal Fellow','Trust Grade Specialty Doctor','Trust Junior Grade','Trust Middle Grade','Trust Practitioner','Trust Registrar','Trust Senior SHO','Trust SHO','Trust SPR','Trust Str - Higher','Trust Str - Lower','Undefined','Undergraduate','Unspecified','Vascular Fellow','VR Fellow','Yorkshire Core Training 1','Yorkshire Core Training 2','Yorkshire Core Training 3','Z1 Clinical Assistant','zGStR3','zzClinical Fellow','zzFTST1','zzFTST2','zzFTST3','zzGP Practice Retainee','zzGStR3','ZZZ Associate Specialist','ZZZ Clin.Fellow/Trust Dr','ZZZ Clinical Assistant (Para 94/10','ZZZ Clinical Fellow','ZZZ Clinical Medical Officer','ZZZ Dental Public Health','ZZZ Fixed Term Training','ZZZ Flexible PRHO','ZZZ Flexible trainee','ZZZ Foundation Year 1','ZZZ GP Returner','ZZZ Hospital Orthodontic Practitio','ZZZ Hospital Practitioner','ZZZ Leicester Trainee','ZZZ Medical Student - HYMS','ZZZ Medical Student - Leeds','ZZZ Medical Student - Sheffield','ZZZ Middle Grade','ZZZ NCHT Trust Grade SpR','ZZZ Permit Free Trainee','ZZZ PH Specialist','ZZZ Retainee','ZZZ S. Senior House Offi','ZZZ Senior House Officer (Unfunded)','ZZZ Senior Lecturer','ZZZ Sessional GP','ZZZ SHO (VTS)','ZZZ Specialty Doctor','ZZZ Staff Grade','ZZZ Staff Grade (SG)','ZZZ Staff Grade (STG)','ZZZ Staff Grade Practioner (SG)','ZZZ Staff Grade Practioner (SGO)','ZZZ Staff Grade Practitioner (SGI)','ZZZ Top Up Trainee','ZZZ Trust Grade','ZZZ VTS/Clinical Fellow')
	then 'NonTrainingGrade'
End