--Others
--- ***** Remember to keep the customer code validated. Do not touch customer code.
if exists (select * from information_schema.tables where table_name = 'zzICD10_Diag')
    drop table zzICD10_Diag
GO
Create table zzICD10_Diag(
    CIHIValue varchar(10),
    shortDescription varchar(100),
    longDescription varchar(500)
)
GO
bulk insert zzICD10_Diag  from '<path>ICD_Code_Eng_Desc_10CA2018_V1_0.txt' with( FIELDTERMINATOR = '|', ROWTERMINATOR ='\n', CODEPAGE = 'RAW' )
--bulk insert zzICD10_Diag  from '<path>ICD_Code_Eng_Desc_10CA2018_20170612 V1_0.txt' with( FIELDTERMINATOR = '|', ROWTERMINATOR ='\n', CODEPAGE = 'RAW' )
GO
update zzICD10_Diag
set CIHIValue = ltrim(rtrim(CIHIValue)),
    shortDescription = ltrim(rtrim(shortDescription)),
    longDescription = ltrim(rtrim(longDescription))
GO
if exists (select * from information_schema.tables where table_name = 'zzICD10_CCI')
    drop table zzICD10_CCI
GO
Create table zzICD10_CCI(
    CIHIValue varchar(10),
    shortDescription varchar(100),
    longDescription varchar(500)
)
GO
bulk insert zzICD10_CCI  from '<path>CCI_Code_Eng_Desc_CCI2018_V1_0.txt' with( FIELDTERMINATOR = '|', ROWTERMINATOR ='\n', CODEPAGE = 'RAW' )
--bulk insert zzICD10_CCI  from '<path>CCI_Code_Eng_Desc_CCI2018_20170612 V1_0.txt' with( FIELDTERMINATOR = '|', ROWTERMINATOR ='\n', CODEPAGE = 'RAW' )
GO
update zzICD10_CCI
set CIHIValue = ltrim(rtrim(CIHIValue)),
    shortDescription = ltrim(rtrim(shortDescription)),
    longDescription = ltrim(rtrim(longDescription))
GO
Update ICD_diagFile10
set UserDescription = z.longDescription,
    DefaultDescription = z.longDescription,
    ShortDescription = z.shortDescription
-- select z.*, icd.*
from zzICD10_diag z
	inner join ICD_diagFile10 Icd on z.CIHIValue = ICD.CIHIValue
where (icd.DefaultDescription <>  z.longDescription or icd.ShortDescription <> z.shortDescription)
    and icd.code < 500000	--this is for NUNAVUT custom code, do not overwrite description
GO
Update dbo.ICD_ProcFile10
set UserDescription = z.longDescription,
    DefaultDescription = z.longDescription,
    ShortDescription = z.shortDescription
-- select z.*, icd.*
from zzICD10_CCI z
	inner join ICD_ProcFile10 Icd on z.CIHIValue = ICD.CIHIValue
where (icd.DefaultDescription <>  z.longDescription or icd.ShortDescription <> z.shortDescription)
    and icd.code < 500000	--this is for NUNAVUT custom code, do not overwrite description
GO
update ICD_diagFile10
set ValidTo = '2099/03/31'
-- select icd.*
from zzICD10_diag z
	inner join ICD_diagFile10 Icd on z.CIHIValue = ICD.CIHIValue
where icd.validto < '2018/04/01'
GO
update ICD_ProcFile10
set ValidTo = '2099/03/31'
-- select icd.*
from zzICD10_CCI z
	inner join ICD_ProcFile10 Icd on z.CIHIValue = ICD.CIHIValue
where icd.validto < '2018/04/01'
GO
update ICD_diagFile10
set ValidTo = '2018/03/31'
-- select icd.*
from zzICD10_diag z
	right join ICD_diagFile10 Icd on z.CIHIValue = ICD.CIHIValue
where z.cihivalue is null and icd.validto > '2018/03/31' and icd.code < 500000
GO
update ICD_ProcFile10
set ValidTo = '2018/03/31'
-- select icd.*
from zzICD10_CCI z
	right join ICD_ProcFile10 Icd on z.CIHIValue = ICD.CIHIValue
where icd.validto > '2018/03/31' and  z.cihivalue is null and icd.code < 500000
GO
if exists (select * from information_schema.tables where table_name = 'zzICD10_diag_New')
    drop table zzICD10_diag_New
GO
if exists (select * from information_schema.tables where table_name = 'zzICD10_CCI_New')
    drop table zzICD10_CCI_New
GO
select  z.CIHIValue, z.shortDescription, z.longDescription, code = identity(int, 1, 1)
    into zzICD10_diag_New
from zzICD10_diag z
	left join ICD_diagFile10 Icd on z.CIHIValue = ICD.CIHIValue
where icd.cihivalue is null
order by z.CIHIValue
GO
select  z.CIHIValue, z.shortDescription, z.longDescription, code = identity(int, 1, 1)
    into zzICD10_CCI_New
from zzICD10_CCI z
	left join ICD_ProcFile10 Icd on z.CIHIValue = ICD.CIHIValue
where icd.cihivalue is null
order by z.CIHIValue
GO
declare @MaxCode int
set @MaxCode = (select max(code) from ICD_DiagFile10 where code < 100000)
if @MaxCode is null set @MaxCode = 0

Insert into dbo.ICD_DiagFile10(Code, DisplayCode, UserDescription, DefaultDescription, ShortDescription, CIHIValue, ValidFrom, ValidTo, DiagnosisGender, AgeLow, AgeHigh, InterfaceIn, HospitalID, LUText)
select Code + @MaxCode, CIHIValue, left(longDescription, 255), left(longDescription, 255), left(longDescription, 255), CIHIValue, '2018/04/01', '2099/12/31', 'A', 0, 130, CIHIValue, 0 , ''	
from zzICD10_diag_New
order by code
GO
Update dbo.ICD_DiagFile10
set Displaycode = left(displaycode, 3) + '.' + substring(displaycode, 4, 10)
where len(cihivalue) > 3 and charindex('.', displaycode) = 0 and ISNUMERIC(cihivalue) = 0
GO
declare @MaxCode int
set @MaxCode = (select max(code) from ICD_ProcFile10 where code < 100000)
if @MaxCode is null set @MaxCode = 0

Insert into dbo.ICD_ProcFile10(Code, DisplayCode, UserDescription, DefaultDescription, ShortDescription, CIHIValue, ValidFrom, ValidTo, DiagnosisGender, AgeLow, AgeHigh, InterfaceIn, HospitalID, LUText)
select Code + @MaxCode, CIHIValue, left(longDescription, 255), left(longDescription, 255), left(longDescription, 255), CIHIValue, '2018/04/01', '2099/12/31', 'A', 0, 130, CIHIValue, 0 , ''	
from zzICD10_CCI_New
where cihivalue <> ''
order by code
GO
Update dbo.ICD_ProcFile10
set Displaycode = left(CIHIValue, 1) + '.' + substring(CIHIValue, 2, 2) + '.' + substring(CIHIValue, 4, 2) +
    (case when len(CIHIValue) > 5 then '.' + substring(CIHIValue, 6, 2) + 
          (case when len(CIHIValue) > 7 then '-' + substring(CIHIValue, 8, 2) + 
                (case when len(CIHIValue) > 9 then '-' + substring(CIHIValue, 10, 1)
                      else '' 
                 end)
                 else ''
           end)
           else ''
     end)
where len(cihivalue) >= 5 and charindex('.', displaycode) = 0
    and isnumeric(left(CIHIValue, 1)) = 1 
GO
delete from icd_procfile10 where cihivalue = 'closure by'
GO
-------------------------Diagnosis Validation
if exists (select * from information_schema.tables where table_name = 'zzICD10CA_Validation')
    drop table zzICD10CA_Validation
GO
Create table zzICD10CA_Validation(
    CIHIValue varchar(10) COLLATE Latin1_General_CI_AS NOT NULL,
    Sector   varchar(1) COLLATE Latin1_General_CI_AS NOT NULL,
    CIHIFlag   varchar(1) COLLATE Latin1_General_CI_AS NOT NULL,
    MRDxFlag   varchar(1) COLLATE Latin1_General_CI_AS NOT NULL,
    NewBorn   varchar(1) COLLATE Latin1_General_CI_AS NOT NULL,
    DiagType   varchar(1) COLLATE Latin1_General_CI_AS NOT NULL,
    Gender   varchar(1) COLLATE Latin1_General_CI_AS NOT NULL,
    AgeLow   varchar(3) COLLATE Latin1_General_CI_AS NOT NULL,
    AgeHigh   varchar(3) COLLATE Latin1_General_CI_AS NOT NULL
)
GO
bulk insert zzICD10CA_Validation  from '<path>ICD_Code_Validation_10CA2018_V1_0.txt' with( FIELDTERMINATOR = '|', ROWTERMINATOR ='\n')
--bulk insert zzICD10CA_Validation  from '<path>ICD_Code_Validation_10CA2018_20170612 V1_0.txt' with( FIELDTERMINATOR = '|', ROWTERMINATOR ='\n')
GO
Update ICD_DiagFile10 set CIHIFlag = v.CIHIFlag
-- select v.*, d.CIHIFlag
from
(select a.CIHIValue, a.Sector, a.CIHIFlag, a.[MRDxFlag], a.[NewBorn], a.[DiagType], a.Gender, a.AgeLow, a.AgeHigh
from 
(select * from zzICD10CA_Validation where Sector = '1') a
inner join
(select * from zzICD10CA_Validation where Sector = 'a') b
on a.cihivalue = b.cihivalue
--where a.CIHIFlag<> b.CIHIFlag
) v
	inner join ICD_DiagFile10 d on v.cihivalue = d.cihivalue   
where isnull(v.CIHIFlag, '@@') <> isnull(d.CIHIFlag, '#') and d.code < 500000
GO
Update ICD_DiagFile10 set MRDxFlag = v.MRDxFlag
--select v.*, d.MRDxFlag
from
(select a.CIHIValue, a.Sector, a.CIHIFlag, MRDxFlag = (case when a.[MRDxFlag] = 'Y' then a.[MRDxFlag] when b.[MRDxFlag] = 'Y' then 'A' else b.[MRDxFlag] end), a.[NewBorn], a.[DiagType], a.Gender, a.AgeLow, a.AgeHigh --, b.MRDxFlag
from 
(select * from zzICD10CA_Validation where Sector = '1') a
inner join
(select * from zzICD10CA_Validation where Sector = 'a') b
on a.cihivalue = b.cihivalue
--where a.MRDxFlag<> b.MRDxFlag
) v
	inner join ICD_DiagFile10 d on v.cihivalue = d.cihivalue   
where isnull(v.MRDxFlag, '@@') <> isnull(d.MRDxFlag, '#') and d.code < 500000
GO
Update ICD_DiagFile10 set NewbornFlag = v.NewBorn
--select v.*, d.NewbornFlag
from
(select a.CIHIValue, a.Sector, a.CIHIFlag, MRDxFlag = (case when a.[MRDxFlag] = 'Y' then a.[MRDxFlag] when b.[MRDxFlag] = 'Y' then 'A' else b.[MRDxFlag] end), a.[NewBorn], a.[DiagType], a.Gender, a.AgeLow, a.AgeHigh --, b.MRDxFlag
from 
(select * from zzICD10CA_Validation where Sector = '1') a
inner join
(select * from zzICD10CA_Validation where Sector = 'a') b
on a.cihivalue = b.cihivalue
--where a.NewBorn<> b.NewBorn
) v
	inner join ICD_DiagFile10 d on v.cihivalue = d.cihivalue   
where isnull(v.NewBorn, '@@') <> isnull(d.NewbornFlag, '#') and d.code < 500000
GO
Update ICD_DiagFile10 set TypeCode = isnull(v.DiagType, '')
--select v.*, d.TypeCode
from
(select a.CIHIValue, a.Sector, a.CIHIFlag, MRDxFlag = (case when a.[MRDxFlag] = 'Y' then a.[MRDxFlag] else b.[MRDxFlag] end), a.[NewBorn], a.[DiagType], a.Gender, a.AgeLow, a.AgeHigh --, b.[DiagType]
from 
(select * from zzICD10CA_Validation where Sector = '1') a
inner join
(select * from zzICD10CA_Validation where Sector = 'a') b
on a.cihivalue = b.cihivalue
--where a.DiagType<> b.DiagType
) v
	inner join ICD_DiagFile10 d on v.cihivalue = d.cihivalue   
where isnull(v.DiagType, '@@') <> isnull(d.TypeCode, '##') and d.code < 500000
GO
Update ICD_DiagFile10 set DiagnosisGender = v.Gender
--select v.*, d.DiagnosisGender
from
(select a.CIHIValue, a.Sector, a.CIHIFlag, a.[MRDxFlag], a.[NewBorn], a.[DiagType], a.Gender, a.AgeLow, a.AgeHigh
from 
(select * from zzICD10CA_Validation where Sector = '1') a
inner join
(select * from zzICD10CA_Validation where Sector = 'a') b
on a.cihivalue = b.cihivalue
--where a.Gender<> b.Gender
) v
	inner join ICD_DiagFile10 d on v.cihivalue = d.cihivalue   
where isnull(v.Gender, '@') <> isnull(d.DiagnosisGender, '#') and d.code < 500000
GO
Update ICD_DiagFile10 set AgeLow = (case when isnull(v.AgeLow, '') = '' then 0 else v.AgeLow end)
--select v.*, d.AgeLow
from
(select a.CIHIValue, a.Sector, a.CIHIFlag, a.[MRDxFlag], a.[NewBorn], a.[DiagType], a.Gender, a.AgeLow, a.AgeHigh
from 
(select * from zzICD10CA_Validation where Sector = '1') a
inner join
(select * from zzICD10CA_Validation where Sector = 'a') b
on a.cihivalue = b.cihivalue
--where a.AgeLow<> b.AgeLow
) v
	inner join ICD_DiagFile10 d on v.cihivalue = d.cihivalue   
