--0855
declare @sql varchar(1000)
declare @table varchar(100)

declare cur_display cursor for
    select t.table_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 = 'displaycode'
    and c.character_maximum_length < 50
    and t.table_name not like 'z%'
    and t.table_name not like 'CMG%'

open cur_display
fetch next from cur_display into @table
while(@@fetch_status = 0)
begin
    set @sql = 'alter table ' + @table + ' alter column displaycode varchar(50) '
    exec (@sql)
    fetch next from cur_display into @table
end
close cur_display
deallocate cur_display
GO
if not exists (select * from information_schema.columns where table_name = 'AB_CMG_FI' and column_name = 'ComorbidityCount')
    alter table AB_CMG_FI add ComorbidityCount varchar(10)
GO
-- 0903 
if not exists (select * from information_schema.columns where table_name = 'M_Login' and column_name = 'Coder')
    alter table M_Login add Coder int
GO
Update [dbo].[M_Login] set LogoutTime = getdate(), Workstation = 'DBupgrade 294.2', CloseBy = 1 where LogoutTime is null and Successful = 'Y'
GO
--End build 1
--0929 -   Ability to filter ED Opioid Visits in NACRS Submission
delete from lu_codefile where code = 15645
GO
Insert into lu_codefile(Code, DisplayCode, UserDescription, DefaultDescription, CIHIValue, ValidFrom, ValidTo, InterfaceIn, LookupID, HospitalID, LUText)
values(15645, 'B-E-O', 'ED Opioid Case', 'ED Opioid Case', 'B-E-O', '1950-01-01', '2099-03-31', 'B-E-O', 7, 0, NULL)
GO
--End build 3
--0945
Update lu_codefile
set UserDescription = 'Bizarre behaviour', DefaultDescription = 'Bizarre behaviour'
where lookupid = 1100 and cihivalue = '358'
GO
Update lu_codefile
set UserDescription = 'Concern for patient''s welfare', DefaultDescription = 'Concern for patient''s welfare'
where lookupid = 1100 and cihivalue = '359'
GO

-- 0938 -   DAD - Wait time in ED in hours
delete from  [dbo].[U_Fields] where fieldid = 753
GO
Insert into [dbo].[U_Fields](FieldID, InHouseDesc, SystemName, PropertyName, FieldAccess, IsGroup)
values(753, 'Wait time in ED in hours', 'WaitTimeERHour', 'WaitTimeERHour', 2, 0)
GO
if not exists (select * from u_usersfields where userid = 1 and fieldid = 753)
begin
	Update U_UsersFields
	set SortOrder = a.SortOrder + 1
	from U_UsersFields a
		inner join (select * from u_Usersfields where fieldid = 662) 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 fieldid = 753)
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 = 753, 
		SortOrder = SortOrder + 1, 
		GroupID, MultipleIndex, MandatoryStatus = 1000, Needed, FieldSize, 
		DataType = (case when appid in (200, 201, 202) then 0 else 100000 end), 
        SubAppID, LookUpTable, CIHI_ID = '', 
		Header_CIHI_English = 'Wait time in ED in hours', Header_CIHI_French = null, Header_CIHI_Spanish = null, 
		Header_User = 'Wait time in ED in hours', 
		FieldEnabled = 2, 
        FieldVisible = (case when appid in (200, 201) then 1 else 2 end), DefaultValue = null, 
		ParamProcID, 
		ParamOrder = 175, 
		ParamName = 'pWaitERHour', 
		ParamDataType, ParamSize, NeedForUserDef = 0, 
		MainDataType = null, UNoteCode = null
	from [dbo].[U_UsersFields]
	where fieldid = 662
end
GO
if not exists (select * from information_schema.columns where table_name = 'AB_Abstract' and column_name = 'WaitTimeERHour')
    alter table AB_Abstract add WaitTimeERHour int
GO
--0946 2017 SCIPP data
if not exists (select * from information_schema.columns where table_name = 'SCIPP_Main' and column_name = 'CMI_Weight_2017')
    alter table SCIPP_Main add CMI_Weight_2017 varchar(2017)
GO
if exists (select * from information_schema.tables where table_name = 'zz2017VSCIPP')
    drop table zz2017VSCIPP
