--RM: Added Sept 7 2006 to clear duplicate settings
select * into RM_W_CustomUserSettings from W_CustomUserSettings 
go
delete W_CustomUserSettings 
go
insert into W_CustomUserSettings select distinct * from RM_W_CustomUserSettings
go
drop table RM_W_CustomUserSettings 
go
--exec dbo.M_SetViewPermission	--Mover to view.sql EN - 00399 Run for every DB upgrade
--Set Permission
--Do not process for WinRecs. Some Hospital do not like it
--if User_Id( N'WinRecs') is not null
--begin
--    exec('exec sp_revokedbaccess N''WinRecs''')
--    exec('sp_grantdbaccess N''WinRecs'', N''WinRecs''')
--    exec('sp_addrolemember N''db_datareader'', N''winrecs''')
--    exec('sp_dropsrvrolemember ''WinRecs'', ''sysadmin''')
--end
--GO
--RM: End

truncate table U_ResourceStrings
go
bulk insert U_ResourceStrings  from '<path>U_ResourceStrings.txt' with ( fieldterminator = '|', rowterminator = '\n' ) 
go
update U_ResourceStrings set StringE = 'Report Files (*.rpt)|*.rpt|', StringF = 'Fichiers de rapport (*.rpt)|*.rpt|' where RecordID = 1267
go
update U_ResourceStrings set StringE = 'All Files (*.*)|*.*|', StringF = 'Tous les fichiers (*.*)|*.*|' where RecordID = 1271
go
truncate table MG_Messages
go
bulk insert MG_Messages  from '<path>MG_Messages.txt' with ( fieldterminator = '|', rowterminator = '\n' ) 
go
truncate table MG_MessageFields
go
bulk insert MG_MessageFields  from '<path>MG_MessageFields.txt' with ( fieldterminator = '|', rowterminator = '\n' ) 
go
-- This needs to be in every update. This message has carriage returns and the Bulk insert caks out because of them.
if not exists (select * from dbo.MG_Messages where MessageKey = 'Q000082')
    insert into dbo.MG_Messages(MessageKey, MessageType, MessageText, ResponseType, CIHIErrorKey, MessageTextE, MessageTextF)
    values('Q000082',  1, 
'Before processing the request, we must complete the current transaction on the screen. 
    Yes - Proceed to completion, then process request
    No  - Cancel current transaction and process request only
    Cancel - Continue to process current transaction

If Yes, Loan/Transfer transaction will stop at record above one with request and Return/Mass Move will include chart requested.', 3, null,
'Before processing the request, we must complete the current transaction on the screen. 
    Yes - Proceed to completion, then process request
    No  - Cancel current transaction and process request only
    Cancel - Continue to process current transaction

If Yes, Loan/Transfer transaction will stop at record above one with request and Return/Mass Move will include chart requested.',
'Avant de traiter la demande, nous devons accomplir la transaction courante sur l''écran. 
    Oui - procédez à l''accomplissement, traitent alors la demande 
    Non - décommandez la demande courante de transaction et de processus seulement
    Annulation - continuez à traiter la transaction courante

Si oui, la transaction de Loan/Transfer s''arrêtera au disque au-dessus d''un avec la demande et le mouvement de Return/Mass inclura le diagramme demandé.'
)
else
Update  MG_Messages
set MessageType = 1, 
    MessageText = 'Before processing the request, we must complete the current transaction on the screen. 
    Yes - Proceed to completion, then process request
    No  - Cancel current transaction and process request only
    Cancel - Continue to process current transaction

If Yes, Loan/Transfer transaction will stop at record above one with request and Return/Mass Move will include chart requested.', 
ResponseType = 3, 
CIHIErrorKey = null,
MessageTextE = 'Before processing the request, we must complete the current transaction on the screen. 
    Yes - Proceed to completion, then process request
    No  - Cancel current transaction and process request only
    Cancel - Continue to process current transaction

If Yes, Loan/Transfer transaction will stop at record above one with request and Return/Mass Move will include chart requested.',
MessageTextF = 'Avant de traiter la demande, nous devons accomplir la transaction courante sur l''écran. 
    Oui - procédez à l''accomplissement, traitent alors la demande 
    Non - décommandez la demande courante de transaction et de processus seulement
    Annulation - continuez à traiter la transaction courante