where (case when isnull(v.AgeLow, '') = '' then 0 else v.AgeLow end) <> isnull(d.AgeLow, -1) and d.code < 500000
GO
Update ICD_DiagFile10 set AgeHigh = (case when isnull(v.AgeHigh, '') = '' then 0 else v.AgeHigh end)
--select v.*, d.AgeHigh
from
(select a.CIHIValue, a.Sector, a.CIHIFlag, a.[MRDxFlag], a.[NewBorn], a.[DiagType], a.Gender, a.AgeLow, a.AgeHigh--, b.AgeHigh
from 
(select * from zzICD10CA_Validation where Sector = '1') a
inner join
(select * from zzICD10CA_Validation where Sector = 'a') b
on a.cihivalue = b.cihivalue
--where a.AgeHigh<> b.AgeHigh
) v
	inner join ICD_DiagFile10 d on v.cihivalue = d.cihivalue   
where (case when isnull(v.AgeHigh, '') = '' then 0 else v.AgeHigh end) <> isnull(d.AgeHigh, -1) and d.code < 500000
GO
if exists (select * from information_schema.tables where table_name = 'zzICD10_CC1_Validation')
    drop table zzICD10_CC1_Validation
GO
Create table zzICD10_CC1_Validation(
    CIHIValue varchar(10) COLLATE Latin1_General_CI_AS NOT NULL,
    Sector   varchar(1) COLLATE Latin1_General_CI_AS NOT NULL,
    CIHIFlag   varchar(1) COLLATE Latin1_General_CI_AS NOT NULL,
    Gender   varchar(1) COLLATE Latin1_General_CI_AS NOT NULL,
    AgeLow   varchar(6) COLLATE Latin1_General_CI_AS NOT NULL,
    AgeHigh   varchar(6) COLLATE Latin1_General_CI_AS NOT NULL
)
GO
bulk insert zzICD10_CC1_Validation  from '<path>CCI_Code_Validation_CCI2018_V1_0.txt' with( FIELDTERMINATOR = '|', ROWTERMINATOR ='\n')
--bulk insert zzICD10_CC1_Validation  from '<path>CCI_Code_Validation_CCI2018_20170612 V1_0.txt' with( FIELDTERMINATOR = '|', ROWTERMINATOR ='\n')
GO
Update ICD_ProcFile10 set Sector = v.Sector
--select v.*, d.Sector
from
(select a.CIHIValue, a.Sector, a.CIHIFlag, a.Gender, a.AgeLow, a.AgeHigh
from 
(select * from zzICD10_CC1_Validation where Sector = '1') a
) v
	inner join ICD_ProcFile10 d on v.cihivalue = d.cihivalue   
where isnull(v.Sector, '@@') <> isnull(d.Sector, '#') and d.Code < 500000
GO
Update ICD_ProcFile10 set Sector = v.Sector
--select v.*, d.CIHIFlag
from
(select a.CIHIValue, a.Sector, a.CIHIFlag, a.Gender, a.AgeLow, a.AgeHigh
from 
(select * from zzICD10_CC1_Validation where Sector = 'a') a
) v
	inner join ICD_ProcFile10 d on v.cihivalue = d.cihivalue   
where isnull(v.Sector, '') = '' and d.Code < 500000
GO
Update ICD_ProcFile10 set CIHIFlag = v.CIHIFlag
--select v.*, d.CIHIFlag
from
(select a.CIHIValue, a.Sector, a.CIHIFlag, a.Gender, a.AgeLow, a.AgeHigh
from 
(select * from zzICD10_CC1_Validation where Sector = '1') a
inner join
(select * from zzICD10_CC1_Validation where Sector = 'a') b
on a.cihivalue = b.cihivalue
) v
	inner join ICD_ProcFile10 d on v.cihivalue = d.cihivalue   
where isnull(v.CIHIFlag, '@@') <> isnull(d.CIHIFlag, '#') and d.Code < 500000
GO
Update ICD_ProcFile10 set DiagnosisGender = v.Gender
--select v.*, d.DiagnosisGender
from
(select a.CIHIValue, a.Sector, a.CIHIFlag, a.Gender, a.AgeLow, a.AgeHigh
from 
(select * from zzICD10_CC1_Validation where Sector = '1') a
inner join
(select * from zzICD10_CC1_Validation where Sector = 'a') b
on a.cihivalue = b.cihivalue
) v
	inner join ICD_ProcFile10 d on v.cihivalue = d.cihivalue   
where isnull(v.Gender, '@') <> isnull(d.DiagnosisGender, '#') and d.Code < 500000
GO
Update ICD_ProcFile10 set AgeLow = (case when isnull(v.AgeLow, '') = '' then 0 else v.AgeLow end)
--select v.*, d.AgeLow
from
(select a.CIHIValue, a.Sector, a.CIHIFlag, a.Gender, a.AgeLow, a.AgeHigh
from 
(select * from zzICD10_CC1_Validation where Sector = '1') a
inner join
(select * from zzICD10_CC1_Validation where Sector = 'a') b
on a.cihivalue = b.cihivalue
) v
	inner join ICD_ProcFile10 d on v.cihivalue = d.cihivalue   
where (case when isnull(v.AgeLow, '') = '' then 0 else v.AgeLow end) <> isnull(d.AgeLow, -1) and d.Code < 500000
GO
Update ICD_ProcFile10 set AgeHigh = (case when isnull(v.AgeHigh, '') = '' then 0 else v.AgeHigh end)
--select v.*, d.AgeHigh
from
(select a.CIHIValue, a.Sector, a.CIHIFlag, a.Gender, a.AgeLow, a.AgeHigh
from 
(select * from zzICD10_CC1_Validation where Sector = '1') a
inner join
(select * from zzICD10_CC1_Validation where Sector = 'a') b
on a.cihivalue = b.cihivalue
--where a.AgeHigh<> b.AgeHigh
) v
	inner join ICD_ProcFile10 d on v.cihivalue = d.cihivalue   
where (case when isnull(v.AgeHigh, '') = '' then 0 else v.AgeHigh end) <> isnull(d.AgeHigh, -1) and d.Code < 500000
GO
-------------------------Intervention Attribute Extent
if exists (select * from information_schema.tables where table_name = 'zzICD10CCI_Extent')
    drop table zzICD10CCI_Extent
GO
Create table zzICD10CCI_Extent(
    CIHIValue varchar(10) COLLATE Latin1_General_CI_AS NOT NULL,
    Sector varchar(10) COLLATE Latin1_General_CI_AS NOT NULL,
    ExtentAttribute varchar(10) COLLATE Latin1_General_CI_AS NOT NULL,
    LinkID varchar(10) COLLATE Latin1_General_CI_AS NOT NULL,
)
GO
bulk insert zzICD10CCI_Extent  from '<path>CCI_Extent_Val_CCI2018_V1_0.txt' with( FIELDTERMINATOR = '|', ROWTERMINATOR ='\n', CODEPAGE = 'RAW' )
--bulk insert zzICD10CCI_Extent  from '<path>CCI_Extent_Val_CCI2018_20170612 V1_0.txt' with( FIELDTERMINATOR = '|', ROWTERMINATOR ='\n', CODEPAGE = 'RAW' )
GO
Update LU_IntervAttExtent
set validto = '2018/03/31'
-- select l.*
from 
    (select * from  zzICD10CCI_Extent where sector = '1') v
    inner join ICD_ProcFile10 icd on v.CIHIValue = icd.CIHIValue
    inner join dbo.LU_IntervAttExtent l on icd.code = l.associationid
where v.LinkID = '' and l.validto > '2018/03/31'
GO
Update LU_IntervAttExtent
set validto = '2018/03/31'
--select l.*
from dbo.LU_IntervAttExtent l
	inner join ICD_ProcFile10 icd on icd.code = l.associationid
	left join
    (select * from  zzICD10CCI_Extent where sector = '1') v 
     on v.CIHIValue = icd.CIHIValue and v.LinkID = l.LinkID and v.ExtentAttribute = l.CIHIValue
where v.CIHIValue is null and l.validto > '2018/03/31'
GO

Update LU_IntervAttExtent
set validto = '2099/03/31' 
-- select l.*
from 
    (select * from  zzICD10CCI_Extent where sector = '1') v
    inner join ICD_ProcFile10 icd on v.CIHIValue = icd.CIHIValue
    inner join dbo.LU_IntervAttExtent l on icd.code = l.associationid and v.LinkID = l.LinkID
        and v.ExtentAttribute = l.CIHIValue
    inner join (select CIHIValue, AssociationID, LinkID, max(ValidTo) as maxDate from LU_IntervAttExtent group by CIHIValue, AssociationID, LinkID) d
        on l.CIHIValue = d.CIHIValue and l.AssociationID = d.AssociationID and l.LinkID = d.LinkID and l.Validto = d.maxdate
where v.LinkID <> '' and l.validto <= '2018/03/31'
GO
if exists (select * from information_schema.tables where table_name = 'zzICD10CCI_Extent_Desc')
    drop table zzICD10CCI_Extent_Desc
GO
Create table zzICD10CCI_Extent_Desc(
    LinkID varchar(10) COLLATE Latin1_General_CI_AS NOT NULL,
    ExtentAttribute varchar(10) COLLATE Latin1_General_CI_AS NOT NULL,
    UserDescription varchar(255) COLLATE Latin1_General_CI_AS NOT NULL
)
GO
bulk insert zzICD10CCI_Extent_Desc  from '<path>CCI_Extent_Eng_CCI2018_V1_0.txt' with( FIELDTERMINATOR = '|', ROWTERMINATOR ='\n', CODEPAGE = 'RAW' )
--bulk insert zzICD10CCI_Extent_Desc  from '<path>CCI_Extent_Eng_CCI2018_20170612 V1_0.txt' with( FIELDTERMINATOR = '|', ROWTERMINATOR ='\n', CODEPAGE = 'RAW' )
GO
Update LU_IntervAttExtent
set UserDescription = d.UserDescription, DefaultDescription = d.UserDescription
-- select d.*, l.*
from 
    (select * from  zzICD10CCI_Extent where sector = '1') v
    inner join ICD_ProcFile10 icd on v.CIHIValue = icd.CIHIValue
    inner join dbo.LU_IntervAttExtent l on icd.code = l.associationid and v.LinkID = l.LinkID
        and v.ExtentAttribute = l.CIHIValue
    inner join zzICD10CCI_Extent_Desc d on v.LinkID = d.LinkID and v.ExtentAttribute = d.ExtentAttribute
where l.DefaultDescription <> d.UserDescription and v.LinkID <> ''
    and l.CIHIValue <> '' and l.ValidTo > '2018/04/01'
GO
if exists (select * from information_schema.tables where table_name = 'zzICD10CCI_Extent_New')
    drop table zzICD10CCI_Extent_New
GO
select
    Code = identity(int, 1, 1),
    v.CIHIValue,
    v.ExtentAttribute,
    v.LinkID
into zzICD10CCI_Extent_New
from ICD_ProcFile10 icd  
	inner join
    (select * from  zzICD10CCI_Extent where sector = '1') v 
     on v.CIHIValue = icd.CIHIValue 
     left join dbo.LU_IntervAttExtent l on icd.code = l.associationid
     and v.LinkID = l.LinkID and v.ExtentAttribute = l.CIHIValue
where l.CIHIValue is null and v.LinkID <> ''
GO
declare @Maxcode int
set @MaxCode = (select max(code) from LU_IntervAttExtent where code < 100000)
if @MaxCode is null set @MaxCode = 500000

insert into dbo.LU_IntervAttExtent(Code, DisplayCode, UserDescription, DefaultDescription, CIHIValue, ValidFrom, ValidTo, AssociationID, LinkID, InterfaceIn, Sector, HospitalID)
select 
    Code = n.Code + @MaxCode, 
    DisplayCode = (case when ltrim(rtrim(n.ExtentAttribute)) = '' then 'None' else ltrim(rtrim(n.ExtentAttribute)) end), 
    UserDescription =  (case when ltrim(rtrim(d.UserDescription)) = '' then 'None' else ltrim(rtrim(d.UserDescription)) end), 
    DefaultDescription = (case when ltrim(rtrim(d.UserDescription)) = '' then 'None' else ltrim(rtrim(d.UserDescription)) end), 
    CIHIValue = ltrim(rtrim(n.ExtentAttribute)), 
    ValidFrom = '2018/04/01', ValidTo = '2099/03/31', 
    AssociationID = icd.Code, LinkID = n.LinkID, 
    InterfaceIn = ltrim(rtrim(n.ExtentAttribute)), Sector = '3', HospitalID = 0
from zzICD10CCI_Extent_New n
    inner join ICD_ProcFile10 icd on n.CIHIValue = icd.CIHIValue
    inner join zzICD10CCI_Extent_Desc d on n.LinkID = d.LinkID and d.ExtentAttribute = n.ExtentAttribute
    left  join LU_IntervAttExtent l on icd.code = l.associationid and n.linkid = l.linkid and n.ExtentAttribute = l.cihivalue
where l.associationid is null
GO
-------------------------Intervention Attribute Location
if exists (select * from information_schema.tables where table_name = 'zzICD10CCI_Location')
    drop table zzICD10CCI_Location
GO
Create table zzICD10CCI_Location(
    CIHIValue varchar(10) COLLATE Latin1_General_CI_AS NOT NULL,
    Sector varchar(10) COLLATE Latin1_General_CI_AS NOT NULL,
    LocationAttribute varchar(10) COLLATE Latin1_General_CI_AS NOT NULL,
    LinkID varchar(10) COLLATE Latin1_General_CI_AS NOT NULL,
)
GO
bulk insert zzICD10CCI_Location  from '<path>CCI_Location_Val_CCI2018_V1_0.txt' with( FIELDTERMINATOR = '|', ROWTERMINATOR ='\n', CODEPAGE = 'RAW' )
--bulk insert zzICD10CCI_Location  from '<path>CCI_Location_Val_CCI2018_20170612 V1_0.txt' with( FIELDTERMINATOR = '|', ROWTERMINATOR ='\n', CODEPAGE = 'RAW' )
GO
Update LU_IntervAttLocation
set validto = '2018/03/31'
--select v.*, l.*
from 
    (select * from  zzICD10CCI_Location where sector = '1') v
    inner join ICD_ProcFile10 icd on v.CIHIValue = icd.CIHIValue
    inner join dbo.LU_IntervAttLocation l on icd.code = l.associationid
