--0684
Update u_usersfields 
set DataType = 0, fieldenabled = 1, fieldvisible = 1,
	Header_User = 'Days in Alternative Level of Care Since Last Assessment or Admission',
	Header_CIHI_English = 'Days in Alternative Level of Care Since Last Assessment or Admission'
where Fieldid = 8313 and AppID in (650, 651)
GO
Update u_usersfields 
set Header_User = 'Days in Alternative Level of Care Since Last Assessment or Admission',
	Header_CIHI_English = 'Days in Alternative Level of Care Since Last Assessment or Admission'
where Fieldid = 8313 and AppID not in (650, 651)
GO
--0717 
--0717 
Update lu_codefile
set userdescription = 'Chinese, Yue (Cantonese)'
where cihivalue = 'yue' and lookupid = 47010
GO
--0727
if exists (select * from information_schema.tables where table_name = 'zz2017Service')
    drop table zz2017Service
GO
create table zz2017Service(
    CIHIValue varchar(12), 
    UserDescription varchar(100)
) 
GO
insert into zz2017Service(CIHIValue, UserDescription) values('01', 'FAMILY PRACTICE')
GO
insert into zz2017Service(CIHIValue, UserDescription) values('10', 'GENERAL MEDICINE')
GO
insert into zz2017Service(CIHIValue, UserDescription) values('11', 'Allergy')
GO
insert into zz2017Service(CIHIValue, UserDescription) values('12', 'Cardiology')
GO
insert into zz2017Service(CIHIValue, UserDescription) values('13', 'Dermatology')
GO
insert into zz2017Service(CIHIValue, UserDescription) values('14', 'Endocrinology')
GO
insert into zz2017Service(CIHIValue, UserDescription) values('15', 'Gastro-Enterology')
GO
insert into zz2017Service(CIHIValue, UserDescription) values('16', 'Nephrology')
GO
insert into zz2017Service(CIHIValue, UserDescription) values('17', 'Neurology')
GO
insert into zz2017Service(CIHIValue, UserDescription) values('18', 'Respirology')
GO
insert into zz2017Service(CIHIValue, UserDescription) values('19', 'Rheumatology')
GO
insert into zz2017Service(CIHIValue, UserDescription) values('20', 'PAEDIATRIC MEDICINE')
GO
insert into zz2017Service(CIHIValue, UserDescription) values('21', 'Paediatric Allergy')
GO
insert into zz2017Service(CIHIValue, UserDescription) values('22', 'Paediatric Cardiology')
GO
insert into zz2017Service(CIHIValue, UserDescription) values('23', 'Paediatric Dermatology')
GO
insert into zz2017Service(CIHIValue, UserDescription) values('24', 'Paediatric Endocrinology')
GO
insert into zz2017Service(CIHIValue, UserDescription) values('25', 'Paediatric Gastro-Enterology')
GO
insert into zz2017Service(CIHIValue, UserDescription) values('26', 'Paediatric Nephrology')
GO
insert into zz2017Service(CIHIValue, UserDescription) values('27', 'Paediatric Neurology')
GO
insert into zz2017Service(CIHIValue, UserDescription) values('28', 'Paediatric Respirology')
GO
insert into zz2017Service(CIHIValue, UserDescription) values('29', 'Paediatric Rheumatology')
GO
insert into zz2017Service(CIHIValue, UserDescription) values('30', 'GENERAL SURGERY')
GO
insert into zz2017Service(CIHIValue, UserDescription) values('31', 'Cardiovascular Surgery')
GO
insert into zz2017Service(CIHIValue, UserDescription) values('32', 'Neurosurgery')
GO
insert into zz2017Service(CIHIValue, UserDescription) values('33', 'Oral Surgery')
GO
insert into zz2017Service(CIHIValue, UserDescription) values('34', 'Orthopaedic Surgery')
GO
insert into zz2017Service(CIHIValue, UserDescription) values('35', 'Plastic Surgery')
GO
insert into zz2017Service(CIHIValue, UserDescription) values('36', 'Thoracic Surgery')
GO
insert into zz2017Service(CIHIValue, UserDescription) values('37', 'Transplant Surgery')
GO
insert into zz2017Service(CIHIValue, UserDescription) values('38', 'Traumatology')
GO
insert into zz2017Service(CIHIValue, UserDescription) values('39', 'Urology')
GO
insert into zz2017Service(CIHIValue, UserDescription) values('40', 'PAEDIATRIC SURGERY')
GO
insert into zz2017Service(CIHIValue, UserDescription) values('41', 'Paediatric Cardiovascular Surgery')
GO
insert into zz2017Service(CIHIValue, UserDescription) values('42', 'Paediatric Neurosurgery')
GO
insert into zz2017Service(CIHIValue, UserDescription) values('43', 'Paediatric Oral Surgery')
GO
insert into zz2017Service(CIHIValue, UserDescription) values('44', 'Paediatric Orthopaedic Surgery')
GO
insert into zz2017Service(CIHIValue, UserDescription) values('45', 'Paediatric Plastic Surgery')
GO
insert into zz2017Service(CIHIValue, UserDescription) values('46', 'Paediatric Thoracic Surgery')
GO
insert into zz2017Service(CIHIValue, UserDescription) values('47', 'Paediatric Transplant Surgery')
GO
insert into zz2017Service(CIHIValue, UserDescription) values('48', 'Paediatric Traumatology')
GO
insert into zz2017Service(CIHIValue, UserDescription) values('49', 'Paediatric Urology')
GO
insert into zz2017Service(CIHIValue, UserDescription) values('51', 'OBS DELIVERED')
GO
insert into zz2017Service(CIHIValue, UserDescription) values('52', 'OBS ANTEPARTUM')
GO
insert into zz2017Service(CIHIValue, UserDescription) values('53', 'OBS ABORTED')
GO
insert into zz2017Service(CIHIValue, UserDescription) values('54', 'NEWBORN')
GO
insert into zz2017Service(CIHIValue, UserDescription) values('55', 'GYNAECOLOGY')
GO
insert into zz2017Service(CIHIValue, UserDescription) values('56', 'Paediatric Gynaecology')
GO
insert into zz2017Service(CIHIValue, UserDescription) values('58', 'Palliative Care')
GO
insert into zz2017Service(CIHIValue, UserDescription) values('59', 'OBS POSTPARTUM')
GO
insert into zz2017Service(CIHIValue, UserDescription) values('60', 'Otolaryngology')
GO
insert into zz2017Service(CIHIValue, UserDescription) values('61', 'Paediatric Otolaryngology')
GO
insert into zz2017Service(CIHIValue, UserDescription) values('62', 'Ophthalmology')
GO
insert into zz2017Service(CIHIValue, UserDescription) values('63', 'Paediatric Ophthalmology')
GO
insert into zz2017Service(CIHIValue, UserDescription) values('64', 'PSYCHIATRY')
GO
insert into zz2017Service(CIHIValue, UserDescription) values('65', 'Paediatric Psychiatry')
GO
insert into zz2017Service(CIHIValue, UserDescription) values('66', 'Haematology')
GO
insert into zz2017Service(CIHIValue, UserDescription) values('67', 'Paediatric Haematology')
GO
insert into zz2017Service(CIHIValue, UserDescription) values('68', 'Immunology')
GO
insert into zz2017Service(CIHIValue, UserDescription) values('69', 'Paediatric Immunology')
GO
insert into zz2017Service(CIHIValue, UserDescription) values('70', 'Physical Med. & Rehab')
GO
insert into zz2017Service(CIHIValue, UserDescription) values('71', 'Paediatric Physical Med. & Rehab')
GO
insert into zz2017Service(CIHIValue, UserDescription) values('72', 'Geriatrics')
GO
insert into zz2017Service(CIHIValue, UserDescription) values('74', 'Oncology')
GO
insert into zz2017Service(CIHIValue, UserDescription) values('76', 'Rehab In Acute Care Hospital')
GO
insert into zz2017Service(CIHIValue, UserDescription) values('81', 'Radiotherapy')
GO
insert into zz2017Service(CIHIValue, UserDescription) values('82', 'Genetics')
GO
insert into zz2017Service(CIHIValue, UserDescription) values('87', 'Dentistry')
GO
insert into zz2017Service(CIHIValue, UserDescription) values('88', 'Paediatric Dentistry')
GO
insert into zz2017Service(CIHIValue, UserDescription) values('89', 'Stillborn')
GO
insert into zz2017Service(CIHIValue, UserDescription) values('90', 'Cadaver (admitted for organ/tissue retrieval)')
GO
insert into zz2017Service(CIHIValue, UserDescription) values('91', 'Podiatry')
GO
insert into zz2017Service(CIHIValue, UserDescription) values('96', 'Infectious Disease')
GO
insert into zz2017Service(CIHIValue, UserDescription) values('97', 'Neonatology')
GO
insert into zz2017Service(CIHIValue, UserDescription) values('98', 'For Use in NS and NL')
GO
insert into zz2017Service(CIHIValue, UserDescription) values('99', 'Alternate Level of Care')
GO
Update lu_patientservice
set DefaultDescription = z.UserDescription
--select z.*, a.*
from lu_patientservice a
    inner join zz2017Service z on a.cihivalue = z.cihivalue
where a.DefaultDescription <> z.userdescription
GO
update lu_patientservice
set ValidTo = '2017/03/31'
--select z.*, a.*
from lu_patientservice a
    left join zz2017Service z on left(a.cihivalue, 2) = z.cihivalue
where z.cihivalue is null and a.ValidTo > '2017/03/31'
GO
if exists (select * from information_schema.tables where table_name = 'zz2017Service')
    drop table zz2017Service
GO
if exists (select * from information_schema.tables where table_name = 'zz2017MiniPostalCode')
    drop table zz2017MiniPostalCode
GO
create table zz2017MiniPostalCode(
    CIHIValue varchar(12), 
    UserDescription varchar(100)
) 
GO
insert into zz2017MiniPostalCode(CIHIValue, UserDescription) values('NL ', 'Newfoundland & Labrador')
GO
insert into zz2017MiniPostalCode(CIHIValue, UserDescription) values('NS ', 'Nova Scotia')
GO
insert into zz2017MiniPostalCode(CIHIValue, UserDescription) values('NB', 'New Brunswick')
GO
insert into zz2017MiniPostalCode(CIHIValue, UserDescription) values('PE', 'Prince Edward Island')
GO
insert into zz2017MiniPostalCode(CIHIValue, UserDescription) values('QC', 'Quebec')
GO
insert into zz2017MiniPostalCode(CIHIValue, UserDescription) values('ON', 'Ontario')
GO
insert into zz2017MiniPostalCode(CIHIValue, UserDescription) values('MB', 'Manitoba')
GO
insert into zz2017MiniPostalCode(CIHIValue, UserDescription) values('SK', 'Saskatchewan')
GO
insert into zz2017MiniPostalCode(CIHIValue, UserDescription) values('AB', 'Alberta')
GO
insert into zz2017MiniPostalCode(CIHIValue, UserDescription) values('BC', 'British Columbia')
GO
insert into zz2017MiniPostalCode(CIHIValue, UserDescription) values('YT', 'Yukon')
GO
insert into zz2017MiniPostalCode(CIHIValue, UserDescription) values('NT', 'Northwest Territories')
GO
insert into zz2017MiniPostalCode(CIHIValue, UserDescription) values('NU', 'Nunavut')
GO
insert into zz2017MiniPostalCode(CIHIValue, UserDescription) values('US', 'USA, state not known')
GO
insert into zz2017MiniPostalCode(CIHIValue, UserDescription) values('AL', 'Alabama')
GO
insert into zz2017MiniPostalCode(CIHIValue, UserDescription) values('AK', 'Alaska')
GO
insert into zz2017MiniPostalCode(CIHIValue, UserDescription) values('AZ', 'Arizona')
GO
insert into zz2017MiniPostalCode(CIHIValue, UserDescription) values('AR', 'Arkansas')
GO
insert into zz2017MiniPostalCode(CIHIValue, UserDescription) values('CA', 'California')
GO
insert into zz2017MiniPostalCode(CIHIValue, UserDescription) values('CO', 'Colorado')
GO
insert into zz2017MiniPostalCode(CIHIValue, UserDescription) values('CT', 'Connecticut')
GO
insert into zz2017MiniPostalCode(CIHIValue, UserDescription) values('DE', 'Delaware')
GO
insert into zz2017MiniPostalCode(CIHIValue, UserDescription) values('DC ', 'District of Columbia')
GO
insert into zz2017MiniPostalCode(CIHIValue, UserDescription) values('FL', 'Florida')
GO
insert into zz2017MiniPostalCode(CIHIValue, UserDescription) values('GA', 'Georgia')
GO
insert into zz2017MiniPostalCode(CIHIValue, UserDescription) values('HI', 'Hawaii')
GO
insert into zz2017MiniPostalCode(CIHIValue, UserDescription) values('ID', 'Idaho')
GO
insert into zz2017MiniPostalCode(CIHIValue, UserDescription) values('IL', 'Illinois')
GO
insert into zz2017MiniPostalCode(CIHIValue, UserDescription) values('IN', 'Indiana')
GO
insert into zz2017MiniPostalCode(CIHIValue, UserDescription) values('IA', 'Iowa')
GO
insert into zz2017MiniPostalCode(CIHIValue, UserDescription) values('KS', 'Kansas')
GO
insert into zz2017MiniPostalCode(CIHIValue, UserDescription) values('KY', 'Kentucky')
GO
insert into zz2017MiniPostalCode(CIHIValue, UserDescription) values('LA', 'Louisiana')
GO
insert into zz2017MiniPostalCode(CIHIValue, UserDescription) values('ME', 'Maine')
GO
insert into zz2017MiniPostalCode(CIHIValue, UserDescription) values('MI', 'Michigan')
GO
insert into zz2017MiniPostalCode(CIHIValue, UserDescription) values('MD', 'Maryland')
GO
insert into zz2017MiniPostalCode(CIHIValue, UserDescription) values('MA', 'Massachusetts')
GO
insert into zz2017MiniPostalCode(CIHIValue, UserDescription) values('MN', 'Minnesota')
GO
insert into zz2017MiniPostalCode(CIHIValue, UserDescription) values('MS', 'Mississippi')
GO
insert into zz2017MiniPostalCode(CIHIValue, UserDescription) values('MO', 'Missouri')
GO
insert into zz2017MiniPostalCode(CIHIValue, UserDescription) values('MT', 'Montana')
GO
insert into zz2017MiniPostalCode(CIHIValue, UserDescription) values('NE', 'Nebraska')
GO
insert into zz2017MiniPostalCode(CIHIValue, UserDescription) values('NV', 'Nevada')
GO
insert into zz2017MiniPostalCode(CIHIValue, UserDescription) values('NH', 'New Hampshire')
GO
insert into zz2017MiniPostalCode(CIHIValue, UserDescription) values('NJ', 'New Jersey')
GO
insert into zz2017MiniPostalCode(CIHIValue, UserDescription) values('NM', 'New Mexico')
GO
insert into zz2017MiniPostalCode(CIHIValue, UserDescription) values('NY', 'New York')
GO
insert into zz2017MiniPostalCode(CIHIValue, UserDescription) values('NC', 'North Carolina')
GO
insert into zz2017MiniPostalCode(CIHIValue, UserDescription) values('ND', 'North Dakota')
GO
insert into zz2017MiniPostalCode(CIHIValue, UserDescription) values('OH', 'Ohio')
GO
insert into zz2017MiniPostalCode(CIHIValue, UserDescription) values('OK', 'Oklahoma')
GO
insert into zz2017MiniPostalCode(CIHIValue, UserDescription) values('OR', 'Oregon')
GO
insert into zz2017MiniPostalCode(CIHIValue, UserDescription) values('PA', 'Pennsylvania')
GO
insert into zz2017MiniPostalCode(CIHIValue, UserDescription) values('RI', 'Rhode Island')
GO
insert into zz2017MiniPostalCode(CIHIValue, UserDescription) values('SC', 'South Carolina')
GO
insert into zz2017MiniPostalCode(CIHIValue, UserDescription) values('SD', 'South Dakota')
GO
insert into zz2017MiniPostalCode(CIHIValue, UserDescription) values('TN', 'Tennessee')
GO
insert into zz2017MiniPostalCode(CIHIValue, UserDescription) values('TX', 'Texas')
GO
insert into zz2017MiniPostalCode(CIHIValue, UserDescription) values('UT', 'Utah')
GO
insert into zz2017MiniPostalCode(CIHIValue, UserDescription) values('VT', 'Vermont')
GO
insert into zz2017MiniPostalCode(CIHIValue, UserDescription) values('VA', 'Virginia')
GO
insert into zz2017MiniPostalCode(CIHIValue, UserDescription) values('WA', 'Washington')
GO
insert into zz2017MiniPostalCode(CIHIValue, UserDescription) values('WV', 'West Virginia')
GO
insert into zz2017MiniPostalCode(CIHIValue, UserDescription) values('WI', 'Wisconsin')
GO
insert into zz2017MiniPostalCode(CIHIValue, UserDescription) values('WY', 'Wyoming')
GO
insert into zz2017MiniPostalCode(CIHIValue, UserDescription) values('OC', 'Other Country')
GO
insert into zz2017MiniPostalCode(CIHIValue, UserDescription) values('XX', 'Homeless')
GO
insert into zz2017MiniPostalCode(CIHIValue, UserDescription) values('99', 'Unknown')
GO
Update lu_postalcode
set DefaultDescription = z.UserDescription
--select z.*, a.*
from lu_postalcode a
    inner join zz2017MiniPostalCode z on a.cihivalue = z.cihivalue
where a.DefaultDescription <> z.userdescription
GO
update lu_postalcode
set ValidTo = '2017/03/31'
--select z.*, a.*
from lu_postalcode a
    left join zz2017MiniPostalCode z on a.cihivalue = z.cihivalue
where z.cihivalue is null and len(a.cihivalue) = 2 and a.ValidTo > '2017/03/31' and isnumeric(substring(a.cihivalue, 2, 1)) = 0
GO
if exists (select * from information_schema.tables where table_name = 'zz2017MiniPostalCode')
    drop table zz2017MiniPostalCode
GO
if exists (select * from information_schema.tables where table_name = 'zz2017ProviderService_DAD')
    drop table zz2017ProviderService_DAD
