--1160
update [dbo].[SU_StructureFile]
set RecodeID = 0
where GroupID in (47, 48) and tablename = 'AB_MentalHealth' and RecodeID in (22, 23)
GO
--1152
if exists (select * from information_schema.tables where table_name = 'zzCHKInst')
    drop table zzCHKInst
GO
create table zzCHKInst(
    code int identity(1, 1),
    CareType varchar(10),
    UserDescription varchar(100),
    CIHIValue varchar(10),
    LevelCode int,
    HospitalType varchar(10)
)
GO
Insert into zzCHKInst(CareType, UserDescription, CIHIValue, LevelCode, HospitalType) values('NACRS', 'Chatham-Kent Health Alliance- Wallaceburg', '54868', 4, 'AM')
Insert into zzCHKInst(CareType, UserDescription, CIHIValue, LevelCode, HospitalType) values('DAD',   'Chatham-Kent Health Alliance- Wallaceburg', '54869', null, 'AT')
Insert into zzCHKInst(CareType, UserDescription, CIHIValue, LevelCode, HospitalType) values('NACRS', 'Chatham-Kent Health Alliance- Chatham',     '54870', 4, 'AM')
Insert into zzCHKInst(CareType, UserDescription, CIHIValue, LevelCode, HospitalType) values('DAD',   'Chatham-Kent Health Alliance- Chatham',     '54871', null, 'AT')
Insert into zzCHKInst(CareType, UserDescription, CIHIValue, LevelCode, HospitalType) values('CCRS',  'Chatham-Kent Health Alliance- Chatham',     '54873', null, 'CR')
Insert into zzCHKInst(CareType, UserDescription, CIHIValue, LevelCode, HospitalType) values('NRS',   'Chatham-Kent Health Alliance- Chatham',     '54874', null, 'GR')
Insert into zzCHKInst(CareType, UserDescription, CIHIValue, LevelCode, HospitalType) values('MH',    'Chatham-Kent Health Alliance- Chatham',     '54872', null, 'MH')
GO
update lu_institution_10
set InstCareType = (case z.HospitalType when 'AT' then 'A' When 'AM' then 'B' when 'GR' then 'R' when 'CR' then 'L' when 'MH' then 'M' else '' end), 
    HospitalType = z.HospitalType, 
    IsAcute =  (case z.HospitalType when 'AT' then 1 else 2 end), 
    InstitutionType = (case z.HospitalType when 'AT' then '1' When 'AM' then 'N' when 'GR' then '2' when 'CR' then '3' when 'MH' then '5' else '' end)
from zzCHKInst z
    inner join lu_institution_10 h on z.CIHIValue = h.CIHIValue
GO
declare @MaxID int
declare @Province varchar(10)
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 @MaxID = 91645
    if exists (select * from lu_institution_10 where code = (@MaxID + 1))
    begin
        set @maxID = (select max(code) from lu_institution_10)
        if @MaxID < 500000 set @MaxID = 500000
    end
    insert into lu_institution_10(Code, DisplayCode, UserDescription, DefaultDescription, CIHIValue, ValidFrom, ValidTo, InterfaceIn, InstCareType, HospitalType, IsAcute, HospitalID, LUText, InstitutionType)
    select @MaxID + z.Code, z.CIHIValue, z.UserDescription + ' - ' + z.CareType, z.UserDescription + ' - ' + z.CareType, z.CIHIValue, '2018/04/01', '2099/03/31', z.CIHIValue,
        InstCareType = (case z.HospitalType when 'AT' then 'A' When 'AM' then 'B' when 'GR' then 'R' when 'CR' then 'L' when 'MH' then 'M' else '' end), 
        z.HospitalType, 
        IsAcute =  (case z.HospitalType when 'AT' then 1 else 2 end), 0, '',
        InstitutionType = (case z.HospitalType when 'AT' then '1' When 'AM' then 'N' when 'GR' then '2' when 'CR' then '3' when 'MH' then '5' else '' end)
    from zzCHKInst z
        left join lu_institution_10 h on z.CIHIValue = h.CIHIValue
    where h.cihivalue is null
end
GO
if exists (select * from information_schema.tables where table_name = 'zzCHKInst')
    drop table zzCHKInst
GO
--2.x-1080
update lu_institution_10 set ValidTo = '2018-03-31' where cihivalue in ('69299', '62014')
GO
--1171 -   Add Institution Type Field to Institution ICD10 LU Table
delete u_fields where [FieldID] = '50028'
GO
Insert into [dbo].[U_Fields](FieldID, InHouseDesc, SystemName, PropertyName, FieldAccess, IsGroup)
values(50028, 'InstitutionType', 'InstitutionType', 'InstitutionType', 2, 0)
GO
if not exists (select * from u_usersfields where userid = 1 and appid = 1128 and fieldid = 50028)
begin
	Update U_UsersFields
	set SortOrder = a.SortOrder + 1
	from U_UsersFields a
		inner join (select * from u_Usersfields where AppID in (1128, 1001) and fieldid = 50004) b 
		on a.UserID = b.UserID and a.AppID = b.AppID and a.SortOrder > b.SortOrder
end
GO
if not exists (select * from u_usersfields where userid = 1 and appid = 1128 and fieldid = 50028)
begin
	insert into  [dbo].[U_UsersFields](AppID, RoleID, UserID, FieldID, SortOrder, GroupID, MultipleIndex, MandatoryStatus, Needed, FieldSize, DataType, SubAppID, LookUpTable, CIHI_ID, Header_CIHI_English, Header_CIHI_French, Header_CIHI_Spanish, Header_User, FieldEnabled, FieldVisible, DefaultValue, ParamProcID, ParamOrder, ParamName, ParamDataType, ParamSize, NeedForUserDef, MainDataType, UNoteCode)
	select AppID, RoleID, UserID, FieldID = 50028, 
		SortOrder = SortOrder + 1, 
		GroupID, MultipleIndex, MandatoryStatus = 1000, Needed = 0, FieldSize = 1, 
		DataType = 2,  
        SubAppID = 0, LookUpTable = '', CIHI_ID = '', 
		Header_CIHI_English = 'Institution Type', Header_CIHI_French = null, Header_CIHI_Spanish = null, 
		Header_User = 'Institution Type', 
		FieldEnabled = 2, 
        FieldVisible = 1, 
        DefaultValue = null, 
		ParamProcID, 
		ParamOrder = 13, 
		ParamName = 'InstType', 
		ParamDataType = 200, ParamSize = 1, NeedForUserDef = 0, 
		MainDataType = null, UNoteCode = null
	from [dbo].[U_UsersFields]
	where  AppID = 1128 and fieldid = 50004

	insert into  [dbo].[U_UsersFields](AppID, RoleID, UserID, FieldID, SortOrder, GroupID, MultipleIndex, MandatoryStatus, Needed, FieldSize, DataType, SubAppID, LookUpTable, CIHI_ID, Header_CIHI_English, Header_CIHI_French, Header_CIHI_Spanish, Header_User, FieldEnabled, FieldVisible, DefaultValue, ParamProcID, ParamOrder, ParamName, ParamDataType, ParamSize, NeedForUserDef, MainDataType, UNoteCode)
	select AppID, RoleID, UserID, FieldID = 50028, 
		SortOrder = SortOrder + 1, 
		GroupID, MultipleIndex, MandatoryStatus = 1000, Needed = 0, FieldSize = 1, 
		DataType = 2,  
        SubAppID = 0, LookUpTable = '', CIHI_ID = '', 
		Header_CIHI_English = 'Institution Type', Header_CIHI_French = null, Header_CIHI_Spanish = null, 
		Header_User = 'Institution Type', 
		FieldEnabled = 2, 
        FieldVisible = 1, 
        DefaultValue = null, 
		ParamProcID, 
		ParamOrder = 93, 
		ParamName = 'InstType', 
		ParamDataType = 200, ParamSize = 1, NeedForUserDef = 0, 
		MainDataType = null, UNoteCode = null
	from [dbo].[U_UsersFields]
	where  AppID = 1001 and fieldid = 50004