where v.LinkID = '' and l.validto > '2018/03/31'
GO
Update LU_IntervAttLocation
set validto = '2018/03/31'
--select l.*
from dbo.LU_IntervAttLocation l
	inner join ICD_ProcFile10 icd on icd.code = l.associationid
	left join
    (select * from  zzICD10CCI_Location where sector = '1') v 
     on v.CIHIValue = icd.CIHIValue and v.LinkID = l.LinkID and v.LocationAttribute = l.CIHIValue
where v.CIHIValue is null and l.validto > '2018/03/31'
GO

Update LU_IntervAttLocation
set validto = '2099/03/31'
-- select l.*
from 
    (select * from  zzICD10CCI_Location where sector = '1') v
    inner join ICD_ProcFile10 icd on v.CIHIValue = icd.CIHIValue
    inner join dbo.LU_IntervAttLocation l on icd.code = l.associationid and v.LinkID = l.LinkID
        and v.LocationAttribute = l.CIHIValue
    inner join (select CIHIValue, AssociationID, LinkID, max(ValidTo) as maxDate from LU_IntervAttLocation group by CIHIValue, AssociationID, LinkID) d
        on l.CIHIValue = d.CIHIValue and l.AssociationID = d.AssociationID and l.LinkID = d.LinkID and l.Validto = d.maxdate
where v.LinkID <> '' and l.validto <= '2018/03/31'
GO
if exists (select * from information_schema.tables where table_name = 'zzICD10CCI_Location_Desc')
    drop table zzICD10CCI_Location_Desc
GO
Create table zzICD10CCI_Location_Desc(
    LinkID varchar(10) COLLATE Latin1_General_CI_AS NOT NULL,
    LocationAttribute varchar(10) COLLATE Latin1_General_CI_AS NOT NULL,
    UserDescription varchar(255) COLLATE Latin1_General_CI_AS NOT NULL
)
GO
bulk insert zzICD10CCI_Location_Desc  from '<path>CCI_Location_Eng_CCI2018_V1_0.txt' with( FIELDTERMINATOR = '|', ROWTERMINATOR ='\n', CODEPAGE = 'RAW' )
--bulk insert zzICD10CCI_Location_Desc  from '<path>CCI_Location_Eng_CCI2018_20170612 V1_0.txt' with( FIELDTERMINATOR = '|', ROWTERMINATOR ='\n', CODEPAGE = 'RAW' )
GO
Update LU_IntervAttLocation
set UserDescription = d.UserDescription, DefaultDescription = d.UserDescription
-- select d.*, l.*
from 
    (select * from  zzICD10CCI_Location where sector = '1') v
    inner join ICD_ProcFile10 icd on v.CIHIValue = icd.CIHIValue
    inner join dbo.LU_IntervAttLocation l on icd.code = l.associationid and v.LinkID = l.LinkID
        and v.LocationAttribute = l.CIHIValue
    inner join zzICD10CCI_Location_Desc d on v.LinkID = d.LinkID and v.LocationAttribute = d.LocationAttribute
where l.DefaultDescription <> d.UserDescription and v.LinkID <> ''
    and l.CIHIValue <> '' and l.ValidTo > '2018/04/01'
GO

--2018 new code
if exists (select * from information_schema.tables where table_name = 'zzICD10CCI_Location_New')
    drop table zzICD10CCI_Location_New
GO
select
    Code = identity(int, 1, 1),
    v.CIHIValue,
    v.LocationAttribute,
    v.LinkID
into zzICD10CCI_Location_New
from ICD_ProcFile10 icd  
	inner join
    (select * from  zzICD10CCI_Location where sector = '1') v 
     on v.CIHIValue = icd.CIHIValue 
     left join dbo.LU_IntervAttLocation l on icd.code = l.associationid
     and v.LinkID = l.LinkID and v.LocationAttribute = l.CIHIValue
where l.CIHIValue is null and v.LinkID <> ''
GO
declare @Maxcode int
set @MaxCode = (select max(code) from LU_IntervAttLocation where code < 100000)
if @MaxCode is null set @MaxCode = 500000
insert into dbo.LU_IntervAttLocation(Code, DisplayCode, UserDescription, DefaultDescription, CIHIValue, ValidFrom, ValidTo, AssociationID, LinkID, InterfaceIn, Sector, HospitalID)
select 
    Code = n.Code + @MaxCode, 
    DisplayCode = (case when ltrim(rtrim(n.LocationAttribute)) = '' then 'None' else ltrim(rtrim(n.LocationAttribute)) end), 
    UserDescription =  (case when ltrim(rtrim(d.UserDescription)) = '' then 'None' else ltrim(rtrim(d.UserDescription)) end), 
    DefaultDescription = (case when ltrim(rtrim(d.UserDescription)) = '' then 'None' else ltrim(rtrim(d.UserDescription)) end), 
    CIHIValue = ltrim(rtrim(n.LocationAttribute)), 
    ValidFrom = '2018/04/01', ValidTo = '2099/03/31', 
    AssociationID = icd.Code, LinkID = n.LinkID, 
    InterfaceIn = ltrim(rtrim(n.LocationAttribute)), Sector = '3', HospitalID = 0
from zzICD10CCI_Location_New n
    inner join ICD_ProcFile10 icd on n.CIHIValue = icd.CIHIValue
    inner join zzICD10CCI_Location_Desc d on n.LinkID = d.LinkID and d.LocationAttribute = n.LocationAttribute
    left  join LU_IntervAttLocation l on icd.code = l.associationid and n.linkid = l.linkid and n.LocationAttribute = l.cihivalue
where l.associationid is null
GO
-------------------------Intervention Attribute Status
if exists (select * from information_schema.tables where table_name = 'zzICD10CCI_Status')
    drop table zzICD10CCI_Status
GO
Create table zzICD10CCI_Status(
    CIHIValue varchar(10) COLLATE Latin1_General_CI_AS NOT NULL,
    Sector varchar(10) COLLATE Latin1_General_CI_AS NOT NULL,
    StatusAttribute varchar(10) COLLATE Latin1_General_CI_AS NOT NULL,
    LinkID varchar(10) COLLATE Latin1_General_CI_AS NOT NULL,
)
GO
bulk insert zzICD10CCI_Status  from '<path>CCI_Status_Val_CCI2018_V1_0.txt' with( FIELDTERMINATOR = '|', ROWTERMINATOR ='\n', CODEPAGE = 'RAW' )
--bulk insert zzICD10CCI_Status  from '<path>CCI_Status_Val_CCI2018_20170612 V1_0.txt' with( FIELDTERMINATOR = '|', ROWTERMINATOR ='\n', CODEPAGE = 'RAW' )
GO
Update LU_IntervAttStatus
set validto = '2018/03/31'
--select v.*, l.*
from 
    (select * from  zzICD10CCI_Status where sector = '1') v
    inner join ICD_ProcFile10 icd on v.CIHIValue = icd.CIHIValue
    inner join dbo.LU_IntervAttStatus l on icd.code = l.associationid
where v.LinkID = '' and l.validto > '2018/03/31'
GO
Update LU_IntervAttStatus
set validto = '2018/03/31'
--select l.*
from dbo.LU_IntervAttStatus l
	inner join ICD_ProcFile10 icd on icd.code = l.associationid
	left join
    (select * from  zzICD10CCI_Status where sector = '1') v 
     on v.CIHIValue = icd.CIHIValue and v.LinkID = l.LinkID and v.StatusAttribute = l.CIHIValue
where v.CIHIValue is null and l.validto > '2018/03/31'
GO

Update LU_IntervAttStatus
set validto = '2099/03/31' 
-- select l.*
from 
    (select * from  zzICD10CCI_Status where sector = '1') v
    inner join ICD_ProcFile10 icd on v.CIHIValue = icd.CIHIValue
    inner join dbo.LU_IntervAttStatus l on icd.code = l.associationid and v.LinkID = l.LinkID
        and v.StatusAttribute = l.CIHIValue
    inner join (select CIHIValue, AssociationID, LinkID, max(ValidTo) as maxDate from LU_IntervAttStatus group by CIHIValue, AssociationID, LinkID) d
        on l.CIHIValue = d.CIHIValue and l.AssociationID = d.AssociationID and l.LinkID = d.LinkID and l.Validto = d.maxdate
where v.LinkID <> '' and l.validto <= '2018/03/31'
GO
if exists (select * from information_schema.tables where table_name = 'zzICD10CCI_Status_Desc')
    drop table zzICD10CCI_Status_Desc
GO
Create table zzICD10CCI_Status_Desc(
    LinkID varchar(10) COLLATE Latin1_General_CI_AS NOT NULL,
    StatusAttribute varchar(10) COLLATE Latin1_General_CI_AS NOT NULL,
    UserDescription varchar(255) COLLATE Latin1_General_CI_AS NOT NULL
)
GO
bulk insert zzICD10CCI_Status_Desc  from '<path>CCI_Status_Eng_CCI2018_V1_0.txt' with( FIELDTERMINATOR = '|', ROWTERMINATOR ='\n', CODEPAGE = 'RAW' )
--bulk insert zzICD10CCI_Status_Desc  from '<path>CCI_Status_Eng_CCI2018_20170612 V1_0.txt' with( FIELDTERMINATOR = '|', ROWTERMINATOR ='\n', CODEPAGE = 'RAW' )
GO
Update LU_IntervAttStatus
set UserDescription = d.UserDescription, DefaultDescription = d.UserDescription
-- select d.*, l.*
from 
    (select * from  zzICD10CCI_Status where sector = '1') v
    inner join ICD_ProcFile10 icd on v.CIHIValue = icd.CIHIValue
    inner join dbo.LU_IntervAttStatus l on icd.code = l.associationid and v.LinkID = l.LinkID
        and v.StatusAttribute = l.CIHIValue
    inner join zzICD10CCI_Status_Desc d on v.LinkID = d.LinkID and v.StatusAttribute = d.StatusAttribute
where l.DefaultDescription <> d.UserDescription and v.LinkID <> ''
    and l.CIHIValue <> '' and l.ValidTo > '2018/04/01'
GO

--2018 new code
if exists (select * from information_schema.tables where table_name = 'zzICD10CCI_Status_New')
    drop table zzICD10CCI_Status_New
GO
select
    Code = identity(int, 1, 1),
    v.CIHIValue,
    v.StatusAttribute,
    v.LinkID
into zzICD10CCI_Status_New
from ICD_ProcFile10 icd  
	inner join
    (select * from  zzICD10CCI_Status where sector = '1') v 
     on v.CIHIValue = icd.CIHIValue 
     left join dbo.LU_IntervAttStatus l on icd.code = l.associationid
     and v.LinkID = l.LinkID and v.StatusAttribute = l.CIHIValue
where l.CIHIValue is null and v.LinkID <> ''
GO
declare @Maxcode int
set @MaxCode = (select max(code) from LU_IntervAttStatus where code < 100000 )
if @MaxCode is null set @MaxCode = 500000
insert into dbo.LU_IntervAttStatus(Code, DisplayCode, UserDescription, DefaultDescription, CIHIValue, ValidFrom, ValidTo, AssociationID, LinkID, InterfaceIn, Sector, HospitalID)
select 
    Code = n.Code + @MaxCode, 
    DisplayCode = (case when ltrim(rtrim(n.StatusAttribute)) = '' then 'None' else ltrim(rtrim(n.StatusAttribute)) end), 
    UserDescription =  (case when ltrim(rtrim(d.UserDescription)) = '' then 'None' else ltrim(rtrim(d.UserDescription)) end), 
    DefaultDescription = (case when ltrim(rtrim(d.UserDescription)) = '' then 'None' else ltrim(rtrim(d.UserDescription)) end), 
    CIHIValue = ltrim(rtrim(n.StatusAttribute)), 
    ValidFrom = '2018/04/01', ValidTo = '2099/03/31', 
    AssociationID = icd.Code, LinkID = n.LinkID, 
    InterfaceIn = ltrim(rtrim(n.StatusAttribute)), Sector = '3', HospitalID = 0
from zzICD10CCI_Status_New n
    inner join ICD_ProcFile10 icd on n.CIHIValue = icd.CIHIValue
    inner join zzICD10CCI_Status_Desc d on n.LinkID = d.LinkID and d.StatusAttribute = n.StatusAttribute
    left  join LU_IntervAttStatus l on icd.code = l.associationid and n.linkid = l.linkid and n.StatusAttribute = l.cihivalue
where l.associationid is null
order by n.code
GO
-------------------------Intervention Block -- dbo.ICD_IntervCat
if exists (select * from information_schema.tables where table_name = 'zzICD10CCI_Cat')
    drop table zzICD10CCI_Cat
GO
Create table zzICD10CCI_Cat(
    CIHIValue varchar(10),
    shortDescription varchar(100),
    longDescription varchar(255)
)
GO
bulk insert zzICD10CCI_Cat  from '<path>CCI_Blk_Eng_Desc_CCI2018_V1_0.txt' with( FIELDTERMINATOR = '|', ROWTERMINATOR ='\n')
--bulk insert zzICD10CCI_Cat  from '<path>CCI_Blk_Eng_Desc_CCI2018_20170612 V1_0.txt' with( FIELDTERMINATOR = '|', ROWTERMINATOR ='\n')
GO
delete from zzICD10CCI_Cat where len(cihivalue) <> 3
GO
update zzICD10CCI_Cat
set CIHIValue = ltrim(rtrim(CIHIValue)),
    shortDescription = ltrim(rtrim(shortDescription)),
    longDescription = ltrim(rtrim(longDescription))
GO
-------------------------Intervention Rubric -- dbo.ICD_IntervClass
if exists (select * from information_schema.tables where table_name = 'zzICD10CCI_Class')
    drop table zzICD10CCI_Class
GO
Create table zzICD10CCI_Class(
    CIHIValue varchar(10),
    shortDescription varchar(100),
    longDescription varchar(255)
)
GO
bulk insert zzICD10CCI_Class  from '<path>CCI_Rubric_Eng_Desc_CCI2018_V1_0.txt' with( FIELDTERMINATOR = '|', ROWTERMINATOR ='\n')
--bulk insert zzICD10CCI_Class  from '<path>CCI_Rubric_Eng_Desc_CCI2018_20170612 V1_0.txt' with( FIELDTERMINATOR = '|', ROWTERMINATOR ='\n')
GO