Si oui, la transaction de Loan/Transfer s''arrêtera au disque au-dessus d''un avec la demande et le mouvement de Return/Mass inclura le diagramme demandé.'
where MessageKey = 'Q000082'
go
-- This needs to be in every update. This message has carriage returns and the Bulk insert caks out because of them.
if not exists (select * from dbo.MG_Messages where MessageKey = 'W000029')
    insert into dbo.MG_Messages(MessageKey, MessageType, MessageText, ResponseType, CIHIErrorKey, MessageTextE, MessageTextF)
    values('W000029',  1, 
'You have selected the Cancel function.  
Do you wish to erase all data entered into this record since the last save?"  

[Yes]  - this will reload the record as it presently does;  
[No]  - this will return the user to "status quo" before they hit the F8 Cancel key.', 2, null, '','')
else
Update  MG_Messages
set MessageText = 'You have selected the Cancel function.  
Do you wish to erase all data entered into this record since the last save?"  

[Yes]  - this will reload the record as it presently does;  
[No]  - this will return the user to "status quo" before they hit the F8 Cancel key.'
where MessageKey = 'W000029'
GO
------------------------------------------------------------------------------------------------------------------------------
--21216
if OBJECT_ID('zzMISFunction') > 0
    drop table zzMISFunction
GO
Create table dbo.zzMISFunction(
     FUNCTIONAL_CENTRE_CODE varchar(10), 
     FUNCTIONAL_CENTRE_SERIES_CODE varchar(10), 
     FUNCT_CENTRE_SERIES_E_DESC varchar(200),
     AMCARE_TYPE_CODE varchar(200),
     AMCARE_TYPE_E_DESC varchar(200),
     AMCARE_GROUP_CODE varchar(200),
     AMCARE_GROUP_E_DESC varchar(200)
)
GO
bulk insert zzMISFunction  from '<path>2023MISFunction.txt' with ( fieldterminator = '|', rowterminator = '\n' ) 
--bulk insert zzMISFunction  from 'f:\2023\2023MISFunction.txt' with ( fieldterminator = '|', rowterminator = '\n', CODEPAGE = 'RAW' ) 
go
update m
set DefaultDescription = z.FUNCT_CENTRE_SERIES_E_DESC,
    UserDescription = (case when m.DefaultDescription = m.UserDescription then z.FUNCT_CENTRE_SERIES_E_DESC else m.UserDescription end)
from zzMISFunction z
    inner join LU_MISCode m on z.FUNCTIONAL_CENTRE_CODE = m.CIHIValue 
where z.FUNCT_CENTRE_SERIES_E_DESC <> m.DefaultDescription and m.AmCareGroup is not null
GO
if OBJECT_ID('zzMISFunction') > 0
    drop table zzMISFunction
GO
--21218
if OBJECT_ID('zz2023NACRSErros') > 0
    drop table zz2023NACRSErros
GO
Create table dbo.zz2023NACRSErros(
     ErrorNumber varchar(10), 
     ErrorMessage varchar(500)
)
GO
bulk insert zz2023NACRSErros  from '<path>2023NACRSErrors.txt' with ( fieldterminator = '|', rowterminator = '\n' ) 
--bulk insert zz2023NACRSErros  from 'f:\2023\2023NACRSErrors.txt' with ( fieldterminator = '|', rowterminator = '\n', CODEPAGE = 'RAW' ) 
go
Delete from M_AmcareErrors where [BatchYear] = 2023
GO
Insert into M_AmcareErrors([ErrorNumber], [ErrorDescription], [IsError], [BatchYear])
select ErrorNumber, ErrorMessage, (case when ErrorNumber like 'W%' then 0 else 1 end), 2023
from zz2023NACRSErros
GO
if OBJECT_ID('zz2023NACRSErros') > 0
    drop table zz2023NACRSErros
GO
--21209
if OBJECT_ID('zz2023DADErros') > 0
    drop table zz2023DADErros
GO
Create table dbo.zz2023DADErros(
     Error varchar(10), 
     Groupnumber varchar(10), 
     Fieldnumber varchar(10), 
     Errornumber varchar(10), 
     ErrorMessage varchar(500),
     FrenchMessage varchar(500),
     report varchar(500)
)
GO
bulk insert zz2023DADErros  from '<path>2023DADErrors.txt' with ( fieldterminator = '|', rowterminator = '\n' ) 
--bulk insert zz2023DADErros  from 'f:\2023\2023DADErrors.txt' with ( fieldterminator = '|', rowterminator = '\n', CODEPAGE = 'RAW' ) 
go
Delete from [dbo].[M_DADErrors] where [BatchYear] = 2023
GO
Insert into [dbo].[M_DADErrors]([BatchYear], [ErrorNumber], [EnglishDescription], [FrenchDescription], [IsError])
select 2023,  ErrorNumber = Error + Groupnumber + Fieldnumber + Errornumber, ErrorMessage, FrenchMessage, (case when Error like 'W%' then 0 else 1 end)
from zz2023DADErros
GO
if OBJECT_ID('zz2023DADErros') > 0
    drop table zz2023DADErros
GO
--- ***** Remember to keep the customer code validated. Do not touch customer code.
--2023 ICD 10 data
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_10CA2023_V1_0.txt' with( FIELDTERMINATOR = '|', ROWTERMINATOR ='\n', CODEPAGE = 'RAW' )
--bulk insert zzICD10_Diag  from 'f:\2023\ICD_Code_Eng_Desc_10CA2023_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_CCI2023_V1_0.txt' with( FIELDTERMINATOR = '|', ROWTERMINATOR ='\n', CODEPAGE = 'RAW' )
--bulk insert zzICD10_CCI  from 'f:\2023\CCI_Code_Eng_Desc_CCI2023_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 < '2023/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 < '2023/04/01'
GO
update ICD_diagFile10
set ValidTo = '2023/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 > '2023/03/31' and icd.code < 500000
GO
update ICD_ProcFile10
set ValidTo = '2023/03/31'
-- select icd.*
from zzICD10_CCI z
	right join ICD_ProcFile10 Icd on z.CIHIValue = ICD.CIHIValue
where icd.validto > '2023/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, '2023/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
    and code < 500000
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, '2023/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 
    and code < 500000
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_10CA2023_V1_0.txt' with( FIELDTERMINATOR = '|', ROWTERMINATOR ='\n')
--bulk insert zzICD10CA_Validation  from 'f:\2023\ICD_Code_Validation_10CA2023_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_CCI2023_V1_0.txt' with( FIELDTERMINATOR = '|', ROWTERMINATOR ='\n')
--bulk insert zzICD10_CC1_Validation  from 'f:\2023\CCI_Code_Validation_CCI2023_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_CCI2023_V1_0.txt' with( FIELDTERMINATOR = '|', ROWTERMINATOR ='\n', CODEPAGE = 'RAW' )
-- bulk insert zzICD10CCI_Extent  from 'f:\2023\CCI_Extent_Val_CCI2023_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

-- select * from zzICD10CCI_Extent z left join ICD_ProcFile10 p on z.cihivalue = p.cihivalue where p.cihivalue is null or p.validto < '2023/04/01'


Update LU_IntervAttExtent
set validto = '2023/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 > '2023/03/31'
GO
Update LU_IntervAttExtent
set validto = '2023/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 > '2023/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 <= '2023/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_CCI2023_V1_0.txt' with( FIELDTERMINATOR = '|', ROWTERMINATOR ='\n', CODEPAGE = 'RAW' )
-- bulk insert zzICD10CCI_Extent_Desc  from 'f:\2023\CCI_Extent_Eng_CCI2023_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 > '2023/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 = '2023/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_CCI2023_V1_0.txt' with( FIELDTERMINATOR = '|', ROWTERMINATOR ='\n', CODEPAGE = 'RAW' )
-- bulk insert zzICD10CCI_Location  from 'f:\2023\CCI_Location_Val_CCI2023_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

-- select * from zzICD10CCI_Location z left join ICD_ProcFile10 p on z.cihivalue = p.cihivalue where p.cihivalue is null or p.validto < '2023/04/01'

Update LU_IntervAttLocation
set validto = '2023/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 > '2023/03/31'
GO
Update LU_IntervAttLocation
set validto = '2023/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 > '2023/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 <= '2023/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_CCI2023_V1_0.txt' with( FIELDTERMINATOR = '|', ROWTERMINATOR ='\n', CODEPAGE = 'RAW' )
--bulk insert zzICD10CCI_Location_Desc  from 'f:\2023\CCI_Location_Eng_CCI2023_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 > '2023/04/01'
GO

--2023 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 = '2023/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_CCI2023_V1_0.txt' with( FIELDTERMINATOR = '|', ROWTERMINATOR ='\n', CODEPAGE = 'RAW' )
-- bulk insert zzICD10CCI_Status  from 'f:\2023\CCI_Status_Val_CCI2023_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

-- select * from zzICD10CCI_Status z left join ICD_ProcFile10 p on z.cihivalue = p.cihivalue where p.cihivalue is null or p.validto < '2023/04/01'

Update LU_IntervAttStatus
set validto = '2023/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 > '2023/03/31'
GO
Update LU_IntervAttStatus
set validto = '2023/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 > '2023/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 <= '2023/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_CCI2023_V1_0.txt' with( FIELDTERMINATOR = '|', ROWTERMINATOR ='\n', CODEPAGE = 'RAW' )
-- bulk insert zzICD10CCI_Status_Desc  from 'f:\2023\CCI_Status_Eng_CCI2023_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 > '2023/04/01'
GO

--2023 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 = '2023/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_CCI2023_V1_0.txt' with( FIELDTERMINATOR = '|', ROWTERMINATOR ='\n')
-- bulk insert zzICD10CCI_Cat  from 'f:\2023\CCI_Blk_Eng_Desc_CCI2023_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_CCI2023_V1_0.txt' with( FIELDTERMINATOR = '|', ROWTERMINATOR ='\n')
--bulk insert zzICD10CCI_Class  from 'f:\2023\CCI_Rubric_Eng_Desc_CCI2023_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_10CA2023_V1_0.txt' with( FIELDTERMINATOR = '|', ROWTERMINATOR ='\n')
--bulk insert zzICD10CA_Cat  from 'f:\2023\ICD_Cat1_Eng_Desc_10CA2023_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
--select z.*, d.*
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
-- select z.*, d.*
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
-- select z.*, d.*
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 ICD 10 CA and CCI code
--ED Discharge Diagnosis Code
if exists (select * from information_schema.tables where table_name = 'zzEDDischargeDiag')
    drop table zzEDDischargeDiag
GO
Create table zzEDDischargeDiag(
    CIHIValue varchar(10),
    Commondiagnosticterm varchar(100),
    longDescription varchar(500),
    Chapter varchar(10),
    ChapterDescription varchar(100)
)
GO
bulk insert zzEDDischargeDiag  from '<path>2023EDDischargeDiag.txt' with( FIELDTERMINATOR = '|', ROWTERMINATOR ='\n') --, CODEPAGE = 'RAW' )
-- bulk insert zzEDDischargeDiag  from 'f:\2023\2023EDDischargeDiag.txt' with( FIELDTERMINATOR = '|', ROWTERMINATOR ='\n', CODEPAGE = 'RAW' )
GO
Update d set EDDiagnosis = 1
-- select d.*
from ICD_DiagFile10 d
    inner join zzEDDischargeDiag z on d.CIHIValue = z.CIHIValue 