end
GO

--end build 5
delete from [dbo].[RPG_RPW] where [FiscalYear] = 2018
GO
Insert into [dbo].[RPG_RPW](FiscalYear, RG, RPG, Weight, ShortTrim, ShortStay, Trim, PDW) values(2018, 'Stroke', '1100', '2.5776', '3', '0.0609', '135', '0.046')
GO
Insert into [dbo].[RPG_RPW](FiscalYear, RG, RPG, Weight, ShortTrim, ShortStay, Trim, PDW) values(2018, 'Stroke', '1110', '2.0183', '3', '0.0609', '120', '0.046')
GO
Insert into [dbo].[RPG_RPW](FiscalYear, RG, RPG, Weight, ShortTrim, ShortStay, Trim, PDW) values(2018, 'Stroke', '1120', '1.4095', '5', '0.0609', '70', '0.046')
GO
Insert into [dbo].[RPG_RPW](FiscalYear, RG, RPG, Weight, ShortTrim, ShortStay, Trim, PDW) values(2018, 'Stroke', '1130', '1.2219', '4', '0.0609', '63', '0.046')
GO
Insert into [dbo].[RPG_RPW](FiscalYear, RG, RPG, Weight, ShortTrim, ShortStay, Trim, PDW) values(2018, 'Stroke', '1140', '0.8632', '3', '0.0609', '45', '0.046')
GO
Insert into [dbo].[RPG_RPW](FiscalYear, RG, RPG, Weight, ShortTrim, ShortStay, Trim, PDW) values(2018, 'Stroke', '1150', '0.7331', '3', '0.0609', '46', '0.046')
GO
Insert into [dbo].[RPG_RPW](FiscalYear, RG, RPG, Weight, ShortTrim, ShortStay, Trim, PDW) values(2018, 'Stroke', '1160', '0.5757', '3', '0.0609', '27', '0.046')
GO
Insert into [dbo].[RPG_RPW](FiscalYear, RG, RPG, Weight, ShortTrim, ShortStay, Trim, PDW) values(2018, 'Traumatic Brain Injury', '1200', '7.9609', '3', '0.0609', '324', '0.0488')
GO
Insert into [dbo].[RPG_RPW](FiscalYear, RG, RPG, Weight, ShortTrim, ShortStay, Trim, PDW) values(2018, 'Traumatic Brain Injury', '1210', '3.1887', '5', '0.0609', '198', '0.0488')
GO
Insert into [dbo].[RPG_RPW](FiscalYear, RG, RPG, Weight, ShortTrim, ShortStay, Trim, PDW) values(2018, 'Traumatic Brain Injury', '1220', '2.5673', '3', '0.0609', '226', '0.0488')
GO
Insert into [dbo].[RPG_RPW](FiscalYear, RG, RPG, Weight, ShortTrim, ShortStay, Trim, PDW) values(2018, 'Traumatic Brain Injury', '1230', '2.2749', '4', '0.0609', '151', '0.0488')
GO
Insert into [dbo].[RPG_RPW](FiscalYear, RG, RPG, Weight, ShortTrim, ShortStay, Trim, PDW) values(2018, 'Traumatic Brain Injury', '1240', '1.8047', '3', '0.0609', '119', '0.0488')
GO
Insert into [dbo].[RPG_RPW](FiscalYear, RG, RPG, Weight, ShortTrim, ShortStay, Trim, PDW) values(2018, 'Traumatic Brain Injury', '1250', '1.0897', '3', '0.0609', '62', '0.0488')
GO
Insert into [dbo].[RPG_RPW](FiscalYear, RG, RPG, Weight, ShortTrim, ShortStay, Trim, PDW) values(2018, 'Non-Traumatic Brain Injury', '1300', '2.5929', '4', '0.0609', '190', '0.0633')
GO
Insert into [dbo].[RPG_RPW](FiscalYear, RG, RPG, Weight, ShortTrim, ShortStay, Trim, PDW) values(2018, 'Non-Traumatic Brain Injury', '1310', '1.61', '3', '0.0609', '102', '0.0633')
GO
Insert into [dbo].[RPG_RPW](FiscalYear, RG, RPG, Weight, ShortTrim, ShortStay, Trim, PDW) values(2018, 'Non-Traumatic Brain Injury', '1320', '1.3753', '4', '0.0609', '89', '0.0633')
GO
Insert into [dbo].[RPG_RPW](FiscalYear, RG, RPG, Weight, ShortTrim, ShortStay, Trim, PDW) values(2018, 'Non-Traumatic Brain Injury', '1330', '0.8252', '3', '0.0609', '74', '0.0633')
GO
Insert into [dbo].[RPG_RPW](FiscalYear, RG, RPG, Weight, ShortTrim, ShortStay, Trim, PDW) values(2018, 'Neurological', '1400', '2.1568', '3', '0.0609', '156', '0.048')
GO
Insert into [dbo].[RPG_RPW](FiscalYear, RG, RPG, Weight, ShortTrim, ShortStay, Trim, PDW) values(2018, 'Neurological', '1410', '1.3013', '4', '0.0609', '89', '0.048')
GO
Insert into [dbo].[RPG_RPW](FiscalYear, RG, RPG, Weight, ShortTrim, ShortStay, Trim, PDW) values(2018, 'Neurological', '1420', '0.8529', '3', '0.0609', '65', '0.048')
GO
Insert into [dbo].[RPG_RPW](FiscalYear, RG, RPG, Weight, ShortTrim, ShortStay, Trim, PDW) values(2018, 'Neurological', '1430', '0.5552', '3', '0.0609', '56', '0.048')
GO
Insert into [dbo].[RPG_RPW](FiscalYear, RG, RPG, Weight, ShortTrim, ShortStay, Trim, PDW) values(2018, 'Traumatic Spinal Cord Injury', '1500', '5.7783', '10', '0.0609', '311', '0.0531')
GO
Insert into [dbo].[RPG_RPW](FiscalYear, RG, RPG, Weight, ShortTrim, ShortStay, Trim, PDW) values(2018, 'Traumatic Spinal Cord Injury', '1510', '4.7592', '14', '0.0609', '239', '0.0531')
GO
Insert into [dbo].[RPG_RPW](FiscalYear, RG, RPG, Weight, ShortTrim, ShortStay, Trim, PDW) values(2018, 'Traumatic Spinal Cord Injury', '1520', '3.4688', '3', '0.0609', '154', '0.0531')
GO
Insert into [dbo].[RPG_RPW](FiscalYear, RG, RPG, Weight, ShortTrim, ShortStay, Trim, PDW) values(2018, 'Traumatic Spinal Cord Injury', '1530', '1.8003', '3', '0.0609', '82', '0.0531')
GO
Insert into [dbo].[RPG_RPW](FiscalYear, RG, RPG, Weight, ShortTrim, ShortStay, Trim, PDW) values(2018, 'Non-Traumatic Spinal Cord Injury', '1600', '3.329', '5', '0.0609', '141', '0.0506')
GO
Insert into [dbo].[RPG_RPW](FiscalYear, RG, RPG, Weight, ShortTrim, ShortStay, Trim, PDW) values(2018, 'Non-Traumatic Spinal Cord Injury', '1610', '1.7984', '5', '0.0609', '110', '0.0506')
GO
Insert into [dbo].[RPG_RPW](FiscalYear, RG, RPG, Weight, ShortTrim, ShortStay, Trim, PDW) values(2018, 'Non-Traumatic Spinal Cord Injury', '1620', '2.175', '4', '0.0609', '102', '0.0506')
GO
Insert into [dbo].[RPG_RPW](FiscalYear, RG, RPG, Weight, ShortTrim, ShortStay, Trim, PDW) values(2018, 'Non-Traumatic Spinal Cord Injury', '1630', '1.206', '3', '0.0609', '69', '0.0506')
GO
Insert into [dbo].[RPG_RPW](FiscalYear, RG, RPG, Weight, ShortTrim, ShortStay, Trim, PDW) values(2018, 'Non-Traumatic Spinal Cord Injury', '1640', '0.9387', '3', '0.0609', '51', '0.0506')
GO
Insert into [dbo].[RPG_RPW](FiscalYear, RG, RPG, Weight, ShortTrim, ShortStay, Trim, PDW) values(2018, 'Amputation, Not Lower Extremity', '1700', '1.2981', '3', '0.0609', '131', '0.0468')
GO
Insert into [dbo].[RPG_RPW](FiscalYear, RG, RPG, Weight, ShortTrim, ShortStay, Trim, PDW) values(2018, 'Amputation, Not Lower Extremity', '1710', '1.3962', '6', '0.0609', '77', '0.0468')
GO
Insert into [dbo].[RPG_RPW](FiscalYear, RG, RPG, Weight, ShortTrim, ShortStay, Trim, PDW) values(2018, 'Amputation, Lower Extremity', '1800', '1.9539', '4', '0.0609', '121', '0.043')
GO
Insert into [dbo].[RPG_RPW](FiscalYear, RG, RPG, Weight, ShortTrim, ShortStay, Trim, PDW) values(2018, 'Amputation, Lower Extremity', '1810', '1.5485', '4', '0.0609', '77', '0.043')
GO
Insert into [dbo].[RPG_RPW](FiscalYear, RG, RPG, Weight, ShortTrim, ShortStay, Trim, PDW) values(2018, 'Amputation, Lower Extremity', '1820', '1.4195', '8', '0.0609', '60', '0.043')
GO
Insert into [dbo].[RPG_RPW](FiscalYear, RG, RPG, Weight, ShortTrim, ShortStay, Trim, PDW) values(2018, 'Amputation, Lower Extremity', '1830', '1.1963', '3', '0.0609', '63', '0.043')
GO
Insert into [dbo].[RPG_RPW](FiscalYear, RG, RPG, Weight, ShortTrim, ShortStay, Trim, PDW) values(2018, 'Osteoarthritis', '1900', '0.8546', '3', '0.0609', '101', '0.0469')
GO
Insert into [dbo].[RPG_RPW](FiscalYear, RG, RPG, Weight, ShortTrim, ShortStay, Trim, PDW) values(2018, 'Osteoarthritis', '1910', '0.6484', '3', '0.0609', '29', '0.0469')
GO
Insert into [dbo].[RPG_RPW](FiscalYear, RG, RPG, Weight, ShortTrim, ShortStay, Trim, PDW) values(2018, 'Rheumatoid arthritis and Other Arthritis', '2000', '1.0298', '3', '0.0609', '67', '0.0453')
GO
Insert into [dbo].[RPG_RPW](FiscalYear, RG, RPG, Weight, ShortTrim, ShortStay, Trim, PDW) values(2018, 'Rheumatoid arthritis and Other Arthritis', '2010', '0.6665', '3', '0.0609', '19', '0.0453')
GO
Insert into [dbo].[RPG_RPW](FiscalYear, RG, RPG, Weight, ShortTrim, ShortStay, Trim, PDW) values(2018, 'Pain', '2100', '0.8501', '3', '0.0609', '68', '0.0431')
GO
Insert into [dbo].[RPG_RPW](FiscalYear, RG, RPG, Weight, ShortTrim, ShortStay, Trim, PDW) values(2018, 'Pain', '2110', '0.5998', '3', '0.0609', '25', '0.0431')
GO
Insert into [dbo].[RPG_RPW](FiscalYear, RG, RPG, Weight, ShortTrim, ShortStay, Trim, PDW) values(2018, 'Fracture of Lower Extremity', '2200', '1.2473', '4', '0.0609', '79', '0.0396')
GO
Insert into [dbo].[RPG_RPW](FiscalYear, RG, RPG, Weight, ShortTrim, ShortStay, Trim, PDW) values(2018, 'Fracture of Lower Extremity', '2210', '1.3908', '4', '0.0609', '87', '0.0396')
GO
Insert into [dbo].[RPG_RPW](FiscalYear, RG, RPG, Weight, ShortTrim, ShortStay, Trim, PDW) values(2018, 'Fracture of Lower Extremity', '2220', '1.0265', '4', '0.0609', '67', '0.0396')
GO
Insert into [dbo].[RPG_RPW](FiscalYear, RG, RPG, Weight, ShortTrim, ShortStay, Trim, PDW) values(2018, 'Fracture of Lower Extremity', '2230', '0.931', '4', '0.0609', '50', '0.0396')
GO
Insert into [dbo].[RPG_RPW](FiscalYear, RG, RPG, Weight, ShortTrim, ShortStay, Trim, PDW) values(2018, 'Fracture of Lower Extremity', '2240', '0.7707', '3', '0.0609', '39', '0.0396')
GO
Insert into [dbo].[RPG_RPW](FiscalYear, RG, RPG, Weight, ShortTrim, ShortStay, Trim, PDW) values(2018, 'Fracture of Lower Extremity', '2250', '0.6615', '3', '0.0609', '35', '0.0396')
GO
Insert into [dbo].[RPG_RPW](FiscalYear, RG, RPG, Weight, ShortTrim, ShortStay, Trim, PDW) values(2018, 'Replacement of Lower Extremity', '2300', '0.8774', '3', '0.0609', '58', '0.0407')
GO
Insert into [dbo].[RPG_RPW](FiscalYear, RG, RPG, Weight, ShortTrim, ShortStay, Trim, PDW) values(2018, 'Replacement of Lower Extremity', '2310', '0.6069', '3', '0.0609', '47', '0.0407')
GO
Insert into [dbo].[RPG_RPW](FiscalYear, RG, RPG, Weight, ShortTrim, ShortStay, Trim, PDW) values(2018, 'Replacement of Lower Extremity', '2320', '0.6186', '3', '0.0609', '40', '0.0407')
GO
Insert into [dbo].[RPG_RPW](FiscalYear, RG, RPG, Weight, ShortTrim, ShortStay, Trim, PDW) values(2018, 'Replacement of Lower Extremity', '2330', '0.4803', '3', '0.0609', '26', '0.0407')
GO
Insert into [dbo].[RPG_RPW](FiscalYear, RG, RPG, Weight, ShortTrim, ShortStay, Trim, PDW) values(2018, 'Replacement of Lower Extremity', '2340', '0.4315', '3', '0.0609', '24', '0.0407')
GO
Insert into [dbo].[RPG_RPW](FiscalYear, RG, RPG, Weight, ShortTrim, ShortStay, Trim, PDW) values(2018, 'Replacement of Lower Extremity', '2350', '0.4221', '3', '0.0609', '23', '0.0407')
GO
Insert into [dbo].[RPG_RPW](FiscalYear, RG, RPG, Weight, ShortTrim, ShortStay, Trim, PDW) values(2018, 'Other Orthopedic', '2400', '1.2014', '4', '0.0609', '79', '0.0447')
GO
Insert into [dbo].[RPG_RPW](FiscalYear, RG, RPG, Weight, ShortTrim, ShortStay, Trim, PDW) values(2018, 'Other Orthopedic', '2410', '1.0435', '3', '0.0609', '77', '0.0447')
GO
Insert into [dbo].[RPG_RPW](FiscalYear, RG, RPG, Weight, ShortTrim, ShortStay, Trim, PDW) values(2018, 'Other Orthopedic', '2420', '0.8647', '3', '0.0609', '61', '0.0447')
GO
Insert into [dbo].[RPG_RPW](FiscalYear, RG, RPG, Weight, ShortTrim, ShortStay, Trim, PDW) values(2018, 'Other Orthopedic', '2430', '0.6286', '3', '0.0609', '50', '0.0447')
GO
Insert into [dbo].[RPG_RPW](FiscalYear, RG, RPG, Weight, ShortTrim, ShortStay, Trim, PDW) values(2018, 'Other Orthopedic', '2440', '0.5684', '3', '0.0609', '38', '0.0447')
GO
Insert into [dbo].[RPG_RPW](FiscalYear, RG, RPG, Weight, ShortTrim, ShortStay, Trim, PDW) values(2018, 'Cardiac', '2500', '1.0335', '3', '0.0609', '76', '0.0395')
GO
Insert into [dbo].[RPG_RPW](FiscalYear, RG, RPG, Weight, ShortTrim, ShortStay, Trim, PDW) values(2018, 'Cardiac', '2510', '0.8618', '3', '0.0609', '69', '0.0395')
GO
Insert into [dbo].[RPG_RPW](FiscalYear, RG, RPG, Weight, ShortTrim, ShortStay, Trim, PDW) values(2018, 'Cardiac', '2520', '0.5395', '3', '0.0609', '35', '0.0395')
GO
Insert into [dbo].[RPG_RPW](FiscalYear, RG, RPG, Weight, ShortTrim, ShortStay, Trim, PDW) values(2018, 'Cardiac', '2530', '0.4927', '3', '0.0609', '31', '0.0395')
GO
Insert into [dbo].[RPG_RPW](FiscalYear, RG, RPG, Weight, ShortTrim, ShortStay, Trim, PDW) values(2018, 'Cardiac', '2540', '0.5937', '3', '0.0609', '42', '0.0395')
GO
Insert into [dbo].[RPG_RPW](FiscalYear, RG, RPG, Weight, ShortTrim, ShortStay, Trim, PDW) values(2018, 'Pulmonary', '2600', '0.9844', '3', '0.0609', '94', '0.0308')
GO
Insert into [dbo].[RPG_RPW](FiscalYear, RG, RPG, Weight, ShortTrim, ShortStay, Trim, PDW) values(2018, 'Pulmonary', '2610', '0.7224', '3', '0.0609', '53', '0.0308')
GO
Insert into [dbo].[RPG_RPW](FiscalYear, RG, RPG, Weight, ShortTrim, ShortStay, Trim, PDW) values(2018, 'Pulmonary', '2620', '0.9375', '3', '0.0609', '65', '0.0308')
GO
Insert into [dbo].[RPG_RPW](FiscalYear, RG, RPG, Weight, ShortTrim, ShortStay, Trim, PDW) values(2018, 'Pulmonary', '2630', '0.9604', '3', '0.0609', '61', '0.0308')
GO
Insert into [dbo].[RPG_RPW](FiscalYear, RG, RPG, Weight, ShortTrim, ShortStay, Trim, PDW) values(2018, 'Burns', '2700', '1.5094', '4', '0.0609', '102', '0.0265')
GO
Insert into [dbo].[RPG_RPW](FiscalYear, RG, RPG, Weight, ShortTrim, ShortStay, Trim, PDW) values(2018, 'Maj Mult Trauma, Oth Mult Trauma & Maj Mult Frac', '2800', '2.1578', '3', '0.0609', '125', '0.0345')
GO
Insert into [dbo].[RPG_RPW](FiscalYear, RG, RPG, Weight, ShortTrim, ShortStay, Trim, PDW) values(2018, 'Maj Mult Trauma, Oth Mult Trauma & Maj Mult Frac', '2810', '1.1257', '3', '0.0609', '86', '0.0345')
GO
Insert into [dbo].[RPG_RPW](FiscalYear, RG, RPG, Weight, ShortTrim, ShortStay, Trim, PDW) values(2018, 'Maj Mult Trauma, Oth Mult Trauma & Maj Mult Frac', '2820', '1.4613', '4', '0.0609', '94', '0.0345')
GO
Insert into [dbo].[RPG_RPW](FiscalYear, RG, RPG, Weight, ShortTrim, ShortStay, Trim, PDW) values(2018, 'Maj Mult Trauma, Oth Mult Trauma & Maj Mult Frac', '2830', '1.0364', '4', '0.0609', '71', '0.0345')
GO
Insert into [dbo].[RPG_RPW](FiscalYear, RG, RPG, Weight, ShortTrim, ShortStay, Trim, PDW) values(2018, 'Maj Mult Trauma, Oth Mult Trauma & Maj Mult Frac', '2840', '0.7986', '3', '0.0609', '69', '0.0345')
GO
Insert into [dbo].[RPG_RPW](FiscalYear, RG, RPG, Weight, ShortTrim, ShortStay, Trim, PDW) values(2018, 'Maj Mult Trauma with Brain or Spinal Cord Injury', '2900', '3.1652', '5', '0.0609', '157', '0.0493')
GO
Insert into [dbo].[RPG_RPW](FiscalYear, RG, RPG, Weight, ShortTrim, ShortStay, Trim, PDW) values(2018, 'Maj Mult Trauma with Brain or Spinal Cord Injury', '2910', '1.3816', '5', '0.0609', '122', '0.0493')
GO
Insert into [dbo].[RPG_RPW](FiscalYear, RG, RPG, Weight, ShortTrim, ShortStay, Trim, PDW) values(2018, 'Maj Mult Trauma with Brain or Spinal Cord Injury', '2920', '0.724', '3', '0.0609', '44', '0.0493')
GO
Insert into [dbo].[RPG_RPW](FiscalYear, RG, RPG, Weight, ShortTrim, ShortStay, Trim, PDW) values(2018, 'Ventilator Dependent Respiratory Disorders', '3000', '0.9139', '3', '0.0609', '57', '0.0393')
GO
Insert into [dbo].[RPG_RPW](FiscalYear, RG, RPG, Weight, ShortTrim, ShortStay, Trim, PDW) values(2018, 'Other Disabilities', '3100', '1.1602', '3', '0.0609', '82', '0.0433')
GO
Insert into [dbo].[RPG_RPW](FiscalYear, RG, RPG, Weight, ShortTrim, ShortStay, Trim, PDW) values(2018, 'Other Disabilities', '3110', '0.8589', '3', '0.0609', '53', '0.0433')
GO
Insert into [dbo].[RPG_RPW](FiscalYear, RG, RPG, Weight, ShortTrim, ShortStay, Trim, PDW) values(2018, 'Other Disabilities', '3120', '1.1448', '3', '0.0609', '127', '0.0433')
GO
Insert into [dbo].[RPG_RPW](FiscalYear, RG, RPG, Weight, ShortTrim, ShortStay, Trim, PDW) values(2018, 'Other Disabilities', '3130', '0.7899', '3', '0.0609', '50', '0.0433')
GO
Insert into [dbo].[RPG_RPW](FiscalYear, RG, RPG, Weight, ShortTrim, ShortStay, Trim, PDW) values(2018, 'Other Disabilities', '3140', '0.7078', '3', '0.0609', '57', '0.0433')
GO
--1186
update lu_codefile set userdescription = 'Frisian, Western', defaultdescription = 'Frisian, Western' where lookupid = 47010 and cihivalue = 'fry' 
GO
update lu_codefile set userdescription = 'Oriya (macrolanguage)', defaultdescription = 'Oriya (macrolanguage)' where lookupid = 47010 and cihivalue = 'ori' 
GO
update lu_codefile set userdescription = 'Panjabi', defaultdescription = 'Panjabi' where lookupid = 47010 and cihivalue = 'pan' 
GO
update lu_codefile set userdescription = 'Uyghur', defaultdescription = 'Uyghur' where lookupid = 47010 and cihivalue = 'uig' 
GO
update lu_codefile set userdescription = 'Neo-Aramaic, Assyrian', defaultdescription = 'Neo-Aramaic, Assyrian' where lookupid = 47010 and cihivalue = 'aii' 
GO
update lu_codefile set userdescription = 'Neo-Aramaic, Chaldean', defaultdescription = 'Neo-Aramaic, Chaldean' where lookupid = 47010 and cihivalue = 'cld' 
GO
update lu_codefile set userdescription = 'Cree, Southern East', defaultdescription = 'Cree, Southern East' where lookupid = 47010 and cihivalue = 'crj' 
GO
update lu_codefile set userdescription = 'Cree, Northern East', defaultdescription = 'Cree, Northern East' where lookupid = 47010 and cihivalue = 'crl' 
GO
update lu_codefile set userdescription = 'Cree, Moose', defaultdescription = 'Cree, Moose' where lookupid = 47010 and cihivalue = 'crm' 
GO
update lu_codefile set ValidTo = '2099/03/31' 
where lookupid = 47010 and cihivalue in ('din', 'fry', 'hil', 'kas', 'msa', 'ori', 'uig', 'uzb', 
'byn', 'war', 'yor', 'aii', 'cld', 'coo', 'crj', 'crl', 'crm', 'fuf', 'har', 'otw', 'vls')
GO
--2.x-1089
if not exists (select * from information_schema.columns where table_name = 'LU_AJCC7_SITE_GRP' and column_name = 'Staging_Version')
    alter table LU_AJCC7_SITE_GRP add Staging_Version varchar(10)