GO
create table zz2017ProviderService_DAD(
    CIHIValue varchar(12), 
    UserDescription varchar(100)
) 
GO
insert into zz2017ProviderService_DAD(CIHIValue, UserDescription) values('00001', 'Family Practice/General Practice')
GO
insert into zz2017ProviderService_DAD(CIHIValue, UserDescription) values('00002', 'Community Medicine')
GO
insert into zz2017ProviderService_DAD(CIHIValue, UserDescription) values('00003', 'Emergency Medicine')
GO
insert into zz2017ProviderService_DAD(CIHIValue, UserDescription) values('00010', 'Internal Medicine')
GO
insert into zz2017ProviderService_DAD(CIHIValue, UserDescription) values('00011', 'Clinical Immunology & Allergy')
GO
insert into zz2017ProviderService_DAD(CIHIValue, UserDescription) values('00012', 'Cardiology')
GO
insert into zz2017ProviderService_DAD(CIHIValue, UserDescription) values('00013', 'Dermatology')
GO
insert into zz2017ProviderService_DAD(CIHIValue, UserDescription) values('00014', 'Endocrinology & Metabolism')
GO
insert into zz2017ProviderService_DAD(CIHIValue, UserDescription) values('00015', 'Gastroenterology')
GO
insert into zz2017ProviderService_DAD(CIHIValue, UserDescription) values('00016', 'Nephrology')
GO
insert into zz2017ProviderService_DAD(CIHIValue, UserDescription) values('00017', 'Neurology')
GO
insert into zz2017ProviderService_DAD(CIHIValue, UserDescription) values('00018', 'Respirology')
GO
insert into zz2017ProviderService_DAD(CIHIValue, UserDescription) values('00019', 'Rheumatology')
GO
insert into zz2017ProviderService_DAD(CIHIValue, UserDescription) values('00020', 'Paediatrics')
GO
insert into zz2017ProviderService_DAD(CIHIValue, UserDescription) values('00021', 'Paediatric Immunology & Allergy')
GO
insert into zz2017ProviderService_DAD(CIHIValue, UserDescription) values('00022', 'Paediatric Cardiology')
GO
insert into zz2017ProviderService_DAD(CIHIValue, UserDescription) values('00023', 'Paediatric Dermatology')
GO
insert into zz2017ProviderService_DAD(CIHIValue, UserDescription) values('00024', 'Paediatric Endocrinology & Metabolism')
GO
insert into zz2017ProviderService_DAD(CIHIValue, UserDescription) values('00025', 'Paediatric Gastro-Enterology')
GO
insert into zz2017ProviderService_DAD(CIHIValue, UserDescription) values('00026', 'Paediatric Nephrology')
GO
insert into zz2017ProviderService_DAD(CIHIValue, UserDescription) values('00027', 'Paediatric Neurology')
GO
insert into zz2017ProviderService_DAD(CIHIValue, UserDescription) values('00028', 'Paediatric Respirology')
GO
insert into zz2017ProviderService_DAD(CIHIValue, UserDescription) values('00029', 'Paediatric Rheumatology')
GO
insert into zz2017ProviderService_DAD(CIHIValue, UserDescription) values('00030', 'General Surgery')
GO
insert into zz2017ProviderService_DAD(CIHIValue, UserDescription) values('00031', 'Cardiac Surgery')
GO
insert into zz2017ProviderService_DAD(CIHIValue, UserDescription) values('00032', 'Neurosurgery')
GO
insert into zz2017ProviderService_DAD(CIHIValue, UserDescription) values('00034', 'Orthopaedic Surgery')
GO
insert into zz2017ProviderService_DAD(CIHIValue, UserDescription) values('00035', 'Plastic Surgery')
GO
insert into zz2017ProviderService_DAD(CIHIValue, UserDescription) values('00036', 'Thoracic Surgery')
GO
insert into zz2017ProviderService_DAD(CIHIValue, UserDescription) values('00037', 'Vascular Surgery')
GO
insert into zz2017ProviderService_DAD(CIHIValue, UserDescription) values('00038', 'Cardiothoracic Surgery')
GO
insert into zz2017ProviderService_DAD(CIHIValue, UserDescription) values('00039', 'Urology')
GO
insert into zz2017ProviderService_DAD(CIHIValue, UserDescription) values('00040', 'Paediatric General Surgery')
GO
insert into zz2017ProviderService_DAD(CIHIValue, UserDescription) values('00041', 'Paediatric Cardiac Surgery')
GO
insert into zz2017ProviderService_DAD(CIHIValue, UserDescription) values('00042', 'Paediatric Neurosurgery')
GO
insert into zz2017ProviderService_DAD(CIHIValue, UserDescription) values('00044', 'Paediatric Orthopedic Surgery')
GO
insert into zz2017ProviderService_DAD(CIHIValue, UserDescription) values('00045', 'Paediatric Plastic Surgery')
GO
insert into zz2017ProviderService_DAD(CIHIValue, UserDescription) values('00049', 'Paediatric Urology')
GO
insert into zz2017ProviderService_DAD(CIHIValue, UserDescription) values('00050', 'Obstetrics & Gynaecology')
GO
insert into zz2017ProviderService_DAD(CIHIValue, UserDescription) values('00051', 'Gynecologic Reproductive Endocrinology & Infertility')
GO
insert into zz2017ProviderService_DAD(CIHIValue, UserDescription) values('00054', 'Maternal-Fetal Medicine')
GO
insert into zz2017ProviderService_DAD(CIHIValue, UserDescription) values('00055', 'Critical Care Medicine')
GO
insert into zz2017ProviderService_DAD(CIHIValue, UserDescription) values('00056', 'Clinical Pharmacology')
GO
insert into zz2017ProviderService_DAD(CIHIValue, UserDescription) values('00057', 'Anaesthesiology')
GO
insert into zz2017ProviderService_DAD(CIHIValue, UserDescription) values('00058', 'Paediatric Anesthesiology')
GO
insert into zz2017ProviderService_DAD(CIHIValue, UserDescription) values('00059', 'Colorectal Surgery')
GO
insert into zz2017ProviderService_DAD(CIHIValue, UserDescription) values('00060', 'Otolaryngology')
GO
insert into zz2017ProviderService_DAD(CIHIValue, UserDescription) values('00061', 'Paediatric Otolaryngology')
GO
insert into zz2017ProviderService_DAD(CIHIValue, UserDescription) values('00062', 'Ophthalmology')
GO
insert into zz2017ProviderService_DAD(CIHIValue, UserDescription) values('00063', 'Paediatric Ophthalmology')
GO
insert into zz2017ProviderService_DAD(CIHIValue, UserDescription) values('00064', 'Psychiatry')
GO
insert into zz2017ProviderService_DAD(CIHIValue, UserDescription) values('00065', 'Paediatric Psychiatry')
GO
insert into zz2017ProviderService_DAD(CIHIValue, UserDescription) values('00066', 'Haematology')
GO
insert into zz2017ProviderService_DAD(CIHIValue, UserDescription) values('00067', 'Pediatric Hematology/Oncology')
GO
insert into zz2017ProviderService_DAD(CIHIValue, UserDescription) values('00070', 'Physical Medicine & Rehabilitation')
GO
insert into zz2017ProviderService_DAD(CIHIValue, UserDescription) values('00072', 'Geriatric Medicine')
GO
insert into zz2017ProviderService_DAD(CIHIValue, UserDescription) values('00073', 'General Surgical Oncology')
GO
insert into zz2017ProviderService_DAD(CIHIValue, UserDescription) values('00074', 'Medical Oncology')
GO
insert into zz2017ProviderService_DAD(CIHIValue, UserDescription) values('00075', 'Radiation Oncology')
GO
insert into zz2017ProviderService_DAD(CIHIValue, UserDescription) values('00076', 'Gynaecological Oncology')
GO
insert into zz2017ProviderService_DAD(CIHIValue, UserDescription) values('00077', 'General Pathology')
GO
insert into zz2017ProviderService_DAD(CIHIValue, UserDescription) values('00078', 'Medical Microbiology')
GO
insert into zz2017ProviderService_DAD(CIHIValue, UserDescription) values('00080', 'Diagnostic Radiology')
GO
insert into zz2017ProviderService_DAD(CIHIValue, UserDescription) values('00082', 'Medical Genetics')
GO
insert into zz2017ProviderService_DAD(CIHIValue, UserDescription) values('00083', 'Anatomical Pathology')
GO
insert into zz2017ProviderService_DAD(CIHIValue, UserDescription) values('00085', 'Haematological Pathology')
GO
insert into zz2017ProviderService_DAD(CIHIValue, UserDescription) values('00086', 'Neuropathology')
GO
insert into zz2017ProviderService_DAD(CIHIValue, UserDescription) values('00089', 'Nuclear Medicine')
GO
insert into zz2017ProviderService_DAD(CIHIValue, UserDescription) values('00090', 'Medical Biochemistry')
GO
insert into zz2017ProviderService_DAD(CIHIValue, UserDescription) values('00092', 'Paediatric Radiology')
GO
insert into zz2017ProviderService_DAD(CIHIValue, UserDescription) values('00093', 'Neuroradiaology')
GO
insert into zz2017ProviderService_DAD(CIHIValue, UserDescription) values('00096', 'Infectious Diseases')
GO
insert into zz2017ProviderService_DAD(CIHIValue, UserDescription) values('00097', 'Neonatal-Perinatal Medicine')
GO
insert into zz2017ProviderService_DAD(CIHIValue, UserDescription) values('00120', 'Paediatric Development')
GO
insert into zz2017ProviderService_DAD(CIHIValue, UserDescription) values('00121', 'Palliative Medicine')
GO
insert into zz2017ProviderService_DAD(CIHIValue, UserDescription) values('00122', 'Adolescent Medicine')
GO
insert into zz2017ProviderService_DAD(CIHIValue, UserDescription) values('00123', 'Forensic Pathology')
GO
insert into zz2017ProviderService_DAD(CIHIValue, UserDescription) values('00124', 'Forensic Psychiatry')
GO
insert into zz2017ProviderService_DAD(CIHIValue, UserDescription) values('00125', 'Geriatric Psychiatry')
GO
insert into zz2017ProviderService_DAD(CIHIValue, UserDescription) values('00126', 'Occupational Medicine')
GO
insert into zz2017ProviderService_DAD(CIHIValue, UserDescription) values('00127', 'Pediatric Emergency Medicine')
GO
insert into zz2017ProviderService_DAD(CIHIValue, UserDescription) values('00128', 'Transfusion Medicine')
GO
insert into zz2017ProviderService_DAD(CIHIValue, UserDescription) values('00129', 'General Internal Medicine')
GO
insert into zz2017ProviderService_DAD(CIHIValue, UserDescription) values('00130', 'Pain Medicine')
GO
insert into zz2017ProviderService_DAD(CIHIValue, UserDescription) values('01000', 'Dentistry Group')
GO
insert into zz2017ProviderService_DAD(CIHIValue, UserDescription) values('01001', 'Dentistry')
GO
insert into zz2017ProviderService_DAD(CIHIValue, UserDescription) values('01002', 'Dental Surgery')
GO
insert into zz2017ProviderService_DAD(CIHIValue, UserDescription) values('01003', 'Oral Surgery')
GO
insert into zz2017ProviderService_DAD(CIHIValue, UserDescription) values('01004', 'Orthodontistry')
GO
insert into zz2017ProviderService_DAD(CIHIValue, UserDescription) values('01005', 'Paedodontistry')
GO
insert into zz2017ProviderService_DAD(CIHIValue, UserDescription) values('01006', 'Periodontistry')
GO
insert into zz2017ProviderService_DAD(CIHIValue, UserDescription) values('01007', 'Oral Pathology')
GO
insert into zz2017ProviderService_DAD(CIHIValue, UserDescription) values('01008', 'Endodontistry')
GO
insert into zz2017ProviderService_DAD(CIHIValue, UserDescription) values('01009', 'Oral Radiology')
GO
insert into zz2017ProviderService_DAD(CIHIValue, UserDescription) values('01010', 'Dental Hygiene Technology')
GO
insert into zz2017ProviderService_DAD(CIHIValue, UserDescription) values('01011', 'Denturology')
GO
insert into zz2017ProviderService_DAD(CIHIValue, UserDescription) values('01012', 'Paediatric Oral Surgery')
GO
insert into zz2017ProviderService_DAD(CIHIValue, UserDescription) values('01013', 'Paediatric Dentistry')
GO
insert into zz2017ProviderService_DAD(CIHIValue, UserDescription) values('01014', 'Prosthodontics')
GO
insert into zz2017ProviderService_DAD(CIHIValue, UserDescription) values('02000', 'Podiatry/Chiropody in General')
GO
insert into zz2017ProviderService_DAD(CIHIValue, UserDescription) values('02001', 'Podiatry')
GO
insert into zz2017ProviderService_DAD(CIHIValue, UserDescription) values('02002', 'Chiropody')
GO
insert into zz2017ProviderService_DAD(CIHIValue, UserDescription) values('03000', 'Therapist Group')
GO
insert into zz2017ProviderService_DAD(CIHIValue, UserDescription) values('03001', 'Radiotherapy')
GO
insert into zz2017ProviderService_DAD(CIHIValue, UserDescription) values('03002', 'Physiotherapy')
GO
insert into zz2017ProviderService_DAD(CIHIValue, UserDescription) values('03003', 'Occupational Therapy')
GO
insert into zz2017ProviderService_DAD(CIHIValue, UserDescription) values('03004', 'Respiratory Therapy')
GO
insert into zz2017ProviderService_DAD(CIHIValue, UserDescription) values('03005', 'Massage Therapy')
GO
insert into zz2017ProviderService_DAD(CIHIValue, UserDescription) values('03006', 'Psychotherapy')
GO
insert into zz2017ProviderService_DAD(CIHIValue, UserDescription) values('03007', 'Recreation Therapy')
GO
insert into zz2017ProviderService_DAD(CIHIValue, UserDescription) values('03008', 'Assistant Therapy')
GO
insert into zz2017ProviderService_DAD(CIHIValue, UserDescription) values('03009', 'Speech Language Pathology')
GO
insert into zz2017ProviderService_DAD(CIHIValue, UserDescription) values('03011', 'Kinesiology')
GO
insert into zz2017ProviderService_DAD(CIHIValue, UserDescription) values('03012', 'Rehabilitation Counselling Therapy')
GO
insert into zz2017ProviderService_DAD(CIHIValue, UserDescription) values('03013', 'Assistant Physiotherapy')
GO
insert into zz2017ProviderService_DAD(CIHIValue, UserDescription) values('03014', 'Assistant Occupational Therapy')
GO
insert into zz2017ProviderService_DAD(CIHIValue, UserDescription) values('03015', 'Rehabilitation Therapy')
GO
insert into zz2017ProviderService_DAD(CIHIValue, UserDescription) values('03016', 'Rehabilitation Engineering Therapy')
GO
insert into zz2017ProviderService_DAD(CIHIValue, UserDescription) values('03017', 'Orthotics')
GO
insert into zz2017ProviderService_DAD(CIHIValue, UserDescription) values('03018', 'Prosthetics')
GO
insert into zz2017ProviderService_DAD(CIHIValue, UserDescription) values('03019', 'Vocational Rehabilitation Counselling Therapy')
GO
insert into zz2017ProviderService_DAD(CIHIValue, UserDescription) values('04000', 'Audiology')
GO
insert into zz2017ProviderService_DAD(CIHIValue, UserDescription) values('05000', 'Chiropractry')
GO
insert into zz2017ProviderService_DAD(CIHIValue, UserDescription) values('06000', 'Nutritional Therapy')
GO
insert into zz2017ProviderService_DAD(CIHIValue, UserDescription) values('07000', 'Osteopathy')
GO
insert into zz2017ProviderService_DAD(CIHIValue, UserDescription) values('08000', 'Optometry')
GO
insert into zz2017ProviderService_DAD(CIHIValue, UserDescription) values('08001', 'Technician Ophthalmology')
GO
insert into zz2017ProviderService_DAD(CIHIValue, UserDescription) values('09000', 'Orthotic/Prosthetic Counselling Therapy')
GO
insert into zz2017ProviderService_DAD(CIHIValue, UserDescription) values('10000', 'Naturopathy')
GO
insert into zz2017ProviderService_DAD(CIHIValue, UserDescription) values('11000', 'Nursing Group')
GO
insert into zz2017ProviderService_DAD(CIHIValue, UserDescription) values('11001', 'RN Nursing')
GO
insert into zz2017ProviderService_DAD(CIHIValue, UserDescription) values('11002', 'CNA/RNA/LPN/RPN Nursing')
GO
insert into zz2017ProviderService_DAD(CIHIValue, UserDescription) values('11003', 'Practitioner Nursing')
GO
insert into zz2017ProviderService_DAD(CIHIValue, UserDescription) values('11004', 'Midwifery')
GO
insert into zz2017ProviderService_DAD(CIHIValue, UserDescription) values('11005', 'Nurse Aide/Healthcare Aide Nursing')
GO
insert into zz2017ProviderService_DAD(CIHIValue, UserDescription) values('12000', 'Social Work')
GO
insert into zz2017ProviderService_DAD(CIHIValue, UserDescription) values('12001', 'Child Care/Child Welfare Work')
GO
insert into zz2017ProviderService_DAD(CIHIValue, UserDescription) values('12002', 'Early Childhood Development Therapy')
GO
insert into zz2017ProviderService_DAD(CIHIValue, UserDescription) values('12003', 'Child Life Specialist')
GO
insert into zz2017ProviderService_DAD(CIHIValue, UserDescription) values('13000', 'Pharmacy')
GO
insert into zz2017ProviderService_DAD(CIHIValue, UserDescription) values('13001', 'Pharmacy Assistant Service')
GO
insert into zz2017ProviderService_DAD(CIHIValue, UserDescription) values('14000', 'Medical Physics')
GO
insert into zz2017ProviderService_DAD(CIHIValue, UserDescription) values('15000', 'Psychology')
GO
insert into zz2017ProviderService_DAD(CIHIValue, UserDescription) values('16000', 'Spiritual/Religious Counselling Therapy')
GO
insert into zz2017ProviderService_DAD(CIHIValue, UserDescription) values('17000', 'Technician Group')
GO
insert into zz2017ProviderService_DAD(CIHIValue, UserDescription) values('17001', 'X-ray Technology')
GO
insert into zz2017ProviderService_DAD(CIHIValue, UserDescription) values('17002', 'Lab. Technology')
GO
insert into zz2017ProviderService_DAD(CIHIValue, UserDescription) values('17003', 'Nuclear Medicine Technology')
GO
insert into zz2017ProviderService_DAD(CIHIValue, UserDescription) values('17004', 'Cast Technology')
GO
insert into zz2017ProviderService_DAD(CIHIValue, UserDescription) values('17005', 'ECG Technology')
GO
insert into zz2017ProviderService_DAD(CIHIValue, UserDescription) values('17006', 'Medical Sonography')
GO
insert into zz2017ProviderService_DAD(CIHIValue, UserDescription) values('18000', 'Alternative Healing Therapy')
GO
insert into zz2017ProviderService_DAD(CIHIValue, UserDescription) values('19000', 'Language Interpretation Therapy')
GO
insert into zz2017ProviderService_DAD(CIHIValue, UserDescription) values('20000', 'Home Support/Home Care Work Therapy')
GO
insert into zz2017ProviderService_DAD(CIHIValue, UserDescription) values('20001', 'Child Care Assistant Service')
GO
insert into zz2017ProviderService_DAD(CIHIValue, UserDescription) values('30000', 'Physician''s Assistant Service')
GO
insert into zz2017ProviderService_DAD(CIHIValue, UserDescription) values('31000', 'Paramedic Service')
GO
insert into zz2017ProviderService_DAD(CIHIValue, UserDescription) values('32000', 'Genetic Counseling Service')
GO
insert into zz2017ProviderService_DAD(CIHIValue, UserDescription) values('33000', 'School Teacher')
GO
Update lu_providerservice
set DefaultDescription = z.UserDescription
--select z.*, a.*
from lu_providerservice a
    inner join zz2017ProviderService_DAD z on a.cihivalue = z.cihivalue
where a.DefaultDescription <> z.userdescription
GO
update lu_providerservice
set ValidTo = '2017/03/31'
--select z.*, a.*
from lu_providerservice a
    left join zz2017ProviderService_DAD z on a.cihivalue = z.cihivalue
where z.cihivalue is null and len(a.cihivalue) = 5 and a.ValidTo > '2017/03/31' and a.cihivalue <> '00000'
GO
update lu_providerservice
set Validto = '2099/03/31'
--select z.*, a.*
from lu_providerservice a
    inner join zz2017ProviderService_DAD z on a.cihivalue = z.cihivalue 
where a.validto < '2017/04/01' 
GO
if exists (select * from information_schema.tables where table_name = 'zz2017ProviderService_DAD')
    drop table zz2017ProviderService_DAD
GO
--0587
Alter table [dbo].[AB_GrouperAudit] alter column [FieldChanged] varchar(50)
GO
--0736 
update lu_institution_10 
set InstCareType = 'H',
    HospitalType = 'HC',
    IsAcute = 2
where cihivalue in ('89997','90006','61999','29089','N9997','V9997','50008','51277','54655','54656','54657','54658','54659','54660','54661','54662',
'54663','54664','54665','54666','54667','54668','49002','79998','Y9997')
GO

--Submission
Delete from dbo.SU_StructureList where batchyear = 2017
GO
Insert into dbo.SU_StructureList(SU_type, Province, AppID, BatchYear, GroupID, GroupOrder, GroupDesc, list_id)
select SU_type, Province, AppID, BatchYear = 2017, GroupID, GroupOrder, 
    GroupDesc = replace(GroupDesc, '2016', '2017'), list_id + 100