update zzICD10CCI_Class
set CIHIValue = ltrim(rtrim(CIHIValue)),
    shortDescription = ltrim(rtrim(shortDescription)),
    longDescription = ltrim(rtrim(longDescription))
GO
-------------------------Diagnosis Cat -- dbo.ICD_DiagCat
if exists (select * from information_schema.tables where table_name = 'zzICD10CA_Cat')
    drop table zzICD10CA_Cat
GO
Create table zzICD10CA_Cat(
    CIHIValue varchar(10) COLLATE Latin1_General_CI_AS NOT NULL,
    shortDescription varchar(100) COLLATE Latin1_General_CI_AS NOT NULL,
    longDescription varchar(255) COLLATE Latin1_General_CI_AS NOT NULL
)
GO
bulk insert zzICD10CA_Cat  from '<path>ICD_Cat1_Eng_Desc_10CA2018_V1_0.txt' with( FIELDTERMINATOR = '|', ROWTERMINATOR ='\n')
--bulk insert zzICD10CA_Cat  from '<path>ICD_Cat1_Eng_Desc_10CA2018_20170612 V1_0.txt' with( FIELDTERMINATOR = '|', ROWTERMINATOR ='\n')
GO
update zzICD10CA_Cat
set CIHIValue = ltrim(rtrim(CIHIValue)),
    shortDescription = ltrim(rtrim(shortDescription)),
    longDescription = ltrim(rtrim(longDescription))
GO
--Add Diag Cat
Update dbo.ICD_DiagCat
set DiagCatDescription = z.LongDescription
from ICD_DiagCat d
	inner join zzICD10CA_Cat z on z.CIHIValue = d.DiagCat
where 	z.LongDescription <> d.DiagCatDescription
GO
Insert into dbo.ICD_DiagCat(DiagCat, DiagCatDescription)
select z.CIHIValue, z.LongDescription
from ICD_DiagCat d
	right join zzICD10CA_Cat z on z.CIHIValue = d.DiagCat
where d.DiagCat is null
GO
--Add Interv Cat
Update dbo.ICD_IntervCat
set IntervCatDescription = z.LongDescription
from ICD_IntervCat d
	inner join zzICD10CCI_Cat z on z.CIHIValue = d.IntervCat
where 	z.LongDescription <> d.IntervCatDescription
GO
Insert into dbo.ICD_IntervCat(IntervCat, IntervCatDescription)
select z.CIHIValue, z.LongDescription
from ICD_IntervCat d
	right join zzICD10CCI_Cat z on z.CIHIValue = d.IntervCat
where d.IntervCat is null
GO
--Add Interv Class
Update dbo.ICD_IntervClass
set IntervClassDescription = z.LongDescription
from ICD_IntervClass d
	inner join zzICD10CCI_Class z on z.CIHIValue = d.IntervClass
where 	z.LongDescription <> d.IntervClassDescription
GO
Insert into dbo.ICD_IntervClass(IntervClass, IntervClassDescription)
select z.CIHIValue, z.LongDescription
from ICD_IntervClass d
	right join zzICD10CCI_Class z on z.CIHIValue = d.IntervClass
where d.IntervClass is null
GO

--Finish CA and CCI code

--########################################### Others ##############################################
if exists (select * from information_schema.tables where table_name = 'LU_Institution_Type')
	drop table LU_Institution_Type
GO
CREATE TABLE [dbo].[LU_Institution_Type](
	[Province] [nvarchar](255) NULL,
	[InstitutionNumber] [nvarchar](255) not NULL,
	[InstitutionType] [nvarchar](255) NULL,
	[InstitutionTypeDescription] [nvarchar](255) NULL,
	[InstitutionName] [nvarchar](255) NULL,
	[SubmittingToDAD] [nvarchar](255) NULL,
	CONSTRAINT [PK_LU_Institution_Type] PRIMARY KEY CLUSTERED 
	(
		[InstitutionNumber] ASC
	)
) ON [PRIMARY]
GO
bulk insert LU_Institution_Type  from '<path>2018DADMasterNumber.txt' with( FIELDTERMINATOR = '|', ROWTERMINATOR ='\n', CODEPAGE = 'RAW' )
GO
if not exists (select * from information_schema.columns where table_name = 'LU_Institution_10' and column_name = 'InstitutionType')
	alter table LU_Institution_10 add InstitutionType varchar(10)
GO
Update a set InstitutionType = z.InstitutionType
from LU_Institution_10 a
	inner join [LU_Institution_Type] z on a.cihivalue = z.InstitutionNumber
GO
Update LU_Institution_10 set InstitutionType = '' where InstitutionType is null
GO
Update LU_Institution_10 set isacute = 1 where isnull(isacute, 0) <> 1 and InstitutionType = '1'
GO
Update LU_Institution_10 set isacute = 0 where CIHIValue in ('99999', 'I9999')
GO
update LU_Institution_10 set InstitutionType = '1' where  HospitalType = 'AT' and InstitutionType = '' and validto < '2018/04/01'
GO

--End build 1
--MIS Code
if exists (select * from information_schema.tables where table_name = 'zz2018MISCode')
    drop table zz2018MISCode
GO
create table zz2018MISCode(
	FUNCTIONAL_CENTRE_CODE	varchar(500) COLLATE Latin1_General_CI_AS,
    FUNCTIONAL_CENTRE_SERIES_CODE	varchar(500) COLLATE Latin1_General_CI_AS,
    FUNCT_CENTRE_SERIES_E_DESC	varchar(500) COLLATE Latin1_General_CI_AS,
    AMCARE_TYPE_CODE	varchar(500) COLLATE Latin1_General_CI_AS,
    AMCARE_TYPE_E_DESC	varchar(500) COLLATE Latin1_General_CI_AS,
    AMCARE_GROUP_CODE	varchar(500) COLLATE Latin1_General_CI_AS,
    AMCARE_GROUP_E_DESC varchar(500) COLLATE Latin1_General_CI_AS
)
GO
bulk insert zz2018MISCode  from '<path>2018MISCode.txt' with ( fieldterminator = '|', rowterminator = '\n', CODEPAGE  = 'RAW' ) 
GO
Update zz2018MISCode
set FUNCTIONAL_CENTRE_CODE = ltrim(rtrim(FUNCTIONAL_CENTRE_CODE)),
	FUNCTIONAL_CENTRE_SERIES_CODE = ltrim(rtrim(FUNCTIONAL_CENTRE_SERIES_CODE)),
	FUNCT_CENTRE_SERIES_E_DESC = ltrim(rtrim(FUNCT_CENTRE_SERIES_E_DESC)),
	AMCARE_TYPE_CODE = ltrim(rtrim(AMCARE_TYPE_CODE)),
	AMCARE_TYPE_E_DESC = ltrim(rtrim(AMCARE_TYPE_E_DESC)),
	AMCARE_GROUP_CODE = ltrim(rtrim(AMCARE_GROUP_CODE)),
	AMCARE_GROUP_E_DESC = ltrim(rtrim(AMCARE_GROUP_E_DESC))
GO
update lu_miscode
set DefaultDescription = z.FUNCT_CENTRE_SERIES_E_DESC
-- select z.*, a.*
from lu_miscode a
    inner join zz2018MISCode z on a.cihivalue = z.FUNCTIONAL_CENTRE_CODE 
--where (a.DefaultDescription <> z.FUNCT_CENTRE_SERIES_E_DESC  or AMCARE_TYPE_CODE <> [AmCareTypeCode] or AMCARE_GROUP_CODE <> [AmCareGroup])
where a.DefaultDescription <> z.FUNCT_CENTRE_SERIES_E_DESC 
	and [AmCareGroup] is not null
GO
update lu_miscode
set Validto = '2018/03/31'
-- select z.*, a.*
from lu_miscode a
    left join zz2018MISCode z on a.cihivalue = z.FUNCTIONAL_CENTRE_CODE 
where z.FUNCT_CENTRE_SERIES_E_DESC is null and a.Validto > '2018/04/01' and [AmCareGroup] is not null
GO
update lu_miscode
set Validto = '2099/03/31'
--select z.*, a.*
from lu_miscode a
    inner join zz2018MISCode z on a.cihivalue = z.FUNCTIONAL_CENTRE_CODE 
where a.validto < '2018/04/01' and [AmCareGroup] is not null
GO
--End build 2
--DAD Error File
if exists (select * from information_schema.tables where table_name = 'zz2018DADError')
    drop table zz2018DADError
GO
create table zz2018DADError(
	IsError	varchar(500) COLLATE Latin1_General_CI_AS,
    Groupnumber	varchar(500) COLLATE Latin1_General_CI_AS,
    Fieldnumber	varchar(500) COLLATE Latin1_General_CI_AS,
    Errornumber	varchar(500) COLLATE Latin1_General_CI_AS,
    Englisherror	varchar(500) COLLATE Latin1_General_CI_AS,
    Frencherror	varchar(500) COLLATE Latin1_General_CI_AS,
    Associatedfile varchar(500) COLLATE Latin1_General_CI_AS
)
GO
bulk insert zz2018DADError  from '<path>2018DADErrorMessages.txt' with ( fieldterminator = '|', rowterminator = '\n', CODEPAGE  = 'RAW' ) 
--bulk insert zz2018DADError  from 'f:\2018\2018DADErrorMessages.txt' with ( fieldterminator = '|', rowterminator = '\n', CODEPAGE  = 'RAW' ) 
GO
--0725 
Delete from [dbo].[M_DADErrors] where batchyear = 2018
GO
alter table zz2018DADError add code int identity(1, 1)
GO
Insert into [dbo].[M_DADErrors](BatchYear, ErrorNumber, EnglishDescription, FrenchDescription, IsError)
select BatchYear = 2018, d.ErrorNumber, z.Englisherror, z.Frencherror, IsError = (case when z.IsError = 'W' then 0 else 1 end)
from 
(select ErrorNumber = IsError + Groupnumber + Fieldnumber + isnull(Errornumber, ''), count(*) cc, min(code) mincode
from zz2018DADError
where Englisherror is not null
group by  IsError + Groupnumber + Fieldnumber + isnull(Errornumber, '')
) d
inner join zz2018DADError z on d.mincode = z.code 
GO
if exists (select * from information_schema.tables where table_name = 'zz2018DADError')
    drop table zz2018DADError
GO
--NACRS Error File
if exists (select * from information_schema.tables where table_name = 'zz2018NACRSError')
    drop table zz2018NACRSError
GO
create table zz2018NACRSError(
    Errornumber	varchar(500) COLLATE Latin1_General_CI_AS,
    Englisherror	varchar(500) COLLATE Latin1_General_CI_AS
)
GO
bulk insert zz2018NACRSError  from '<path>2018NACRSErrorMessages.txt' with ( fieldterminator = '|', rowterminator = '\n', CODEPAGE  = 'RAW' ) 
--bulk insert zz2018NACRSError  from 'f:\2018\2018NACRSErrorMessages.txt' with ( fieldterminator = '|', rowterminator = '\n', CODEPAGE  = 'RAW' ) 
GO
Delete from [dbo].[M_AmcareErrors] where batchyear = 2018
GO
Insert into [dbo].[M_AmcareErrors](ErrorNumber, ErrorDescription, IsError, BatchYear)
select z.ErrorNumber, z.Englisherror, IsError = (case when z.ErrorNumber like 'w%' then 0 else 1 end), BatchYear = 2018
from zz2018NACRSError z
GO
if exists (select * from information_schema.tables where table_name = 'zz2018NACRSError')
    drop table zz2018NACRSError
GO
--Discharge Disposition Code
if exists (select * from information_schema.tables where table_name = 'zz2018DischargeDiagnosis')
    drop table zz2018DischargeDiagnosis
GO
create table zz2018DischargeDiagnosis(
    CIHIValue	varchar(500) COLLATE Latin1_General_CI_AS
)
GO
bulk insert zz2018DischargeDiagnosis  from '<path>2018DischargeDiagnsisCode.txt' with ( fieldterminator = '|', rowterminator = '\n', CODEPAGE  = 'RAW' ) 
--bulk insert zz2018DischargeDiagnosis  from 'f:\2018\2018DischargeDiagnsisCode.txt' with ( fieldterminator = '|', rowterminator = '\n', CODEPAGE  = 'RAW' ) 
GO
update ICD_DiagFile10
set [IsDischarge] = 1,[EDDiagnosis] = 1
from ICD_DiagFile10 a
	inner join zz2018DischargeDiagnosis z on a.CIHIValue = z.CIHIValue