GO
Update LU_AJCC7_SITE_GRP set Staging_Version = '07' where Staging_Version is null
GO
Update LU_AJCC7_SITE_GRP set ValidTo = '2017/12/31' where Staging_Version = '07'
GO
if not exists (select * from information_schema.columns where table_name = 'LLU_CC_STAGE_VALUE' and column_name = 'Staging_Version')
    alter table LLU_CC_STAGE_VALUE add Staging_Version varchar(10)
GO
Update LLU_CC_STAGE_VALUE set Staging_Version = '07' where Staging_Version is null and isnumeric([AJCC_SITE_GRP]) = 1
GO
Update LLU_CC_STAGE_VALUE set Staging_Version = '06' where Staging_Version is null and isnumeric([AJCC_SITE_GRP]) = 0
GO
Update lu_codefile set Validto = '2017/12/31' where lookupid = 1001 and CIHIValue = '07'
GO
delete from lu_codefile where code = 200508
GO
Insert into LU_CodeFile(Code, DisplayCode, UserDescription, DefaultDescription, CIHIValue, ValidFrom, ValidTo, InterfaceIn, LookupID, HospitalID, LUText)
values(200508, '08', 'AJCC 8th Edition', 'AJCC 8th Edition', '08', '2018-01-01', '2099-03-31', '08', 1001, 0, '')
GO
Update LLU_CC_STAGE_VALUE set ValidTo = '2017/12/31' where Staging_Version = '07'
GO
Update LLU_CC_SITE_GROUP set ValidTo = '2017/12/31' where Staging_Version = '07'
GO
Update LLU_CC_SITE_GROUP set ValidTo = '2010/03/31' where Staging_Version = '06'
GO
Update [dbo].[LLU_CC_ICD03_Topo_Morpho] set ValidTo = '2017/12/31' where Staging_Version = '07'
GO
Update [dbo].[LLU_CC_ICD03_Topo_Morpho] set ValidTo = '2010/03/31' where Staging_Version = '06'
GO