from dbo.SU_StructureList 
where batchyear = 2016
GO
--OMHRS Full Assessment
Update dbo.SU_StructureList set GroupID = 9650 where batchyear = 2017 and appid = 650 and groupid = 8650
GO
delete from [dbo].[SU_StructureFile] where groupid = 9650
GO
Insert into [dbo].[SU_StructureFile](GroupID, SubGroupID, ICDType, TableName, FieldAlias, FieldName, FieldID, Occurrence, RecodeID, TextPosition, TextLength, TextFormat)
select GroupID = 9650, SubGroupID, ICDType, TableName, FieldAlias, FieldName, FieldID, Occurrence, RecodeID, TextPosition, TextLength, TextFormat
from [dbo].[SU_StructureFile]
where groupid = 8650
order by textposition
GO
Update [dbo].[SU_StructureFile] set TextPosition = Textposition + 3 where GroupID = 9650 and textposition >= 496
GO
Insert into [dbo].[SU_StructureFile](GroupID, SubGroupID, ICDType, TableName, FieldAlias, FieldName, FieldID, Occurrence, RecodeID, TextPosition, TextLength, TextFormat)
values(9650, 26, 'T', 'OMHRS_SecS', 'Total Days in Alternative Level of Care', 'X75', 8313, 0, 0, 496, 3, 0)
GO

--OMHRS Short Stay
Update dbo.SU_StructureList set GroupID = 9651 where batchyear = 2017 and appid = 651 and groupid = 8651
GO
delete from [dbo].[SU_StructureFile] where groupid = 9651
GO
Insert into [dbo].[SU_StructureFile](GroupID, SubGroupID, ICDType, TableName, FieldAlias, FieldName, FieldID, Occurrence, RecodeID, TextPosition, TextLength, TextFormat)
select GroupID = 9651, SubGroupID, ICDType, TableName, FieldAlias, FieldName, FieldID, Occurrence, RecodeID, TextPosition, TextLength, TextFormat
from [dbo].[SU_StructureFile]
where groupid = 8651
order by textposition
GO
Update [dbo].[SU_StructureFile] set TextPosition = Textposition + 3 where GroupID = 9651 and textposition >= 512
GO
Insert into [dbo].[SU_StructureFile](GroupID, SubGroupID, ICDType, TableName, FieldAlias, FieldName, FieldID, Occurrence, RecodeID, TextPosition, TextLength, TextFormat)
values(9651, 26, 'T', 'OMHRS_SecS', 'Total Days in Alternative Level of Care', 'X75', 8313, 0, 0, 512, 3, 0)
GO
--IAR Full Admission
Update dbo.SU_StructureList set GroupID = 800041 where batchyear = 2017 and appid = 650 and groupid = 800031
GO
delete from [dbo].[SU_StructureFile] where groupid = 800041
GO
Insert into [dbo].[SU_StructureFile](GroupID, SubGroupID, ICDType, TableName, FieldAlias, FieldName, FieldID, Occurrence, RecodeID, TextPosition, TextLength, TextFormat)
select GroupID = 800041, SubGroupID, ICDType, TableName, FieldAlias, FieldName, FieldID, Occurrence, RecodeID, TextPosition, TextLength, TextFormat
from [dbo].[SU_StructureFile]
where groupid = 800031
order by textposition
GO
Update [dbo].[SU_StructureFile] set TextPosition = Textposition + 3 where GroupID = 800041 and textposition >= 496
GO
Insert into [dbo].[SU_StructureFile](GroupID, SubGroupID, ICDType, TableName, FieldAlias, FieldName, FieldID, Occurrence, RecodeID, TextPosition, TextLength, TextFormat)
values(800041, 26, 'T', 'OMHRS_SecS', 'Total Days in Alternative Level of Care', 'X75', 8313, 0, 0, 496, 3, 0)
GO
--IAR Short Stay
Update dbo.SU_StructureList set GroupID = 800042 where batchyear = 2017 and appid = 651 and groupid = 800032
GO
delete from [dbo].[SU_StructureFile] where groupid = 800042
GO
Insert into [dbo].[SU_StructureFile](GroupID, SubGroupID, ICDType, TableName, FieldAlias, FieldName, FieldID, Occurrence, RecodeID, TextPosition, TextLength, TextFormat)
select GroupID = 800042, SubGroupID, ICDType, TableName, FieldAlias, FieldName, FieldID, Occurrence, RecodeID, TextPosition, TextLength, TextFormat
from [dbo].[SU_StructureFile]
where groupid = 800032
order by textposition
GO
Update [dbo].[SU_StructureFile] set TextPosition = Textposition + 3 where GroupID = 800042 and textposition >= 512
GO
Insert into [dbo].[SU_StructureFile](GroupID, SubGroupID, ICDType, TableName, FieldAlias, FieldName, FieldID, Occurrence, RecodeID, TextPosition, TextLength, TextFormat)
values(800042, 26, 'T', 'OMHRS_SecS', 'Total Days in Alternative Level of Care', 'X75', 8313, 0, 0, 512, 3, 0)
GO
--end build 1
if not exists (select * from information_schema.columns where table_name = 'ENGN_LogicalCondition' and column_name = 'MultipleIndex')
       alter table ENGN_LogicalCondition add [MultipleIndex] [int] null
go
--0771
if exists (select * from sysindexes where name = 'IX_CM_CPI_RegionalPatientID') 	
	drop index CM_CPI.IX_CM_CPI_RegionalPatientID
GO
create index IX_CM_CPI_RegionalPatientID on CM_CPI(RegionalPatientID)
GO
--0784 -   NRS RPG 2017-2018 EN
if exists (select * from information_schema.tables where table_name = 'zz2017RPG')
	drop table zz2017RPG