GO
--End build 4
--Grouper Tables
if not exists (select * from information_schema.tables where table_name = 'tGrouper_CACS_ED_Interv')
CREATE TABLE [dbo].[tGrouper_CACS_ED_Interv](
	[fiscal_year] [varchar](255) not null,
	[cacs_ed_interv_code] [varchar](255) not null,
	[cacs_ed_interv_e_desc] [varchar](255) NULL,
	[cacs_ed_interv_f_desc] [varchar](255) NULL,
	[inv_tech_ind_code] [varchar](255) NULL,
	CONSTRAINT [PK_tGrouper_CACS_ED_Interv] PRIMARY KEY CLUSTERED (fiscal_year, cacs_ed_interv_code)
)
GO
if not exists (select * from information_schema.tables where table_name = 'tGrouper_CACS_ED_INTERV_MAP')
CREATE TABLE [dbo].[tGrouper_CACS_ED_INTERV_MAP](
	[methodology_year] [int] not NULL,
	[cacs_ed_interv_code] [varchar](255) not NULL,
	[interv_code] [varchar](255) NULL,
	CONSTRAINT [PK_tGrouper_CACS_ED_INTERV_MAP] PRIMARY KEY CLUSTERED (methodology_year, cacs_ed_interv_code)
)
GO
if not exists (select * from information_schema.tables where table_name = 'tGrouper_CACS_SPLIT_MODE_OF_VISIT')
CREATE TABLE [dbo].[tGrouper_CACS_SPLIT_MODE_OF_VISIT](
	[methodology_year] [int] not NULL,
	[cacs_split_code] [varchar](255) not NULL,
	[mode_of_visit_code] [varchar](255) not NULL,
	CONSTRAINT [PK_tGrouper_CACS_SPLIT_MODE_OF_VISIT] PRIMARY KEY CLUSTERED (methodology_year, cacs_split_code, mode_of_visit_code)
) ON [PRIMARY]
GO
if not exists (select * from information_schema.tables where table_name = 'tGrouper_CACS_ED_INTERV_BRANCH_ASSIGN')
CREATE TABLE [dbo].[tGrouper_CACS_ED_INTERV_BRANCH_ASSIGN](
	[methodology_year] [int] not NULL,
	[logic_type_code] [varchar](255) not NULL,
	[cacs_partition_code] [varchar](255) not NULL,
	[interv_branch_code] [varchar](255) not NULL,
	[diag_branch_code] [varchar](255) not NULL,
	[branch_code] [varchar](255) NULL,
	CONSTRAINT [PK_tGrouper_CACS_ED_INTERV_BRANCH_ASSIGN] PRIMARY KEY CLUSTERED (methodology_year, logic_type_code, cacs_partition_code, interv_branch_code, diag_branch_code)
) ON [PRIMARY]
GO
if not exists (select * from information_schema.tables where table_name = 'tGrouper_CACS_MODE_OF_VISIT')
CREATE TABLE [dbo].[tGrouper_CACS_MODE_OF_VISIT](
	[mode_of_visit_code] [varchar](255) not NULL,
	[mode_of_visit_e_desc] [varchar](255) NULL,
	[mode_of_visit_f_desc] [varchar](255) NULL,
	CONSTRAINT [PK_tGrouper_CACS_MODE_OF_VISIT] PRIMARY KEY CLUSTERED (mode_of_visit_code)
) ON [PRIMARY]
GO
Delete from   [dbo].[tGrouper_CACS_MODE_OF_VISIT]
GO
insert into [dbo].[tGrouper_CACS_MODE_OF_VISIT] values('1', 'Visit (face-to-face) with individual patient', 'Visite (en personne) du patient')
GO
insert into [dbo].[tGrouper_CACS_MODE_OF_VISIT] values('2', 'Visit (face-to-face) with a group of patients at facility', 'Visite (en personne) d?un groupe de patients à l?établissement')
GO
insert into [dbo].[tGrouper_CACS_MODE_OF_VISIT] values('3', 'Telephone or email contact with patient', 'Communication par téléphone ou par courriel avec le patient')
GO
insert into [dbo].[tGrouper_CACS_MODE_OF_VISIT] values('4', 'Videoconference contact with patient', 'Communication par vidéoconférence avec le patient')
GO
insert into [dbo].[tGrouper_CACS_MODE_OF_VISIT] values('5', 'Visit with patient off-site in patient?s home', 'Visite du patient à sa résidence')
GO
insert into [dbo].[tGrouper_CACS_MODE_OF_VISIT] values('6', 'Visit with patient off-site at outreach setting', 'Visite du patient dans une clinique/visite dans des locaux à l?extérieur de l?établissement')
GO
insert into [dbo].[tGrouper_CACS_MODE_OF_VISIT] values('7', 'Visit with patient in other setting', 'Visite du patient dans un autre milieu')
GO

Delete from tGrouper_CACS_VISIT_DISP
GO
insert into [dbo].tGrouper_CACS_VISIT_DISP values('06', 'Admit to reporting facility as inpatient to  SCU or OR from amb care', 'Admission USS ou salle d?op de l?établ déclarant après visite amb')
GO
insert into [dbo].tGrouper_CACS_VISIT_DISP values('07', 'Admit to reporting facility as inpatient to another unit from amb care', 'Admission autre unité de l?établ déclarant après visite amb')
GO
insert into [dbo].tGrouper_CACS_VISIT_DISP values('08', 'Transfer to another acute care facility directly from amb care', 'Transfert vers autre établ de SCD après visite amb')
GO
insert into [dbo].tGrouper_CACS_VISIT_DISP values('09', 'Transfer to another non-acute care facility directly from amb care', 'Transfert vers autre établ non-SCD après visite amb')
GO
insert into [dbo].tGrouper_CACS_VISIT_DISP values('12', 'Intra-facility transfer to day surgery', 'Transfert intra-établissement à l?unité de chirurgie d?un jour')
GO
insert into [dbo].tGrouper_CACS_VISIT_DISP values('13', 'Intra-facility transfer to ED', 'Transfert intra-établissement au service d?urgence')
GO
insert into [dbo].tGrouper_CACS_VISIT_DISP values('14', 'Intra-facility transfer to clinic', 'Transfert intra-établissement dans une clinique')
GO
insert into [dbo].tGrouper_CACS_VISIT_DISP values('16', 'Discharge to private home, condo, apt with support service/referral', 'Retour domicile, condo ou appartement privé avec soutien/orientation')
GO
insert into [dbo].tGrouper_CACS_VISIT_DISP values('17', 'Discharge to private home, condo, apt without support service/referral', 'Retour domicile, condo ou appartement privé sans soutien/orientation')
GO
insert into [dbo].tGrouper_CACS_VISIT_DISP values('30', 'Transfer to Residential care', 'Transfert vers des soins en hébergement')
GO
insert into [dbo].tGrouper_CACS_VISIT_DISP values('40', 'Transfer to Group/supportive living', 'Transfert vers un foyer de groupe ou un logement supervisé')
GO
insert into [dbo].tGrouper_CACS_VISIT_DISP values('61', 'Left at his/her own risk following registration', 'Patient ayant quitté à ses propres risques après l?inscription')
GO
insert into [dbo].tGrouper_CACS_VISIT_DISP values('62', 'Left at his/her own risk post-initial treatment', 'Patient ayant quitté à ses propres risques après le traitement initial')
GO
insert into [dbo].tGrouper_CACS_VISIT_DISP values('63', 'Left after triage', 'Patient ayant quitté après le triage')
GO
insert into [dbo].tGrouper_CACS_VISIT_DISP values('64', 'Left after initial assessment', 'Patient ayant quitté après l?évaluation initiale')
GO
insert into [dbo].tGrouper_CACS_VISIT_DISP values('71', 'Dead on arrival', 'Décédé à l?arrivée')
GO
insert into [dbo].tGrouper_CACS_VISIT_DISP values('72', 'Died in facility', 'Décédé à l?établissement')
GO
insert into [dbo].tGrouper_CACS_VISIT_DISP values('73', 'Medical assistance in dying (MAID)', 'Aide médicale à mourir (AMAM)')
GO
insert into [dbo].tGrouper_CACS_VISIT_DISP values('74', 'Suicide in facility', 'Suicide à l?établissement')
GO
insert into [dbo].tGrouper_CACS_VISIT_DISP values('90', 'Transfer to correctional facility', 'Transfert vers un établissement correctionnel')
GO
if not exists (select * from information_schema.tables where table_name = 'tGrouper_CACS_VISIT_DISP_MAP')
CREATE TABLE [dbo].tGrouper_CACS_VISIT_DISP_MAP(
	[hist_visit_disp_code] [varchar](255) NULL,
	[visit_disp_code] [varchar](255) NULL
) ON [PRIMARY]

