--- ***** 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_10CA2022_V1_0.txt' with( FIELDTERMINATOR = '|', ROWTERMINATOR ='\n', CODEPAGE = 'RAW' )
--bulk insert zzICD10_Diag  from 'f:\2022\ICD_Code_Eng_Desc_10CA2022_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_CCI2022_V1_0.txt' with( FIELDTERMINATOR = '|', ROWTERMINATOR ='\n', CODEPAGE = 'RAW' )
--bulk insert zzICD10_CCI  from 'f:\2022\CCI_Code_Eng_Desc_CCI2022_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 < '2022/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 < '2022/04/01'
GO
update ICD_diagFile10
set ValidTo = '2022/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 > '2022/03/31' and icd.code < 500000
GO
update ICD_ProcFile10
set ValidTo = '2022/03/31'
-- select icd.*
from zzICD10_CCI z
	right join ICD_ProcFile10 Icd on z.CIHIValue = ICD.CIHIValue
where icd.validto > '2022/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, '2022/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, '2022/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_10CA2022_V1_0.txt' with( FIELDTERMINATOR = '|', ROWTERMINATOR ='\n')
--bulk insert zzICD10CA_Validation  from 'f:\2022\ICD_Code_Validation_10CA2022_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_CCI2022_V1_0.txt' with( FIELDTERMINATOR = '|', ROWTERMINATOR ='\n')
--bulk insert zzICD10_CC1_Validation  from 'f:\2022\CCI_Code_Validation_CCI2022_V1_0.txt' with( FIELDTERMINATOR = '|', ROWTERMINATOR ='\n')
GO
--Checking Differenct
--select a.*, b.*
--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.CIHIFlag<> b.CIHIFlag or a.Gender <> b.Gender or  a.AgeLow <>  b.AgeLow or a.AgeHigh <> b.AgeHigh
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_CCI2022_V1_0.txt' with( FIELDTERMINATOR = '|', ROWTERMINATOR ='\n', CODEPAGE = 'RAW' )
--bulk insert zzICD10CCI_Extent  from 'f:\2022\CCI_Extent_Val_CCI2022_V1_0.txt' with( FIELDTERMINATOR = '|', ROWTERMINATOR ='\n', CODEPAGE = 'RAW' )
GO
----Checking Attributes
--select a.*, b.*
--from 
--(select * from zzICD10CCI_Extent where Sector = '1') a
--full join
--(select * from zzICD10CCI_Extent where Sector = 'a') b
--on a.cihivalue = b.cihivalue and a.ExtentAttribute = b.ExtentAttribute and a.LinkID = b.LinkID
--where a.cihivalue is null or b.cihivalue is null
Update LU_IntervAttExtent
set validto = '2022/03/31'
-- select l.*
from 
    (select isnull(a.cihivalue, b.CIHIValue) as CIHIValue, isnull(a.ExtentAttribute, b.ExtentAttribute) as ExtentAttribute, isnull(a.LinkID, b.LinkID) as LinkID,
        Sector = (case when a.Sector is not null and b.Sector is not null then '3' else isnull(a.Sector, b.Sector) end)
        from        
        (select * from  zzICD10CCI_Extent where sector = '1') a 
        full join
        (select * from  zzICD10CCI_Extent where sector = 'A') b on a.cihivalue = b.cihivalue and a.ExtentAttribute = b.ExtentAttribute and a.LinkID = b.LinkID
    ) 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 > '2022/03/31'
GO
Update LU_IntervAttExtent
set validto = '2022/03/31'
--select l.*
from dbo.LU_IntervAttExtent l
	inner join ICD_ProcFile10 icd on icd.code = l.associationid
	left join
        (select isnull(a.cihivalue, b.CIHIValue) as CIHIValue, isnull(a.ExtentAttribute, b.ExtentAttribute) as ExtentAttribute, isnull(a.LinkID, b.LinkID) as LinkID,
            Sector = (case when a.Sector is not null and b.Sector is not null then '3' else isnull(a.Sector, b.Sector) end)
         from        
            (select * from  zzICD10CCI_Extent where sector = '1') a 
            full join
            (select * from  zzICD10CCI_Extent where sector = 'A') b on a.cihivalue = b.cihivalue and a.ExtentAttribute = b.ExtentAttribute and a.LinkID = b.LinkID
        ) v 
     on v.CIHIValue = icd.CIHIValue and v.LinkID = l.LinkID and v.ExtentAttribute = l.CIHIValue and v.Sector = l.Sector