GO
create table zz2017RPG(
	RehabilitationGroup varchar(50), 
	RPG varchar(50), 
	RCW_Typical_2017 varchar(50), 
	Short_Stay_Trim_2017 varchar(50), 
	Short_Stay_PDRCW_2017 varchar(50), 
	Long_Stay_Trim_2017 varchar(50), 
	Long_Stay_PDRCW_2017 varchar(50)
)
GO
insert into zz2017RPG(RehabilitationGroup, RPG, RCW_Typical_2017, Short_Stay_Trim_2017, Short_Stay_PDRCW_2017, Long_Stay_Trim_2017, Long_Stay_PDRCW_2017) values('Stroke', '1100', '2.5405', '4', '0.0555', '150', '0.0454')
GO
insert into zz2017RPG(RehabilitationGroup, RPG, RCW_Typical_2017, Short_Stay_Trim_2017, Short_Stay_PDRCW_2017, Long_Stay_Trim_2017, Long_Stay_PDRCW_2017) values('Stroke', '1110', '2.0681', '4', '0.0555', '133', '0.0454')
GO
insert into zz2017RPG(RehabilitationGroup, RPG, RCW_Typical_2017, Short_Stay_Trim_2017, Short_Stay_PDRCW_2017, Long_Stay_Trim_2017, Long_Stay_PDRCW_2017) values('Stroke', '1120', '1.4077', '4', '0.0555', '75', '0.0454')
GO
insert into zz2017RPG(RehabilitationGroup, RPG, RCW_Typical_2017, Short_Stay_Trim_2017, Short_Stay_PDRCW_2017, Long_Stay_Trim_2017, Long_Stay_PDRCW_2017) values('Stroke', '1130', '1.2422', '4', '0.0555', '70', '0.0454')
GO
insert into zz2017RPG(RehabilitationGroup, RPG, RCW_Typical_2017, Short_Stay_Trim_2017, Short_Stay_PDRCW_2017, Long_Stay_Trim_2017, Long_Stay_PDRCW_2017) values('Stroke', '1140', '0.9209', '3', '0.0555', '50', '0.0454')
GO
insert into zz2017RPG(RehabilitationGroup, RPG, RCW_Typical_2017, Short_Stay_Trim_2017, Short_Stay_PDRCW_2017, Long_Stay_Trim_2017, Long_Stay_PDRCW_2017) values('Stroke', '1150', '0.8009', '3', '0.0555', '51', '0.0454')
GO
insert into zz2017RPG(RehabilitationGroup, RPG, RCW_Typical_2017, Short_Stay_Trim_2017, Short_Stay_PDRCW_2017, Long_Stay_Trim_2017, Long_Stay_PDRCW_2017) values('Stroke', '1160', '0.5612', '3', '0.0555', '30', '0.0454')
GO
insert into zz2017RPG(RehabilitationGroup, RPG, RCW_Typical_2017, Short_Stay_Trim_2017, Short_Stay_PDRCW_2017, Long_Stay_Trim_2017, Long_Stay_PDRCW_2017) values('Traumatic Brain Injury', '1200', '8.8637', '4', '0.0555', '333', '0.0543')
GO
insert into zz2017RPG(RehabilitationGroup, RPG, RCW_Typical_2017, Short_Stay_Trim_2017, Short_Stay_PDRCW_2017, Long_Stay_Trim_2017, Long_Stay_PDRCW_2017) values('Traumatic Brain Injury', '1210', '3.1188', '6', '0.0555', '220', '0.0543')
GO
insert into zz2017RPG(RehabilitationGroup, RPG, RCW_Typical_2017, Short_Stay_Trim_2017, Short_Stay_PDRCW_2017, Long_Stay_Trim_2017, Long_Stay_PDRCW_2017) values('Traumatic Brain Injury', '1220', '2.5713', '4', '0.0555', '245', '0.0543')
GO
insert into zz2017RPG(RehabilitationGroup, RPG, RCW_Typical_2017, Short_Stay_Trim_2017, Short_Stay_PDRCW_2017, Long_Stay_Trim_2017, Long_Stay_PDRCW_2017) values('Traumatic Brain Injury', '1230', '2.3163', '3', '0.0555', '168', '0.0543')
GO
insert into zz2017RPG(RehabilitationGroup, RPG, RCW_Typical_2017, Short_Stay_Trim_2017, Short_Stay_PDRCW_2017, Long_Stay_Trim_2017, Long_Stay_PDRCW_2017) values('Traumatic Brain Injury', '1240', '1.7688', '4', '0.0555', '108', '0.0543')
GO
insert into zz2017RPG(RehabilitationGroup, RPG, RCW_Typical_2017, Short_Stay_Trim_2017, Short_Stay_PDRCW_2017, Long_Stay_Trim_2017, Long_Stay_PDRCW_2017) values('Traumatic Brain Injury', '1250', '1.0185', '3', '0.0555', '69', '0.0543')
GO
insert into zz2017RPG(RehabilitationGroup, RPG, RCW_Typical_2017, Short_Stay_Trim_2017, Short_Stay_PDRCW_2017, Long_Stay_Trim_2017, Long_Stay_PDRCW_2017) values('Non-Traumatic Brain Injury', '1300', '2.7371', '4', '0.0555', '211', '0.0577')
GO
insert into zz2017RPG(RehabilitationGroup, RPG, RCW_Typical_2017, Short_Stay_Trim_2017, Short_Stay_PDRCW_2017, Long_Stay_Trim_2017, Long_Stay_PDRCW_2017) values('Non-Traumatic Brain Injury', '1310', '1.7926', '4', '0.0555', '113', '0.0577')
GO
insert into zz2017RPG(RehabilitationGroup, RPG, RCW_Typical_2017, Short_Stay_Trim_2017, Short_Stay_PDRCW_2017, Long_Stay_Trim_2017, Long_Stay_PDRCW_2017) values('Non-Traumatic Brain Injury', '1320', '1.4345', '5', '0.0555', '82', '0.0577')
GO
insert into zz2017RPG(RehabilitationGroup, RPG, RCW_Typical_2017, Short_Stay_Trim_2017, Short_Stay_PDRCW_2017, Long_Stay_Trim_2017, Long_Stay_PDRCW_2017) values('Non-Traumatic Brain Injury', '1330', '0.828', '3', '0.0555', '82', '0.0577')
GO
insert into zz2017RPG(RehabilitationGroup, RPG, RCW_Typical_2017, Short_Stay_Trim_2017, Short_Stay_PDRCW_2017, Long_Stay_Trim_2017, Long_Stay_PDRCW_2017) values('Neurological', '1400', '2.0068', '4', '0.0555', '173', '0.0445')
GO
insert into zz2017RPG(RehabilitationGroup, RPG, RCW_Typical_2017, Short_Stay_Trim_2017, Short_Stay_PDRCW_2017, Long_Stay_Trim_2017, Long_Stay_PDRCW_2017) values('Neurological', '1410', '1.4106', '5', '0.0555', '99', '0.0445')
GO
insert into zz2017RPG(RehabilitationGroup, RPG, RCW_Typical_2017, Short_Stay_Trim_2017, Short_Stay_PDRCW_2017, Long_Stay_Trim_2017, Long_Stay_PDRCW_2017) values('Neurological', '1420', '0.944', '3', '0.0555', '72', '0.0445')
GO
insert into zz2017RPG(RehabilitationGroup, RPG, RCW_Typical_2017, Short_Stay_Trim_2017, Short_Stay_PDRCW_2017, Long_Stay_Trim_2017, Long_Stay_PDRCW_2017) values('Neurological', '1430', '0.6182', '3', '0.0555', '62', '0.0445')
GO
insert into zz2017RPG(RehabilitationGroup, RPG, RCW_Typical_2017, Short_Stay_Trim_2017, Short_Stay_PDRCW_2017, Long_Stay_Trim_2017, Long_Stay_PDRCW_2017) values('Traumatic Spinal Cord Injury', '1500', '5.9089', '11', '0.0555', '346', '0.0484')
GO
insert into zz2017RPG(RehabilitationGroup, RPG, RCW_Typical_2017, Short_Stay_Trim_2017, Short_Stay_PDRCW_2017, Long_Stay_Trim_2017, Long_Stay_PDRCW_2017) values('Traumatic Spinal Cord Injury', '1510', '4.3431', '15', '0.0555', '265', '0.0484')
GO
insert into zz2017RPG(RehabilitationGroup, RPG, RCW_Typical_2017, Short_Stay_Trim_2017, Short_Stay_PDRCW_2017, Long_Stay_Trim_2017, Long_Stay_PDRCW_2017) values('Traumatic Spinal Cord Injury', '1520', '3.4387', '4', '0.0555', '171', '0.0484')
GO
insert into zz2017RPG(RehabilitationGroup, RPG, RCW_Typical_2017, Short_Stay_Trim_2017, Short_Stay_PDRCW_2017, Long_Stay_Trim_2017, Long_Stay_PDRCW_2017) values('Traumatic Spinal Cord Injury', '1530', '1.7597', '3', '0.0555', '91', '0.0484')
GO
insert into zz2017RPG(RehabilitationGroup, RPG, RCW_Typical_2017, Short_Stay_Trim_2017, Short_Stay_PDRCW_2017, Long_Stay_Trim_2017, Long_Stay_PDRCW_2017) values('Non-Traumatic Spinal Cord Injury', '1600', '3.7065', '6', '0.0555', '157', '0.0485')
GO
insert into zz2017RPG(RehabilitationGroup, RPG, RCW_Typical_2017, Short_Stay_Trim_2017, Short_Stay_PDRCW_2017, Long_Stay_Trim_2017, Long_Stay_PDRCW_2017) values('Non-Traumatic Spinal Cord Injury', '1610', '1.8419', '5', '0.0555', '122', '0.0485')
GO
insert into zz2017RPG(RehabilitationGroup, RPG, RCW_Typical_2017, Short_Stay_Trim_2017, Short_Stay_PDRCW_2017, Long_Stay_Trim_2017, Long_Stay_PDRCW_2017) values('Non-Traumatic Spinal Cord Injury', '1620', '2.4065', '4', '0.0555', '113', '0.0485')
GO
insert into zz2017RPG(RehabilitationGroup, RPG, RCW_Typical_2017, Short_Stay_Trim_2017, Short_Stay_PDRCW_2017, Long_Stay_Trim_2017, Long_Stay_PDRCW_2017) values('Non-Traumatic Spinal Cord Injury', '1630', '1.3428', '3', '0.0555', '77', '0.0485')
GO
insert into zz2017RPG(RehabilitationGroup, RPG, RCW_Typical_2017, Short_Stay_Trim_2017, Short_Stay_PDRCW_2017, Long_Stay_Trim_2017, Long_Stay_PDRCW_2017) values('Non-Traumatic Spinal Cord Injury', '1640', '0.9799', '3', '0.0555', '57', '0.0485')
GO
insert into zz2017RPG(RehabilitationGroup, RPG, RCW_Typical_2017, Short_Stay_Trim_2017, Short_Stay_PDRCW_2017, Long_Stay_Trim_2017, Long_Stay_PDRCW_2017) values('Amputation, Not Lower Extremity', '1700', '1.4066', '3', '0.0555', '146', '0.0426')
GO
insert into zz2017RPG(RehabilitationGroup, RPG, RCW_Typical_2017, Short_Stay_Trim_2017, Short_Stay_PDRCW_2017, Long_Stay_Trim_2017, Long_Stay_PDRCW_2017) values('Amputation, Not Lower Extremity', '1710', '1.5545', '5', '0.0555', '85', '0.0426')
GO
insert into zz2017RPG(RehabilitationGroup, RPG, RCW_Typical_2017, Short_Stay_Trim_2017, Short_Stay_PDRCW_2017, Long_Stay_Trim_2017, Long_Stay_PDRCW_2017) values('Amputation, Lower Extremity', '1800', '1.8732', '5', '0.0555', '123', '0.0437')
GO
insert into zz2017RPG(RehabilitationGroup, RPG, RCW_Typical_2017, Short_Stay_Trim_2017, Short_Stay_PDRCW_2017, Long_Stay_Trim_2017, Long_Stay_PDRCW_2017) values('Amputation, Lower Extremity', '1810', '1.503', '4', '0.0555', '86', '0.0437')
GO
insert into zz2017RPG(RehabilitationGroup, RPG, RCW_Typical_2017, Short_Stay_Trim_2017, Short_Stay_PDRCW_2017, Long_Stay_Trim_2017, Long_Stay_PDRCW_2017) values('Amputation, Lower Extremity', '1820', '1.3863', '7', '0.0555', '67', '0.0437')
GO
insert into zz2017RPG(RehabilitationGroup, RPG, RCW_Typical_2017, Short_Stay_Trim_2017, Short_Stay_PDRCW_2017, Long_Stay_Trim_2017, Long_Stay_PDRCW_2017) values('Amputation, Lower Extremity', '1830', '1.2781', '3', '0.0555', '63', '0.0437')
GO
insert into zz2017RPG(RehabilitationGroup, RPG, RCW_Typical_2017, Short_Stay_Trim_2017, Short_Stay_PDRCW_2017, Long_Stay_Trim_2017, Long_Stay_PDRCW_2017) values('Osteoarthritis', '1900', '0.9515', '3', '0.0555', '112', '0.0459')
GO
insert into zz2017RPG(RehabilitationGroup, RPG, RCW_Typical_2017, Short_Stay_Trim_2017, Short_Stay_PDRCW_2017, Long_Stay_Trim_2017, Long_Stay_PDRCW_2017) values('Osteoarthritis', '1910', '0.7219', '3', '0.0555', '32', '0.0459')
GO
insert into zz2017RPG(RehabilitationGroup, RPG, RCW_Typical_2017, Short_Stay_Trim_2017, Short_Stay_PDRCW_2017, Long_Stay_Trim_2017, Long_Stay_PDRCW_2017) values('Rheumatoid arthritis and Other Arthritis', '2000', '0.9381', '3', '0.0555', '74', '0.0461')
GO
insert into zz2017RPG(RehabilitationGroup, RPG, RCW_Typical_2017, Short_Stay_Trim_2017, Short_Stay_PDRCW_2017, Long_Stay_Trim_2017, Long_Stay_PDRCW_2017) values('Rheumatoid arthritis and Other Arthritis', '2010', '0.7421', '3', '0.0555', '21', '0.0461')
GO
insert into zz2017RPG(RehabilitationGroup, RPG, RCW_Typical_2017, Short_Stay_Trim_2017, Short_Stay_PDRCW_2017, Long_Stay_Trim_2017, Long_Stay_PDRCW_2017) values('Pain', '2100', '0.9077', '3', '0.0555', '67', '0.0402')
GO
insert into zz2017RPG(RehabilitationGroup, RPG, RCW_Typical_2017, Short_Stay_Trim_2017, Short_Stay_PDRCW_2017, Long_Stay_Trim_2017, Long_Stay_PDRCW_2017) values('Pain', '2110', '0.5806', '3', '0.0555', '23', '0.0402')
GO
insert into zz2017RPG(RehabilitationGroup, RPG, RCW_Typical_2017, Short_Stay_Trim_2017, Short_Stay_PDRCW_2017, Long_Stay_Trim_2017, Long_Stay_PDRCW_2017) values('Fracture of Lower Extremity', '2200', '1.1853', '4', '0.0555', '88', '0.0382')
GO
insert into zz2017RPG(RehabilitationGroup, RPG, RCW_Typical_2017, Short_Stay_Trim_2017, Short_Stay_PDRCW_2017, Long_Stay_Trim_2017, Long_Stay_PDRCW_2017) values('Fracture of Lower Extremity', '2210', '1.267', '3', '0.0555', '97', '0.0382')
GO
insert into zz2017RPG(RehabilitationGroup, RPG, RCW_Typical_2017, Short_Stay_Trim_2017, Short_Stay_PDRCW_2017, Long_Stay_Trim_2017, Long_Stay_PDRCW_2017) values('Fracture of Lower Extremity', '2220', '0.9776', '4', '0.0555', '74', '0.0382')
GO
insert into zz2017RPG(RehabilitationGroup, RPG, RCW_Typical_2017, Short_Stay_Trim_2017, Short_Stay_PDRCW_2017, Long_Stay_Trim_2017, Long_Stay_PDRCW_2017) values('Fracture of Lower Extremity', '2230', '0.9016', '5', '0.0555', '56', '0.0382')
GO
insert into zz2017RPG(RehabilitationGroup, RPG, RCW_Typical_2017, Short_Stay_Trim_2017, Short_Stay_PDRCW_2017, Long_Stay_Trim_2017, Long_Stay_PDRCW_2017) values('Fracture of Lower Extremity', '2240', '0.757', '3', '0.0555', '42', '0.0382')
GO
insert into zz2017RPG(RehabilitationGroup, RPG, RCW_Typical_2017, Short_Stay_Trim_2017, Short_Stay_PDRCW_2017, Long_Stay_Trim_2017, Long_Stay_PDRCW_2017) values('Fracture of Lower Extremity', '2250', '0.6462', '3', '0.0555', '39', '0.0382')
GO
insert into zz2017RPG(RehabilitationGroup, RPG, RCW_Typical_2017, Short_Stay_Trim_2017, Short_Stay_PDRCW_2017, Long_Stay_Trim_2017, Long_Stay_PDRCW_2017) values('Replacement of Lower Extremity', '2300', '0.8601', '3', '0.0555', '64', '0.0382')
GO
insert into zz2017RPG(RehabilitationGroup, RPG, RCW_Typical_2017, Short_Stay_Trim_2017, Short_Stay_PDRCW_2017, Long_Stay_Trim_2017, Long_Stay_PDRCW_2017) values('Replacement of Lower Extremity', '2310', '0.5746', '3', '0.0555', '48', '0.0382')
GO
insert into zz2017RPG(RehabilitationGroup, RPG, RCW_Typical_2017, Short_Stay_Trim_2017, Short_Stay_PDRCW_2017, Long_Stay_Trim_2017, Long_Stay_PDRCW_2017) values('Replacement of Lower Extremity', '2320', '0.6054', '3', '0.0555', '44', '0.0382')
GO
insert into zz2017RPG(RehabilitationGroup, RPG, RCW_Typical_2017, Short_Stay_Trim_2017, Short_Stay_PDRCW_2017, Long_Stay_Trim_2017, Long_Stay_PDRCW_2017) values('Replacement of Lower Extremity', '2330', '0.4477', '3', '0.0555', '28', '0.0382')
GO
insert into zz2017RPG(RehabilitationGroup, RPG, RCW_Typical_2017, Short_Stay_Trim_2017, Short_Stay_PDRCW_2017, Long_Stay_Trim_2017, Long_Stay_PDRCW_2017) values('Replacement of Lower Extremity', '2340', '0.4143', '3', '0.0555', '26', '0.0382')
GO
insert into zz2017RPG(RehabilitationGroup, RPG, RCW_Typical_2017, Short_Stay_Trim_2017, Short_Stay_PDRCW_2017, Long_Stay_Trim_2017, Long_Stay_PDRCW_2017) values('Replacement of Lower Extremity', '2350', '0.4127', '3', '0.0555', '25', '0.0382')
GO
insert into zz2017RPG(RehabilitationGroup, RPG, RCW_Typical_2017, Short_Stay_Trim_2017, Short_Stay_PDRCW_2017, Long_Stay_Trim_2017, Long_Stay_PDRCW_2017) values('Other Orthopedic', '2400', '1.1596', '3', '0.0555', '88', '0.041')
GO
insert into zz2017RPG(RehabilitationGroup, RPG, RCW_Typical_2017, Short_Stay_Trim_2017, Short_Stay_PDRCW_2017, Long_Stay_Trim_2017, Long_Stay_PDRCW_2017) values('Other Orthopedic', '2410', '0.9606', '3', '0.0555', '85', '0.041')
GO
insert into zz2017RPG(RehabilitationGroup, RPG, RCW_Typical_2017, Short_Stay_Trim_2017, Short_Stay_PDRCW_2017, Long_Stay_Trim_2017, Long_Stay_PDRCW_2017) values('Other Orthopedic', '2420', '0.8196', '4', '0.0555', '63', '0.041')
GO
insert into zz2017RPG(RehabilitationGroup, RPG, RCW_Typical_2017, Short_Stay_Trim_2017, Short_Stay_PDRCW_2017, Long_Stay_Trim_2017, Long_Stay_PDRCW_2017) values('Other Orthopedic', '2430', '0.6472', '3', '0.0555', '55', '0.041')
GO
insert into zz2017RPG(RehabilitationGroup, RPG, RCW_Typical_2017, Short_Stay_Trim_2017, Short_Stay_PDRCW_2017, Long_Stay_Trim_2017, Long_Stay_PDRCW_2017) values('Other Orthopedic', '2440', '0.5606', '3', '0.0555', '42', '0.041')
GO
insert into zz2017RPG(RehabilitationGroup, RPG, RCW_Typical_2017, Short_Stay_Trim_2017, Short_Stay_PDRCW_2017, Long_Stay_Trim_2017, Long_Stay_PDRCW_2017) values('Cardiac', '2500', '1.0592', '3', '0.0555', '84', '0.0389')
GO
insert into zz2017RPG(RehabilitationGroup, RPG, RCW_Typical_2017, Short_Stay_Trim_2017, Short_Stay_PDRCW_2017, Long_Stay_Trim_2017, Long_Stay_PDRCW_2017) values('Cardiac', '2510', '0.8626', '3', '0.0555', '77', '0.0389')
GO
insert into zz2017RPG(RehabilitationGroup, RPG, RCW_Typical_2017, Short_Stay_Trim_2017, Short_Stay_PDRCW_2017, Long_Stay_Trim_2017, Long_Stay_PDRCW_2017) values('Cardiac', '2520', '0.5043', '3', '0.0555', '39', '0.0389')
GO
insert into zz2017RPG(RehabilitationGroup, RPG, RCW_Typical_2017, Short_Stay_Trim_2017, Short_Stay_PDRCW_2017, Long_Stay_Trim_2017, Long_Stay_PDRCW_2017) values('Cardiac', '2530', '0.4508', '3', '0.0555', '32', '0.0389')
GO
insert into zz2017RPG(RehabilitationGroup, RPG, RCW_Typical_2017, Short_Stay_Trim_2017, Short_Stay_PDRCW_2017, Long_Stay_Trim_2017, Long_Stay_PDRCW_2017) values('Cardiac', '2540', '0.6047', '3', '0.0555', '47', '0.0389')
GO
insert into zz2017RPG(RehabilitationGroup, RPG, RCW_Typical_2017, Short_Stay_Trim_2017, Short_Stay_PDRCW_2017, Long_Stay_Trim_2017, Long_Stay_PDRCW_2017) values('Pulmonary', '2600', '1.019', '4', '0.0555', '85', '0.0331')
GO
insert into zz2017RPG(RehabilitationGroup, RPG, RCW_Typical_2017, Short_Stay_Trim_2017, Short_Stay_PDRCW_2017, Long_Stay_Trim_2017, Long_Stay_PDRCW_2017) values('Pulmonary', '2610', '0.7672', '3', '0.0555', '53', '0.0331')
GO
insert into zz2017RPG(RehabilitationGroup, RPG, RCW_Typical_2017, Short_Stay_Trim_2017, Short_Stay_PDRCW_2017, Long_Stay_Trim_2017, Long_Stay_PDRCW_2017) values('Pulmonary', '2620', '0.9417', '3', '0.0555', '72', '0.0331')
GO
insert into zz2017RPG(RehabilitationGroup, RPG, RCW_Typical_2017, Short_Stay_Trim_2017, Short_Stay_PDRCW_2017, Long_Stay_Trim_2017, Long_Stay_PDRCW_2017) values('Pulmonary', '2630', '0.9931', '3', '0.0555', '67', '0.0331')
GO
insert into zz2017RPG(RehabilitationGroup, RPG, RCW_Typical_2017, Short_Stay_Trim_2017, Short_Stay_PDRCW_2017, Long_Stay_Trim_2017, Long_Stay_PDRCW_2017) values('Burns', '2700', '1.375', '3', '0.0555', '111', '0.0241')
GO
insert into zz2017RPG(RehabilitationGroup, RPG, RCW_Typical_2017, Short_Stay_Trim_2017, Short_Stay_PDRCW_2017, Long_Stay_Trim_2017, Long_Stay_PDRCW_2017) values('Maj Mult Trauma, Oth Mult Trauma & Maj Mult Frac', '2800', '2.0823', '3', '0.0555', '137', '0.0384')
GO
insert into zz2017RPG(RehabilitationGroup, RPG, RCW_Typical_2017, Short_Stay_Trim_2017, Short_Stay_PDRCW_2017, Long_Stay_Trim_2017, Long_Stay_PDRCW_2017) values('Maj Mult Trauma, Oth Mult Trauma & Maj Mult Frac', '2810', '1.1451', '3', '0.0555', '95', '0.0384')
GO
insert into zz2017RPG(RehabilitationGroup, RPG, RCW_Typical_2017, Short_Stay_Trim_2017, Short_Stay_PDRCW_2017, Long_Stay_Trim_2017, Long_Stay_PDRCW_2017) values('Maj Mult Trauma, Oth Mult Trauma & Maj Mult Frac', '2820', '1.4292', '5', '0.0555', '95', '0.0384')
GO
insert into zz2017RPG(RehabilitationGroup, RPG, RCW_Typical_2017, Short_Stay_Trim_2017, Short_Stay_PDRCW_2017, Long_Stay_Trim_2017, Long_Stay_PDRCW_2017) values('Maj Mult Trauma, Oth Mult Trauma & Maj Mult Frac', '2830', '1.0173', '5', '0.0555', '79', '0.0384')
GO
insert into zz2017RPG(RehabilitationGroup, RPG, RCW_Typical_2017, Short_Stay_Trim_2017, Short_Stay_PDRCW_2017, Long_Stay_Trim_2017, Long_Stay_PDRCW_2017) values('Maj Mult Trauma, Oth Mult Trauma & Maj Mult Frac', '2840', '0.7603', '3', '0.0555', '77', '0.0384')
GO
insert into zz2017RPG(RehabilitationGroup, RPG, RCW_Typical_2017, Short_Stay_Trim_2017, Short_Stay_PDRCW_2017, Long_Stay_Trim_2017, Long_Stay_PDRCW_2017) values('Maj Mult Trauma with Brain or Spinal Cord Injury', '2900', '3.5241', '6', '0.0555', '174', '0.0549')
GO
insert into zz2017RPG(RehabilitationGroup, RPG, RCW_Typical_2017, Short_Stay_Trim_2017, Short_Stay_PDRCW_2017, Long_Stay_Trim_2017, Long_Stay_PDRCW_2017) values('Maj Mult Trauma with Brain or Spinal Cord Injury', '2910', '1.3273', '5', '0.0555', '135', '0.0549')
GO
insert into zz2017RPG(RehabilitationGroup, RPG, RCW_Typical_2017, Short_Stay_Trim_2017, Short_Stay_PDRCW_2017, Long_Stay_Trim_2017, Long_Stay_PDRCW_2017) values('Maj Mult Trauma with Brain or Spinal Cord Injury', '2920', '0.6595', '3', '0.0555', '49', '0.0549')
GO
insert into zz2017RPG(RehabilitationGroup, RPG, RCW_Typical_2017, Short_Stay_Trim_2017, Short_Stay_PDRCW_2017, Long_Stay_Trim_2017, Long_Stay_PDRCW_2017) values('Ventilator Dependent Respiratory Disorders', '3000', '0.8695', '3', '0.0555', '52', '0.0358')
GO
insert into zz2017RPG(RehabilitationGroup, RPG, RCW_Typical_2017, Short_Stay_Trim_2017, Short_Stay_PDRCW_2017, Long_Stay_Trim_2017, Long_Stay_PDRCW_2017) values('Other Disabilities', '3100', '1.1949', '4', '0.0555', '88', '0.0397')
GO
insert into zz2017RPG(RehabilitationGroup, RPG, RCW_Typical_2017, Short_Stay_Trim_2017, Short_Stay_PDRCW_2017, Long_Stay_Trim_2017, Long_Stay_PDRCW_2017) values('Other Disabilities', '3110', '0.891', '4', '0.0555', '59', '0.0397')
GO
insert into zz2017RPG(RehabilitationGroup, RPG, RCW_Typical_2017, Short_Stay_Trim_2017, Short_Stay_PDRCW_2017, Long_Stay_Trim_2017, Long_Stay_PDRCW_2017) values('Other Disabilities', '3120', '1.2025', '3', '0.0555', '138', '0.0397')
GO
insert into zz2017RPG(RehabilitationGroup, RPG, RCW_Typical_2017, Short_Stay_Trim_2017, Short_Stay_PDRCW_2017, Long_Stay_Trim_2017, Long_Stay_PDRCW_2017) values('Other Disabilities', '3130', '0.8095', '3', '0.0555', '55', '0.0397')
GO
insert into zz2017RPG(RehabilitationGroup, RPG, RCW_Typical_2017, Short_Stay_Trim_2017, Short_Stay_PDRCW_2017, Long_Stay_Trim_2017, Long_Stay_PDRCW_2017) values('Other Disabilities', '3140', '0.745', '3', '0.0555', '63', '0.0397')
GO
delete from [dbo].[RPG_RPW] where [FiscalYear] = 2017
GO
Insert into [dbo].[RPG_RPW](RPG, Weight, Trim, PDW, RG, FiscalYear, ShortStay, ShortTrim)
select RPG, RCW_Typical_2017, Long_Stay_Trim_2017, Long_Stay_PDRCW_2017, RehabilitationGroup, 2017, Short_Stay_PDRCW_2017, Short_Stay_Trim_2017
from zz2017RPG
GO
if exists (select * from information_schema.tables where table_name = 'zz2017RPG')
	drop table zz2017RPG
GO
--0746 -   Revised Manitoba - MH Facility Table
if exists (select * from information_schema.tables where table_name = 'zz2017MH')
	drop table zz2017MH