GO
Delete from tGrouper_CACS_VISIT_DISP_MAP
GO
Insert into tGrouper_CACS_VISIT_DISP_MAP(hist_visit_disp_code, visit_disp_code) values('01', '17')
GO
Insert into tGrouper_CACS_VISIT_DISP_MAP(hist_visit_disp_code, visit_disp_code) values('02', '61')
GO
Insert into tGrouper_CACS_VISIT_DISP_MAP(hist_visit_disp_code, visit_disp_code) values('03', '63')
GO
Insert into tGrouper_CACS_VISIT_DISP_MAP(hist_visit_disp_code, visit_disp_code) values('04', '64')
GO
Insert into tGrouper_CACS_VISIT_DISP_MAP(hist_visit_disp_code, visit_disp_code) values('05', '62')
GO
Insert into tGrouper_CACS_VISIT_DISP_MAP(hist_visit_disp_code, visit_disp_code) values('06', '06')
GO
Insert into tGrouper_CACS_VISIT_DISP_MAP(hist_visit_disp_code, visit_disp_code) values('07', '07')
GO
Insert into tGrouper_CACS_VISIT_DISP_MAP(hist_visit_disp_code, visit_disp_code) values('08', '08')
GO
Insert into tGrouper_CACS_VISIT_DISP_MAP(hist_visit_disp_code, visit_disp_code) values('09', '09')
GO
Insert into tGrouper_CACS_VISIT_DISP_MAP(hist_visit_disp_code, visit_disp_code) values('10', '72')
GO
Insert into tGrouper_CACS_VISIT_DISP_MAP(hist_visit_disp_code, visit_disp_code) values('11', '71')
GO
Insert into tGrouper_CACS_VISIT_DISP_MAP(hist_visit_disp_code, visit_disp_code) values('12', '12')
GO
Insert into tGrouper_CACS_VISIT_DISP_MAP(hist_visit_disp_code, visit_disp_code) values('13', '13')
GO
Insert into tGrouper_CACS_VISIT_DISP_MAP(hist_visit_disp_code, visit_disp_code) values('14', '14')
GO
Insert into tGrouper_CACS_VISIT_DISP_MAP(hist_visit_disp_code, visit_disp_code) values('15', '16')
GO
if not exists (select * from information_schema.tables where table_name = 'tGrouper_CMG_DISCHARGE_DISP')
CREATE TABLE [dbo].[tGrouper_CMG_DISCHARGE_DISP](
	[discharge_disp_code] [varchar](255) not NULL,
	[discharge_disp_e_desc] [varchar](255) NULL,
	[discharge_disp_f_desc] [varchar](255) NULL
) ON [PRIMARY]
GO
delete from  [dbo].[tGrouper_CMG_DISCHARGE_DISP]
GO
insert into tGrouper_CMG_DISCHARGE_DISP values('04', 'Discharge to private home, condo, apt with support service/referral', 'Retour domicile, condo ou appartement privé avec soutien/orientation')
GO
insert into tGrouper_CMG_DISCHARGE_DISP values('05', 'Discharge to private home, condo, apt without support service/referral', 'Retour domicile, condo ou appartement privé sans soutien/orientation')
GO
insert into tGrouper_CMG_DISCHARGE_DISP values('08', 'Cadaveric donor admitted for organ/tissue procurement', 'Donneur décédé admis pour un prélèvement d?organes ou de tissus')
GO
insert into tGrouper_CMG_DISCHARGE_DISP values('09', 'Stillbirth', 'Mortinaissance')
GO
insert into tGrouper_CMG_DISCHARGE_DISP values('10', 'Transfer to reporting or another hospital for inpatient care', 'Transfert pour hospitalisation (établissement déclarant ou autre)')
GO
insert into tGrouper_CMG_DISCHARGE_DISP values('20', 'Transfer to reporting or another emergency dept or ambulatory care', 'Transfert vers SU ou soins ambulatoires (établ déclarant ou autre)')
GO
insert into tGrouper_CMG_DISCHARGE_DISP values('30', 'Transfer to residential care', 'Transfert vers des soins en hébergement')
GO
insert into tGrouper_CMG_DISCHARGE_DISP values('40', 'Transfer to group / supportive living', 'Transfert vers un foyer de groupe ou un logement supervisé')
GO
insert into tGrouper_CMG_DISCHARGE_DISP values('61', 'Absent without leave (AWOL)', 'Absent sans permission (ASP)')
GO
insert into tGrouper_CMG_DISCHARGE_DISP values('62', 'Left against medical advice (LAMA)', 'Sortie contre l?avis du médecin')
GO
insert into tGrouper_CMG_DISCHARGE_DISP values('65', 'Did not return from pass/leave', 'N?est pas revenu d?un congé temporaire')
GO
insert into tGrouper_CMG_DISCHARGE_DISP values('66', 'Died while on pass/leave', 'Décédé pendant un congé temporaire')
GO
insert into tGrouper_CMG_DISCHARGE_DISP values('67', 'Suicide out of facility', 'Suicide hors établissement')
GO
insert into tGrouper_CMG_DISCHARGE_DISP values('72', 'Died in facility', 'Décédé à l?établissement')
GO
insert into tGrouper_CMG_DISCHARGE_DISP values('73', 'Medical assistance in dying (MAID)', 'Aide médicale à mourir (AMAM)')
GO
insert into tGrouper_CMG_DISCHARGE_DISP values('74', 'Suicide in facility', 'Suicide à l?établissement')
GO
insert into tGrouper_CMG_DISCHARGE_DISP values('90', 'Transfer to correctional facility', 'Transfert vers un établissement correctionnel')
GO
if not exists (select * from information_schema.tables where table_name = 'tGrouper_CMG_DISCHARGE_DISP_MAP')
CREATE TABLE [dbo].[tGrouper_CMG_DISCHARGE_DISP_MAP](
	[hist_discharge_disp_code] [varchar](255) NULL,
	[discharge_disp_code] [varchar](255) NULL
) ON [PRIMARY]
GO
Delete from [dbo].[tGrouper_CMG_DISCHARGE_DISP_MAP]
GO
insert into tGrouper_CMG_DISCHARGE_DISP_MAP(hist_discharge_disp_code, discharge_disp_code) values('01', '10')
GO
insert into tGrouper_CMG_DISCHARGE_DISP_MAP(hist_discharge_disp_code, discharge_disp_code) values('02', '30')
GO
insert into tGrouper_CMG_DISCHARGE_DISP_MAP(hist_discharge_disp_code, discharge_disp_code) values('03', '20')
GO
insert into tGrouper_CMG_DISCHARGE_DISP_MAP(hist_discharge_disp_code, discharge_disp_code) values('04', '04')
GO
insert into tGrouper_CMG_DISCHARGE_DISP_MAP(hist_discharge_disp_code, discharge_disp_code) values('05', '05')
GO
insert into tGrouper_CMG_DISCHARGE_DISP_MAP(hist_discharge_disp_code, discharge_disp_code) values('06', '62')
GO
insert into tGrouper_CMG_DISCHARGE_DISP_MAP(hist_discharge_disp_code, discharge_disp_code) values('07', '72')
GO
insert into tGrouper_CMG_DISCHARGE_DISP_MAP(hist_discharge_disp_code, discharge_disp_code) values('08', '08')
GO
insert into tGrouper_CMG_DISCHARGE_DISP_MAP(hist_discharge_disp_code, discharge_disp_code) values('09', '09')
GO
insert into tGrouper_CMG_DISCHARGE_DISP_MAP(hist_discharge_disp_code, discharge_disp_code) values('12', '65')
GO
if not exists (select * from information_schema.tables where table_name = 'tGrouper_CMG_INPATIENT_CACS_EXCLUSION')
CREATE TABLE [dbo].[tGrouper_CMG_INPATIENT_CACS_EXCLUSION](
	[methodology_year] [int] not NULL,
	[cacs_code] [varchar](255) not NULL,
	CONSTRAINT [PK_tGrouper_CMG_INPATIENT_CACS_EXCLUSION] PRIMARY KEY CLUSTERED ([methodology_year], [cacs_code])
)
GO
delete from tGrouper_CACS_Aanaesthetic where methodology_year = 2018 
GO
delete from tGrouper_CACS_Aanaesthetic_Assign where methodology_year = 2018 
GO
delete from tGrouper_CACS_Age_Category where methodology_year = 2018 
GO
delete from tGrouper_CACS_Age_Category_Assign where methodology_year = 2018 
GO
delete from tGrouper_CACS_Ambulatory_Diag_Assign where methodology_year = 2018 
GO
delete from tGrouper_CACS_Ambulatory_Interv_Assign where methodology_year = 2018 
GO
delete from tGrouper_CACS_ANAESTHETIC_ASSIGN where methodology_year = 2018 
GO
delete from tGrouper_CACS_Base_RIW where methodology_year = 2018 
GO
delete from tGrouper_CACS_Branch_Logic where methodology_year = 2018 
GO
delete from tGrouper_CACS_CACS where methodology_year = 2018 
GO
delete from tGrouper_CACS_ED_Diag_Assign where methodology_year = 2018 
GO
delete from tGrouper_CACS_ED_Interv_Assign where methodology_year = 2018 
GO
delete from tGrouper_CACS_ED_INTERV_BRANCH_ASSIGN where methodology_year = 2018 
GO
delete from tGrouper_CACS_ED_INTERV_MAP where methodology_year = 2018 
GO
delete from tGrouper_CACS_Interaction_Block where methodology_year = 2018 
GO
delete from tGrouper_CACS_Interaction_RIW where methodology_year = 2018 
GO
delete from tGrouper_CACS_Inv_Tech_Category where methodology_year = 2018 
GO
delete from tGrouper_CACS_Inv_Tech_Category_Assign where methodology_year = 2018 
GO
delete from tGrouper_CACS_Inv_Tech_RIW where methodology_year = 2018 
GO
delete from tGrouper_CACS_Logic where methodology_year = 2018 
GO
delete from tGrouper_CACS_Logic_Type where methodology_year = 2018 
GO
delete from tGrouper_CACS_MAC where methodology_year = 2018 
GO
delete from tGrouper_CACS_Partition where methodology_year = 2018 
GO
delete from tGrouper_CACS_Rehab_Program_Aarea_Assign where methodology_year = 2018 
GO
delete from tGrouper_CACS_Split where methodology_year = 2018 
GO
delete from tGrouper_CACS_Split_Interv where methodology_year = 2018 
GO
delete from tGrouper_CACS_SPLIT_MODE_OF_VISIT where methodology_year = 2018 
GO
delete from tGrouper_CACS_Split_Type where methodology_year = 2018 
GO
delete from tGrouper_CACS_Split_Visit_Disp where methodology_year = 2018 
GO
delete from tGrouper_CMG_ATYPICAL_RIW_2015 where methodology_year = 2018 
GO
delete from tGrouper_CMG_BASE_ELOS_RIW where methodology_year = 2018 
GO
delete from tGrouper_CMG_BRANCH_LOGIC where methodology_year = 2018 
GO
delete from tGrouper_CMG_CMG where methodology_year = 2018 
GO
delete from tGrouper_CMG_CMG_AGE_CATEGORY where methodology_year = 2018 
GO
delete from tGrouper_CMG_CMG_AGE_CATEGORY_ASSIGN where methodology_year = 2018 
GO
delete from tGrouper_CMG_CMG_LOGIC where methodology_year = 2018 
GO
delete from tGrouper_CMG_CMG_SPLIT where methodology_year = 2018 
GO
delete from tGrouper_CMG_CMG_SPLIT_ANAESTHETIC where methodology_year = 2018 
GO
delete from tGrouper_CMG_CMG_SPLIT_DIAG where methodology_year = 2018 
GO
delete from tGrouper_CMG_CMG_SPLIT_ENTRY where methodology_year = 2018 
GO
delete from tGrouper_CMG_CMG_SPLIT_GESTATION where methodology_year = 2018 
GO
delete from tGrouper_CMG_CMG_SPLIT_INTERV where methodology_year = 2018 
GO
delete from tGrouper_CMG_CMG_WEIGHT_ASSIGN where methodology_year = 2018 
GO
delete from tGrouper_CMG_COMORBIDITY_CNT where methodology_year = 2018 
GO
delete from tGrouper_CMG_COMORBIDITY_CNT_FACTOR_ASSIGN where methodology_year = 2018 
GO
delete from tGrouper_CMG_COMORBIDITY_ELOS_RIW where methodology_year = 2018 
GO
delete from tGrouper_CMG_COMORBIDITY_EXCLUSION_PAIR where methodology_year = 2018 
GO
delete from tGrouper_CMG_COMORBIDITY_FACTOR_ASSIGN where methodology_year = 2018 
GO
delete from tGrouper_CMG_COMORBIDITY_INTERACTION where methodology_year = 2018 
GO
delete from tGrouper_CMG_COMORBIDITY_LEVEL where methodology_year = 2018 
GO
delete from tGrouper_CMG_COMORBIDITY_LEVEL_ASSIGN where methodology_year = 2018 
GO
delete from tGrouper_CMG_EXCEPTION_BRANCH_ASSIGN where methodology_year = 2018 
GO
delete from tGrouper_CMG_FACTOR_INTERACTION_ELOS_RIW_2011 where methodology_year = 2018 
GO
delete from tGrouper_CMG_FLAGGED_INTERV_ASSIGN where methodology_year = 2018 
GO
delete from tGrouper_CMG_FLAGGED_INTERV_CATEGORY where methodology_year = 2018 
GO
delete from tGrouper_CMG_FLAGGED_INTERV_ELOS_RIW_2015 where methodology_year = 2018 
GO
delete from tGrouper_CMG_FLAGGED_INTERV_EXCLUSION where methodology_year = 2018 
GO
delete from tGrouper_CMG_FLAGGED_INTERV_GROUP where methodology_year = 2018 
GO
delete from tGrouper_CMG_FLAGGED_INTERV_STATUS where methodology_year = 2018 
GO
delete from tGrouper_CMG_FLAGGED_INTERV_STATUS_ASSIGN where methodology_year = 2018 
GO
delete from tGrouper_CMG_INPATIENT_CACS_EXCLUSION where methodology_year = 2018 
GO
delete from tGrouper_CMG_INTERV_EVENT_CNT where methodology_year = 2018 
GO
delete from tGrouper_CMG_INTERV_EVENT_ELOS_RIW where methodology_year = 2018 
GO
delete from tGrouper_CMG_INTERV_OOH_ASSIGN where methodology_year = 2018 
GO
delete from tGrouper_CMG_INTERV_OOH_CATEGORY where methodology_year = 2018 
GO
delete from tGrouper_CMG_INTERV_OOH_RIW where methodology_year = 2018 
GO
delete from tGrouper_CMG_IP_FACTOR_INTERACTION_BLOCK where methodology_year = 2018 
GO
delete from tGrouper_CMG_IP_RI_LEVEL where methodology_year = 2018 
GO
delete from tGrouper_CMG_IP_RIW_ATYPICAL where methodology_year = 2018 
GO
delete from tGrouper_CMG_IP_RIW_ATYPICAL_TYPE where methodology_year = 2018 
GO
delete from tGrouper_CMG_LOS_GROUP where methodology_year = 2018 
GO
delete from tGrouper_CMG_LOS_PERCENTILE where methodology_year = 2018 
GO
delete from tGrouper_CMG_MCC_ASSIGN where methodology_year = 2018 
GO
delete from tGrouper_CMG_MCC_INTERV where methodology_year = 2018 
GO
delete from tGrouper_CMG_MCC_INTERV_PARTITION where methodology_year = 2018 
GO
delete from tGrouper_CMG_MCC_LOGIC where methodology_year = 2018 
GO
delete from tGrouper_CMG_MCC_PARTITION where methodology_year = 2018 
GO
delete from tGrouper_CMG_NEWBORN_BRANCH_ASSIGN where methodology_year = 2018 
GO
delete from tGrouper_CMG_PERCENTILE where methodology_year = 2018 
GO
delete from tGrouper_HIG where methodology_year = 2018 
GO
delete from tGrouper_HIG_Age_Category where methodology_year = 2018 
GO
delete from tGrouper_HIG_Age_ELOS_Weight where methodology_year = 2018 
GO
delete from tGrouper_HIG_Atypical where methodology_year = 2018 
GO
delete from tGrouper_HIG_Atypical_Percentile where methodology_year = 2018 
GO
delete from tGrouper_HIG_Base_Elos_Weight where methodology_year = 2018 
GO
delete from tGrouper_HIG_Branch_Logic where methodology_year = 2018 
GO
delete from tGrouper_HIG_Event_Elos_Weight where methodology_year = 2018 
GO
delete from tGrouper_HIG_FI_Elos_Weight where methodology_year = 2018 
GO
delete from tGrouper_HIG_Logic where methodology_year = 2018 
GO
delete from tGrouper_HIG_OOH_Assign where methodology_year = 2018 
GO
delete from tGrouper_HIG_OOH_Factor where methodology_year = 2018 
GO
delete from tGrouper_HIG_Split where methodology_year = 2018 
GO
delete from tGrouper_HIG_Split_Diag where methodology_year = 2018 
GO
delete from tGrouper_HIG_Split_Interv where methodology_year = 2018 
GO
delete from tGrouper_HIG_Split_Type where methodology_year = 2018 
GO
delete from tGrouper_PROV_CACS_BASE_RIW where methodology_year = 2018 
GO
delete from tGrouper_PROV_CACS_INTERACTION_RIW where methodology_year = 2018 
GO
delete from tGrouper_PROV_CACS_INV_TECH_RIW where methodology_year = 2018 
GO
bulk insert tGrouper_CACS_Aanaesthetic from '<path>tGrouper_CACS_Aanaesthetic.txt' with ( fieldterminator = '|', rowterminator = '\n', CODEPAGE  = 'RAW' )  
GO
bulk insert tGrouper_CACS_Aanaesthetic_Assign from '<path>tGrouper_CACS_Aanaesthetic_Assign.txt' with ( fieldterminator = '|', rowterminator = '\n', CODEPAGE  = 'RAW' )  
GO
bulk insert tGrouper_CACS_Age_Category from '<path>tGrouper_CACS_Age_Category.txt' with ( fieldterminator = '|', rowterminator = '\n', CODEPAGE  = 'RAW' )  
GO
bulk insert tGrouper_CACS_Age_Category_Assign from '<path>tGrouper_CACS_Age_Category_Assign.txt' with ( fieldterminator = '|', rowterminator = '\n', CODEPAGE  = 'RAW' )  
GO
bulk insert tGrouper_CACS_Ambulatory_Diag_Assign from '<path>tGrouper_CACS_Ambulatory_Diag_Assign.txt' with ( fieldterminator = '|', rowterminator = '\n', CODEPAGE  = 'RAW' )  
GO
bulk insert tGrouper_CACS_Ambulatory_Interv_Assign from '<path>tGrouper_CACS_Ambulatory_Interv_Assign.txt' with ( fieldterminator = '|', rowterminator = '\n', CODEPAGE  = 'RAW' )  
GO
bulk insert tGrouper_CACS_ANAESTHETIC_ASSIGN from '<path>tGrouper_CACS_ANAESTHETIC_ASSIGN.txt' with ( fieldterminator = '|', rowterminator = '\n', CODEPAGE  = 'RAW' )  
GO
bulk insert tGrouper_CACS_Base_RIW from '<path>tGrouper_CACS_Base_RIW.txt' with ( fieldterminator = '|', rowterminator = '\n', CODEPAGE  = 'RAW' )  
GO
bulk insert tGrouper_CACS_Branch_Logic from '<path>tGrouper_CACS_Branch_Logic.txt' with ( fieldterminator = '|', rowterminator = '\n', CODEPAGE  = 'RAW' )  
GO
bulk insert tGrouper_CACS_CACS from '<path>tGrouper_CACS_CACS.txt' with ( fieldterminator = '|', rowterminator = '\n', CODEPAGE  = 'RAW' )  
GO
bulk insert tGrouper_CACS_ED_Diag_Assign from '<path>tGrouper_CACS_ED_Diag_Assign.txt' with ( fieldterminator = '|', rowterminator = '\n', CODEPAGE  = 'RAW' )  
GO
bulk insert tGrouper_CACS_ED_Interv_Assign from '<path>tGrouper_CACS_ED_Interv_Assign.txt' with ( fieldterminator = '|', rowterminator = '\n', CODEPAGE  = 'RAW' )  
GO
bulk insert tGrouper_CACS_ED_INTERV_BRANCH_ASSIGN from '<path>tGrouper_CACS_ED_INTERV_BRANCH_ASSIGN.txt' with ( fieldterminator = '|', rowterminator = '\n', CODEPAGE  = 'RAW' )  
GO
bulk insert tGrouper_CACS_ED_INTERV_MAP from '<path>tGrouper_CACS_ED_INTERV_MAP.txt' with ( fieldterminator = '|', rowterminator = '\n', CODEPAGE  = 'RAW' )  
GO
bulk insert tGrouper_CACS_Interaction_Block from '<path>tGrouper_CACS_Interaction_Block.txt' with ( fieldterminator = '|', rowterminator = '\n', CODEPAGE  = 'RAW' )  
GO
bulk insert tGrouper_CACS_Interaction_RIW from '<path>tGrouper_CACS_Interaction_RIW.txt' with ( fieldterminator = '|', rowterminator = '\n', CODEPAGE  = 'RAW' )  
GO
bulk insert tGrouper_CACS_Inv_Tech_Category from '<path>tGrouper_CACS_Inv_Tech_Category.txt' with ( fieldterminator = '|', rowterminator = '\n', CODEPAGE  = 'RAW' )  
GO
bulk insert tGrouper_CACS_Inv_Tech_Category_Assign from '<path>tGrouper_CACS_Inv_Tech_Category_Assign.txt' with ( fieldterminator = '|', rowterminator = '\n', CODEPAGE  = 'RAW' )  
GO
bulk insert tGrouper_CACS_Inv_Tech_RIW from '<path>tGrouper_CACS_Inv_Tech_RIW.txt' with ( fieldterminator = '|', rowterminator = '\n', CODEPAGE  = 'RAW' )  
GO
bulk insert tGrouper_CACS_Logic from '<path>tGrouper_CACS_Logic.txt' with ( fieldterminator = '|', rowterminator = '\n', CODEPAGE  = 'RAW' )  
GO
bulk insert tGrouper_CACS_Logic_Type from '<path>tGrouper_CACS_Logic_Type.txt' with ( fieldterminator = '|', rowterminator = '\n', CODEPAGE  = 'RAW' )  
GO
bulk insert tGrouper_CACS_MAC from '<path>tGrouper_CACS_MAC.txt' with ( fieldterminator = '|', rowterminator = '\n', CODEPAGE  = 'RAW' )  
GO
bulk insert tGrouper_CACS_Partition from '<path>tGrouper_CACS_Partition.txt' with ( fieldterminator = '|', rowterminator = '\n', CODEPAGE  = 'RAW' )  
GO
bulk insert tGrouper_CACS_Rehab_Program_Aarea_Assign from '<path>tGrouper_CACS_Rehab_Program_Aarea_Assign.txt' with ( fieldterminator = '|', rowterminator = '\n', CODEPAGE  = 'RAW' )  
GO
bulk insert tGrouper_CACS_Split from '<path>tGrouper_CACS_Split.txt' with ( fieldterminator = '|', rowterminator = '\n', CODEPAGE  = 'RAW' )  
GO
bulk insert tGrouper_CACS_Split_Interv from '<path>tGrouper_CACS_Split_Interv.txt' with ( fieldterminator = '|', rowterminator = '\n', CODEPAGE  = 'RAW' )  
GO
bulk insert tGrouper_CACS_SPLIT_MODE_OF_VISIT from '<path>tGrouper_CACS_SPLIT_MODE_OF_VISIT.txt' with ( fieldterminator = '|', rowterminator = '\n', CODEPAGE  = 'RAW' )  
GO
bulk insert tGrouper_CACS_Split_Type from '<path>tGrouper_CACS_Split_Type.txt' with ( fieldterminator = '|', rowterminator = '\n', CODEPAGE  = 'RAW' )  
GO
bulk insert tGrouper_CACS_Split_Visit_Disp from '<path>tGrouper_CACS_Split_Visit_Disp.txt' with ( fieldterminator = '|', rowterminator = '\n', CODEPAGE  = 'RAW' )  
GO
bulk insert tGrouper_CMG_ATYPICAL_RIW_2015 from '<path>tGrouper_CMG_ATYPICAL_RIW_2015.txt' with ( fieldterminator = '|', rowterminator = '\n', CODEPAGE  = 'RAW' )  
GO
bulk insert tGrouper_CMG_BASE_ELOS_RIW from '<path>tGrouper_CMG_BASE_ELOS_RIW.txt' with ( fieldterminator = '|', rowterminator = '\n', CODEPAGE  = 'RAW' )  
GO
bulk insert tGrouper_CMG_BRANCH_LOGIC from '<path>tGrouper_CMG_BRANCH_LOGIC.txt' with ( fieldterminator = '|', rowterminator = '\n', CODEPAGE  = 'RAW' )  
GO
bulk insert tGrouper_CMG_CMG from '<path>tGrouper_CMG_CMG.txt' with ( fieldterminator = '|', rowterminator = '\n', CODEPAGE  = 'RAW' )  
GO
bulk insert tGrouper_CMG_CMG_AGE_CATEGORY from '<path>tGrouper_CMG_CMG_AGE_CATEGORY.txt' with ( fieldterminator = '|', rowterminator = '\n', CODEPAGE  = 'RAW' )  
GO
bulk insert tGrouper_CMG_CMG_AGE_CATEGORY_ASSIGN from '<path>tGrouper_CMG_CMG_AGE_CATEGORY_ASSIGN.txt' with ( fieldterminator = '|', rowterminator = '\n', CODEPAGE  = 'RAW' )  
GO
bulk insert tGrouper_CMG_CMG_LOGIC from '<path>tGrouper_CMG_CMG_LOGIC.txt' with ( fieldterminator = '|', rowterminator = '\n', CODEPAGE  = 'RAW' )  
GO
bulk insert tGrouper_CMG_CMG_SPLIT from '<path>tGrouper_CMG_CMG_SPLIT.txt' with ( fieldterminator = '|', rowterminator = '\n', CODEPAGE  = 'RAW' )  
GO
bulk insert tGrouper_CMG_CMG_SPLIT_ANAESTHETIC from '<path>tGrouper_CMG_CMG_SPLIT_ANAESTHETIC.txt' with ( fieldterminator = '|', rowterminator = '\n', CODEPAGE  = 'RAW' )  
GO
bulk insert tGrouper_CMG_CMG_SPLIT_DIAG from '<path>tGrouper_CMG_CMG_SPLIT_DIAG.txt' with ( fieldterminator = '|', rowterminator = '\n', CODEPAGE  = 'RAW' )  
GO
bulk insert tGrouper_CMG_CMG_SPLIT_ENTRY from '<path>tGrouper_CMG_CMG_SPLIT_ENTRY.txt' with ( fieldterminator = '|', rowterminator = '\n', CODEPAGE  = 'RAW' )  
GO
bulk insert tGrouper_CMG_CMG_SPLIT_GESTATION from '<path>tGrouper_CMG_CMG_SPLIT_GESTATION.txt' with ( fieldterminator = '|', rowterminator = '\n', CODEPAGE  = 'RAW' )  
GO
bulk insert tGrouper_CMG_CMG_SPLIT_INTERV from '<path>tGrouper_CMG_CMG_SPLIT_INTERV.txt' with ( fieldterminator = '|', rowterminator = '\n', CODEPAGE  = 'RAW' )  
GO
bulk insert tGrouper_CMG_CMG_WEIGHT_ASSIGN from '<path>tGrouper_CMG_CMG_WEIGHT_ASSIGN.txt' with ( fieldterminator = '|', rowterminator = '\n', CODEPAGE  = 'RAW' )  
GO
bulk insert tGrouper_CMG_COMORBIDITY_CNT from '<path>tGrouper_CMG_COMORBIDITY_CNT.txt' with ( fieldterminator = '|', rowterminator = '\n', CODEPAGE  = 'RAW' )  
GO
bulk insert tGrouper_CMG_COMORBIDITY_CNT_FACTOR_ASSIGN from '<path>tGrouper_CMG_COMORBIDITY_CNT_FACTOR_ASSIGN.txt' with ( fieldterminator = '|', rowterminator = '\n', CODEPAGE  = 'RAW' )  
GO
bulk insert tGrouper_CMG_COMORBIDITY_ELOS_RIW from '<path>tGrouper_CMG_COMORBIDITY_ELOS_RIW.txt' with ( fieldterminator = '|', rowterminator = '\n', CODEPAGE  = 'RAW' )  
GO
bulk insert tGrouper_CMG_COMORBIDITY_EXCLUSION_PAIR from '<path>tGrouper_CMG_COMORBIDITY_EXCLUSION_PAIR.txt' with ( fieldterminator = '|', rowterminator = '\n', CODEPAGE  = 'RAW' )  
GO
bulk insert tGrouper_CMG_COMORBIDITY_FACTOR_ASSIGN from '<path>tGrouper_CMG_COMORBIDITY_FACTOR_ASSIGN.txt' with ( fieldterminator = '|', rowterminator = '\n', CODEPAGE  = 'RAW' )  
GO
bulk insert tGrouper_CMG_COMORBIDITY_INTERACTION from '<path>tGrouper_CMG_COMORBIDITY_INTERACTION.txt' with ( fieldterminator = '|', rowterminator = '\n', CODEPAGE  = 'RAW' )  
GO
bulk insert tGrouper_CMG_COMORBIDITY_LEVEL from '<path>tGrouper_CMG_COMORBIDITY_LEVEL.txt' with ( fieldterminator = '|', rowterminator = '\n', CODEPAGE  = 'RAW' )  
GO
bulk insert tGrouper_CMG_COMORBIDITY_LEVEL_ASSIGN from '<path>tGrouper_CMG_COMORBIDITY_LEVEL_ASSIGN.txt' with ( fieldterminator = '|', rowterminator = '\n', CODEPAGE  = 'RAW' )  
GO
bulk insert tGrouper_CMG_EXCEPTION_BRANCH_ASSIGN from '<path>tGrouper_CMG_EXCEPTION_BRANCH_ASSIGN.txt' with ( fieldterminator = '|', rowterminator = '\n', CODEPAGE  = 'RAW' )  
GO
bulk insert tGrouper_CMG_FACTOR_INTERACTION_ELOS_RIW_2011 from '<path>tGrouper_CMG_FACTOR_INTERACTION_ELOS_RIW_2011.txt' with ( fieldterminator = '|', rowterminator = '\n', CODEPAGE  = 'RAW' )  
GO
bulk insert tGrouper_CMG_FLAGGED_INTERV_ASSIGN from '<path>tGrouper_CMG_FLAGGED_INTERV_ASSIGN.txt' with ( fieldterminator = '|', rowterminator = '\n', CODEPAGE  = 'RAW' )  
GO
bulk insert tGrouper_CMG_FLAGGED_INTERV_CATEGORY from '<path>tGrouper_CMG_FLAGGED_INTERV_CATEGORY.txt' with ( fieldterminator = '|', rowterminator = '\n', CODEPAGE  = 'RAW' )  
GO
bulk insert tGrouper_CMG_FLAGGED_INTERV_ELOS_RIW_2015 from '<path>tGrouper_CMG_FLAGGED_INTERV_ELOS_RIW_2015.txt' with ( fieldterminator = '|', rowterminator = '\n', CODEPAGE  = 'RAW' )  
GO
bulk insert tGrouper_CMG_FLAGGED_INTERV_EXCLUSION from '<path>tGrouper_CMG_FLAGGED_INTERV_EXCLUSION.txt' with ( fieldterminator = '|', rowterminator = '\n', CODEPAGE  = 'RAW' )  
GO
bulk insert tGrouper_CMG_FLAGGED_INTERV_GROUP from '<path>tGrouper_CMG_FLAGGED_INTERV_GROUP.txt' with ( fieldterminator = '|', rowterminator = '\n', CODEPAGE  = 'RAW' )  
GO
bulk insert tGrouper_CMG_FLAGGED_INTERV_STATUS from '<path>tGrouper_CMG_FLAGGED_INTERV_STATUS.txt' with ( fieldterminator = '|', rowterminator = '\n', CODEPAGE  = 'RAW' )  
GO
bulk insert tGrouper_CMG_FLAGGED_INTERV_STATUS_ASSIGN from '<path>tGrouper_CMG_FLAGGED_INTERV_STATUS_ASSIGN.txt' with ( fieldterminator = '|', rowterminator = '\n', CODEPAGE  = 'RAW' )  
GO
bulk insert tGrouper_CMG_INPATIENT_CACS_EXCLUSION from '<path>tGrouper_CMG_INPATIENT_CACS_EXCLUSION.txt' with ( fieldterminator = '|', rowterminator = '\n', CODEPAGE  = 'RAW' )  
GO
bulk insert tGrouper_CMG_INTERV_EVENT_CNT from '<path>tGrouper_CMG_INTERV_EVENT_CNT.txt' with ( fieldterminator = '|', rowterminator = '\n', CODEPAGE  = 'RAW' )  
GO
bulk insert tGrouper_CMG_INTERV_EVENT_ELOS_RIW from '<path>tGrouper_CMG_INTERV_EVENT_ELOS_RIW.txt' with ( fieldterminator = '|', rowterminator = '\n', CODEPAGE  = 'RAW' )  
GO
bulk insert tGrouper_CMG_INTERV_OOH_ASSIGN from '<path>tGrouper_CMG_INTERV_OOH_ASSIGN.txt' with ( fieldterminator = '|', rowterminator = '\n', CODEPAGE  = 'RAW' )  
GO
bulk insert tGrouper_CMG_INTERV_OOH_CATEGORY from '<path>tGrouper_CMG_INTERV_OOH_CATEGORY.txt' with ( fieldterminator = '|', rowterminator = '\n', CODEPAGE  = 'RAW' )  
GO
bulk insert tGrouper_CMG_INTERV_OOH_RIW from '<path>tGrouper_CMG_INTERV_OOH_RIW.txt' with ( fieldterminator = '|', rowterminator = '\n', CODEPAGE  = 'RAW' )  
GO
bulk insert tGrouper_CMG_IP_FACTOR_INTERACTION_BLOCK from '<path>tGrouper_CMG_IP_FACTOR_INTERACTION_BLOCK.txt' with ( fieldterminator = '|', rowterminator = '\n', CODEPAGE  = 'RAW' )  
GO
bulk insert tGrouper_CMG_IP_RI_LEVEL from '<path>tGrouper_CMG_IP_RI_LEVEL.txt' with ( fieldterminator = '|', rowterminator = '\n', CODEPAGE  = 'RAW' )  
GO
bulk insert tGrouper_CMG_IP_RIW_ATYPICAL from '<path>tGrouper_CMG_IP_RIW_ATYPICAL.txt' with ( fieldterminator = '|', rowterminator = '\n', CODEPAGE  = 'RAW' )  
GO
bulk insert tGrouper_CMG_IP_RIW_ATYPICAL_TYPE from '<path>tGrouper_CMG_IP_RIW_ATYPICAL_TYPE.txt' with ( fieldterminator = '|', rowterminator = '\n', CODEPAGE  = 'RAW' )  
GO
bulk insert tGrouper_CMG_LOS_GROUP from '<path>tGrouper_CMG_LOS_GROUP.txt' with ( fieldterminator = '|', rowterminator = '\n', CODEPAGE  = 'RAW' )  
GO
bulk insert tGrouper_CMG_LOS_PERCENTILE from '<path>tGrouper_CMG_LOS_PERCENTILE.txt' with ( fieldterminator = '|', rowterminator = '\n', CODEPAGE  = 'RAW' )  
GO
bulk insert tGrouper_CMG_MCC_ASSIGN from '<path>tGrouper_CMG_MCC_ASSIGN.txt' with ( fieldterminator = '|', rowterminator = '\n', CODEPAGE  = 'RAW' )  
GO
bulk insert tGrouper_CMG_MCC_INTERV from '<path>tGrouper_CMG_MCC_INTERV.txt' with ( fieldterminator = '|', rowterminator = '\n', CODEPAGE  = 'RAW' )  
GO
bulk insert tGrouper_CMG_MCC_INTERV_PARTITION from '<path>tGrouper_CMG_MCC_INTERV_PARTITION.txt' with ( fieldterminator = '|', rowterminator = '\n', CODEPAGE  = 'RAW' )  
GO
bulk insert tGrouper_CMG_MCC_LOGIC from '<path>tGrouper_CMG_MCC_LOGIC.txt' with ( fieldterminator = '|', rowterminator = '\n', CODEPAGE  = 'RAW' )  
GO
bulk insert tGrouper_CMG_MCC_PARTITION from '<path>tGrouper_CMG_MCC_PARTITION.txt' with ( fieldterminator = '|', rowterminator = '\n', CODEPAGE  = 'RAW' )  
GO
bulk insert tGrouper_CMG_NEWBORN_BRANCH_ASSIGN from '<path>tGrouper_CMG_NEWBORN_BRANCH_ASSIGN.txt' with ( fieldterminator = '|', rowterminator = '\n', CODEPAGE  = 'RAW' )  
GO
bulk insert tGrouper_CMG_PERCENTILE from '<path>tGrouper_CMG_PERCENTILE.txt' with ( fieldterminator = '|', rowterminator = '\n', CODEPAGE  = 'RAW' )  
GO
bulk insert tGrouper_HIG from '<path>tGrouper_HIG.txt' with ( fieldterminator = '|', rowterminator = '\n', CODEPAGE  = 'RAW' )  
GO
bulk insert tGrouper_HIG_Age_Category from '<path>tGrouper_HIG_Age_Category.txt' with ( fieldterminator = '|', rowterminator = '\n', CODEPAGE  = 'RAW' )  
GO
bulk insert tGrouper_HIG_Age_ELOS_Weight from '<path>tGrouper_HIG_Age_ELOS_Weight.txt' with ( fieldterminator = '|', rowterminator = '\n', CODEPAGE  = 'RAW' )  
GO
bulk insert tGrouper_HIG_Atypical from '<path>tGrouper_HIG_Atypical.txt' with ( fieldterminator = '|', rowterminator = '\n', CODEPAGE  = 'RAW' )  
GO
bulk insert tGrouper_HIG_Atypical_Percentile from '<path>tGrouper_HIG_Atypical_Percentile.txt' with ( fieldterminator = '|', rowterminator = '\n', CODEPAGE  = 'RAW' )  
GO
bulk insert tGrouper_HIG_Base_Elos_Weight from '<path>tGrouper_HIG_Base_Elos_Weight.txt' with ( fieldterminator = '|', rowterminator = '\n', CODEPAGE  = 'RAW' )  
GO
bulk insert tGrouper_HIG_Branch_Logic from '<path>tGrouper_HIG_Branch_Logic.txt' with ( fieldterminator = '|', rowterminator = '\n', CODEPAGE  = 'RAW' )  
GO
bulk insert tGrouper_HIG_Event_Elos_Weight from '<path>tGrouper_HIG_Event_Elos_Weight.txt' with ( fieldterminator = '|', rowterminator = '\n', CODEPAGE  = 'RAW' )  
GO
bulk insert tGrouper_HIG_FI_Elos_Weight from '<path>tGrouper_HIG_FI_Elos_Weight.txt' with ( fieldterminator = '|', rowterminator = '\n', CODEPAGE  = 'RAW' )  
GO
bulk insert tGrouper_HIG_Logic from '<path>tGrouper_HIG_Logic.txt' with ( fieldterminator = '|', rowterminator = '\n', CODEPAGE  = 'RAW' )  
GO
bulk insert tGrouper_HIG_OOH_Assign from '<path>tGrouper_HIG_OOH_Assign.txt' with ( fieldterminator = '|', rowterminator = '\n', CODEPAGE  = 'RAW' )  
GO
bulk insert tGrouper_HIG_OOH_Factor from '<path>tGrouper_HIG_OOH_Factor.txt' with ( fieldterminator = '|', rowterminator = '\n', CODEPAGE  = 'RAW' )  
GO
bulk insert tGrouper_HIG_Split from '<path>tGrouper_HIG_Split.txt' with ( fieldterminator = '|', rowterminator = '\n', CODEPAGE  = 'RAW' )  
GO
bulk insert tGrouper_HIG_Split_Diag from '<path>tGrouper_HIG_Split_Diag.txt' with ( fieldterminator = '|', rowterminator = '\n', CODEPAGE  = 'RAW' )  
GO
bulk insert tGrouper_HIG_Split_Interv from '<path>tGrouper_HIG_Split_Interv.txt' with ( fieldterminator = '|', rowterminator = '\n', CODEPAGE  = 'RAW' )  
GO
bulk insert tGrouper_HIG_Split_Type from '<path>tGrouper_HIG_Split_Type.txt' with ( fieldterminator = '|', rowterminator = '\n', CODEPAGE  = 'RAW' )  
GO
bulk insert tGrouper_PROV_CACS_BASE_RIW from '<path>tGrouper_PROV_CACS_BASE_RIW.txt' with ( fieldterminator = '|', rowterminator = '\n', CODEPAGE  = 'RAW' )  
GO
bulk insert tGrouper_PROV_CACS_INTERACTION_RIW from '<path>tGrouper_PROV_CACS_INTERACTION_RIW.txt' with ( fieldterminator = '|', rowterminator = '\n', CODEPAGE  = 'RAW' )  
GO
bulk insert tGrouper_PROV_CACS_INV_TECH_RIW from '<path>tGrouper_PROV_CACS_INV_TECH_RIW.txt' with ( fieldterminator = '|', rowterminator = '\n', CODEPAGE  = 'RAW' )  
GO
update tGrouper_CACS_Ambulatory_Diag_Assign set gender_code = '' where gender_code is null
GO
update tGrouper_CACS_Interaction_Block set variable3 = '' where variable3 is null
GO
update tGrouper_CACS_Inv_Tech_Category_Assign set status_attribute_code = '' where status_attribute_code is null
GO
update tGrouper_CACS_Inv_Tech_Category_Assign set location_attribute_code = '' where location_attribute_code is null
GO
update tGrouper_CACS_Inv_Tech_Category_Assign set extent_attribute_code = '' where extent_attribute_code is null
GO
update tGrouper_CACS_Split_Interv set status_attribute_code = '' where status_attribute_code is null
GO
update tGrouper_CACS_Split_Interv set location_attribute_code = '' where location_attribute_code is null
GO
update tGrouper_CACS_Split_Interv set extent_attribute_code = '' where extent_attribute_code is null
GO
update tGrouper_CACS_ED_Diag_Assign set gender_code = '' where gender_code is null
GO
update tGrouper_CACS_ED_Interv_Assign set status_attribute_code = '' where status_attribute_code is null
GO
update tGrouper_CACS_ED_Interv_Assign set location_attribute_code = '' where location_attribute_code is null
GO
update tGrouper_CACS_ED_Interv_Assign set extent_attribute_code = '' where extent_attribute_code is null
GO
update tGrouper_CMG_CMG_SPLIT_INTERV set status_attribute_code = '' where status_attribute_code is null
GO
update tGrouper_CMG_CMG_SPLIT_INTERV set location_attribute_code = '' where location_attribute_code is null
GO
update tGrouper_CMG_CMG_SPLIT_INTERV set extent_attribute_code = '' where extent_attribute_code is null
GO
update tGrouper_CMG_FLAGGED_INTERV_ASSIGN set status_attribute_code = '' where status_attribute_code is null
GO
update tGrouper_CMG_FLAGGED_INTERV_ASSIGN set location_attribute_code = '' where location_attribute_code is null
GO
update tGrouper_CMG_FLAGGED_INTERV_ASSIGN set extent_attribute_code = '' where extent_attribute_code is null
GO
update tGrouper_CMG_IP_FACTOR_INTERACTION_BLOCK set variable3 = '' where variable3 is null
GO
update tGrouper_CMG_MCC_ASSIGN set gender_code = '' where gender_code is null
GO
update tGrouper_CMG_MCC_INTERV set status_attribute_code = '' where status_attribute_code is null
GO
update tGrouper_CMG_MCC_INTERV set location_attribute_code = '' where location_attribute_code is null
GO
update tGrouper_CMG_MCC_INTERV set extent_attribute_code = '' where extent_attribute_code is null
GO
update tGrouper_CMG_MCC_INTERV_PARTITION set status_attribute_code = '' where status_attribute_code is null
GO
update tGrouper_CMG_MCC_INTERV_PARTITION set location_attribute_code = '' where location_attribute_code is null
GO
update tGrouper_CMG_MCC_INTERV_PARTITION set extent_attribute_code = '' where extent_attribute_code is null
GO