GO
create table dbo.zz2017VSCIPP(
    scipp_group varchar(100),
    SCIPP_CMI_2016 varchar(100),
    SCIPP_CMI_2017 varchar(100)
)
GO
insert into zz2017VSCIPP(scipp_group, SCIPP_CMI_2016, SCIPP_CMI_2017) values('0_SHORT_STAY', '1.1731', '1.0177')
GO
insert into zz2017VSCIPP(scipp_group, SCIPP_CMI_2016, SCIPP_CMI_2017) values('1_SZPA1', '1.1846', '1.1768')
GO
insert into zz2017VSCIPP(scipp_group, SCIPP_CMI_2016, SCIPP_CMI_2017) values('1_SZPA2', '1.1358', '1.099')
GO
insert into zz2017VSCIPP(scipp_group, SCIPP_CMI_2016, SCIPP_CMI_2017) values('1_SZPBA1', '1.1959', '1.2081')
GO
insert into zz2017VSCIPP(scipp_group, SCIPP_CMI_2016, SCIPP_CMI_2017) values('1_SZPBB1', '1.3185', '1.4409')
GO
insert into zz2017VSCIPP(scipp_group, SCIPP_CMI_2016, SCIPP_CMI_2017) values('1_SZPBB2', '1.3892', '1.4345')
GO
insert into zz2017VSCIPP(scipp_group, SCIPP_CMI_2016, SCIPP_CMI_2017) values('1_SZPBC1', '1.2327', '1.2132')
GO
insert into zz2017VSCIPP(scipp_group, SCIPP_CMI_2016, SCIPP_CMI_2017) values('1_SZPBC2', '1.1528', '1.1184')
GO
insert into zz2017VSCIPP(scipp_group, SCIPP_CMI_2016, SCIPP_CMI_2017) values('1_SZPBC3', '1.1179', '1.1028')
GO
insert into zz2017VSCIPP(scipp_group, SCIPP_CMI_2016, SCIPP_CMI_2017) values('1_SZPBD1', '1.1013', '1.0764')
GO
insert into zz2017VSCIPP(scipp_group, SCIPP_CMI_2016, SCIPP_CMI_2017) values('1_SZPBE1', '1.0834', '1.0865')
GO
insert into zz2017VSCIPP(scipp_group, SCIPP_CMI_2016, SCIPP_CMI_2017) values('1_SZPBE2', '1.1615', '1.2318')
GO
insert into zz2017VSCIPP(scipp_group, SCIPP_CMI_2016, SCIPP_CMI_2017) values('1_SZPBF1', '1.2444', '1.2155')
GO
insert into zz2017VSCIPP(scipp_group, SCIPP_CMI_2016, SCIPP_CMI_2017) values('1_SZPBF2', '1.1121', '1.1556')
GO
insert into zz2017VSCIPP(scipp_group, SCIPP_CMI_2016, SCIPP_CMI_2017) values('1_SZPCA1', '1.459', '1.5019')
GO
insert into zz2017VSCIPP(scipp_group, SCIPP_CMI_2016, SCIPP_CMI_2017) values('1_SZPCB1', '1.3177', '1.4592')
GO
insert into zz2017VSCIPP(scipp_group, SCIPP_CMI_2016, SCIPP_CMI_2017) values('1_SZPCB2', '1.3012', '1.3365')
GO
insert into zz2017VSCIPP(scipp_group, SCIPP_CMI_2016, SCIPP_CMI_2017) values('1_SZPCC1', '1.3082', '1.3375')
GO
insert into zz2017VSCIPP(scipp_group, SCIPP_CMI_2016, SCIPP_CMI_2017) values('1_SZPCC2', '1.2843', '1.3018')
GO
insert into zz2017VSCIPP(scipp_group, SCIPP_CMI_2016, SCIPP_CMI_2017) values('2_ODA1', '1.3979', '1.4033')
GO
insert into zz2017VSCIPP(scipp_group, SCIPP_CMI_2016, SCIPP_CMI_2017) values('2_ODB1', '1.3187', '1.3111')
GO
insert into zz2017VSCIPP(scipp_group, SCIPP_CMI_2016, SCIPP_CMI_2017) values('2_ODB2', '1.2202', '1.2669')
GO
insert into zz2017VSCIPP(scipp_group, SCIPP_CMI_2016, SCIPP_CMI_2017) values('2_ODC1', '1.2401', '1.2615')
GO
insert into zz2017VSCIPP(scipp_group, SCIPP_CMI_2016, SCIPP_CMI_2017) values('3_MDAA1', '1.1738', '1.1158')
GO
insert into zz2017VSCIPP(scipp_group, SCIPP_CMI_2016, SCIPP_CMI_2017) values('3_MDAA2', '1.1973', '1.1594')
GO
insert into zz2017VSCIPP(scipp_group, SCIPP_CMI_2016, SCIPP_CMI_2017) values('3_MDAB1', '1.1855', '1.1756')
GO
insert into zz2017VSCIPP(scipp_group, SCIPP_CMI_2016, SCIPP_CMI_2017) values('3_MDBA1', '1.2411', '1.2067')
GO
insert into zz2017VSCIPP(scipp_group, SCIPP_CMI_2016, SCIPP_CMI_2017) values('3_MDBA2', '1.1393', '1.1172')
GO
insert into zz2017VSCIPP(scipp_group, SCIPP_CMI_2016, SCIPP_CMI_2017) values('3_MDBC1', '1.2696', '1.324')
GO
insert into zz2017VSCIPP(scipp_group, SCIPP_CMI_2016, SCIPP_CMI_2017) values('3_MDBC2', '1.2237', '1.1602')
GO
insert into zz2017VSCIPP(scipp_group, SCIPP_CMI_2016, SCIPP_CMI_2017) values('3_MDBC3', '1.1791', '1.2024')
GO
insert into zz2017VSCIPP(scipp_group, SCIPP_CMI_2016, SCIPP_CMI_2017) values('3_MDBD1', '1.1842', '1.1021')
GO
insert into zz2017VSCIPP(scipp_group, SCIPP_CMI_2016, SCIPP_CMI_2017) values('3_MDBD2', '1.1202', '1.0394')
GO
insert into zz2017VSCIPP(scipp_group, SCIPP_CMI_2016, SCIPP_CMI_2017) values('3_MDBE1', '1.0964', '1.0869')
GO
insert into zz2017VSCIPP(scipp_group, SCIPP_CMI_2016, SCIPP_CMI_2017) values('3_MDBE2', '1.1134', '1.0516')
GO
insert into zz2017VSCIPP(scipp_group, SCIPP_CMI_2016, SCIPP_CMI_2017) values('4_PDA1', '1.3877', '1.4365')
GO
insert into zz2017VSCIPP(scipp_group, SCIPP_CMI_2016, SCIPP_CMI_2017) values('4_PDA2', '1.3089', '1.4235')
GO
insert into zz2017VSCIPP(scipp_group, SCIPP_CMI_2016, SCIPP_CMI_2017) values('5_EDA1', '1.6786', '1.391')
GO
insert into zz2017VSCIPP(scipp_group, SCIPP_CMI_2016, SCIPP_CMI_2017) values('6_SDAA1', '1.2325', '1.2361')
GO
insert into zz2017VSCIPP(scipp_group, SCIPP_CMI_2016, SCIPP_CMI_2017) values('6_SDAA2', '1.137', '1.1879')
GO
insert into zz2017VSCIPP(scipp_group, SCIPP_CMI_2016, SCIPP_CMI_2017) values('6_SDBA1', '1.1652', '1.1811')
GO
insert into zz2017VSCIPP(scipp_group, SCIPP_CMI_2016, SCIPP_CMI_2017) values('7_OTHAA1', '1.7228', '1.9009')
GO
insert into zz2017VSCIPP(scipp_group, SCIPP_CMI_2016, SCIPP_CMI_2017) values('7_OTHBA1', '1.1715', '1.1127')
GO
insert into zz2017VSCIPP(scipp_group, SCIPP_CMI_2016, SCIPP_CMI_2017) values('7_OTHBA2', '1.3402', '1.4858')
GO
insert into zz2017VSCIPP(scipp_group, SCIPP_CMI_2016, SCIPP_CMI_2017) values('7_OTHCA1', '1.264', '1.3466')
GO
insert into zz2017VSCIPP(scipp_group, SCIPP_CMI_2016, SCIPP_CMI_2017) values('7_OTHDA1', '1.1179', '1.1416')
GO
insert into zz2017VSCIPP(scipp_group, SCIPP_CMI_2016, SCIPP_CMI_2017) values('7_OTHDA2', '1.0492', '1.0299')
GO
insert into zz2017VSCIPP(scipp_group, SCIPP_CMI_2016, SCIPP_CMI_2017) values('7_OTHDB2', '1.3352', '1.3437')
GO
insert into zz2017VSCIPP(scipp_group, SCIPP_CMI_2016, SCIPP_CMI_2017) values('8_UNGROUPABLE', '0', '0')
GO
insert into zz2017VSCIPP(scipp_group, SCIPP_CMI_2016, SCIPP_CMI_2017) values('9_NOT_MH', '', '0')
GO
update [dbo].[SCIPP_Main]
set [CMI_Weight_2017] = z.SCIPP_CMI_2017
from [dbo].[SCIPP_Main] a inner join zz2017VSCIPP z on a.[SCIPP_Group] = z.scipp_group
GO
if exists (select * from information_schema.tables where table_name = 'zz2017VSCIPP')
    drop table zz2017VSCIPP
GO
--End build 5