Delete from tGrouper_CACS_ANAESTHETIC_ASSIGN where methodology_year in (2016, 2106)
GO
Insert into [dbo].[tGrouper_CACS_ANAESTHETIC_ASSIGN](methodology_year, anaesthetic_technique_code, cacs_anaesthetic_code) values(2016, 0, 8)
GO
Insert into [dbo].[tGrouper_CACS_ANAESTHETIC_ASSIGN](methodology_year, anaesthetic_technique_code, cacs_anaesthetic_code) values(2016, 1, 1)
GO
Insert into [dbo].[tGrouper_CACS_ANAESTHETIC_ASSIGN](methodology_year, anaesthetic_technique_code, cacs_anaesthetic_code) values(2016, 2, 1)
GO
Insert into [dbo].[tGrouper_CACS_ANAESTHETIC_ASSIGN](methodology_year, anaesthetic_technique_code, cacs_anaesthetic_code) values(2016, 3, 1)
GO
Insert into [dbo].[tGrouper_CACS_ANAESTHETIC_ASSIGN](methodology_year, anaesthetic_technique_code, cacs_anaesthetic_code) values(2016, 4, 1)
GO
Insert into [dbo].[tGrouper_CACS_ANAESTHETIC_ASSIGN](methodology_year, anaesthetic_technique_code, cacs_anaesthetic_code) values(2016, 5, 2)
GO
Insert into [dbo].[tGrouper_CACS_ANAESTHETIC_ASSIGN](methodology_year, anaesthetic_technique_code, cacs_anaesthetic_code) values(2016, 6, 2)
GO
Insert into [dbo].[tGrouper_CACS_ANAESTHETIC_ASSIGN](methodology_year, anaesthetic_technique_code, cacs_anaesthetic_code) values(2016, 7, 4)
GO
Insert into [dbo].[tGrouper_CACS_ANAESTHETIC_ASSIGN](methodology_year, anaesthetic_technique_code, cacs_anaesthetic_code) values(2016, 8, 8)
GO
Insert into [dbo].[tGrouper_CACS_ANAESTHETIC_ASSIGN](methodology_year, anaesthetic_technique_code, cacs_anaesthetic_code) values(2016, 9, 3)
GO
Insert into [dbo].[tGrouper_CACS_ANAESTHETIC_ASSIGN](methodology_year, anaesthetic_technique_code, cacs_anaesthetic_code) values(2016, 'C', 1)
GO
Insert into [dbo].[tGrouper_CACS_ANAESTHETIC_ASSIGN](methodology_year, anaesthetic_technique_code, cacs_anaesthetic_code) values(2016, 'U', 8)
GO
--0816
Update LU_Institution_10
set ValidTo = '2017/03/31'
where cihivalue in ('53975', '54152', '53984', '54154', '54573', 
'53940', '53941', '53943', '54139', '54572', '54014', '54140', '54151', 
'54638', '54106', '54784', '51100', '54105', '54528')
GO
-- 0817  -   new institutions - Ajax
if exists (select * from information_schema.tables where table_name = 'zzInst54844')
    drop table zzInst54844
GO
create table zzInst54844(
    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 zzInst54844(code, UserDescription, HospitalType, CIHIValue) values(1, 'Lakeridge Health-AJAX SITE', 'AT', '54844')
GO
Insert into zzInst54844(code, UserDescription, HospitalType, CIHIValue) values(2, 'Lakeridge Health-AJAX SITE', 'AM', '54845')
GO
Insert into zzInst54844(code, UserDescription, HospitalType, CIHIValue) values(3, 'Lakeridge Health-AJAX SITE', 'GR', '54846')
GO
Insert into zzInst54844(code, UserDescription, HospitalType, CIHIValue) values(4, 'Lakeridge Health-AJAX SITE', 'CR', '54847')
GO
Insert into zzInst54844(code, UserDescription, HospitalType, CIHIValue) values(5, 'Lakeridge Health-AJAX SITE', 'MH', '54848')
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 zzInst54844 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 zzInst54844 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 = 91631
    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 zzInst54844 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 = 'zzInst54844')
    drop table zzInst54844
GO
--End build 1
--For 3M bridge
if not exists (select * from information_schema.columns where table_name = 'H_HospitalProfile' and column_name = 'ThreeM_EncNo')
       alter table H_HospitalProfile add ThreeM_EncNo int
go
update H_HospitalProfile set ThreeM_EncNo = 2 where ThreeM_EncNo is null
GO
if not exists (select * from information_schema.columns where table_name = 'ENGN_FieldValues' and column_name = 'FieldDescription')
                alter table ENGN_FieldValues add [FieldDescription] varchar(255) null
go
--End build 2
--0834 
if not exists (select * from information_schema.columns where table_name = 'M_Login' and column_name = 'DomainName')
begin
    alter table [dbo].[M_Login] add DomainName varchar(200)
    alter table [dbo].[M_Login] add Workstation varchar(200)
    alter table [dbo].[M_Login] add CloseBy int
end
GO
if exists (select * from sysindexes where name = 'idx_M_Login_LogoutTime')
    drop index M_Login.idx_M_Login_LogoutTime
GO
create index idx_M_Login_LogoutTime on [dbo].[M_Login](LogoutTime)
GO
if not exists (select * from information_schema.columns where table_name = 'WM_WorkLoad' and column_name = 'LoginID')
begin
    alter table [dbo].WM_WorkLoad add LoginID int
end
GO
if exists (select * from sysindexes where name = 'idx_WM_WorkLoad_LoginIn')
    drop index WM_WorkLoad.idx_WM_WorkLoad_LoginIn
GO
create index idx_WM_WorkLoad_LoginIn on [dbo].WM_WorkLoad(LoginID)
GO
Update [dbo].[M_Login] set LogoutTime = getdate(), Workstation = 'DBupgrade 241.1', CloseBy = 1 where LogoutTime is null and Successful = 'Y'
GO
insert WM_WorkLoad 	(UserID, LogID, AppID, ModID, LinkID, OpID, OpType, OpTime, LoginID) 
select UserID, LogID, -1, 0, 0, 0, 18511, getdate(), LoginID
from WM_WorkLoad
where OpType = 18501 and RecordID not in (select LogID from WM_WorkLoad w where w.OpType = 18510 or w.OpType = 18511)
GO
--End build 3
--0200
update LU_Project 
set Question7 = 'Record Completed After Initial Coding',
    Question8 = 'Data Changed From Original Coding'
where cihivalue = '100'
and getdate() between validfrom and validto
GO
--End build 4
--2.x-0837 zero pad and right allignment
Update [dbo].[SU_StructureFile] set TextFormat = 2 where [GroupID]  in (99990473, 99991473) and [FieldName] = 'u6'
GO
--0847
Update LU_Institution_10
set DefaultDescription = 'Kipling Integrated Health Centre',
    UserDescription = 'Kipling Integrated Health Centre'
where cihivalue = '70068'
GO
--End build 6