insert into [dbo].[tGrouper_CMG_MCC_PARTITION_Combination](MCC, Partition, CMG)
select distinct a.[mcc_code], a.[mcc_partition_code], a.[cmg_code]
from [dbo].[tGrouper_CMG_CMG_LOGIC] a
	left join [dbo].[tGrouper_CMG_MCC_PARTITION_Combination] b
	on a.[mcc_code] = b.mcc and a.[mcc_partition_code] = b.[partition] and a.[cmg_code] = b.cmg
where b.mcc is null
GO
Insert into dbo.CMG_LU_MCCDescription(DisplayCode, UserDescription, CMGYear)
select mcc_code, mcc_long_e_desc, methodology_year
from dbo.tGrouper_CMG_MCC_LOGIC
where methodology_year >= 2013 and methodology_year not in (select distinct CMGYear from CMG_LU_MCCDescription)
GO
--end grouper data
--end build 5
--CCRS CA
if exists (select * from information_schema.tables where table_name = 'zz2018CCRS_CA')
drop table zz2018CCRS_CA
GO
create table zz2018CCRS_CA(
	UserDescription	varchar(500) COLLATE Latin1_General_CI_AS
)
GO
--bulk insert zz2018CCRS_CA  from '<path>2018CCRS_CA.txt' with ( fieldterminator = '|', rowterminator = '\n', CODEPAGE  = 'RAW' ) 
bulk insert zz2018CCRS_CA  from '<path>2018CCRS_CA.txt' with ( rowterminator = '\n', CODEPAGE  = 'RAW' ) 
GO
update [dbo].zz2018CCRS_CA set UserDescription = ltrim(rtrim(UserDescription)) 
GO
if exists (select * from information_schema.tables where table_name = 'zz2018CCRS_CA_F')
drop table zz2018CCRS_CA_F
GO
select code = identity(int, 1, 1), CIHIValue = left(UserDescription, charindex(' ', UserDescription, 1) - 1), UserDescription = substring(UserDescription, charindex(' ', UserDescription, 1) + 1, 500)
into zz2018CCRS_CA_F
from zz2018CCRS_CA
GO
Update zz2018CCRS_CA_F set CIHIValue = replace(CIHIValue, '?', '') where charindex('?', CIHIVAlue, 1) > 0
GO