where isnull(d.EDDiagnosis, 0) <> 1
GO
Update d set IsDischarge = 1
-- select d.*
from ICD_DiagFile10 d
    inner join zzEDDischargeDiag z on d.CIHIValue = z.CIHIValue 
where isnull(d.IsDischarge, 0) <> 1
GO
if exists (select * from information_schema.tables where table_name = 'zzEDDischargeDiag')
    drop table zzEDDischargeDiag
GO
--EDVS
if exists (select * from information_schema.tables where table_name = 'zzEDVS')
    drop table zzEDVS
GO
Create table zzEDVS(
    Interventiontype varchar(100),
    Englishcommonterm varchar(100),
    SNOMEDCTCAconceptID varchar(100),
    DescriptionID varchar(100),
    SCTdescription varchar(100),
    CCIcode varchar(100),
    CCIlongtitleEnglish varchar(255)
)
GO
bulk insert zzEDVS  from '<path>2023EDVS.txt' with( FIELDTERMINATOR = '|', ROWTERMINATOR ='\n') --, CODEPAGE = 'RAW' )
-- bulk insert zzEDVS  from 'f:\2023\2023EDVS.txt' with( FIELDTERMINATOR = '|', ROWTERMINATOR ='\n', CODEPAGE = 'RAW' )
GO
Update d 
set UserDescription = (case when UserDescription = DefaultDescription then z.CCIlongtitleEnglish else UserDescription end),
    DefaultDescription = z.CCIlongtitleEnglish