Delete from [dbo].[LU_AJCC7_SITE_GRP] where code between 101 and 167
GO
if exists (select * from information_schema.tables where table_name = 'zzACCC8')
    drop table zzACCC8
GO
create table dbo.zzACCC8(
    code int identity(1, 1),
    AJCC8_CHAPTER_KEY varchar(10),
    AJCC8_CHAPTER_NUM varchar(10),
    AJCC8_CHAPTER_DESCR  varchar(200)
)
GO
Insert into zzACCC8(AJCC8_CHAPTER_KEY, AJCC8_CHAPTER_NUM, AJCC8_CHAPTER_DESCR) values('1', '6', 'Cervical Lymph Nodes and Unknown Primary Tumors of the Head and Neck')
GO
Insert into zzACCC8(AJCC8_CHAPTER_KEY, AJCC8_CHAPTER_NUM, AJCC8_CHAPTER_DESCR) values('2', '7', 'Lip and Oral Cavity')
GO
Insert into zzACCC8(AJCC8_CHAPTER_KEY, AJCC8_CHAPTER_NUM, AJCC8_CHAPTER_DESCR) values('3', '8', 'Major Salivary Glands')
GO
Insert into zzACCC8(AJCC8_CHAPTER_KEY, AJCC8_CHAPTER_NUM, AJCC8_CHAPTER_DESCR) values('4', '9', 'Nasopharynx')
GO
Insert into zzACCC8(AJCC8_CHAPTER_KEY, AJCC8_CHAPTER_NUM, AJCC8_CHAPTER_DESCR) values('5', '10', 'HPV-Mediated (p16+) Oropharyngeal Cancer')
GO
Insert into zzACCC8(AJCC8_CHAPTER_KEY, AJCC8_CHAPTER_NUM, AJCC8_CHAPTER_DESCR) values('6', '11', 'Oropharynx (p16-) and Hypopharynx')
GO
Insert into zzACCC8(AJCC8_CHAPTER_KEY, AJCC8_CHAPTER_NUM, AJCC8_CHAPTER_DESCR) values('7', '12', 'Nasal Cavity and Paranasal Sinuses')
GO
Insert into zzACCC8(AJCC8_CHAPTER_KEY, AJCC8_CHAPTER_NUM, AJCC8_CHAPTER_DESCR) values('8', '13', 'Larynx')
GO
Insert into zzACCC8(AJCC8_CHAPTER_KEY, AJCC8_CHAPTER_NUM, AJCC8_CHAPTER_DESCR) values('9', '15', 'Cutaneous Squamous Cell Carcinoma of the Head and Neck')
GO
Insert into zzACCC8(AJCC8_CHAPTER_KEY, AJCC8_CHAPTER_NUM, AJCC8_CHAPTER_DESCR) values('10', '16A', 'Esophagus and Esophagogastric Junction (squamous component)')
GO
Insert into zzACCC8(AJCC8_CHAPTER_KEY, AJCC8_CHAPTER_NUM, AJCC8_CHAPTER_DESCR) values('11', '16B', 'Esophagus and Esophagogastric Junction (no squamous component)')
GO
Insert into zzACCC8(AJCC8_CHAPTER_KEY, AJCC8_CHAPTER_NUM, AJCC8_CHAPTER_DESCR) values('12', '17', 'Stomach')
GO
Insert into zzACCC8(AJCC8_CHAPTER_KEY, AJCC8_CHAPTER_NUM, AJCC8_CHAPTER_DESCR) values('13', '18', 'Small Intestine')
GO
Insert into zzACCC8(AJCC8_CHAPTER_KEY, AJCC8_CHAPTER_NUM, AJCC8_CHAPTER_DESCR) values('14', '19', 'Appendix - Carcinoma')
GO
Insert into zzACCC8(AJCC8_CHAPTER_KEY, AJCC8_CHAPTER_NUM, AJCC8_CHAPTER_DESCR) values('15', '20', 'Colon and Rectum')
GO
Insert into zzACCC8(AJCC8_CHAPTER_KEY, AJCC8_CHAPTER_NUM, AJCC8_CHAPTER_DESCR) values('16', '21', 'Anus')
GO
Insert into zzACCC8(AJCC8_CHAPTER_KEY, AJCC8_CHAPTER_NUM, AJCC8_CHAPTER_DESCR) values('17', '22', 'Liver')
GO
Insert into zzACCC8(AJCC8_CHAPTER_KEY, AJCC8_CHAPTER_NUM, AJCC8_CHAPTER_DESCR) values('18', '23', 'Intrahepatic Bile Ducts')
GO
Insert into zzACCC8(AJCC8_CHAPTER_KEY, AJCC8_CHAPTER_NUM, AJCC8_CHAPTER_DESCR) values('19', '24', 'Gallbladder')
GO
Insert into zzACCC8(AJCC8_CHAPTER_KEY, AJCC8_CHAPTER_NUM, AJCC8_CHAPTER_DESCR) values('20', '25', 'Perihilar Bile Ducts')
GO
Insert into zzACCC8(AJCC8_CHAPTER_KEY, AJCC8_CHAPTER_NUM, AJCC8_CHAPTER_DESCR) values('21', '26', 'Distal Bile Duct')
GO
Insert into zzACCC8(AJCC8_CHAPTER_KEY, AJCC8_CHAPTER_NUM, AJCC8_CHAPTER_DESCR) values('22', '27', 'Ampulla of Vater')
GO
Insert into zzACCC8(AJCC8_CHAPTER_KEY, AJCC8_CHAPTER_NUM, AJCC8_CHAPTER_DESCR) values('23', '28', 'Pancreas - Exocrine')
GO
Insert into zzACCC8(AJCC8_CHAPTER_KEY, AJCC8_CHAPTER_NUM, AJCC8_CHAPTER_DESCR) values('24', '29', 'Neuroendocrine Tumors of the Stomach')
GO
Insert into zzACCC8(AJCC8_CHAPTER_KEY, AJCC8_CHAPTER_NUM, AJCC8_CHAPTER_DESCR) values('25', '30', 'Neuroendocrine Tumors of the Duodenum and Ampulla of Vater')
GO
Insert into zzACCC8(AJCC8_CHAPTER_KEY, AJCC8_CHAPTER_NUM, AJCC8_CHAPTER_DESCR) values('26', '31', 'Neuroendocrine Tumors of the Jejunum and Ileum')
GO
Insert into zzACCC8(AJCC8_CHAPTER_KEY, AJCC8_CHAPTER_NUM, AJCC8_CHAPTER_DESCR) values('27', '32', 'Neuroendocrine Tumors of the Appendix')
GO
Insert into zzACCC8(AJCC8_CHAPTER_KEY, AJCC8_CHAPTER_NUM, AJCC8_CHAPTER_DESCR) values('28', '33', 'Neuroendocrine Tumors of the Colon and Rectum')
GO
Insert into zzACCC8(AJCC8_CHAPTER_KEY, AJCC8_CHAPTER_NUM, AJCC8_CHAPTER_DESCR) values('29', '34', 'Neuroendocrine Tumors of the Pancreas')
GO
Insert into zzACCC8(AJCC8_CHAPTER_KEY, AJCC8_CHAPTER_NUM, AJCC8_CHAPTER_DESCR) values('30', '35', 'Thymus')
GO
Insert into zzACCC8(AJCC8_CHAPTER_KEY, AJCC8_CHAPTER_NUM, AJCC8_CHAPTER_DESCR) values('31', '36', 'Lung')
GO
Insert into zzACCC8(AJCC8_CHAPTER_KEY, AJCC8_CHAPTER_NUM, AJCC8_CHAPTER_DESCR) values('32', '37', 'Malignant Pleural Mesothelioma')
GO
Insert into zzACCC8(AJCC8_CHAPTER_KEY, AJCC8_CHAPTER_NUM, AJCC8_CHAPTER_DESCR) values('33', '38', 'Bone (appendicular skeleton, spine, and pelvis)')
GO
Insert into zzACCC8(AJCC8_CHAPTER_KEY, AJCC8_CHAPTER_NUM, AJCC8_CHAPTER_DESCR) values('34', '41', 'Soft Tissue Sarcoma of the Trunk and Extremities')
GO
Insert into zzACCC8(AJCC8_CHAPTER_KEY, AJCC8_CHAPTER_NUM, AJCC8_CHAPTER_DESCR) values('35', '43A', 'Gastrointestinal Stromal Tumor (Small Intestinal, Esophageal, Colorectal, Mesenteric, and Peritoneal)')
GO
Insert into zzACCC8(AJCC8_CHAPTER_KEY, AJCC8_CHAPTER_NUM, AJCC8_CHAPTER_DESCR) values('36', '43B', 'Gastrointestinal Stromal Tumor (Gastric and Omental)')
GO
Insert into zzACCC8(AJCC8_CHAPTER_KEY, AJCC8_CHAPTER_NUM, AJCC8_CHAPTER_DESCR) values('37', '44', 'Soft Tissue Sarcoma of the Retroperitoneum')
GO
Insert into zzACCC8(AJCC8_CHAPTER_KEY, AJCC8_CHAPTER_NUM, AJCC8_CHAPTER_DESCR) values('38', '46', 'Merkel Cell Carcinoma')
GO
Insert into zzACCC8(AJCC8_CHAPTER_KEY, AJCC8_CHAPTER_NUM, AJCC8_CHAPTER_DESCR) values('39', '47', 'Melanoma of the Skin')
GO
Insert into zzACCC8(AJCC8_CHAPTER_KEY, AJCC8_CHAPTER_NUM, AJCC8_CHAPTER_DESCR) values('40', '48', 'Breast')
GO
Insert into zzACCC8(AJCC8_CHAPTER_KEY, AJCC8_CHAPTER_NUM, AJCC8_CHAPTER_DESCR) values('41', '50', 'Vulva')
GO
Insert into zzACCC8(AJCC8_CHAPTER_KEY, AJCC8_CHAPTER_NUM, AJCC8_CHAPTER_DESCR) values('42', '51', 'Vagina')
GO
Insert into zzACCC8(AJCC8_CHAPTER_KEY, AJCC8_CHAPTER_NUM, AJCC8_CHAPTER_DESCR) values('43', '52', 'Cervix Uteri')
GO
Insert into zzACCC8(AJCC8_CHAPTER_KEY, AJCC8_CHAPTER_NUM, AJCC8_CHAPTER_DESCR) values('44', '53', 'Corpus Uteri - Carcinoma and Carcinosarcoma')
GO
Insert into zzACCC8(AJCC8_CHAPTER_KEY, AJCC8_CHAPTER_NUM, AJCC8_CHAPTER_DESCR) values('45', '54A', 'Corpus Uteri - Leiomyosarcoma and Endometrial Stromal Sarcoma')
GO
Insert into zzACCC8(AJCC8_CHAPTER_KEY, AJCC8_CHAPTER_NUM, AJCC8_CHAPTER_DESCR) values('46', '54B', 'Corpus Uteri - Adenosarcoma')
GO
Insert into zzACCC8(AJCC8_CHAPTER_KEY, AJCC8_CHAPTER_NUM, AJCC8_CHAPTER_DESCR) values('47', '55', 'Ovary, Fallopian Tube, and Primary Peritoneal Carcinoma')
GO
Insert into zzACCC8(AJCC8_CHAPTER_KEY, AJCC8_CHAPTER_NUM, AJCC8_CHAPTER_DESCR) values('48', '56', 'Gestational Trophoblastic Neoplasms')
GO
Insert into zzACCC8(AJCC8_CHAPTER_KEY, AJCC8_CHAPTER_NUM, AJCC8_CHAPTER_DESCR) values('49', '57', 'Penis')
GO
Insert into zzACCC8(AJCC8_CHAPTER_KEY, AJCC8_CHAPTER_NUM, AJCC8_CHAPTER_DESCR) values('50', '58', 'Prostate')
GO
Insert into zzACCC8(AJCC8_CHAPTER_KEY, AJCC8_CHAPTER_NUM, AJCC8_CHAPTER_DESCR) values('51', '59', 'Testis')
GO
Insert into zzACCC8(AJCC8_CHAPTER_KEY, AJCC8_CHAPTER_NUM, AJCC8_CHAPTER_DESCR) values('52', '60', 'Kidney')
GO
Insert into zzACCC8(AJCC8_CHAPTER_KEY, AJCC8_CHAPTER_NUM, AJCC8_CHAPTER_DESCR) values('53', '61', 'Renal Pelvis and Ureter')
GO
Insert into zzACCC8(AJCC8_CHAPTER_KEY, AJCC8_CHAPTER_NUM, AJCC8_CHAPTER_DESCR) values('54', '62', 'Urinary Bladder')
GO
Insert into zzACCC8(AJCC8_CHAPTER_KEY, AJCC8_CHAPTER_NUM, AJCC8_CHAPTER_DESCR) values('55', '63', 'Urethra')
GO
Insert into zzACCC8(AJCC8_CHAPTER_KEY, AJCC8_CHAPTER_NUM, AJCC8_CHAPTER_DESCR) values('56', '64', 'Eyelid Carcinoma')
GO
Insert into zzACCC8(AJCC8_CHAPTER_KEY, AJCC8_CHAPTER_NUM, AJCC8_CHAPTER_DESCR) values('57', '67', 'Uveal Melanoma')
GO
Insert into zzACCC8(AJCC8_CHAPTER_KEY, AJCC8_CHAPTER_NUM, AJCC8_CHAPTER_DESCR) values('58', '68', 'Retinoblastoma')
GO
Insert into zzACCC8(AJCC8_CHAPTER_KEY, AJCC8_CHAPTER_NUM, AJCC8_CHAPTER_DESCR) values('59', '73A', 'Thyroid - Differentiated Carcinoma')
GO
Insert into zzACCC8(AJCC8_CHAPTER_KEY, AJCC8_CHAPTER_NUM, AJCC8_CHAPTER_DESCR) values('60', '73B', 'Thyroid - Anaplastic Carcinoma')
GO
Insert into zzACCC8(AJCC8_CHAPTER_KEY, AJCC8_CHAPTER_NUM, AJCC8_CHAPTER_DESCR) values('61', '74', 'Thyroid - Medullary Carcinoma')
GO
Insert into zzACCC8(AJCC8_CHAPTER_KEY, AJCC8_CHAPTER_NUM, AJCC8_CHAPTER_DESCR) values('62', '76', 'Adrenal Cortical Carcinoma')
GO
Insert into zzACCC8(AJCC8_CHAPTER_KEY, AJCC8_CHAPTER_NUM, AJCC8_CHAPTER_DESCR) values('63', '77', 'Adrenal - Neuroendocrine Tumors')
GO
Insert into zzACCC8(AJCC8_CHAPTER_KEY, AJCC8_CHAPTER_NUM, AJCC8_CHAPTER_DESCR) values('64', '79', 'Hodgkin and Non-Hodgkin Lymphomas')
GO
Insert into zzACCC8(AJCC8_CHAPTER_KEY, AJCC8_CHAPTER_NUM, AJCC8_CHAPTER_DESCR) values('65', '80', 'Pediatric Hodgkin and Non-Hodgkin Lymphomas')
GO
Insert into zzACCC8(AJCC8_CHAPTER_KEY, AJCC8_CHAPTER_NUM, AJCC8_CHAPTER_DESCR) values('66', '81', 'Primary Cutaneous Lymphomas')
GO
Insert into zzACCC8(AJCC8_CHAPTER_KEY, AJCC8_CHAPTER_NUM, AJCC8_CHAPTER_DESCR) values('67', '82', 'Multiple Myeloma and Plasma Cell Disorders')
GO
Insert into [dbo].[LU_AJCC7_SITE_GRP](Code, DisplayCode, UserDescription, DefaultDescription, CIHIValue, ValidFrom, ValidTo, InterfaceIn, HospitalID, LUText, Staging_Version)
select 100 + code, AJCC8_CHAPTER_KEY, AJCC8_CHAPTER_DESCR, AJCC8_CHAPTER_DESCR, AJCC8_CHAPTER_KEY, '2018/01/01', '2099/03/31', AJCC8_CHAPTER_KEY, 0, '', '08'
from zzACCC8
GO
if exists (select * from information_schema.tables where table_name = 'zzACCC8')
    drop table zzACCC8