GO
create table zz2017MH(
	Inst varchar(10), 
	FacilityName varchar(100), 
	NameRevision varchar(100), 
	NewStatus varchar(100)
)
GO
insert into zz2017MH(Inst, FacilityName, NameRevision, NewStatus) values('0001', 'BRANDON GENERAL HOSPITAL', 'Brandon Regional Health Centre', 'Revise')
GO
insert into zz2017MH(Inst, FacilityName, NameRevision, NewStatus) values('0003', 'SALVATION ARM GRACE GENERAL HOSPITAL', 'Grace Hospital', 'Revise')
GO
insert into zz2017MH(Inst, FacilityName, NameRevision, NewStatus) values('0004', 'MISERICORDIA GENERAL HOSPITAL', 'Misericordia Health Centre', 'Revise')
GO
insert into zz2017MH(Inst, FacilityName, NameRevision, NewStatus) values('0005', 'ST. BONIFACE GENERAL HOSPITAL', 'St Boniface Hospital', 'Revise')
GO
insert into zz2017MH(Inst, FacilityName, NameRevision, NewStatus) values('0006', 'RIVERVIEW HEALTH CENTRE INC', '', 'Keep')
GO
insert into zz2017MH(Inst, FacilityName, NameRevision, NewStatus) values('0007', 'VICTORIA GENERAL HOSPITAL', '', 'Keep')
GO
insert into zz2017MH(Inst, FacilityName, NameRevision, NewStatus) values('0009', 'CONCORDIA HOSPITAL', '', 'Keep')
GO
insert into zz2017MH(Inst, FacilityName, NameRevision, NewStatus) values('0011', 'SEVEN OAKS GENERAL HOSPITAL', '', 'Keep')
GO
insert into zz2017MH(Inst, FacilityName, NameRevision, NewStatus) values('0013', 'CANCERCARE MANITOBA', 'Cancercare Manitoba - HSC', 'Revise')
GO
insert into zz2017MH(Inst, FacilityName, NameRevision, NewStatus) values('0014', 'MOUNT CARMEL CLINIC', '', 'Discontinue as of March 31, 2017')
GO
insert into zz2017MH(Inst, FacilityName, NameRevision, NewStatus) values('0016', 'HEALTH SCIENCES CENTRE', '', 'Keep')
GO
insert into zz2017MH(Inst, FacilityName, NameRevision, NewStatus) values('0017', 'REHABILITATION CENTRE FOR CHILDREN', '', 'Keep')
GO
insert into zz2017MH(Inst, FacilityName, NameRevision, NewStatus) values('0019', 'DEER LODGE CENTRE ET', 'Deer Lodge Centre', 'Revise')
GO
insert into zz2017MH(Inst, FacilityName, NameRevision, NewStatus) values('0020', 'MANITOBA ADOLESCENT TREATMENT CENTRE INC', '', 'Keep')
GO
insert into zz2017MH(Inst, FacilityName, NameRevision, NewStatus) values('0022', 'MANITOBA CANCER TREATMENT RESEARCH FDN', 'Cancercare Manitoba - SBH', 'Revise')
GO
insert into zz2017MH(Inst, FacilityName, NameRevision, NewStatus) values('0026', 'JOCELYN HOUSE INC.', '', 'Keep')
GO
insert into zz2017MH(Inst, FacilityName, NameRevision, NewStatus) values('0030', 'WESTERN SURGERY CENTRE', '', 'Keep')
GO
insert into zz2017MH(Inst, FacilityName, NameRevision, NewStatus) values('0031', 'WINNIPEG SURGICAL CENTRE', '', 'Keep')
GO
insert into zz2017MH(Inst, FacilityName, NameRevision, NewStatus) values('0036', 'PAN AM SURGICAL CENTRE', 'Pan Am Clinic', 'Revise')
GO
insert into zz2017MH(Inst, FacilityName, NameRevision, NewStatus) values('0044', 'COMMUNITY THERAPY SERVICES OF MANITOBA', '', 'Keep')
GO
insert into zz2017MH(Inst, FacilityName, NameRevision, NewStatus) values('0045', 'MANITOBA DEVELOPMENTAL CENTRE', '', 'Keep')
GO
insert into zz2017MH(Inst, FacilityName, NameRevision, NewStatus) values('0046', 'SELKIRK MENTAL HEALTH CENTRE', '', 'Keep')
GO
insert into zz2017MH(Inst, FacilityName, NameRevision, NewStatus) values('0052', 'LAC DU BONNE DISTRICT HEALTH CENTRE', '', 'Discontinue as of March 31, 2017')
GO
insert into zz2017MH(Inst, FacilityName, NameRevision, NewStatus) values('0062', 'CLINIQUE YOUVILLE   CLINIC', '', 'Discontinue as of March 31, 2017')
GO
insert into zz2017MH(Inst, FacilityName, NameRevision, NewStatus) values('0064', 'ST AMANT CENTRE', '', 'Keep')
GO
insert into zz2017MH(Inst, FacilityName, NameRevision, NewStatus) values('0102', 'ALTONA COMMUNITY MEMORIAL HEALTH CENTRE', '', 'Keep')
GO
insert into zz2017MH(Inst, FacilityName, NameRevision, NewStatus) values('0103', 'ARBORG AND DISTRICTS HEALTH CENTRE', '', 'Keep')
GO
insert into zz2017MH(Inst, FacilityName, NameRevision, NewStatus) values('0106', 'BALDUR DISTRICT HOSPITAL', '', 'Keep')
GO
insert into zz2017MH(Inst, FacilityName, NameRevision, NewStatus) values('0107', 'BEAUSEJOUR DISTRICT HOSPITAL', '', 'Keep')
GO
insert into zz2017MH(Inst, FacilityName, NameRevision, NewStatus) values('0108', 'BENITO HEALTH CENTRE', '', 'Discontinue as of March 31, 2017')
GO
insert into zz2017MH(Inst, FacilityName, NameRevision, NewStatus) values('0110', 'BETHESDA HOSPITAL', 'Bethesda Hospital - Steinbach', 'Revise')
GO
insert into zz2017MH(Inst, FacilityName, NameRevision, NewStatus) values('0111', 'SNOW LAKE MEDICAL NURSING UNIT', '', 'Keep')
GO
insert into zz2017MH(Inst, FacilityName, NameRevision, NewStatus) values('0113', 'BOISSEVAIN HEALTH CENTRE', '', 'Keep')
GO
insert into zz2017MH(Inst, FacilityName, NameRevision, NewStatus) values('0114', 'BOUNDARY TRAILS HEALTH CENTRE', '', 'Keep')
GO
insert into zz2017MH(Inst, FacilityName, NameRevision, NewStatus) values('0115', 'RIVER EAST ACCESS CENTER', '', 'Keep')
GO
insert into zz2017MH(Inst, FacilityName, NameRevision, NewStatus) values('0116', 'CARMAN MEMORIAL HOSPITAL', '', 'Keep')
GO
insert into zz2017MH(Inst, FacilityName, NameRevision, NewStatus) values('0117', 'DAVIDSON MEMORIAL HEALTH CENTRE', '', 'Keep')
GO
insert into zz2017MH(Inst, FacilityName, NameRevision, NewStatus) values('0118', 'WINNIPEGOSIS & DISTRICT HEALTH CENTRE', '', 'Keep')
GO
insert into zz2017MH(Inst, FacilityName, NameRevision, NewStatus) values('0119', 'ROCK LAKE HEALTH DISTRICT', 'Rock Lake Health District - Crystal City', 'Revise')
GO
insert into zz2017MH(Inst, FacilityName, NameRevision, NewStatus) values('0120', 'ADDICTIONS FOUNDATION OF MANITOBA', '', 'Keep')
GO
insert into zz2017MH(Inst, FacilityName, NameRevision, NewStatus) values('0122', 'DAUPHIN GENERAL HOSPITAL', '', 'Keep')
GO
insert into zz2017MH(Inst, FacilityName, NameRevision, NewStatus) values('0123', 'SOUTH WEST HEALTH CENTRE', '', 'Keep')
GO
insert into zz2017MH(Inst, FacilityName, NameRevision, NewStatus) values('0124', 'CENTRE-MEDICO-SOCIAL DESALABERRY DIST.HC', 'DeSalaberry Health Centre - St Pierre-Jolys', 'Revise')
GO
insert into zz2017MH(Inst, FacilityName, NameRevision, NewStatus) values('0125', 'EDEN MENTAL HEALTH  CENTRE', '', 'Keep')
GO
insert into zz2017MH(Inst, FacilityName, NameRevision, NewStatus) values('0128', 'LAKESHORE DIST.HEALTH SYS-ERIKSDALE HOSP', 'E M Crowe Memorial Hospital - Eriksdale', 'Revise')
GO
insert into zz2017MH(Inst, FacilityName, NameRevision, NewStatus) values('0129', 'ERICKSON DISTRICT HEALTH CENTRE', '', 'Keep')
GO
insert into zz2017MH(Inst, FacilityName, NameRevision, NewStatus) values('0130', 'CHURCHILL HEALTH CENTRE', '', 'Keep')
GO
insert into zz2017MH(Inst, FacilityName, NameRevision, NewStatus) values('0131', 'EMERSON HOSPITAL', '', 'Keep')
GO
insert into zz2017MH(Inst, FacilityName, NameRevision, NewStatus) values('0134', 'FLIN FLON GENERAL HOSPITAL', '', 'Keep')
GO
insert into zz2017MH(Inst, FacilityName, NameRevision, NewStatus) values('0135', 'CARBERRY PLAINS HEALTH FACILITY', '', 'Keep')
GO
insert into zz2017MH(Inst, FacilityName, NameRevision, NewStatus) values('0136', 'GILLAM HOSPITAL INC', '', 'Keep')
GO
insert into zz2017MH(Inst, FacilityName, NameRevision, NewStatus) values('0138', 'GLADSTONE DISTRICT HOSPITAL', 'Gladstone Health Centre', 'Revise')
GO
insert into zz2017MH(Inst, FacilityName, NameRevision, NewStatus) values('0139', 'GLENBORO DISTRICT HOSPITAL', '', 'Keep')
GO
insert into zz2017MH(Inst, FacilityName, NameRevision, NewStatus) values('0140', 'GRANDVIEW DISTRICT HOSPITAL', '', 'Keep')
GO
insert into zz2017MH(Inst, FacilityName, NameRevision, NewStatus) values('0143', 'HAMIOTA DISTRICT HEALTH CENTRE', '', 'Keep')
GO
insert into zz2017MH(Inst, FacilityName, NameRevision, NewStatus) values('0144', 'HUNTER MEMORIAL HOSPITAL', '', 'Keep')
GO
insert into zz2017MH(Inst, FacilityName, NameRevision, NewStatus) values('0146', 'JOHNSON MEMORIAL HOSPITAL', '', 'Keep')
GO
insert into zz2017MH(Inst, FacilityName, NameRevision, NewStatus) values('0147', 'LORNE MEMORIAL HOSPITAL', 'Lorne Memorial Hospital - Swan Lake', 'Revise')
GO
insert into zz2017MH(Inst, FacilityName, NameRevision, NewStatus) values('0148', 'KILLARNEY   DISTRICT HOSPITAL', 'Tri-Lake Health Centre - Killarney', 'Revise')
GO
insert into zz2017MH(Inst, FacilityName, NameRevision, NewStatus) values('0149', 'LYNN LAKE HOSPITAL', '', 'Keep')
GO
insert into zz2017MH(Inst, FacilityName, NameRevision, NewStatus) values('0150', 'MCCREARY ALONSA HEALTH CENTRE', '', 'Keep')
GO
insert into zz2017MH(Inst, FacilityName, NameRevision, NewStatus) values('0151', 'PEMBINA MANITOU HOSPITAL', '', 'Keep')
GO
insert into zz2017MH(Inst, FacilityName, NameRevision, NewStatus) values('0152', 'MINNEDOSA DISTRICT HOSPITAL', '', 'Keep')
GO
insert into zz2017MH(Inst, FacilityName, NameRevision, NewStatus) values('0154', 'MORRIS DISTRICT HOSPITAL', '', 'Keep')
GO
insert into zz2017MH(Inst, FacilityName, NameRevision, NewStatus) values('0155', 'LEAF RAPIDS HEALTH CENTRE', '', 'Keep')
GO
insert into zz2017MH(Inst, FacilityName, NameRevision, NewStatus) values('0156', 'NORTH NORFOLK-MACGREGOR MED NURSING UNIT', '', 'Keep')
GO
insert into zz2017MH(Inst, FacilityName, NameRevision, NewStatus) values('0158', 'NEEPAWA HOSPITAL DISTRICT NO. 9', '', 'Keep')
GO
insert into zz2017MH(Inst, FacilityName, NameRevision, NewStatus) values('0159', 'NOTRE DAME MEDICAL NURSING UNIT', 'Centre de Santé-Notre-Dame Health Centre', 'Revise')
GO
insert into zz2017MH(Inst, FacilityName, NameRevision, NewStatus) values('0161', 'PINE FALLS GENERAL HOSPITAL', '', 'Keep')
GO
insert into zz2017MH(Inst, FacilityName, NameRevision, NewStatus) values('0162', 'PORTAGE DISTRICT GENERAL HOSPITAL', '', 'Keep')
GO
insert into zz2017MH(Inst, FacilityName, NameRevision, NewStatus) values('0163', 'PINAWA HOSPITAL', '', 'Keep')
GO
insert into zz2017MH(Inst, FacilityName, NameRevision, NewStatus) values('0164', 'RESTON COMMUNITY HOSPITAL', '', 'Keep')
GO
insert into zz2017MH(Inst, FacilityName, NameRevision, NewStatus) values('0165', 'ROBLIN DISTRICT HEALTH CENTRE', '', 'Keep')
GO
insert into zz2017MH(Inst, FacilityName, NameRevision, NewStatus) values('0166', 'RIVERDALE HEALTH SERVICES DISTRICT', '', 'Keep')
GO
insert into zz2017MH(Inst, FacilityName, NameRevision, NewStatus) values('0167', 'ROSSBURN DISTRICT HEALTH CENTRE', '', 'Keep')
GO
insert into zz2017MH(Inst, FacilityName, NameRevision, NewStatus) values('0169', 'RUSSELL DISTRICT HOSPITAL', '', 'Keep')
GO
insert into zz2017MH(Inst, FacilityName, NameRevision, NewStatus) values('0170', 'THE PAS HEALTH COMPLEX', 'St Anthony''s Hospital - The Pas', 'Revise')
GO
insert into zz2017MH(Inst, FacilityName, NameRevision, NewStatus) values('0171', 'BIRTLE HEALTH SERVICES DISTRICT', '', 'Keep')
GO
insert into zz2017MH(Inst, FacilityName, NameRevision, NewStatus) values('0172', 'STE. ROSE GENERAL HOSPITAL', '', 'Keep')
GO
insert into zz2017MH(Inst, FacilityName, NameRevision, NewStatus) values('0173', 'SELKIRK & DISTRICT GENERAL HOSPITAL', '', 'Keep')
GO
insert into zz2017MH(Inst, FacilityName, NameRevision, NewStatus) values('0174', 'SHOAL LAKE-STRATHCLAIR   HEALTH CENTRE', '', 'Keep')
GO
insert into zz2017MH(Inst, FacilityName, NameRevision, NewStatus) values('0175', 'SOURIS DISTRICT HOSPITAL', '', 'Keep')
GO
insert into zz2017MH(Inst, FacilityName, NameRevision, NewStatus) values('0176', 'STONEWALL & DISTRICT HEALTH CENTRE', '', 'Keep')
GO
insert into zz2017MH(Inst, FacilityName, NameRevision, NewStatus) values('0177', 'SWAN RIVER VALLEY HOSPITAL', 'Swan Valley Health Centre - Swan River', 'Revise')
GO
insert into zz2017MH(Inst, FacilityName, NameRevision, NewStatus) values('0178', 'LAKESHORE DIST.HEALTH SYS.-ASHERN HOSP.', 'Lakeshore General Hospital - Ashern', 'Revise')
GO
insert into zz2017MH(Inst, FacilityName, NameRevision, NewStatus) values('0179', 'HOPITAL STE ANNE HOSPITAL', '', 'Keep')
GO
insert into zz2017MH(Inst, FacilityName, NameRevision, NewStatus) values('0180', 'VIRDEN DISTRICT HOSPITAL', '', 'Keep')
GO
insert into zz2017MH(Inst, FacilityName, NameRevision, NewStatus) values('0181', 'VITA & DISTRICT HEALTH CENTRE', '', 'Keep')
GO
insert into zz2017MH(Inst, FacilityName, NameRevision, NewStatus) values('0182', 'STE CLAUDE HOSPITAL', '', 'Keep')
GO
insert into zz2017MH(Inst, FacilityName, NameRevision, NewStatus) values('0183', 'TIGER HILLS HEALTH DISTRICT', 'Tiger Hills Health Centre - Treherne', 'Revise')
GO
insert into zz2017MH(Inst, FacilityName, NameRevision, NewStatus) values('0184', 'MELITA HEALTH CENTRE', '', 'Keep')
GO
insert into zz2017MH(Inst, FacilityName, NameRevision, NewStatus) values('0185', 'WHITEMOUTH MEDICAL NURSING UNIT', '', 'Keep')
GO
insert into zz2017MH(Inst, FacilityName, NameRevision, NewStatus) values('0186', 'WAWANESA & DIST.MEMORIAL HEALTH CENTRE', 'Wawanesa Health Centre', 'Revise')
GO
insert into zz2017MH(Inst, FacilityName, NameRevision, NewStatus) values('0187', 'THOMPSON GENERAL HOSPITAL', '', 'Keep')
GO
insert into zz2017MH(Inst, FacilityName, NameRevision, NewStatus) values('0190', 'KLINIC INCORPORATED', '', 'Discontinue as of March 31, 2017')
GO
insert into zz2017MH(Inst, FacilityName, NameRevision, NewStatus) values('0191', 'MANITOBA CARDIAC INSTITUTE', 'Manitoba Cardiac Institute - RehFit', 'Revise')
GO
insert into zz2017MH(Inst, FacilityName, NameRevision, NewStatus) values('0194', 'THE MAIN STREET PROJECT (INC)', '', 'Discontinue as of March 31, 2017')
GO
insert into zz2017MH(Inst, FacilityName, NameRevision, NewStatus) values('0196', 'CHILD AND ADOLESCENT TREATMENT CENTRE', 'Child and Adolescent Treatment Centre - Brandon', 'Revise')
GO
insert into zz2017MH(Inst, FacilityName, NameRevision, NewStatus) values('0202', 'SHILO BASE HOSPITAL', '', 'Keep')
GO
insert into zz2017MH(Inst, FacilityName, NameRevision, NewStatus) values('0210', 'PERCY E.MOORE HOSPITAL', '', 'Keep')
GO
insert into zz2017MH(Inst, FacilityName, NameRevision, NewStatus) values('0212', 'NORWAY HOUSE HOSPITAL', '', 'Keep')
GO
insert into zz2017MH(Inst, FacilityName, NameRevision, NewStatus) values('0270', 'PAUINGASSI NURSING STATION', '', 'Keep')
GO
insert into zz2017MH(Inst, FacilityName, NameRevision, NewStatus) values('0271', 'CROSS LAKE NURSING STATION', '', 'Keep')
GO
insert into zz2017MH(Inst, FacilityName, NameRevision, NewStatus) values('0272', 'GOD''S LAKE NURSING STATION', '', 'Keep')
GO
insert into zz2017MH(Inst, FacilityName, NameRevision, NewStatus) values('0273', 'GARDEN HILL NURSING STATION', '', 'Keep')
GO
insert into zz2017MH(Inst, FacilityName, NameRevision, NewStatus) values('0274', 'LITTLE GRAND RAPIDS NURSING STATION', '', 'Keep')
GO
insert into zz2017MH(Inst, FacilityName, NameRevision, NewStatus) values('0277', 'NELSON HOUSE NURSING STATION', '', 'Keep')
GO
insert into zz2017MH(Inst, FacilityName, NameRevision, NewStatus) values('0278', 'OXFORD HOUSE NURSING STATION', '', 'Keep')
GO
insert into zz2017MH(Inst, FacilityName, NameRevision, NewStatus) values('0279', 'SPLIT LAKE NURSING STATION', '', 'Keep')
GO
insert into zz2017MH(Inst, FacilityName, NameRevision, NewStatus) values('0280', 'ST. THERESE POINT NURSING STATION', '', 'Keep')
GO
insert into zz2017MH(Inst, FacilityName, NameRevision, NewStatus) values('0281', 'PUKATAWAGAN NURSING STATION', '', 'Keep')
GO
insert into zz2017MH(Inst, FacilityName, NameRevision, NewStatus) values('0282', 'BROCHET NURSING STATION', '', 'Keep')
GO
insert into zz2017MH(Inst, FacilityName, NameRevision, NewStatus) values('0283', 'SOUTH INDIAN LAKE STATION', '', 'Keep')
GO
insert into zz2017MH(Inst, FacilityName, NameRevision, NewStatus) values('0284', 'SHAMATTAWA NURSING STATION', '', 'Keep')
GO
insert into zz2017MH(Inst, FacilityName, NameRevision, NewStatus) values('0285', 'POPLAR RIVER NURSING STATION', '', 'Keep')
GO
insert into zz2017MH(Inst, FacilityName, NameRevision, NewStatus) values('0286', 'WASSAGAMACK NURSING STATION', '', 'Keep')
GO
insert into zz2017MH(Inst, FacilityName, NameRevision, NewStatus) values('0287', 'BERENS RIVER NURSING STATION', '', 'Keep')
GO
insert into zz2017MH(Inst, FacilityName, NameRevision, NewStatus) values('0288', 'BLOODVEIN NURSING STATION', '', 'Keep')
GO
insert into zz2017MH(Inst, FacilityName, NameRevision, NewStatus) values('0289', 'RED SUCKER LAKE     NURSING STATION', '', 'Keep')
GO
insert into zz2017MH(Inst, FacilityName, NameRevision, NewStatus) values('0290', 'LAC BROCHET NURSING STATION', '', 'Keep')
GO
insert into zz2017MH(Inst, FacilityName, NameRevision, NewStatus) values('0291', 'GOD''S RIVER NURSING STATION', '', 'Keep')
GO
insert into zz2017MH(Inst, FacilityName, NameRevision, NewStatus) values('0292', 'TADOULE LAKE NURSING STATION', '', 'Keep')
GO
insert into zz2017MH(Inst, FacilityName, NameRevision, NewStatus) values('0293', 'YORK FACTORY NURSING STATION', 'York Landing Nursing Station', 'Revise')
GO
insert into zz2017MH(Inst, FacilityName, NameRevision, NewStatus) values('0299', 'WINNIPEG BIRTHING CENTRE (Birthing Centre)', 'Winnipeg Birth Centre', 'Revise')
GO
insert into zz2017MH(Inst, FacilityName, NameRevision, NewStatus) values('0304', 'EASTERVILLE NURSING STATION', '', 'Keep')
GO
insert into zz2017MH(Inst, FacilityName, NameRevision, NewStatus) values('0305', 'CORMORANT NURSING STATION', '', 'Keep')
GO
insert into zz2017MH(Inst, FacilityName, NameRevision, NewStatus) values('0306', 'MOOSE LAKE NURSING STATION', '', 'Keep')
GO
insert into zz2017MH(Inst, FacilityName, NameRevision, NewStatus) values('0390', 'BREAST HEALTH CENTRE', '', 'Keep')
GO
insert into zz2017MH(Inst, FacilityName, NameRevision, NewStatus) values('0391', 'ASSINIBOIA SURGICAL CENTRE', '', 'Keep')
GO
insert into zz2017MH(Inst, FacilityName, NameRevision, NewStatus) values('0398', 'MAPLES SURGICAL CENTRE', '', 'Keep')
GO
insert into zz2017MH(Inst, FacilityName, NameRevision, NewStatus) values('0400', 'LIONS PLACE', '', 'Keep')
GO
insert into zz2017MH(Inst, FacilityName, NameRevision, NewStatus) values('0501', 'RIDEAU PARK PERSONAL CARE HOME', '', 'Keep')
GO
insert into zz2017MH(Inst, FacilityName, NameRevision, NewStatus) values('0503', 'PEMBINA-MANITOU HEALTH CENTRE', '', 'Keep')
GO
insert into zz2017MH(Inst, FacilityName, NameRevision, NewStatus) values('0504', 'KIN-PLACE PERSONAL CARE HOME', '', 'Keep')
GO
insert into zz2017MH(Inst, FacilityName, NameRevision, NewStatus) values('0506', 'CALVARY PLACE PERSONAL CARE HOME', '', 'Keep')
GO
insert into zz2017MH(Inst, FacilityName, NameRevision, NewStatus) values('0507', 'COMPANION CARE', '', 'Keep')
GO
insert into zz2017MH(Inst, FacilityName, NameRevision, NewStatus) values('0508', 'BEACON HILL LODGE', '', 'Keep')
GO
insert into zz2017MH(Inst, FacilityName, NameRevision, NewStatus) values('0509', 'MISERICORDIA PLACE', '', 'Keep')
GO
insert into zz2017MH(Inst, FacilityName, NameRevision, NewStatus) values('0512', 'SOUTHEAST PERSONAL CARE HOME', '', 'Keep')
GO
insert into zz2017MH(Inst, FacilityName, NameRevision, NewStatus) values('0515', 'NORTHERN SPIRIT MANOR', '', 'Keep')
GO
insert into zz2017MH(Inst, FacilityName, NameRevision, NewStatus) values('0521', 'POSEIDON CARE CENTRE', '', 'Keep')
GO
insert into zz2017MH(Inst, FacilityName, NameRevision, NewStatus) values('0525', 'PARKVIEW PLACE CARE CENTRE', '', 'Keep')
GO
insert into zz2017MH(Inst, FacilityName, NameRevision, NewStatus) values('0529', 'VALLEYVIEW CARE CENTRE', '', 'Keep')
GO
insert into zz2017MH(Inst, FacilityName, NameRevision, NewStatus) values('0550', 'ERICKSON PERSONAL CARE HOME', '', 'Keep')
GO
insert into zz2017MH(Inst, FacilityName, NameRevision, NewStatus) values('0551', 'GOLDEN DOOR GERIATRIC CENTRE', '', 'Keep')
GO
insert into zz2017MH(Inst, FacilityName, NameRevision, NewStatus) values('0552', 'GILBERT PLAINS HEALTH CENTRE', '', 'Keep')
GO
insert into zz2017MH(Inst, FacilityName, NameRevision, NewStatus) values('0554', 'RESTON PERSONAL CARE HOME', '', 'Keep')
GO
insert into zz2017MH(Inst, FacilityName, NameRevision, NewStatus) values('0555', 'HERITAGE LODGE PERSONAL CARE HOME', '', 'Keep')
GO
insert into zz2017MH(Inst, FacilityName, NameRevision, NewStatus) values('0556', 'FISHER PERSONAL CARE HOME', '', 'Keep')
GO
insert into zz2017MH(Inst, FacilityName, NameRevision, NewStatus) values('0557', 'HILLCREST PLACE INC', '', 'Keep')
GO
insert into zz2017MH(Inst, FacilityName, NameRevision, NewStatus) values('0559', 'CHARLESWOOD CARE CENTRE', '', 'Keep')
GO
insert into zz2017MH(Inst, FacilityName, NameRevision, NewStatus) values('0564', 'KILDONAN PERSONAL CARE CENTRE INC.', '', 'Keep')
GO
insert into zz2017MH(Inst, FacilityName, NameRevision, NewStatus) values('0566', 'LAC DU BONNET PERSONAL CARE HOME', '', 'Keep')
GO
insert into zz2017MH(Inst, FacilityName, NameRevision, NewStatus) values('0568', 'MAPLES PERSONAL CARE HOME', '', 'Keep')
GO
insert into zz2017MH(Inst, FacilityName, NameRevision, NewStatus) values('0570', 'NISICHAWAYASIHK  (NELSON HOUSE)', '', 'Keep')
GO
insert into zz2017MH(Inst, FacilityName, NameRevision, NewStatus) values('0571', 'OAKVIEW PLACE', '', 'Keep')
GO
insert into zz2017MH(Inst, FacilityName, NameRevision, NewStatus) values('0573', 'CONCORDIA PLACE', '', 'Keep')
GO
insert into zz2017MH(Inst, FacilityName, NameRevision, NewStatus) values('0574', 'RIVER EAST PERSONAL CARE HOME LTD', '', 'Keep')
GO
insert into zz2017MH(Inst, FacilityName, NameRevision, NewStatus) values('0581', 'ST NORBERT PERSONAL CARE HOME', '', 'Keep')
GO
insert into zz2017MH(Inst, FacilityName, NameRevision, NewStatus) values('0583', 'RED RIVER PLACE', '', 'Keep')
GO
insert into zz2017MH(Inst, FacilityName, NameRevision, NewStatus) values('0584', 'HARTNEY PERSONAL CARE HOME', '', 'Keep')
GO
insert into zz2017MH(Inst, FacilityName, NameRevision, NewStatus) values('0587', 'RIVERVIEW HEALTH CENTRE', '', 'Keep')
GO
insert into zz2017MH(Inst, FacilityName, NameRevision, NewStatus) values('0589', 'BENITO HEALTH CENTRE', '', 'Keep')
GO
insert into zz2017MH(Inst, FacilityName, NameRevision, NewStatus) values('0591', 'ELKHORN PERSONAL CARE HOME', '', 'Keep')
GO
insert into zz2017MH(Inst, FacilityName, NameRevision, NewStatus) values('0592', 'DOUGLAS CAMPBELL LODGE', '', 'Keep')
GO
insert into zz2017MH(Inst, FacilityName, NameRevision, NewStatus) values('0593', 'TUDOR HOUSE P.C.H. HEALTH CORP.', '', 'Keep')
GO
insert into zz2017MH(Inst, FacilityName, NameRevision, NewStatus) values('0594', 'TUXEDO VILLA', '', 'Keep')
GO
insert into zz2017MH(Inst, FacilityName, NameRevision, NewStatus) values('0595', 'VISTA PARK LODGE', '', 'Keep')
GO
insert into zz2017MH(Inst, FacilityName, NameRevision, NewStatus) values('0596', 'WEST PARK MANOR PERSONAL CARE HOME', '', 'Keep')
GO
insert into zz2017MH(Inst, FacilityName, NameRevision, NewStatus) values('0597', 'SUNNYWOOD MANOR', '', 'Keep')
GO
insert into zz2017MH(Inst, FacilityName, NameRevision, NewStatus) values('0598', 'WHITEMOUTH DIST. HEALTH CENTRE', '', 'Keep')
GO
insert into zz2017MH(Inst, FacilityName, NameRevision, NewStatus) values('0599', 'DINSDALE PERSONAL CARE HOME', '', 'Keep')
GO
insert into zz2017MH(Inst, FacilityName, NameRevision, NewStatus) values('0600', 'ALTONA COMMUNITY MEMORIAL HEALTH CENTRE', '', 'Keep')
GO
insert into zz2017MH(Inst, FacilityName, NameRevision, NewStatus) values('0601', 'BAYSIDE PERSONAL CARE HOME INC', '', 'Keep')
GO
insert into zz2017MH(Inst, FacilityName, NameRevision, NewStatus) values('0602', 'BALDUR MANOR', '', 'Keep')
GO
insert into zz2017MH(Inst, FacilityName, NameRevision, NewStatus) values('0603', 'BETEL HOME FOUNDATION', '', 'Keep')
GO
insert into zz2017MH(Inst, FacilityName, NameRevision, NewStatus) values('0604', 'ASHERN PERSONAL CARE HOME', '', 'Keep')
GO
insert into zz2017MH(Inst, FacilityName, NameRevision, NewStatus) values('0605', 'BETEL HOME FOUNDATION', '', 'Keep')
GO
insert into zz2017MH(Inst, FacilityName, NameRevision, NewStatus) values('0606', 'ERIKSDALE PERSONAL CARE HOME', '', 'Keep')
GO
insert into zz2017MH(Inst, FacilityName, NameRevision, NewStatus) values('0607', 'BETHANIA MENNONITE P C HOME INC', '', 'Keep')
GO
insert into zz2017MH(Inst, FacilityName, NameRevision, NewStatus) values('0608', 'BIRTLE PERSONAL CARE HOME', '', 'Keep')
GO
insert into zz2017MH(Inst, FacilityName, NameRevision, NewStatus) values('0609', 'BETHESDA PLACE', '', 'Keep')
GO
insert into zz2017MH(Inst, FacilityName, NameRevision, NewStatus) values('0610', 'EVERGREEN PLACE', '', 'Keep')
GO
insert into zz2017MH(Inst, FacilityName, NameRevision, NewStatus) values('0611', 'BOYNE VALLEY LODGE', '', 'Keep')
GO
insert into zz2017MH(Inst, FacilityName, NameRevision, NewStatus) values('0612', 'LUNDAR PERSONAL CARE HOME', '', 'Keep')
GO
insert into zz2017MH(Inst, FacilityName, NameRevision, NewStatus) values('0613', 'BREN-DEL-WIN LOGDE PCH', '', 'Keep')
GO
insert into zz2017MH(Inst, FacilityName, NameRevision, NewStatus) values('0614', 'DAUPHIN PERSONAL CARE HOME INC.', '', 'Keep')
GO
insert into zz2017MH(Inst, FacilityName, NameRevision, NewStatus) values('0615', 'THE SAUL AND CLARIBEL SIMKIN CENTRE', '', 'Keep')
GO
insert into zz2017MH(Inst, FacilityName, NameRevision, NewStatus) values('0616', 'CARBERRY PERSONAL CARE   HOME', '', 'Keep')
GO
insert into zz2017MH(Inst, FacilityName, NameRevision, NewStatus) values('0617', 'THE CONVALESCENT HOME OF WINNIPEG', '', 'Keep')
GO
insert into zz2017MH(Inst, FacilityName, NameRevision, NewStatus) values('0618', 'DELWYNDA COURT PERSONAL CARE HOME', '', 'Keep')
GO
insert into zz2017MH(Inst, FacilityName, NameRevision, NewStatus) values('0619', 'DONWOOD MANOR PERSONAL CARE HOME INC', '', 'Keep')
GO
insert into zz2017MH(Inst, FacilityName, NameRevision, NewStatus) values('0620', 'EASTGATE LODGE', '', 'Keep')
GO
insert into zz2017MH(Inst, FacilityName, NameRevision, NewStatus) values('0621', 'COUNTRY MEADOWS', '', 'Keep')
GO
insert into zz2017MH(Inst, FacilityName, NameRevision, NewStatus) values('0622', 'FLIN FLON PERSONAL CARE HOME', '', 'Keep')
GO
insert into zz2017MH(Inst, FacilityName, NameRevision, NewStatus) values('0624', 'EMERSON PERSONAL CARE HOME', '', 'Keep')
GO
insert into zz2017MH(Inst, FacilityName, NameRevision, NewStatus) values('0625', 'FAIRVIEW HOME', '', 'Keep')
GO
insert into zz2017MH(Inst, FacilityName, NameRevision, NewStatus) values('0626', 'ACTION MARGUERITE (SAINT-VITAL) INC.', '', 'Keep')
GO
insert into zz2017MH(Inst, FacilityName, NameRevision, NewStatus) values('0627', 'RIVER PARK GARDENS', '', 'Keep')
GO
insert into zz2017MH(Inst, FacilityName, NameRevision, NewStatus) values('0628', 'FRED DOUGLAS LODGE', '', 'Keep')
GO
insert into zz2017MH(Inst, FacilityName, NameRevision, NewStatus) values('0629', 'FOYER NOTRE DAME', '', 'Keep')
GO
insert into zz2017MH(Inst, FacilityName, NameRevision, NewStatus) values('0631', 'GOODWIN LODGE', '', 'Keep')
GO
insert into zz2017MH(Inst, FacilityName, NameRevision, NewStatus) values('0632', 'GOLDEN WEST CENTENNIAL LODGE', '', 'Keep')
GO
insert into zz2017MH(Inst, FacilityName, NameRevision, NewStatus) values('0633', 'THE GRANDVIEW PERSONAL CARE HOME', '', 'Keep')
GO
insert into zz2017MH(Inst, FacilityName, NameRevision, NewStatus) values('0634', 'Heritage Lodge PCH (Niverville)', '', 'Keep')
GO
insert into zz2017MH(Inst, FacilityName, NameRevision, NewStatus) values('0635', 'HOLY FAMILY NURSING HOME', '', 'Keep')
GO
insert into zz2017MH(Inst, FacilityName, NameRevision, NewStatus) values('0636', 'PEMBINA PLACE MENNONITE P.C.H.', '', 'Keep')
GO
insert into zz2017MH(Inst, FacilityName, NameRevision, NewStatus) values('0638', 'DR GENDREAU PERSONAL CARE HOME', '', 'Keep')
GO
insert into zz2017MH(Inst, FacilityName, NameRevision, NewStatus) values('0639', 'LIONS PERSONAL CARE CENTRE', '', 'Keep')
GO
insert into zz2017MH(Inst, FacilityName, NameRevision, NewStatus) values('0640', 'LIONS PRAIRIE MANOR', '', 'Keep')
GO
insert into zz2017MH(Inst, FacilityName, NameRevision, NewStatus) values('0641', 'MCCREARY ALONSA HEALTH CENTRE PCH', '', 'Keep')
GO
insert into zz2017MH(Inst, FacilityName, NameRevision, NewStatus) values('0642', 'MEADOWOOD MANOR', '', 'Keep')
GO
insert into zz2017MH(Inst, FacilityName, NameRevision, NewStatus) values('0643', 'LUTHER HOME', '', 'Keep')
GO
insert into zz2017MH(Inst, FacilityName, NameRevision, NewStatus) values('0645', 'REPOS JOLYS', '', 'Keep')
GO
insert into zz2017MH(Inst, FacilityName, NameRevision, NewStatus) values('0646', 'MENNO HOME FOR THE AGED', '', 'Keep')
GO
insert into zz2017MH(Inst, FacilityName, NameRevision, NewStatus) values('0647', 'METRO KIWANIS COURTS', '', 'Keep')
GO
insert into zz2017MH(Inst, FacilityName, NameRevision, NewStatus) values('0648', 'MINNEDOSA PERSONAL CARE HOME', '', 'Keep')
GO
insert into zz2017MH(Inst, FacilityName, NameRevision, NewStatus) values('0649', 'THE MIDDLECHURCH HOME OF WINNIPEG', '', 'Keep')
GO
insert into zz2017MH(Inst, FacilityName, NameRevision, NewStatus) values('0650', 'SHOAL LAKE STRATHCLAIRE PCH', '', 'Keep')
GO
insert into zz2017MH(Inst, FacilityName, NameRevision, NewStatus) values('0652', 'MELITA PERSONAL CARE HOME', '', 'Keep')
GO
insert into zz2017MH(Inst, FacilityName, NameRevision, NewStatus) values('0653', 'NORTHERN LIGHTS MANOR', '', 'Keep')
GO
insert into zz2017MH(Inst, FacilityName, NameRevision, NewStatus) values('0654', 'ROCK LAKE PERSONAL CARE HOME', '', 'Keep')
GO
insert into zz2017MH(Inst, FacilityName, NameRevision, NewStatus) values('0656', 'PIONEER HEALTH SERVICES', '', 'Keep')
GO
insert into zz2017MH(Inst, FacilityName, NameRevision, NewStatus) values('0657', 'PARK MANOR PERSONAL CARE HOME INC.', '', 'Keep')
GO
insert into zz2017MH(Inst, FacilityName, NameRevision, NewStatus) values('0658', 'PINAOW WACHI LIMITED', 'Pinaow Wachi PCH - Norway House', 'Revise')
GO
insert into zz2017MH(Inst, FacilityName, NameRevision, NewStatus) values('0659', 'PRAIRIE VIEW LODGE', '', 'Keep')
GO
insert into zz2017MH(Inst, FacilityName, NameRevision, NewStatus) values('0661', 'RED RIVER VALLEY LODGE', '', 'Keep')
GO
insert into zz2017MH(Inst, FacilityName, NameRevision, NewStatus) values('0662', 'REST HAVEN NURSING HOME', '', 'Keep')
GO
insert into zz2017MH(Inst, FacilityName, NameRevision, NewStatus) values('0663', 'ROSEWOOD LODGE PCH', '', 'Keep')
GO
insert into zz2017MH(Inst, FacilityName, NameRevision, NewStatus) values('0664', 'RUSSELL PERSONAL CARE HOME', '', 'Keep')
GO
insert into zz2017MH(Inst, FacilityName, NameRevision, NewStatus) values('0666', 'CROCUS COURT PERSONAL CARE HOME', '', 'Keep')
GO
insert into zz2017MH(Inst, FacilityName, NameRevision, NewStatus) values('0667', 'ST JOSEPHS RESIDENCE', '', 'Keep')
GO
insert into zz2017MH(Inst, FacilityName, NameRevision, NewStatus) values('0669', 'ST PAUL''S HOME', '', 'Keep')
GO
insert into zz2017MH(Inst, FacilityName, NameRevision, NewStatus) values('0670', 'ST PAULS RESIDENCE', '', 'Keep')
GO
insert into zz2017MH(Inst, FacilityName, NameRevision, NewStatus) values('0671', 'RIVERS PERSONAL CARE HOME', '', 'Keep')
GO
insert into zz2017MH(Inst, FacilityName, NameRevision, NewStatus) values('0672', 'SALEM HOME,INC.', '', 'Keep')
GO
insert into zz2017MH(Inst, FacilityName, NameRevision, NewStatus) values('0674', 'ST CLAUDE PAVILION', '', 'Keep')
GO
insert into zz2017MH(Inst, FacilityName, NameRevision, NewStatus) values('0677', 'HAMIOTA PERSONAL CARE HOME', '', 'Keep')
GO
insert into zz2017MH(Inst, FacilityName, NameRevision, NewStatus) values('0678', 'SANDY LAKE PERSONAL CARE HOME', '', 'Keep')
GO
insert into zz2017MH(Inst, FacilityName, NameRevision, NewStatus) values('0679', 'VITA & DISTRICT HEALTH CENTRE', '', 'Keep')
GO
insert into zz2017MH(Inst, FacilityName, NameRevision, NewStatus) values('0681', 'SHERWOOD LODGE PCH', '', 'Keep')
GO
insert into zz2017MH(Inst, FacilityName, NameRevision, NewStatus) values('0682', 'SOURIS PERSONAL CARE HOME', '', 'Keep')
GO
insert into zz2017MH(Inst, FacilityName, NameRevision, NewStatus) values('0683', 'SWAN VALLEY LOGDE (1991) INC', '', 'Keep')
GO
insert into zz2017MH(Inst, FacilityName, NameRevision, NewStatus) values('0684', 'SWAN RIVER VALLEY PERSONAL CARE HOME INC', '', 'Keep')
GO
insert into zz2017MH(Inst, FacilityName, NameRevision, NewStatus) values('0685', 'GOLDEN LINKS LODGE', '', 'Keep')
GO
insert into zz2017MH(Inst, FacilityName, NameRevision, NewStatus) values('0686', 'TABOR HOME INC', '', 'Keep')
GO
insert into zz2017MH(Inst, FacilityName, NameRevision, NewStatus) values('0687', 'TIGER HILLS PERSONAL CARE HOME', '', 'Keep')
GO
insert into zz2017MH(Inst, FacilityName, NameRevision, NewStatus) values('0688', 'ACTION MARGUERITE (SAINT-BONIFACE) INC.', '', 'Keep')
GO
insert into zz2017MH(Inst, FacilityName, NameRevision, NewStatus) values('0689', 'THIRD CROSSING MANOR', '', 'Keep')
GO
insert into zz2017MH(Inst, FacilityName, NameRevision, NewStatus) values('0690', 'VICTORIA PARK LODGE', '', 'Discontinue as of March 31, 2017')
GO
insert into zz2017MH(Inst, FacilityName, NameRevision, NewStatus) values('0691', 'MACGREGOR PERSONAL  CARE HOME', '', 'Keep')
GO
insert into zz2017MH(Inst, FacilityName, NameRevision, NewStatus) values('0692', 'ROSSBURN PERSONAL CARE HOME', '', 'Keep')
GO
insert into zz2017MH(Inst, FacilityName, NameRevision, NewStatus) values('0693', 'VILLA YOUVILLE', '', 'Keep')
GO
insert into zz2017MH(Inst, FacilityName, NameRevision, NewStatus) values('0694', 'WAWANESA PERSONAL CARE HOME', '', 'Keep')
GO
insert into zz2017MH(Inst, FacilityName, NameRevision, NewStatus) values('0695', 'WEST-MAN NURSING HOME', '', 'Keep')
GO
insert into zz2017MH(Inst, FacilityName, NameRevision, NewStatus) values('0696', 'WESTVIEW LODGE', '', 'Keep')
GO
insert into zz2017MH(Inst, FacilityName, NameRevision, NewStatus) values('0697', 'WINNIPEGOSIS & DISTRICT HEALTH CENTRE', '', 'Keep')
GO
insert into zz2017MH(Inst, FacilityName, NameRevision, NewStatus) values('0698', 'GLENBORO PERSONAL CARE HOME', '', 'Keep')
GO
insert into zz2017MH(Inst, FacilityName, NameRevision, NewStatus) values('0699', 'DEER LODGE CENTRE PC', '', 'Keep')
GO
insert into zz2017MH(Inst, FacilityName, NameRevision, NewStatus) values('0900', 'NURSING HOMES MISCELLANEOUS', '', 'Keep')
GO
insert into zz2017MH(Inst, FacilityName, NameRevision, NewStatus) values('1058', 'BRITISH COLUMBIA''S CHILDREN''S HOSPITAL', '', 'Keep')
GO
insert into zz2017MH(Inst, FacilityName, NameRevision, NewStatus) values('2001', 'CALGARY GENERAL HOSPITAL', '', 'Discontinue as of March 31, 2017')
GO
insert into zz2017MH(Inst, FacilityName, NameRevision, NewStatus) values('2002', 'UNIVERSITY OF ALBERTA HOSPITAL', 'University of Alberta Hospital -Stollery', 'Revise')
GO
insert into zz2017MH(Inst, FacilityName, NameRevision, NewStatus) values('2060', 'ALBERTA CHILDREN''S HOSPITAL', '', 'Keep')
GO
insert into zz2017MH(Inst, FacilityName, NameRevision, NewStatus) values('2137', 'EDMONTON GENERAL HOSPITAL', '', 'Discontinue as of March 31, 2017')
GO
insert into zz2017MH(Inst, FacilityName, NameRevision, NewStatus) values('3004', 'MOOSOMIN UNION HOSPITAL', '', 'Keep')
GO
insert into zz2017MH(Inst, FacilityName, NameRevision, NewStatus) values('3008', 'YORKTON UNION HOSPITAL', '', 'Keep')
GO
insert into zz2017MH(Inst, FacilityName, NameRevision, NewStatus) values('3009', 'KAMSACK UNION HOSPITAL', '', 'Keep')
GO
insert into zz2017MH(Inst, FacilityName, NameRevision, NewStatus) values('3015', 'SALTCOATS & DIST WAR MEMORIAL HOSPITAL', '', 'Discontinue as of March 31, 2017')
GO
insert into zz2017MH(Inst, FacilityName, NameRevision, NewStatus) values('3103', 'REGINA GENERAL HOSPITAL', '', 'Keep')
GO
insert into zz2017MH(Inst, FacilityName, NameRevision, NewStatus) values('3107', 'SASKATOON CITY HOSPITAL', '', 'Discontinue as of March 31, 2017')
GO
insert into zz2017MH(Inst, FacilityName, NameRevision, NewStatus) values('3110', 'ROYAL UNIVERSITY HOSPITAL', '', 'Keep')
GO
insert into zz2017MH(Inst, FacilityName, NameRevision, NewStatus) values('3119', 'PASQUA HOSPITAL', '', 'Keep')
GO
insert into zz2017MH(Inst, FacilityName, NameRevision, NewStatus) values('3125', 'WHITEWOOD MOOSOMIN UNION HOSPITAL', '', 'Keep')
GO
insert into zz2017MH(Inst, FacilityName, NameRevision, NewStatus) values('3140', 'VICTORIA UNION HOSPITAL', '', 'Discontinue as of March 31, 2017')
GO
insert into zz2017MH(Inst, FacilityName, NameRevision, NewStatus) values('3144', 'ST. PAUL''S HOSPITAL', 'St Paul''s Hospial - Saskatoon', 'Revise')
GO
insert into zz2017MH(Inst, FacilityName, NameRevision, NewStatus) values('3192', 'SANDY BAY OUTPOST HOSPITAL', '', 'Discontinue as of March 31, 2017')
GO
insert into zz2017MH(Inst, FacilityName, NameRevision, NewStatus) values('4411', 'BLOORVIEW HUGH MACMILLAN HLTH CENTRE#939', '', 'Discontinue as of March 31, 2017')
GO
insert into zz2017MH(Inst, FacilityName, NameRevision, NewStatus) values('4421', 'CHILDREN''S HOSP OF EASTERN ONT #751', 'Children''s Hospital of Eastern Ontario - CHEO', 'Revise')
GO
insert into zz2017MH(Inst, FacilityName, NameRevision, NewStatus) values('4427', 'DRYDEN REGIONAL HEALTH CENTRE  #647', '', 'Keep')
GO
insert into zz2017MH(Inst, FacilityName, NameRevision, NewStatus) values('4447', 'HOSPITAL FOR SICK CHILDREN  #837', '', 'Keep')
GO
insert into zz2017MH(Inst, FacilityName, NameRevision, NewStatus) values('4462', 'LAKE OF THE WOODS DISTRICT HOSP  #826', '', 'Keep')
GO
insert into zz2017MH(Inst, FacilityName, NameRevision, NewStatus) values('4468', 'LONDON HEALTH SCIENCES CENTRE  #936', '', 'Keep')
GO
insert into zz2017MH(Inst, FacilityName, NameRevision, NewStatus) values('4493', 'REDLAKE MARGARET COCHENOUR MEM HOSP #896', '', 'Keep')
GO
insert into zz2017MH(Inst, FacilityName, NameRevision, NewStatus) values('4495', 'RIVERSIDE HEALTH CARE FACILITIES INC 900', '', 'Keep')
GO
insert into zz2017MH(Inst, FacilityName, NameRevision, NewStatus) values('4507', 'SIOUX LOOKOUT MENO YAWIN HLTH CTR  #964', '', 'Keep')
GO
insert into zz2017MH(Inst, FacilityName, NameRevision, NewStatus) values('4544', 'THUNDER BAY REGIONAL HOSPITAL  #935', '', 'Keep')
GO
insert into zz2017MH(Inst, FacilityName, NameRevision, NewStatus) values('4549', 'UNIVERSITY HEALTH NETWORK  #947', '', 'Keep')
GO
insert into zz2017MH(Inst, FacilityName, NameRevision, NewStatus) values('5038', 'MONTREAL CHILDREN''S HOSPITAL', '', 'Keep')
GO
insert into zz2017MH(Inst, FacilityName, NameRevision, NewStatus) values('6308', 'DR CHARLES A JANEWAY CHILD HEALTH CENTRE', '', 'Keep')
GO
insert into zz2017MH(Inst, FacilityName, NameRevision, NewStatus) values('7011', 'PELLY CROSSING HEALTH CENTRE', '', 'Discontinue as of March 31, 2017')
GO
insert into zz2017MH(Inst, FacilityName, NameRevision, NewStatus) values('7012', 'TESLIN HEALTH CENTRE', '', 'Discontinue as of March 31, 2017')
GO
insert into zz2017MH(Inst, FacilityName, NameRevision, NewStatus) values('7013', 'ROSS RIVER HEALTH CENTRE', '', 'Discontinue as of March 31, 2017')
GO
insert into zz2017MH(Inst, FacilityName, NameRevision, NewStatus) values('7014', 'DESTRUCTION BAY HEALTH CENTRE', '', 'Discontinue as of March 31, 2017')
GO
insert into zz2017MH(Inst, FacilityName, NameRevision, NewStatus) values('7015', 'BEAVER CREEK HEALTH CENTRE', '', 'Discontinue as of March 31, 2017')
GO
insert into zz2017MH(Inst, FacilityName, NameRevision, NewStatus) values('7016', 'CARCROSS HEALTH CENTRE', '', 'Discontinue as of March 31, 2017')
GO
insert into zz2017MH(Inst, FacilityName, NameRevision, NewStatus) values('7100', 'H. H. WILLIAMS MEMORIAL HOSPITAL', '', 'Discontinue as of March 31, 2017')
GO
insert into zz2017MH(Inst, FacilityName, NameRevision, NewStatus) values('7102', 'STANTON YELLOWKNIFE HOSPITAL', '', 'Discontinue as of March 31, 2017')
GO
insert into zz2017MH(Inst, FacilityName, NameRevision, NewStatus) values('7103', 'ALL SAINTS HOSPITAL', '', 'Discontinue as of March 31, 2017')
GO
insert into zz2017MH(Inst, FacilityName, NameRevision, NewStatus) values('7104', 'IMMACULATE CONCEPTION HOSPITAL', '', 'Discontinue as of March 31, 2017')
GO
insert into zz2017MH(Inst, FacilityName, NameRevision, NewStatus) values('7106', 'FARAUD HOSPITAL', '', 'Discontinue as of March 31, 2017')
GO
insert into zz2017MH(Inst, FacilityName, NameRevision, NewStatus) values('7107', 'FORT SMITH HEALTH CENTRE', '', 'Discontinue as of March 31, 2017')
GO
insert into zz2017MH(Inst, FacilityName, NameRevision, NewStatus) values('7108', 'DEH CHO HEALTH & SOCIAL SVCS NURSING STN', '', 'Discontinue as of March 31, 2017')
GO
insert into zz2017MH(Inst, FacilityName, NameRevision, NewStatus) values('7109', 'INUVIK GENERAL HOSPITAL', '', 'Discontinue as of March 31, 2017')
GO
insert into zz2017MH(Inst, FacilityName, NameRevision, NewStatus) values('7111', 'FORT SIMPSON GENERAL HOSPITAL', '', 'Discontinue as of March 31, 2017')
GO
insert into zz2017MH(Inst, FacilityName, NameRevision, NewStatus) values('7124', 'SANIKILUAQ HEALTH CENTER', '', 'Discontinue as of March 31, 2017')
GO
insert into zz2017MH(Inst, FacilityName, NameRevision, NewStatus) values('7125', 'PANGNIRTUNG HEALTH CENTER', '', 'Discontinue as of March 31, 2017')
GO
insert into zz2017MH(Inst, FacilityName, NameRevision, NewStatus) values('7126', 'WHALE COVE HEALTH CENTRE', '', 'Discontinue as of March 31, 2017')
GO
insert into zz2017MH(Inst, FacilityName, NameRevision, NewStatus) values('7127', 'CAPE DORSET HEALTH CENTRE', '', 'Discontinue as of March 31, 2017')
GO
insert into zz2017MH(Inst, FacilityName, NameRevision, NewStatus) values('7128', 'HEALTH CENTER FORT RAE', '', 'Discontinue as of March 31, 2017')
GO
insert into zz2017MH(Inst, FacilityName, NameRevision, NewStatus) values('7129', 'PANGNIRTUNG HEALTH CENTRE', '', 'Discontinue as of March 31, 2017')
GO
insert into zz2017MH(Inst, FacilityName, NameRevision, NewStatus) values('7130', 'GRISE FIORD HEALTH CENTRE', '', 'Discontinue as of March 31, 2017')
GO
insert into zz2017MH(Inst, FacilityName, NameRevision, NewStatus) values('7201', 'ARCTIC BAY HEALTH CENTRE', '', 'Discontinue as of March 31, 2017')
GO
insert into zz2017MH(Inst, FacilityName, NameRevision, NewStatus) values('7202', 'BAFFIN REGIONAL HOSPITAL', '', 'Discontinue as of March 31, 2017')
GO
insert into zz2017MH(Inst, FacilityName, NameRevision, NewStatus) values('7203', 'BAKER LAKE HEALTH CENTRE', '', 'Keep')
GO
insert into zz2017MH(Inst, FacilityName, NameRevision, NewStatus) values('7204', 'BROUGHTON ISLAND HEALTH CENTRE', '', 'Discontinue as of March 31, 2017')
GO
insert into zz2017MH(Inst, FacilityName, NameRevision, NewStatus) values('7205', 'CAMBRIDGE BAY HEALTH CENTRE', '', 'Discontinue as of March 31, 2017')
GO
insert into zz2017MH(Inst, FacilityName, NameRevision, NewStatus) values('7206', 'CAPE DORSET HEALTH CENTRE', '', 'Discontinue as of March 31, 2017')
GO
insert into zz2017MH(Inst, FacilityName, NameRevision, NewStatus) values('7207', 'CHESTERFIELD INLET HEALTH CENTRE', '', 'Discontinue as of March 31, 2017')
GO
insert into zz2017MH(Inst, FacilityName, NameRevision, NewStatus) values('7208', 'GRISE FIORD HEALTH CENTRE', '', 'Discontinue as of March 31, 2017')
GO
insert into zz2017MH(Inst, FacilityName, NameRevision, NewStatus) values('7209', 'HALL BEACH HEALTH CENTRE', '', 'Discontinue as of March 31, 2017')
GO
insert into zz2017MH(Inst, FacilityName, NameRevision, NewStatus) values('7210', 'PANGNIRTUNG HEALTH CENTRE', '', 'Discontinue as of March 31, 2017')
GO
insert into zz2017MH(Inst, FacilityName, NameRevision, NewStatus) values('7211', 'KEEWATIN REGIONAL OFFICE', '', 'Keep')
GO
insert into zz2017MH(Inst, FacilityName, NameRevision, NewStatus) values('7212', 'REPULSE BAY HEALTH CENTRE', '', 'Discontinue as of March 31, 2017')
GO
insert into zz2017MH(Inst, FacilityName, NameRevision, NewStatus) values('7213', 'RESOLUTE BAY HEALTH CENTRE', '', 'Discontinue as of March 31, 2017')
GO
insert into zz2017MH(Inst, FacilityName, NameRevision, NewStatus) values('7214', 'SANIKILUAQ HEALTH CENTER', '', 'Discontinue as of March 31, 2017')
GO
insert into zz2017MH(Inst, FacilityName, NameRevision, NewStatus) values('7215', 'NANISIVIK HEALTH CENTRE', '', 'Discontinue as of March 31, 2017')
GO
insert into zz2017MH(Inst, FacilityName, NameRevision, NewStatus) values('7216', 'WHALE COVE HEALTH CENTRE', '', 'Discontinue as of March 31, 2017')
GO
insert into zz2017MH(Inst, FacilityName, NameRevision, NewStatus) values('7217', 'ARVIAT NURSING STATION', '', 'Keep')
GO
insert into zz2017MH(Inst, FacilityName, NameRevision, NewStatus) values('8000', 'U.S.A. HOSPITALS', '', 'Keep')
GO
insert into zz2017MH(Inst, FacilityName, NameRevision, NewStatus) values('8001', 'ROSEAU MEDICAL CLINIC', '', 'Keep')
GO
insert into zz2017MH(Inst, FacilityName, NameRevision, NewStatus) values('8002', 'ROSEAU AREA HOSPITAL', '', 'Keep')
GO
insert into zz2017MH(Inst, FacilityName, NameRevision, NewStatus) values('8003', 'ROSEAU AREA HOSPITAL DISTRICT EXT CARE', '', 'Keep')
GO
insert into zz2017MH(Inst, FacilityName, NameRevision, NewStatus) values('9000', 'OUTSIDE CANADA HOSPITALS', '', 'Keep')
GO
insert into zz2017MH(Inst, FacilityName, NameRevision, NewStatus) values('9910', 'BRITISH COLUMBIA PERSONAL CARE HOMES', '', 'Keep')
GO
insert into zz2017MH(Inst, FacilityName, NameRevision, NewStatus) values('9920', 'ALBERTA PERSONAL CARE HOMES', '', 'Keep')
GO
insert into zz2017MH(Inst, FacilityName, NameRevision, NewStatus) values('9930', 'SASKATCHEWAN PERSONAL CARE HOMES', '', 'Keep')
GO
insert into zz2017MH(Inst, FacilityName, NameRevision, NewStatus) values('9940', 'ONTARIO PERSONAL CARE HOMES', '', 'Keep')
GO
insert into zz2017MH(Inst, FacilityName, NameRevision, NewStatus) values('9950', 'QUEBEC PERSONAL CARE HOMES', '', 'Keep')
GO
insert into zz2017MH(Inst, FacilityName, NameRevision, NewStatus) values('9960', 'NEW BRUNSWICK PERSONAL CARE HOMES', '', 'Keep')
GO
insert into zz2017MH(Inst, FacilityName, NameRevision, NewStatus) values('9961', 'NOVA SCOTIA PERSONAL CARE HOMES', '', 'Keep')
GO
insert into zz2017MH(Inst, FacilityName, NameRevision, NewStatus) values('9962', 'PEI PERSONAL CARE HOMES', '', 'Keep')
GO
insert into zz2017MH(Inst, FacilityName, NameRevision, NewStatus) values('9963', 'NEWFOUNDLAND PERSONAL CARE HOMES', '', 'Keep')
GO
insert into zz2017MH(Inst, FacilityName, NameRevision, NewStatus) values('9970', 'YUKON PERSONAL CARE HOMES', '', 'Keep')
GO
insert into zz2017MH(Inst, FacilityName, NameRevision, NewStatus) values('9971', 'NORTHWEST TERRITORIES PERSONAL CARE', '', 'Keep')
GO
insert into zz2017MH(Inst, FacilityName, NameRevision, NewStatus) values('9972', 'Nunavut', '', 'Keep')
GO
insert into zz2017MH(Inst, FacilityName, NameRevision, NewStatus) values('9980', 'U.S.A. PERSONAL CARE HOMES', '', 'Keep')
GO
insert into zz2017MH(Inst, FacilityName, NameRevision, NewStatus) values('9990', 'FOREIGN PERSONAL CARE HOMES - Update Utility', '', 'Keep')
GO
insert into zz2017MH(Inst, FacilityName, NameRevision, NewStatus) values('9998', 'Supportive Housing', '', 'Keep')
GO
insert into zz2017MH(Inst, FacilityName, NameRevision, NewStatus) values('9999', 'Other', '', 'Keep')
GO
Update lu_codefile
set Defaultdescription = z.NameRevision
from zz2017MH z
	inner join lu_codefile l on z.inst = l.cihivalue and lookupid = 62