-- select d.[DefaultDescription] , z.CCIlongtitleEnglish, d.*, z.*
from ICD_EDInterventionValueSet d
    inner join zzEDVS z on d.CIHIValue = z.CCIcode and d.DescriptionID = z.DescriptionID and d.SNOMED_CT_CA_conceptID = z.SNOMEDCTCAconceptID
where d.[DefaultDescription] <> z.CCIlongtitleEnglish
GO
Update d 
set UserDescription = (case when UserDescription = DefaultDescription then z.CCIlongtitleEnglish else UserDescription end),
    DefaultDescription = z.CCIlongtitleEnglish
-- select d.[DefaultDescription] , z.CCIlongtitleEnglish, d.*, z.*
from ICD_EDInterventionValueSet d
    inner join zzEDVS z on d.CIHIValue = z.CCIcode and d.DescriptionID = z.DescriptionID and d.SNOMED_CT_CA_conceptID = z.SNOMEDCTCAconceptID
where d.[DefaultDescription] <> z.CCIlongtitleEnglish
GO
Update d 
set InterventionType = (case when z.Interventiontype = 'Investigative Technology' then 1 else 2 end)
-- select  d.*, z.*
from ICD_EDInterventionValueSet d
    inner join zzEDVS z on d.CIHIValue = z.CCIcode and d.DescriptionID = z.DescriptionID and d.SNOMED_CT_CA_conceptID = z.SNOMEDCTCAconceptID