update zz2018CCRS_CA_F
set UserDescription = replace(UserDescription, '  ', ' ')
where charindex('  ', UserDescription, 1) > 0
GO
update a
set ValidTo = '2018/03/31'
--Select z.*, a.*
from [dbo].[ICD_DiagFileMDS] a 
	left join zz2018CCRS_CA_F z on a.CIHIValue = z.CIHIValue
where z.cihivalue is null and a.validto > '2018/04/01'
GO
if exists (select * from information_schema.tables where table_name = 'zz2018CCRS_CA_N')
drop table zz2018CCRS_CA_N
GO
Select [ID] = identity(int, 1, 1), x.* into zz2018CCRS_CA_N 
from zz2018CCRS_CA_F z
	left join [dbo].[ICD_DiagFileMDS] a on a.CIHIValue = z.CIHIValue
	inner join ICD_DiagFile10 x on z.CIHIValue = x.CIHIValue and x.code < 100000
where a.cihivalue is null
GO
declare @MaxID int
set @MaxID = (select max(code) from [dbo].[ICD_DiagFileMDS])
insert into [dbo].[ICD_DiagFileMDS](Code, DisplayCode, UserDescription, DefaultDescription, ShortDescription, CIHIValue, ValidFrom, ValidTo, DiagnosisGender, AgeLow, AgeHigh, InterfaceIn, HospitalID, LUText)
select @MaxID + [ID], DisplayCode, UserDescription, DefaultDescription, ShortDescription, CIHIValue, ValidFrom, ValidTo, DiagnosisGender, AgeLow, AgeHigh, InterfaceIn, HospitalID, LUText
from zz2018CCRS_CA_N
GO
if exists (select * from information_schema.tables where table_name = 'zz2018CCRS_CA')
drop table zz2018CCRS_CA
GO
if exists (select * from information_schema.tables where table_name = 'zz2018CCRS_CA_F')
drop table zz2018CCRS_CA_F
GO
if exists (select * from information_schema.tables where table_name = 'zz2018CCRS_CA_N')
drop table zz2018CCRS_CA_N
GO
--end build 9