GO
--1202 
update h_institutionprofile set rehabsubdate = '2018-05-15' where associationid = 3
GO
update h_institutionprofile set rehabsubdate = '2018-06-05' where associationid = 4
GO
update h_institutionprofile set rehabsubdate = '2018-06-01' where associationid = 7
GO
--End build 6
if exists (select * from information_schema.tables where table_name = 'zzCHKInst')
    drop table zzCHKInst
GO
create table zzCHKInst(
    code int identity(1, 1),
    CareType varchar(10),
    UserDescription varchar(100),
    CIHIValue varchar(10),
    LevelCode int,
    HospitalType varchar(10)
)
GO
Insert into zzCHKInst(CareType, UserDescription, CIHIValue, LevelCode, HospitalType) values('NACRS', 'PROVIDENCE ST. JOSEPH''S AND ST. MICHAEL''S HEALTHCARE-ST. JOSEPH''S - NACRS', '54857', 4, 'AM')
Insert into zzCHKInst(CareType, UserDescription, CIHIValue, LevelCode, HospitalType) values('DAD',   'PROVIDENCE ST. JOSEPH''S AND ST. MICHAEL''S HEALTHCARE-ST. JOSEPH''S - DAD', '54858', null, 'AT')
GO
update lu_institution_10
set InstCareType = (case z.HospitalType when 'AT' then 'A' When 'AM' then 'B' when 'GR' then 'R' when 'CR' then 'L' when 'MH' then 'M' else '' end), 
    HospitalType = z.HospitalType, 
    IsAcute =  (case z.HospitalType when 'AT' then 1 else 2 end), 
    InstitutionType = (case z.HospitalType when 'AT' then '1' When 'AM' then 'N' when 'GR' then '2' when 'CR' then '3' when 'MH' then '5' else '' end)