where z.NewStatus = 'Revise' and z.NameRevision <> ''
GO
Update lu_codefile
set ValidTo = '2017/03/31'
from zz2017MH z
	inner join lu_codefile l on z.inst = l.cihivalue and lookupid = 62
where z.NewStatus = 'Discontinue as of March 31, 2017'
GO
if exists (select * from information_schema.tables where table_name = 'zz2017MH_New')
	drop table zz2017MH_New
GO
select code = identity(int, 1, 1), z.* into zz2017MH_New
from zz2017MH z
	left join lu_codefile l on z.inst = l.cihivalue and lookupid = 62
where l.code is null and NewStatus in ('Keep', 'Revise')
order by z.inst
GO
declare @maxID int
declare @province varchar(50)

set @province = (select top 1 p.CIHIValue from H_HospitalProfile h inner join lu_province p on h.provincenumber = p.code)

if @province = '6'
begin
	set @MaxID = 19959
	if exists (select * from lu_codefile where code = @MaxID)
	begin
		set @MaxID = (select max(code) from lu_codefile where code between 500000 and 599999) + 1
		if @MaxID is null set @MaxID = 500001
	end

	Insert into lu_codefile(Code, DisplayCode, UserDescription, DefaultDescription, CIHIValue, ValidFrom, ValidTo, InterfaceIn, LookupID, HospitalID, LUText)
	select code + @MaxID - 1, Inst, FacilityName, FacilityName, Inst, '1950/04/01', '2099/03/31', Inst, 62, 0, ''
	from zz2017MH_New