where isnull(d.InterventionType, 0) <> (case when z.Interventiontype = 'Investigative Technology' then 1 else 2 end)
GO
Update d 
set InterventionType = (case when z.Interventiontype = 'Investigative Technology' then 1 else 2 end)
-- select  d.*, z.*
from ICD_EDInterventionValueSet d
    inner join zzEDVS z on d.CIHIValue = z.CCIcode and d.DescriptionID = z.DescriptionID and d.SNOMED_CT_CA_conceptID = z.SNOMEDCTCAconceptID
where isnull(d.InterventionType, 0) <> (case when z.Interventiontype = 'Investigative Technology' then 1 else 2 end)
GO
Update d 
set ShortDescription = z.Englishcommonterm
-- select d.ShortDescription, z.Englishcommonterm, d.*, z.*
from ICD_EDInterventionValueSet d
    inner join zzEDVS z on d.CIHIValue = z.CCIcode and d.DescriptionID = z.DescriptionID and d.SNOMED_CT_CA_conceptID = z.SNOMEDCTCAconceptID
where isnull(d.ShortDescription, '') <> z.Englishcommonterm
GO
Update d 
set SCTdescription = z.SCTdescription
-- select d.SCTdescription, z.SCTdescription, d.*, z.*
from ICD_EDInterventionValueSet d
    inner join zzEDVS z on d.CIHIValue = z.CCIcode and d.DescriptionID = z.DescriptionID and d.SNOMED_CT_CA_conceptID = z.SNOMEDCTCAconceptID
where isnull(d.SCTdescription, '') <> z.SCTdescription
GO
if exists (select * from information_schema.tables where table_name = 'zzEDVS')
    drop table zzEDVS