from zzCHKInst z
    inner join lu_institution_10 h on z.CIHIValue = h.CIHIValue
GO
declare @MaxID int
declare @Province varchar(10)
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 @MaxID = 91652
    if exists (select * from lu_institution_10 where code = (@MaxID + 1))
    begin
        set @maxID = (select max(code) from lu_institution_10)
        if @MaxID < 500000 set @MaxID = 500000
    end
    insert into lu_institution_10(Code, DisplayCode, UserDescription, DefaultDescription, CIHIValue, ValidFrom, ValidTo, InterfaceIn, InstCareType, HospitalType, IsAcute, HospitalID, LUText, InstitutionType)
    select @MaxID + z.Code, z.CIHIValue, z.UserDescription + ' - ' + z.CareType, z.UserDescription + ' - ' + z.CareType, z.CIHIValue, '2018/04/01', '2099/03/31', z.CIHIValue,
        InstCareType = (case z.HospitalType when 'AT' then 'A' When 'AM' then 'B' when 'GR' then 'R' when 'CR' then 'L' when 'MH' then 'M' else '' end), 
        z.HospitalType, 
        IsAcute =  (case z.HospitalType when 'AT' then 1 else 2 end), 0, '',
        InstitutionType = (case z.HospitalType when 'AT' then '1' When 'AM' then 'N' when 'GR' then '2' when 'CR' then '3' when 'MH' then '5' else '' end)
    from zzCHKInst z
        left join lu_institution_10 h on z.CIHIValue = h.CIHIValue
    where h.cihivalue is null
end
GO
if exists (select * from information_schema.tables where table_name = 'zzCHKInst')
    drop table zzCHKInst
GO