where v.CIHIValue is null and l.validto > '2022/03/31'
GO
Update LU_IntervAttExtent
set validto = '2099/03/31' 
-- select l.*
from 
    (select isnull(a.cihivalue, b.CIHIValue) as CIHIValue, isnull(a.ExtentAttribute, b.ExtentAttribute) as ExtentAttribute, isnull(a.LinkID, b.LinkID) as LinkID,
        Sector = (case when a.Sector is not null and b.Sector is not null then '3' else isnull(a.Sector, b.Sector) end)
        from        
        (select * from  zzICD10CCI_Extent where sector = '1') a 
        full join
        (select * from  zzICD10CCI_Extent where sector = 'A') b on a.cihivalue = b.cihivalue and a.ExtentAttribute = b.ExtentAttribute and a.LinkID = b.LinkID
    ) 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, Sector, max(ValidTo) as maxDate from LU_IntervAttExtent group by CIHIValue, AssociationID, LinkID, Sector) d
        on l.CIHIValue = d.CIHIValue and l.AssociationID = d.AssociationID and l.LinkID = d.LinkID and l.Validto = d.maxdate and l.Sector = d.Sector
where v.LinkID <> '' and l.validto <= '2022/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_CCI2022_V1_0.txt' with( FIELDTERMINATOR = '|', ROWTERMINATOR ='\n', CODEPAGE = 'RAW' )
--bulk insert zzICD10CCI_Extent_Desc  from 'f:\2022\CCI_Extent_Eng_CCI2022_V1_0.txt' with( FIELDTERMINATOR = '|', ROWTERMINATOR ='\n', CODEPAGE = 'RAW' )
GO
Update LU_IntervAttExtent
set UserDescription = rtrim(d.UserDescription), DefaultDescription = rtrim(d.UserDescription)
-- select d.*, l.*
from 
    (select isnull(a.cihivalue, b.CIHIValue) as CIHIValue, isnull(a.ExtentAttribute, b.ExtentAttribute) as ExtentAttribute, isnull(a.LinkID, b.LinkID) as LinkID,
        Sector = (case when a.Sector is not null and b.Sector is not null then '3' else isnull(a.Sector, b.Sector) end)
        from        
        (select * from  zzICD10CCI_Extent where sector = '1') a 
        full join
        (select * from  zzICD10CCI_Extent where sector = 'A') b on a.cihivalue = b.cihivalue and a.ExtentAttribute = b.ExtentAttribute and a.LinkID = b.LinkID
    ) 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 and v.Sector = l.Sector
    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 > '2022/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,
    v.Sector
into zzICD10CCI_Extent_New
from ICD_ProcFile10 icd  
	inner join
    (select isnull(a.cihivalue, b.CIHIValue) as CIHIValue, isnull(a.ExtentAttribute, b.ExtentAttribute) as ExtentAttribute, isnull(a.LinkID, b.LinkID) as LinkID,
        Sector = (case when a.Sector is not null and b.Sector is not null then '3' else isnull(a.Sector, b.Sector) end)
        from        
        (select * from  zzICD10CCI_Extent where sector = '1') a 
        full join
        (select * from  zzICD10CCI_Extent where sector = 'A') b on a.cihivalue = b.cihivalue and a.ExtentAttribute = b.ExtentAttribute and a.LinkID = b.LinkID
    ) 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 and v.Sector = l.Sector
where l.CIHIValue is null and v.LinkID <> ''
order by v.CIHIValue, v.ExtentAttribute
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 = '2022/04/01', ValidTo = '2099/03/31', 
    AssociationID = icd.Code, LinkID = n.LinkID, 
    InterfaceIn = ltrim(rtrim(n.ExtentAttribute)), n.Sector, HospitalID = 0 --Sector = '3'
    --,icd.CIHIValue
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
order by n.Code
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_CCI2022_V1_0.txt' with( FIELDTERMINATOR = '|', ROWTERMINATOR ='\n', CODEPAGE = 'RAW' )
--bulk insert zzICD10CCI_Location  from 'f:\2022\CCI_Location_Val_CCI2022_V1_0.txt' with( FIELDTERMINATOR = '|', ROWTERMINATOR ='\n', CODEPAGE = 'RAW' )
GO
----Check Attribute Location
--select a.*, b.*
--from 
--(select * from zzICD10CCI_Location where Sector = '1') a
--full join
--(select * from zzICD10CCI_Location where Sector = 'a') b
--on a.cihivalue = b.cihivalue and a.LocationAttribute = b.LocationAttribute and a.LinkID = b.LinkID
--where a.cihivalue is null or b.cihivalue is null