end
GO
if exists (select * from information_schema.tables where table_name = 'zz2017MH')
	drop table zz2017MH
GO
if exists (select * from information_schema.tables where table_name = 'zz2017MH_New')
	drop table zz2017MH_New
GO
--0649 -   Change Table Valid To Dates, 
declare @table varchar(500)
declare @sql varchar(1000)

declare cur_lu cursor for
    select c.table_name --, c.column_name
    from information_schema.tables t
        inner join information_schema.columns c on t.table_name = c.table_name
    where t.table_type = 'base table' and c.column_name = 'validto'
    and c.data_type = 'datetime' and t.Table_name not like 'BI%' and t.Table_name not in ('ENGN_Edit', 'GI_WeightUnit')
    order by c.table_name

open cur_lu
fetch next from cur_lu into @table
while (@@fetch_status = 0)
begin
    set @sql = 'update [' + @table + '] set validto = ''2099/03/31'' where validto >= ''2020/01/01'' and validto <> ''2099/03/31'' '  -- > 2017/04/01
    --print @sql
    exec (@sql)
    fetch next from cur_lu into @table
end
close cur_lu
deallocate cur_lu
GO

--0788
declare @province varchar(20)
set @Province = (select top 1 p.CIHIValue from H_HospitalProfile h inner join lu_province p on h.provincenumber = p.code)
if @Province = '9'
begin
    delete from lu_batchperiod where code between 447 and 459
	insert into [dbo].[LU_BatchPeriod](Code, DisplayCode, UserDescription, DefaultDescription, CIHIValue, ValidFrom, ValidTo, CIHIDateFrom, CIHIDateTo, InterfaceIn, HospitalID, LUText)
	values(447, '01', 'Period 01 - 2017', 'Period 01 - 2017', '01', '2017/04/01', '2018/03/31', '01-Apr-2017', '20-Apr-2017', '01', 0, '')
	insert into [dbo].[LU_BatchPeriod](Code, DisplayCode, UserDescription, DefaultDescription, CIHIValue, ValidFrom, ValidTo, CIHIDateFrom, CIHIDateTo, InterfaceIn, HospitalID, LUText)
	values(448, '02', 'Period 02 - 2017', 'Period 02 - 2017', '02', '2017/04/01', '2018/03/31', '21-Apr-2017', '18-May-2017', '02', 0, '')
	insert into [dbo].[LU_BatchPeriod](Code, DisplayCode, UserDescription, DefaultDescription, CIHIValue, ValidFrom, ValidTo, CIHIDateFrom, CIHIDateTo, InterfaceIn, HospitalID, LUText)
	values(449, '03', 'Period 03 - 2017', 'Period 03 - 2017', '03', '2017/04/01', '2018/03/31', '19-May-2017', '15-Jun-2017', '03', 0, '')
	insert into [dbo].[LU_BatchPeriod](Code, DisplayCode, UserDescription, DefaultDescription, CIHIValue, ValidFrom, ValidTo, CIHIDateFrom, CIHIDateTo, InterfaceIn, HospitalID, LUText)
	values(450, '04', 'Period 04 - 2017', 'Period 04 - 2017', '04', '2017/04/01', '2018/03/31', '16-Jun-2017', '13-Jul-2017', '04', 0, '')
	insert into [dbo].[LU_BatchPeriod](Code, DisplayCode, UserDescription, DefaultDescription, CIHIValue, ValidFrom, ValidTo, CIHIDateFrom, CIHIDateTo, InterfaceIn, HospitalID, LUText)
	values(451, '05', 'Period 05 - 2017', 'Period 05 - 2017', '05', '2017/04/01', '2018/03/31', '14-Jul-2017', '10-Aug-2017', '05', 0, '')
	insert into [dbo].[LU_BatchPeriod](Code, DisplayCode, UserDescription, DefaultDescription, CIHIValue, ValidFrom, ValidTo, CIHIDateFrom, CIHIDateTo, InterfaceIn, HospitalID, LUText)
	values(452, '06', 'Period 06 - 2017', 'Period 06 - 2017', '06', '2017/04/01', '2018/03/31', '11-Aug-2017', '07-Sep-2017', '06', 0, '')
	insert into [dbo].[LU_BatchPeriod](Code, DisplayCode, UserDescription, DefaultDescription, CIHIValue, ValidFrom, ValidTo, CIHIDateFrom, CIHIDateTo, InterfaceIn, HospitalID, LUText)
	values(453, '07', 'Period 07 - 2017', 'Period 07 - 2017', '07', '2017/04/01', '2018/03/31', '08-Sep-2017', '05-Oct-2017', '07', 0, '')
	insert into [dbo].[LU_BatchPeriod](Code, DisplayCode, UserDescription, DefaultDescription, CIHIValue, ValidFrom, ValidTo, CIHIDateFrom, CIHIDateTo, InterfaceIn, HospitalID, LUText)
	values(454, '08', 'Period 08 - 2017', 'Period 08 - 2017', '08', '2017/04/01', '2018/03/31', '06-Oct-2017', '02-Nov-2017', '08', 0, '')
	insert into [dbo].[LU_BatchPeriod](Code, DisplayCode, UserDescription, DefaultDescription, CIHIValue, ValidFrom, ValidTo, CIHIDateFrom, CIHIDateTo, InterfaceIn, HospitalID, LUText)
	values(455, '09', 'Period 09 - 2017', 'Period 09 - 2017', '09', '2017/04/01', '2018/03/31', '03-Nov-2017', '30-Nov-2017', '09', 0, '')
	insert into [dbo].[LU_BatchPeriod](Code, DisplayCode, UserDescription, DefaultDescription, CIHIValue, ValidFrom, ValidTo, CIHIDateFrom, CIHIDateTo, InterfaceIn, HospitalID, LUText)
	values(456, '10', 'Period 10 - 2017', 'Period 10 - 2017', '10', '2017/04/01', '2018/03/31', '01-Dec-2017', '28-Dec-2017', '10', 0, '')
	insert into [dbo].[LU_BatchPeriod](Code, DisplayCode, UserDescription, DefaultDescription, CIHIValue, ValidFrom, ValidTo, CIHIDateFrom, CIHIDateTo, InterfaceIn, HospitalID, LUText)
	values(457, '11', 'Period 11 - 2017', 'Period 11 - 2017', '11', '2017/04/01', '2018/03/31', '29-Dec-2017', '25-Jan-2018', '11', 0, '')
	insert into [dbo].[LU_BatchPeriod](Code, DisplayCode, UserDescription, DefaultDescription, CIHIValue, ValidFrom, ValidTo, CIHIDateFrom, CIHIDateTo, InterfaceIn, HospitalID, LUText)
	values(458, '12', 'Period 12 - 2017', 'Period 12 - 2017', '12', '2017/04/01', '2018/03/31', '26-Jan-2018', '22-Feb-2018', '12', 0, '')
	insert into [dbo].[LU_BatchPeriod](Code, DisplayCode, UserDescription, DefaultDescription, CIHIValue, ValidFrom, ValidTo, CIHIDateFrom, CIHIDateTo, InterfaceIn, HospitalID, LUText)
	values(459, '13', 'Period 13 - 2017', 'Period 13 - 2017', '13', '2017/04/01', '2018/03/31', '23-Feb-2018', '31-Mar-2018', '13', 0, '')