GO
--Grouper Data
--Delete 2023 data
delete from tGrouper_CACS_Aanaesthetic where methodology_year = 2023 
GO
delete from tGrouper_CACS_Aanaesthetic_Assign where methodology_year = 2023 
GO
delete from tGrouper_CACS_Age_Category where methodology_year = 2023 
GO
delete from tGrouper_CACS_Age_Category_Assign where methodology_year = 2023 
GO
delete from tGrouper_CACS_Ambulatory_Diag_Assign where methodology_year = 2023 
GO
delete from tGrouper_CACS_Ambulatory_Interv_Assign where methodology_year = 2023 
GO
delete from tGrouper_CACS_ANAESTHETIC_ASSIGN where methodology_year = 2023 
GO
delete from tGrouper_CACS_Base_RIW where methodology_year = 2023 
GO
delete from tGrouper_CACS_Branch_Logic where methodology_year = 2023 
GO
delete from tGrouper_CACS_CACS where methodology_year = 2023 
GO
delete from tGrouper_CACS_ED_CCI_INTERV where fiscal_year = 2023 
GO
delete from tGrouper_CACS_ED_Diag_Assign where methodology_year = 2023 
GO
delete from tGrouper_CACS_ED_Interv_Assign where methodology_year = 2023 
GO
delete from tGrouper_CACS_ED_INTERV_BRANCH_ASSIGN where methodology_year = 2023 
GO
delete from tGrouper_CACS_Interaction_Block where methodology_year = 2023 
GO
delete from tGrouper_CACS_Interaction_RIW where methodology_year = 2023 
GO
delete from tGrouper_CACS_Inv_Tech_Category where methodology_year = 2023 
GO
delete from tGrouper_CACS_Inv_Tech_Category_Assign where methodology_year = 2023 
GO
delete from tGrouper_CACS_Inv_Tech_RIW where methodology_year = 2023 
GO
delete from tGrouper_CACS_Logic where methodology_year = 2023 
GO
delete from tGrouper_CACS_Logic_Type where methodology_year = 2023 
GO
delete from tGrouper_CACS_MAC where methodology_year = 2023 
GO
delete from tGrouper_CACS_Partition where methodology_year = 2023 
GO
delete from tGrouper_CACS_Rehab_Program_Aarea_Assign where methodology_year = 2023 
GO
delete from tGrouper_CACS_Split where methodology_year = 2023 
GO
delete from tGrouper_CACS_Split_Interv where methodology_year = 2023 
GO
delete from tGrouper_CACS_Split_Type where methodology_year = 2023 
GO
delete from tGrouper_CACS_Split_Visit_Disp where methodology_year = 2023 
GO
delete from tGrouper_CMG_ATYPICAL_RIW_2015 where METHODOLOGY_YEAR = 2023 
GO
delete from tGrouper_CMG_BASE_ELOS_RIW where methodology_year = 2023 
GO
delete from tGrouper_CMG_BRANCH_LOGIC where methodology_year = 2023 
GO
delete from tGrouper_CMG_CMG where methodology_year = 2023 
GO
delete from tGrouper_CMG_CMG_AGE_CATEGORY where methodology_year = 2023 
GO
delete from tGrouper_CMG_CMG_AGE_CATEGORY_ASSIGN where methodology_year = 2023 
GO
delete from tGrouper_CMG_CMG_LOGIC where methodology_year = 2023 
GO
delete from tGrouper_CMG_CMG_SPLIT where methodology_year = 2023 
GO
delete from tGrouper_CMG_CMG_SPLIT_ANAESTHETIC where methodology_year = 2023 
GO
delete from tGrouper_CMG_CMG_SPLIT_DIAG where methodology_year = 2023 
GO
delete from tGrouper_CMG_CMG_SPLIT_ENTRY where methodology_year = 2023 
GO
delete from tGrouper_CMG_CMG_SPLIT_GESTATION where methodology_year = 2023 
GO
delete from tGrouper_CMG_CMG_SPLIT_INTERV where methodology_year = 2023 
GO
delete from tGrouper_CMG_CMG_WEIGHT_ASSIGN where methodology_year = 2023 
GO
delete from tGrouper_CMG_COMORBIDITY_CNT where methodology_year = 2023 
GO
delete from tGrouper_CMG_COMORBIDITY_CNT_FACTOR_ASSIGN where methodology_year = 2023 
GO
delete from tGrouper_CMG_COMORBIDITY_ELOS_RIW where methodology_year = 2023 
GO
delete from tGrouper_CMG_COMORBIDITY_EXCLUSION_PAIR where methodology_year = 2023 
GO
delete from tGrouper_CMG_COMORBIDITY_FACTOR_ASSIGN where methodology_year = 2023 
GO
delete from tGrouper_CMG_COMORBIDITY_INTERACTION where methodology_year = 2023 
GO
delete from tGrouper_CMG_COMORBIDITY_LEVEL where methodology_year = 2023 
GO
delete from tGrouper_CMG_COMORBIDITY_LEVEL_ASSIGN where methodology_year = 2023 
GO
delete from tGrouper_CMG_EXCEPTION_BRANCH_ASSIGN where methodology_year = 2023 
GO
delete from tGrouper_CMG_FACTOR_INTERACTION_ELOS_RIW_2011 where methodology_year = 2023 
GO
delete from tGrouper_CMG_FLAGGED_INTERV_ASSIGN where methodology_year = 2023 
GO
delete from tGrouper_CMG_FLAGGED_INTERV_CATEGORY where methodology_year = 2023 
GO
delete from tGrouper_CMG_FLAGGED_INTERV_ELOS_RIW_2015 where methodology_year = 2023 
GO
delete from tGrouper_CMG_FLAGGED_INTERV_EXCLUSION where methodology_year = 2023 
GO
delete from tGrouper_CMG_FLAGGED_INTERV_GROUP where methodology_year = 2023 
GO
delete from tGrouper_CMG_FLAGGED_INTERV_STATUS where methodology_year = 2023 
GO
delete from tGrouper_CMG_FLAGGED_INTERV_STATUS_ASSIGN where methodology_year = 2023 
GO
delete from tGrouper_CMG_HL_CMG where methodology_year = 2023 
GO
delete from tGrouper_CMG_HL_CMG_ASSIGN where methodology_year = 2023 
GO
delete from tGrouper_CMG_INPATIENT_CACS_EXCLUSION where methodology_year = 2023 
GO
delete from tGrouper_CMG_INTERV_EVENT_CNT where methodology_year = 2023 
GO
delete from tGrouper_CMG_INTERV_EVENT_ELOS_RIW where methodology_year = 2023 
GO
delete from tGrouper_CMG_INTERV_OOH_ASSIGN where methodology_year = 2023 
GO
delete from tGrouper_CMG_INTERV_OOH_CATEGORY where methodology_year = 2023 
GO
delete from tGrouper_CMG_INTERV_OOH_RIW where methodology_year = 2023 
GO
delete from tGrouper_CMG_IP_FACTOR_INTERACTION_BLOCK where methodology_year = 2023 
GO
delete from tGrouper_CMG_IP_RI_LEVEL where methodology_year = 2023 
GO
delete from tGrouper_CMG_IP_RIW_ATYPICAL where methodology_year = 2023 
GO
delete from tGrouper_CMG_IP_RIW_ATYPICAL_TYPE where methodology_year = 2023 
GO
delete from tGrouper_CMG_LOS_GROUP where methodology_year = 2023 
GO
delete from tGrouper_CMG_LOS_PERCENTILE where methodology_year = 2023 
GO
delete from tGrouper_CMG_MCC_ASSIGN where methodology_year = 2023 
GO
delete from tGrouper_CMG_MCC_INTERV where methodology_year = 2023 
GO
delete from tGrouper_CMG_MCC_INTERV_PARTITION where methodology_year = 2023 
GO
delete from tGrouper_CMG_MCC_LOGIC where methodology_year = 2023 
GO
delete from tGrouper_CMG_MCC_PARTITION where methodology_year = 2023 
GO
delete from tGrouper_CMG_NEWBORN_BRANCH_ASSIGN where methodology_year = 2023 
GO
delete from tGrouper_CMG_PERCENTILE where METHODOLOGY_YEAR = 2023 
GO
delete from tGrouper_HIG where methodology_year = 2023 
GO
delete from tGrouper_HIG_Age_Category where methodology_year = 2023 
GO
delete from tGrouper_HIG_Age_ELOS_Weight where methodology_year = 2023 
GO
delete from tGrouper_HIG_Atypical where methodology_year = 2023 
GO
delete from tGrouper_HIG_Atypical_Percentile where methodology_year = 2023 
GO
delete from tGrouper_HIG_Base_Elos_Weight where methodology_year = 2023 
GO
delete from tGrouper_HIG_Branch_Logic where methodology_year = 2023 
GO
delete from tGrouper_HIG_Event_Elos_Weight where methodology_year = 2023 
GO
delete from tGrouper_HIG_FI_Elos_Weight where methodology_year = 2023 
GO
delete from tGrouper_HIG_Logic where methodology_year = 2023 
GO
delete from tGrouper_HIG_OOH_Assign where methodology_year = 2023 
GO
delete from tGrouper_HIG_OOH_Factor where methodology_year = 2023 
GO
delete from tGrouper_HIG_Split where methodology_year = 2023 
GO
delete from tGrouper_HIG_Split_Diag where methodology_year = 2023 
GO
delete from tGrouper_HIG_Split_Interv where methodology_year = 2023 
GO
delete from tGrouper_HIG_Split_Type where methodology_year = 2023 
GO
delete from tGrouper_PROV_CACS_BASE_RIW where methodology_year = 2023 
GO
delete from tGrouper_PROV_CACS_INTERACTION_RIW where methodology_year = 2023 
GO
delete from tGrouper_PROV_CACS_INV_TECH_RIW where methodology_year = 2023 
GO
--Bulk Insert 2023 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_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_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_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 >= 2023 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 >= 2023 and methodology_year not in (select distinct CMGYear from CMG_LU_MCCDescription)
GO
--Finish Grouper Data Insert