Update LU_IntervAttLocation
set validto = '2022/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 > '2022/03/31'
GO
Update LU_IntervAttLocation
set validto = '2022/03/31'
--select l.*
from dbo.LU_IntervAttLocation l
	inner join ICD_ProcFile10 icd on icd.code = l.associationid
	left join
    (select isnull(a.cihivalue, b.CIHIValue) as CIHIValue, isnull(a.LocationAttribute, b.LocationAttribute) as LocationAttribute, isnull(a.LinkID, b.LinkID) as LinkID,
        Sector = (case when a.Sector is not null and b.Sector is not null then '3' else isnull(a.Sector, b.Sector) end)
        from        
        (select * from  zzICD10CCI_Location where sector = '1') a 
        full join
        (select * from  zzICD10CCI_Location where sector = 'A') b on a.cihivalue = b.cihivalue and a.LocationAttribute = b.LocationAttribute and a.LinkID = b.LinkID
    ) v 
     on v.CIHIValue = icd.CIHIValue and v.LinkID = l.LinkID and v.LocationAttribute = l.CIHIValue and v.Sector = l.Sector
where v.CIHIValue is null and l.validto > '2022/03/31'
GO

Update LU_IntervAttLocation
set validto = '2099/03/31'
-- select l.*
from 
    (select isnull(a.cihivalue, b.CIHIValue) as CIHIValue, isnull(a.LocationAttribute, b.LocationAttribute) as LocationAttribute, isnull(a.LinkID, b.LinkID) as LinkID,
        Sector = (case when a.Sector is not null and b.Sector is not null then '3' else isnull(a.Sector, b.Sector) end)
        from        
        (select * from  zzICD10CCI_Location where sector = '1') a 
        full join
        (select * from  zzICD10CCI_Location where sector = 'A') b on a.cihivalue = b.cihivalue and a.LocationAttribute = b.LocationAttribute and a.LinkID = b.LinkID
    ) 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, Sector, max(ValidTo) as maxDate from LU_IntervAttLocation group by CIHIValue, AssociationID, LinkID, Sector) d
        on l.CIHIValue = d.CIHIValue and l.AssociationID = d.AssociationID and l.LinkID = d.LinkID and l.Validto = d.maxdate and l.Sector = d.Sector