end
GO
--end build 2
delete from [dbo].[LLU_BatchYears] where [BatchYear] = 2017
GO
Insert into [dbo].[LLU_BatchYears](Code, BatchYear, CareType, ICDType)
select Code = Code + 6, BatchYear = 2017, CareType, ICDType
from [dbo].[LLU_BatchYears]
where [BatchYear] = 2016
GO
--0789 Disable EMT for CJRR
update u_usersfields 
set fieldenabled = 2, 
	fieldvisible = 2
where appid in (40001, 40000) and fieldid = 3010
GO
--end build 3
Update H_Hospitalprofile set cdkey = 'UQ9WGAW5GWEEM9Q7C5579OAOAYRI0040CAIJBBFR' where code = 2089
GO
--0597
Update H_Hospitalprofile set cdkey = 'Q9W3AW95GWEEE9Q7C9OAKAYMXKRI0090BJIJB6GQ' where code = 1989
GO
update H_HospitalProfile set CDKey = (left(CDKey,26) + 'QI' + right(CDKey,12)), cdwr = 0 where CDKey <> ''
go 
Update LU_Institution_10 
set ValidTo = '2099/03/31',
    UserDescription = 'Perth & Smiths Falls Dist-Smiths Falls - Smiths Falls (AT)'
where CIHIValue = '51269'
GO
--0800
update [dbo].[H_InstitutionProfile] set RehabSubDate = 'May 15, 2017' where AssociationID = 3
GO
update [dbo].[H_InstitutionProfile] set RehabSubDate = 'June 5, 2017' where AssociationID = 4
GO
update [dbo].[H_InstitutionProfile] set RehabSubDate = 'June 1, 2017' where AssociationID = 7
GO
--????
Update U_Usersfields
set FieldSize = 50, ParamSize = 50
where appid = 1104 and fieldid = 50010
GO
--2.x-0798 
Update  [dbo].[SU_StructureList] 
set groupid = 25
where batchyear = 2017 and appid in (201, 202) and province = 13 and groupid = 18
GO
delete from [dbo].[SU_StructureFile] where groupid = 25
GO
Insert into [dbo].[SU_StructureFile](GroupID, SubGroupID, ICDType, TableName, FieldAlias, FieldName, FieldID, Occurrence, RecodeID, TextPosition, TextLength, TextFormat)
select GroupID = 25, SubGroupID, ICDType, TableName, FieldAlias, FieldName, FieldID, Occurrence, RecodeID, TextPosition, TextLength, TextFormat
from [dbo].[SU_StructureFile] 
where groupid = 18
order by TextPosition
GO
update [dbo].[SU_StructureFile]
set TextLength = 3425
where groupid = 25 and TextPosition = 30
GO
Update  [dbo].[SU_StructureList] 
set groupid = 46
where batchyear = 2017 and appid in (201, 202) and province = 13 and groupid = 44
GO
delete from [dbo].[SU_StructureFile] where groupid = 46
GO
Insert into [dbo].[SU_StructureFile](GroupID, SubGroupID, ICDType, TableName, FieldAlias, FieldName, FieldID, Occurrence, RecodeID, TextPosition, TextLength, TextFormat)
select GroupID = 46, SubGroupID, ICDType, TableName, FieldAlias, FieldName, FieldID, Occurrence, RecodeID, TextPosition, TextLength, TextFormat
from [dbo].[SU_StructureFile] 
where groupid = 44
order by TextPosition
GO
Update [dbo].[SU_StructureFile]
set TextPosition = TextPosition + 214
where GroupID = 46 and TextPosition between 2566 and 2700 and  TableName = 'AB_Project'
GO
Insert into [dbo].[SU_StructureFile](GroupID, SubGroupID, ICDType, TableName, FieldAlias, FieldName, FieldID, Occurrence, RecodeID, TextPosition, TextLength, TextFormat)
values(46, 0, 'T', 'AB_Abstract', 'Filler2566', 'Filler2566', null, 0, 2, 2566, 135, 2)
GO
Update [dbo].[SU_StructureFile]
set TextLength = 6
where GroupID = 46 and TextPosition = 2774
GO
declare @occ int
--declare @pos int
declare @sub int
--set @pos = 2780
set @occ = 6
set @sub = 77
while(@occ < 26)
begin
    Insert into [dbo].[SU_StructureFile](GroupID, SubGroupID, ICDType, TableName, FieldAlias, FieldName, FieldID, Occurrence, RecodeID, TextPosition, TextLength, TextFormat)
    select GroupID, SubGroupID = @sub, ICDType, TableName, FieldAlias, FieldName, FieldID, Occurrence = @occ, RecodeID, TextPosition = TextPosition + 27, TextLength, TextFormat 
    from [dbo].[SU_StructureFile]
    where GroupID = 46 and TableName = 'AB_Project' and Occurrence = @occ - 1
    set @sub = @sub + 1
    set @occ = @occ + 1
end
GO
Update u_usersfields
set Header_user = 'Special Project Data 21'
where appid = 401 and fieldid = 1423
GO
Update u_usersfields
set Header_user = 'Special Project Data 22'
where appid = 401 and fieldid = 1424
GO
Update u_usersfields
set Header_user = 'Special Project Data 23'
where appid = 401 and fieldid = 1425
GO
--End build 5
-- 0806 -   new institutions - Kingston
Update LU_Institution_10
set [HospitalType] = 'AM',
    [InstCareType] = 'B',
    [IsAcute] = 0
where CIHIValue in ('54829', '54832')
GO
Update LU_Institution_10
set [HospitalType] = 'AT',
    [InstCareType] = 'A',
    [IsAcute] = 1
where CIHIValue in ('54830', '54831')
GO
declare @ID int
declare @province varchar(50)
set @Province = (select top 1 p.CIHIValue from H_HospitalProfile h inner join lu_province p on h.ProvinceNumber = p.code)
if @Province = '5'
begin
    set @ID = 91618
    if exists (select * from LU_Institution_10 where code = @ID)
    begin
        set @ID = (select max(code) from LU_Institution_10)
        if isnull(@ID, 0) < 500000 set @ID = 500001
    end
    if not exists (select * from LU_Institution_10 where CIHIValue = '54829')
        Insert into [dbo].[LU_Institution_10](Code, DisplayCode, UserDescription, DefaultDescription, CIHIValue, ValidFrom, ValidTo, InterfaceIn, InstCareType, HospitalType, IsAcute, HospitalID, LUText)
        values(@ID, '54829', 'Kingston Health Sciences Centre - Hotel Dieu', 'Kingston Health Sciences Centre - Hotel Dieu', '54829', '2017/04/01', '2099/03/31', '54829', 'B', 'AM', 0, 0, '')

    set @ID = @ID + 1
    if not exists (select * from LU_Institution_10 where CIHIValue = '54830')
        Insert into [dbo].[LU_Institution_10](Code, DisplayCode, UserDescription, DefaultDescription, CIHIValue, ValidFrom, ValidTo, InterfaceIn, InstCareType, HospitalType, IsAcute, HospitalID, LUText)
        values(@ID, '54830', 'Kingston Health Sciences Centre - Hotel Dieu', 'Kingston Health Sciences Centre - Hotel Dieu', '54830', '2017/04/01', '2099/03/31', '54830', 'A', 'AT', 1, 0, '')

    set @ID = @ID + 1
    if not exists (select * from LU_Institution_10 where CIHIValue = '54831')
        Insert into [dbo].[LU_Institution_10](Code, DisplayCode, UserDescription, DefaultDescription, CIHIValue, ValidFrom, ValidTo, InterfaceIn, InstCareType, HospitalType, IsAcute, HospitalID, LUText)
        values(@ID, '54831', 'Kingston Health Sciences Centre - Kingston General', 'Kingston Health Sciences Centre - Kingston General', '54831', '2017/04/01', '2099/03/31', '54831', 'A', 'AT', 1, 0, '')

    set @ID = @ID + 1
    if not exists (select * from LU_Institution_10 where CIHIValue = '54832')
        Insert into [dbo].[LU_Institution_10](Code, DisplayCode, UserDescription, DefaultDescription, CIHIValue, ValidFrom, ValidTo, InterfaceIn, InstCareType, HospitalType, IsAcute, HospitalID, LUText)
        values(@ID, '54832', 'Kingston Health Sciences Centre - Kingston General', 'Kingston Health Sciences Centre - Kingston General', '54832', '2017/04/01', '2099/03/31', '54832', 'B', 'AM', 0, 0, '')
end
GO
-- 0807 -   new institutions - Scarborough and Rouge Valley
if exists (select * from information_schema.tables where table_name = 'zzInst54834')
    drop table zzInst54834
GO
create table zzInst54834(
    code int,
    UserDescription varchar(50) collate Latin1_General_CI_AS,
    HospitalType varchar(10) collate Latin1_General_CI_AS,
    CIHIValue varchar(10) collate Latin1_General_CI_AS
)
GO
Insert into zzInst54834(code, UserDescription, HospitalType, CIHIValue) values(1, 'Scarborough and Rouge Hospital-CENTENARY', 'CR', '54834')
GO
Insert into zzInst54834(code, UserDescription, HospitalType, CIHIValue) values(2, 'Scarborough and Rouge Hospital-CENTENARY', 'GR', '54835')
GO
Insert into zzInst54834(code, UserDescription, HospitalType, CIHIValue) values(3, 'Scarborough and Rouge Hospital-CENTENARY', 'AT', '54836')
GO
Insert into zzInst54834(code, UserDescription, HospitalType, CIHIValue) values(4, 'Scarborough and Rouge Hospital-CENTENARY', 'AM', '54837')
GO
Insert into zzInst54834(code, UserDescription, HospitalType, CIHIValue) values(5, 'Scarborough and Rouge Hospital-CENTENARY', 'MH', '54838')
GO
Insert into zzInst54834(code, UserDescription, HospitalType, CIHIValue) values(6, 'Scarborough and Rouge Hospital -SCAR.GEN.SITE', 'AM', '54839')
GO
Insert into zzInst54834(code, UserDescription, HospitalType, CIHIValue) values(7, 'Scarborough and Rouge Hospital -SCAR.GEN.SITE', 'AT', '54840')
GO
Insert into zzInst54834(code, UserDescription, HospitalType, CIHIValue) values(8, 'Scarborough and Rouge Hospital -BIRCHMOUNT ', 'AM', '54841')
GO
Insert into zzInst54834(code, UserDescription, HospitalType, CIHIValue) values(9, 'Scarborough and Rouge Hospital -BIRCHMOUNT ', 'AT', '54842')
GO
Insert into zzInst54834(code, UserDescription, HospitalType, CIHIValue) values(10, 'Scarborough and Rouge Hospital-BIRCHMOUNT ', 'MH', '54843')
GO
Update LU_Institution_10
set [HospitalType] = z.HospitalType,
    [InstCareType] = (case z.HospitalType when 'AT' then 'A' when 'AM' then 'B' when 'CR' then 'L' else left(z.HospitalType, 1) end),
    [IsAcute] = (case when z.HospitalType = 'AT' then 1 else 0 end)
from LU_Institution_10 l inner join zzInst54834 z on l.cihivalue = z.cihivalue
GO
Update LU_Institution_10
set UserDescription = z.UserDescription, 
    DefaultDescription = z.UserDescription,
    ValidFrom = (case when l.ValidFrom < '2017/04/01' then l.ValidFrom else '2017/04/01' end),
    Validto = '2099/03/31'
from LU_Institution_10 l inner join zzInst54834 z on l.cihivalue = z.cihivalue
where l.UserDescription like 'zz%' or l.UserDescription like 'undef%' or isnull(l.UserDescription, '') = ''
GO
declare @ID int
declare @province varchar(50)
set @Province = (select top 1 p.CIHIValue from H_HospitalProfile h inner join lu_province p on h.ProvinceNumber = p.code)
if @Province = '5'
begin
    set @ID = 91621
    if exists (select * from LU_Institution_10 where code = @ID + 1)
    begin
        set @ID = (select max(code) from LU_Institution_10)
        if isnull(@ID, 0) < 500000 set @ID = 500001
    end
    Insert into [dbo].[LU_Institution_10](Code, DisplayCode, UserDescription, DefaultDescription, CIHIValue, ValidFrom, ValidTo, InterfaceIn, InstCareType, HospitalType, IsAcute, HospitalID, LUText)
    select @ID + z.Code, z.CIHIValue, z.UserDescription, z.UserDescription, z.CIHIValue, '2017/04/01', '2099/03/31', z.CIHIValue, 
        (case z.HospitalType when 'AT' then 'A' when 'AM' then 'B' when 'CR' then 'L' else left(z.HospitalType, 1) end), z.HospitalType,
        (case when z.HospitalType = 'AT' then 1 else 0 end), 0, ''
    from zzInst54834 z
        left join LU_Institution_10 l on l.cihivalue = z.cihivalue
    where l.code is null
end
GO
if exists (select * from information_schema.tables where table_name = 'zzInst54834')
    drop table zzInst54834
GO
update LU_Institution_10 
set ValidTo = '2017/03/31'
where cihivalue in ('53975', '54152', '53984', '54154', '54573', '53940', '53941', '53943', '54139', '54572')
GO

--Final