where v.LinkID <> '' and l.validto <= '2022/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_CCI2022_V1_0.txt' with( FIELDTERMINATOR = '|', ROWTERMINATOR ='\n', CODEPAGE = 'RAW' )
--bulk insert zzICD10CCI_Location_Desc  from 'f:\2022\CCI_Location_Eng_CCI2022_V1_0.txt' with( FIELDTERMINATOR = '|', ROWTERMINATOR ='\n', CODEPAGE = 'RAW' )
GO
Update LU_IntervAttLocation
set UserDescription = rtrim(d.UserDescription), DefaultDescription = rtrim(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 > '2022/04/01'
GO

--2022 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,
    v.Sector
into zzICD10CCI_Location_New
from ICD_ProcFile10 icd  
	inner join
    (select isnull(a.cihivalue, b.CIHIValue) as CIHIValue, isnull(a.LocationAttribute, b.LocationAttribute) as LocationAttribute, isnull(a.LinkID, b.LinkID) as LinkID,
        Sector = (case when a.Sector is not null and b.Sector is not null then '3' else isnull(a.Sector, b.Sector) end)
        from        
        (select * from  zzICD10CCI_Location where sector = '1') a 
        full join
        (select * from  zzICD10CCI_Location where sector = 'A') b on a.cihivalue = b.cihivalue and a.LocationAttribute = b.LocationAttribute and a.LinkID = b.LinkID
    ) 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 and v.Sector = l.Sector
where l.CIHIValue is null and v.LinkID <> ''
order by v.CIHIValue, v.LocationAttribute
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 = '2022/04/01', ValidTo = '2099/03/31', 
    AssociationID = icd.Code, LinkID = n.LinkID, 
    InterfaceIn = ltrim(rtrim(n.LocationAttribute)), n.Sector, HospitalID = 0   --Sector = '3'
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
order  by n.Code
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_CCI2022_V1_0.txt' with( FIELDTERMINATOR = '|', ROWTERMINATOR ='\n', CODEPAGE = 'RAW' )
--bulk insert zzICD10CCI_Status  from 'f:\2022\CCI_Status_Val_CCI2022_V1_0.txt' with( FIELDTERMINATOR = '|', ROWTERMINATOR ='\n', CODEPAGE = 'RAW' )
GO
----Check Attribute Status
--select a.*, b.*
--from 
--(select * from zzICD10CCI_Status where Sector = '1') a
--full join
--(select * from zzICD10CCI_Status where Sector = 'a') b
--on a.cihivalue = b.cihivalue and a.StatusAttribute = b.StatusAttribute and a.LinkID = b.LinkID
--where a.cihivalue is null or b.cihivalue is null

Update LU_IntervAttStatus
set validto = '2022/03/31'
--select v.*, l.*
from 
    (select isnull(a.cihivalue, b.CIHIValue) as CIHIValue, isnull(a.StatusAttribute, b.StatusAttribute) as StatusAttribute, isnull(a.LinkID, b.LinkID) as LinkID,
        Sector = (case when a.Sector is not null and b.Sector is not null then '3' else isnull(a.Sector, b.Sector) end)
     from        
        (select * from  zzICD10CCI_Status where sector = '1') a 
        full join
        (select * from  zzICD10CCI_Status where sector = 'A') b on a.cihivalue = b.cihivalue and a.StatusAttribute = b.StatusAttribute and a.LinkID = b.LinkID
    ) 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 > '2022/03/31'
GO
Update LU_IntervAttStatus
set validto = '2022/03/31'
--select l.*
from dbo.LU_IntervAttStatus l
	inner join ICD_ProcFile10 icd on icd.code = l.associationid
	left join
    (select isnull(a.cihivalue, b.CIHIValue) as CIHIValue, isnull(a.StatusAttribute, b.StatusAttribute) as StatusAttribute, isnull(a.LinkID, b.LinkID) as LinkID,
        Sector = (case when a.Sector is not null and b.Sector is not null then '3' else isnull(a.Sector, b.Sector) end)
     from        
        (select * from  zzICD10CCI_Status where sector = '1') a 
        full join
        (select * from  zzICD10CCI_Status where sector = 'A') b on a.cihivalue = b.cihivalue and a.StatusAttribute = b.StatusAttribute and a.LinkID = b.LinkID
    ) v 
     on v.CIHIValue = icd.CIHIValue and v.LinkID = l.LinkID and v.StatusAttribute = l.CIHIValue and v.Sector = l.Sector
where v.CIHIValue is null and l.validto > '2022/03/31'
GO

Update LU_IntervAttStatus
set validto = '2099/03/31' 
-- select l.*
from 
    (select isnull(a.cihivalue, b.CIHIValue) as CIHIValue, isnull(a.StatusAttribute, b.StatusAttribute) as StatusAttribute, isnull(a.LinkID, b.LinkID) as LinkID,
        Sector = (case when a.Sector is not null and b.Sector is not null then '3' else isnull(a.Sector, b.Sector) end)
     from        
        (select * from  zzICD10CCI_Status where sector = '1') a 
        full join
        (select * from  zzICD10CCI_Status where sector = 'A') b on a.cihivalue = b.cihivalue and a.StatusAttribute = b.StatusAttribute and a.LinkID = b.LinkID
    ) 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 and v.Sector = l.Sector
    inner join (select CIHIValue, AssociationID, LinkID, Sector, max(ValidTo) as maxDate from LU_IntervAttStatus group by CIHIValue, AssociationID, LinkID, Sector) d
        on l.CIHIValue = d.CIHIValue and l.AssociationID = d.AssociationID and l.LinkID = d.LinkID and l.Validto = d.maxdate and l.Sector = d.Sector 
where v.LinkID <> '' and l.validto <= '2022/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_CCI2022_V1_0.txt' with( FIELDTERMINATOR = '|', ROWTERMINATOR ='\n', CODEPAGE = 'RAW' )
--bulk insert zzICD10CCI_Status_Desc  from 'f:\2022\CCI_Status_Eng_CCI2022_V1_0.txt' with( FIELDTERMINATOR = '|', ROWTERMINATOR ='\n', CODEPAGE = 'RAW' )
GO
Update LU_IntervAttStatus
set UserDescription = rtrim(d.UserDescription), DefaultDescription = rtrim(d.UserDescription)
-- select d.*, l.*
from 
    (select isnull(a.cihivalue, b.CIHIValue) as CIHIValue, isnull(a.StatusAttribute, b.StatusAttribute) as StatusAttribute, isnull(a.LinkID, b.LinkID) as LinkID,
        Sector = (case when a.Sector is not null and b.Sector is not null then '3' else isnull(a.Sector, b.Sector) end)
     from        
        (select * from  zzICD10CCI_Status where sector = '1') a 
        full join
        (select * from  zzICD10CCI_Status where sector = 'A') b on a.cihivalue = b.cihivalue and a.StatusAttribute = b.StatusAttribute and a.LinkID = b.LinkID
    ) 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 and v.Sector = l.Sector
    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 > '2022/04/01'
GO

--2022 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,
    v.Sector
into zzICD10CCI_Status_New
from ICD_ProcFile10 icd  
	inner join
    (select isnull(a.cihivalue, b.CIHIValue) as CIHIValue, isnull(a.StatusAttribute, b.StatusAttribute) as StatusAttribute, isnull(a.LinkID, b.LinkID) as LinkID,
        Sector = (case when a.Sector is not null and b.Sector is not null then '3' else isnull(a.Sector, b.Sector) end)
     from        
        (select * from  zzICD10CCI_Status where sector = '1') a 
        full join
        (select * from  zzICD10CCI_Status where sector = 'A') b on a.cihivalue = b.cihivalue and a.StatusAttribute = b.StatusAttribute and a.LinkID = b.LinkID
    ) 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 and v.Sector = l.Sector
where l.CIHIValue is null and v.LinkID <> ''
order by v.CIHIValue, v.StatusAttribute
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 = '2022/04/01', ValidTo = '2099/03/31', 
    AssociationID = icd.Code, LinkID = n.LinkID, 
    InterfaceIn = ltrim(rtrim(n.StatusAttribute)), n.Sector, 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_CCI2022_V1_0.txt' with( FIELDTERMINATOR = '|', ROWTERMINATOR ='\n')
--bulk insert zzICD10CCI_Cat  from 'f:\2022\CCI_Blk_Eng_Desc_CCI2022_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_CCI2022_V1_0.txt' with( FIELDTERMINATOR = '|', ROWTERMINATOR ='\n')
--bulk insert zzICD10CCI_Class  from 'f:\2022\CCI_Rubric_Eng_Desc_CCI2022_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_10CA2022_V1_0.txt' with( FIELDTERMINATOR = '|', ROWTERMINATOR ='\n')
--bulk insert zzICD10CA_Cat  from 'f:\2022\ICD_Cat1_Eng_Desc_10CA2022_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
--2022 Grouper Data
Delete tGrouper_CMG_LOGIC_TYPE where [logic_type_code] = '0'
GO
Insert into tGrouper_CMG_LOGIC_TYPE([logic_type_code], [logic_type_e_desc], [logic_type_f_desc])
values('0', 'Transplant Logic', 'TBD')
GO
if exists (select * from INFORMATION_SCHEMA.tables where TABLE_NAME = 'tGrouper_CACS_ED_CCI_INTERV') 
	drop table tGrouper_CACS_ED_CCI_INTERV
GO
CREATE TABLE [dbo].[tGrouper_CACS_ED_CCI_INTERV](
	[fiscal_year] int not NULL,
	[ed_cci_interv_code] [varchar](255) not NULL,
	[ed_cci_interv_type_code] [varchar](255) NULL,
	CONSTRAINT PK_tGrouper_CACS_ED_CCI_INTERV PRIMARY KEY CLUSTERED ([fiscal_year], [ed_cci_interv_code]) 
)
GO
if exists (select * from INFORMATION_SCHEMA.tables where TABLE_NAME = 'tGrouper_CACS_ED_CCI_INTERV_TYPE') 
	drop table tGrouper_CACS_ED_CCI_INTERV_TYPE
GO
CREATE TABLE [dbo].[tGrouper_CACS_ED_CCI_INTERV_TYPE](
	[ed_cci_interv_type_code] [varchar](255) not NULL,
	[ed_cci_interv_type_e_desc] [varchar](255) NULL,
	[ed_cci_interv_type_f_desc] [varchar](255) NULL,
	CONSTRAINT PK_tGrouper_CACS_ED_CCI_INTERV_TYPE PRIMARY KEY CLUSTERED ([ed_cci_interv_type_code]) 
)
GO
if exists (select * from INFORMATION_SCHEMA.tables where TABLE_NAME = 'tGrouper_CMG_CMG_CARE_LEVEL') 
	drop table tGrouper_CMG_CMG_CARE_LEVEL
GO
CREATE TABLE [dbo].[tGrouper_CMG_CMG_CARE_LEVEL](
	[cmg_care_level_code] [varchar](255) not NULL,
	[cmg_care_level_e_desc] [varchar](255) not NULL,
	[cmg_care_level_f_desc] [varchar](255) NULL,
	CONSTRAINT PK_tGrouper_CMG_CMG_CARE_LEVEL PRIMARY KEY CLUSTERED ([cmg_care_level_code]) 
)
GO
if exists (select * from INFORMATION_SCHEMA.tables where TABLE_NAME = 'tGrouper_CMG_CMG_PROVIDER_SERV') 
	drop table tGrouper_CMG_CMG_PROVIDER_SERV
GO
CREATE TABLE [dbo].[tGrouper_CMG_CMG_PROVIDER_SERV](
	[cmg_provider_serv_code] [varchar](255) not NULL,
	[cmg_provider_serv_e_desc] [varchar](255) not NULL,
	[cmg_provider_serv_f_desc] [varchar](255) NULL,
	[cmg_provider_serv_group_code] [varchar](255) NULL,
	CONSTRAINT PK_tGrouper_CMG_CMG_PROVIDER_SERV PRIMARY KEY CLUSTERED ([cmg_provider_serv_code]) 
)
GO
if exists (select * from INFORMATION_SCHEMA.tables where TABLE_NAME = 'tGrouper_CMG_CMG_PROVIDER_SERV_GROUP') 
	drop table tGrouper_CMG_CMG_PROVIDER_SERV_GROUP
GO
CREATE TABLE [dbo].[tGrouper_CMG_CMG_PROVIDER_SERV_GROUP](
	[cmg_provider_serv_group_code] [varchar](255) not NULL,
	[cmg_provider_serv_group_e_desc] [varchar](255) not NULL,
	[cmg_provider_serv_group_f_desc] [varchar](255) NULL
	CONSTRAINT PK_tGrouper_CMG_CMG_PROVIDER_SERV_GROUP PRIMARY KEY CLUSTERED ([cmg_provider_serv_group_code]) 
)
GO
if exists (select * from INFORMATION_SCHEMA.tables where TABLE_NAME = 'tGrouper_CMG_HL_CMG') 
	drop table tGrouper_CMG_HL_CMG
GO
CREATE TABLE [dbo].[tGrouper_CMG_HL_CMG](
	[methodology_year] [int] not NULL,
	[hl_cmg_code] [varchar](255) not NULL,
	[hl_cmg_long_e_desc] [varchar](255) NULL,
	[hl_cmg_long_f_desc] [varchar](255) NULL,
	[hl_cmg_short_e_desc] [varchar](255) NULL,
	[hl_cmg_short_f_desc] [varchar](255) NULL,
	CONSTRAINT PK_tGrouper_CMG_HL_CMG PRIMARY KEY CLUSTERED ([methodology_year], [hl_cmg_code]) 
)
GO
if exists (select * from INFORMATION_SCHEMA.tables where TABLE_NAME = 'tGrouper_CMG_HL_CMG_ASSIGN') 
	drop table tGrouper_CMG_HL_CMG_ASSIGN
GO
CREATE TABLE [dbo].[tGrouper_CMG_HL_CMG_ASSIGN](
	[methodology_year] [int] not NULL,
	[cmg_code] [varchar](255) not NULL,
	[hl_cmg_code] [varchar](255) NULL,
	[cmg_provider_serv_code] [varchar](255) NULL,
	[cmg_care_level_code] [varchar](255) NULL,
	CONSTRAINT PK_tGrouper_CMG_HL_CMG_ASSIGN PRIMARY KEY CLUSTERED ([methodology_year], [cmg_code]) 
)
GO
--Delete 2022 Data
delete from tGrouper_CACS_Aanaesthetic where methodology_year = 2022
GO
delete from tGrouper_CACS_Aanaesthetic_Assign where methodology_year = 2022
GO
delete from tGrouper_CACS_Age_Category where methodology_year = 2022
GO
delete from tGrouper_CACS_Age_Category_Assign where methodology_year = 2022
GO
delete from tGrouper_CACS_Ambulatory_Diag_Assign where methodology_year = 2022
GO
delete from tGrouper_CACS_Ambulatory_Interv_Assign where methodology_year = 2022
GO
delete from tGrouper_CACS_ANAESTHETIC_ASSIGN where methodology_year = 2022
GO
delete from tGrouper_CACS_Base_RIW where methodology_year = 2022
GO
delete from tGrouper_CACS_Branch_Logic where methodology_year = 2022
GO
delete from tGrouper_CACS_CACS where methodology_year = 2022
GO
delete from tGrouper_CACS_ED_Diag_Assign where methodology_year = 2022
GO
delete from tGrouper_CACS_ED_Interv where fiscal_year = 2022
GO
delete from tGrouper_CACS_ED_Interv_Assign where methodology_year = 2022
GO
delete from tGrouper_CACS_ED_INTERV_BRANCH_ASSIGN where methodology_year = 2022
GO
delete from tGrouper_CACS_ED_INTERV_MAP where methodology_year = 2022
GO
delete from tGrouper_CACS_Interaction_Block where methodology_year = 2022
GO
delete from tGrouper_CACS_Interaction_RIW where methodology_year = 2022
GO
delete from tGrouper_CACS_Inv_Tech_Category where methodology_year = 2022
GO
delete from tGrouper_CACS_Inv_Tech_Category_Assign where methodology_year = 2022
GO
delete from tGrouper_CACS_Inv_Tech_RIW where methodology_year = 2022
GO
delete from tGrouper_CACS_Logic where methodology_year = 2022
GO
delete from tGrouper_CACS_Logic_Type where methodology_year = 2022
GO
delete from tGrouper_CACS_MAC where methodology_year = 2022
GO
delete from tGrouper_CACS_Partition where methodology_year = 2022
GO
delete from tGrouper_CACS_Rehab_Program_Aarea_Assign where methodology_year = 2022
GO
delete from tGrouper_CACS_Split where methodology_year = 2022
GO
delete from tGrouper_CACS_Split_Interv where methodology_year = 2022
GO
delete from tGrouper_CACS_SPLIT_MODE_OF_VISIT where methodology_year = 2022
GO
delete from tGrouper_CACS_Split_Type where methodology_year = 2022
GO
delete from tGrouper_CACS_Split_Visit_Disp where methodology_year = 2022
GO
delete from tGrouper_CMG_ATYPICAL_RIW where METHODOLOGY_YEAR = 2022
GO
delete from tGrouper_CMG_ATYPICAL_RIW_2015 where METHODOLOGY_YEAR = 2022
GO
delete from tGrouper_CMG_BASE_ELOS_RIW where methodology_year = 2022
GO
delete from tGrouper_CMG_BRANCH_LOGIC where methodology_year = 2022
GO
delete from tGrouper_CMG_CMG where methodology_year = 2022
GO
delete from tGrouper_CMG_CMG_AGE_CATEGORY where methodology_year = 2022
GO
delete from tGrouper_CMG_CMG_AGE_CATEGORY_ASSIGN where methodology_year = 2022
GO
delete from tGrouper_CMG_CMG_LOGIC where methodology_year = 2022
GO
delete from tGrouper_CMG_CMG_SPLIT where methodology_year = 2022
GO
delete from tGrouper_CMG_CMG_SPLIT_ANAESTHETIC where methodology_year = 2022
GO
delete from tGrouper_CMG_CMG_SPLIT_DIAG where methodology_year = 2022
GO
delete from tGrouper_CMG_CMG_SPLIT_ENTRY where methodology_year = 2022
GO
delete from tGrouper_CMG_CMG_SPLIT_GESTATION where methodology_year = 2022
GO
delete from tGrouper_CMG_CMG_SPLIT_INTERV where methodology_year = 2022
GO
delete from tGrouper_CMG_CMG_WEIGHT_ASSIGN where methodology_year = 2022
GO
delete from tGrouper_CMG_COMORBIDITY_CNT where methodology_year = 2022
GO
delete from tGrouper_CMG_COMORBIDITY_CNT_FACTOR_ASSIGN where methodology_year = 2022
GO
delete from tGrouper_CMG_COMORBIDITY_ELOS_RIW where methodology_year = 2022
GO
delete from tGrouper_CMG_COMORBIDITY_EXCLUSION_PAIR where methodology_year = 2022
GO
delete from tGrouper_CMG_COMORBIDITY_FACTOR_ASSIGN where methodology_year = 2022
GO
delete from tGrouper_CMG_COMORBIDITY_INTERACTION where methodology_year = 2022
GO
delete from tGrouper_CMG_COMORBIDITY_LEVEL where methodology_year = 2022
GO
delete from tGrouper_CMG_COMORBIDITY_LEVEL_ASSIGN where methodology_year = 2022
GO
delete from tGrouper_CMG_EXCEPTION_BRANCH_ASSIGN where methodology_year = 2022
GO
delete from tGrouper_CMG_FACTOR_INTERACTION_ELOS_RIW_2011 where methodology_year = 2022
GO
delete from tGrouper_CMG_FLAGGED_INTERV_ASSIGN where methodology_year = 2022
GO
delete from tGrouper_CMG_FLAGGED_INTERV_CATEGORY where methodology_year = 2022
GO
delete from tGrouper_CMG_FLAGGED_INTERV_ELOS_RIW_2015 where methodology_year = 2022
GO
delete from tGrouper_CMG_FLAGGED_INTERV_EXCLUSION where methodology_year = 2022
GO
delete from tGrouper_CMG_FLAGGED_INTERV_GROUP where methodology_year = 2022
GO
delete from tGrouper_CMG_FLAGGED_INTERV_STATUS where methodology_year = 2022
GO
delete from tGrouper_CMG_FLAGGED_INTERV_STATUS_ASSIGN where methodology_year = 2022
GO
delete from tGrouper_CMG_INPATIENT_CACS_EXCLUSION where methodology_year = 2022
GO
delete from tGrouper_CMG_INTERV_EVENT_CNT where methodology_year = 2022
GO
delete from tGrouper_CMG_INTERV_EVENT_ELOS_RIW where methodology_year = 2022
GO
delete from tGrouper_CMG_INTERV_OOH_ASSIGN where methodology_year = 2022
GO
delete from tGrouper_CMG_INTERV_OOH_CATEGORY where methodology_year = 2022
GO
delete from tGrouper_CMG_INTERV_OOH_RIW where methodology_year = 2022
GO
delete from tGrouper_CMG_IP_FACTOR_INTERACTION_BLOCK where methodology_year = 2022
GO
delete from tGrouper_CMG_IP_RI_LEVEL where methodology_year = 2022
GO
delete from tGrouper_CMG_IP_RIW_ATYPICAL where methodology_year = 2022
GO
delete from tGrouper_CMG_IP_RIW_ATYPICAL_TYPE where methodology_year = 2022
GO
delete from tGrouper_CMG_LOS_GROUP where methodology_year = 2022
GO
delete from tGrouper_CMG_LOS_PERCENTILE where methodology_year = 2022
GO
delete from tGrouper_CMG_MCC_ASSIGN where methodology_year = 2022
GO
delete from tGrouper_CMG_MCC_INTERV where methodology_year = 2022
GO
delete from tGrouper_CMG_MCC_INTERV_PARTITION where methodology_year = 2022
GO
delete from tGrouper_CMG_MCC_LOGIC where methodology_year = 2022
GO
delete from tGrouper_CMG_MCC_PARTITION where methodology_year = 2022
GO
delete from tGrouper_CMG_NEWBORN_BRANCH_ASSIGN where methodology_year = 2022
GO
delete from tGrouper_CMG_PERCENTILE where METHODOLOGY_YEAR = 2022
GO
delete from tGrouper_HIG where methodology_year = 2022
GO
delete from tGrouper_HIG_Age_Category where methodology_year = 2022
GO
delete from tGrouper_HIG_Age_ELOS_Weight where methodology_year = 2022
GO
delete from tGrouper_HIG_Atypical where methodology_year = 2022
GO
delete from tGrouper_HIG_Atypical_Percentile where methodology_year = 2022
GO
delete from tGrouper_HIG_Base_Elos_Weight where methodology_year = 2022
GO
delete from tGrouper_HIG_Branch_Logic where methodology_year = 2022
GO
delete from tGrouper_HIG_Event_Elos_Weight where methodology_year = 2022
GO
delete from tGrouper_HIG_FI_Elos_Weight where methodology_year = 2022
GO
delete from tGrouper_HIG_Logic where methodology_year = 2022
GO
delete from tGrouper_HIG_OOH_Assign where methodology_year = 2022
GO
delete from tGrouper_HIG_OOH_Factor where methodology_year = 2022
GO
delete from tGrouper_HIG_Split where methodology_year = 2022
GO
delete from tGrouper_HIG_Split_Diag where methodology_year = 2022
GO
delete from tGrouper_HIG_Split_Interv where methodology_year = 2022
GO
delete from tGrouper_HIG_Split_Type where methodology_year = 2022
GO
delete from tGrouper_PROV_CACS_BASE_RIW where methodology_year = 2022
GO
delete from tGrouper_PROV_CACS_INTERACTION_RIW where methodology_year = 2022
GO
delete from tGrouper_PROV_CACS_INV_TECH_RIW where methodology_year = 2022
GO

--Bulk Insert 2022 Data
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_CCI_INTERV from '<path>tGrouper_CACS_ED_CCI_INTERV.txt' with ( fieldterminator = '|', rowterminator = '\n', CODEPAGE  = 'RAW' )  
GO
bulk insert tGrouper_CACS_ED_CCI_INTERV_TYPE from '<path>tGrouper_CACS_ED_CCI_INTERV_TYPE.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_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_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_CARE_LEVEL from '<path>tGrouper_CMG_CMG_CARE_LEVEL.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_PROVIDER_SERV from '<path>tGrouper_CMG_CMG_PROVIDER_SERV.txt' with ( fieldterminator = '|', rowterminator = '\n', CODEPAGE  = 'RAW' )  
GO
bulk insert tGrouper_CMG_CMG_PROVIDER_SERV_GROUP from '<path>tGrouper_CMG_CMG_PROVIDER_SERV_GROUP.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_HL_CMG from '<path>tGrouper_CMG_HL_CMG.txt' with ( fieldterminator = '|', rowterminator = '\n', CODEPAGE  = 'RAW' )  
GO
bulk insert tGrouper_CMG_HL_CMG_ASSIGN from '<path>tGrouper_CMG_HL_CMG_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
--Special process
Delete from [dbo].[CMG_LU_MCCDescription] 
where CMGYear >= 2019 and  CMGYear in (select distinct methodology_year from dbo.tGrouper_CMG_MCC_LOGIC)
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 >= 2019 and methodology_year not in (select distinct CMGYear from CMG_LU_MCCDescription)
GO

--End 2022 Grouper