--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
----SNOMED
if Not exists (select * from information_schema.tables where table_name = 'ICD_SNOMEDCT_Diagnosis')
Create table dbo.ICD_SNOMEDCT_Diagnosis(
    [Code] [int] NOT NULL,
	[DisplayCode] [varchar](50) NULL,   
	[UserDescription] [varchar](255) NULL,
	[DefaultDescription] [varchar](255) NULL,
	[CIHIValue] [varchar](50) NULL, --referencedComponentId
	[ValidFrom] [datetime] NULL,    --referencedComponentName
	[ValidTo] [datetime] NULL,
	[InterfaceIn] [varchar](50) NULL,
	[HospitalID] [int] NULL,
	[LUText] [varchar](255) NULL,
    mapGroup varchar(200),
    mapPriority varchar(200),
    mapRule varchar(200),
    mapAdvice varchar(500),
    mapTarget varchar(200),
    mapTargetName varchar(500),
    correlationId varchar(200),
    mapCategoryId varchar(200),
    mapCategoryName varchar(500),
    CONSTRAINT [PK_ICD_SNOMEDCT_Diagnosis] PRIMARY KEY CLUSTERED ([Code] ASC)
)
GO
if Not exists (select * from information_schema.tables where table_name = 'ICD_SNOMEDCT_Discharge_Diagnosis')
Create table dbo.ICD_SNOMEDCT_Discharge_Diagnosis(
    [Code] [int] NOT NULL,
	[DisplayCode] [varchar](50) NULL,   
	[UserDescription] [varchar](255) NULL,
	[DefaultDescription] [varchar](255) NULL,
	[CIHIValue] [varchar](50) NULL, --referencedComponentId
	[ValidFrom] [datetime] NULL,    --referencedComponentName
	[ValidTo] [datetime] NULL,
	[InterfaceIn] [varchar](50) NULL,
	[HospitalID] [int] NULL,
	[LUText] [varchar](255) NULL,
    mapGroup varchar(200),
    mapPriority varchar(200),
    mapRule varchar(200),
    mapAdvice varchar(500),
    mapTarget varchar(200),
    mapTargetName varchar(500),
    correlationId varchar(200),
    mapCategoryId varchar(200),
    mapCategoryName varchar(500),
    CONSTRAINT [PK_ICD_SNOMEDCT_Discharge_Diagnosis] PRIMARY KEY CLUSTERED ([Code] ASC)
)
GO

if exists (select * from information_schema.tables where table_name = 'zzSNOMEDCT')
    drop table zzSNOMEDCT
GO
Create table zzSNOMEDCT(
    id varchar(200),
    effectiveTime varchar(200),
    active varchar(200),
    moduleId varchar(200),
    refsetId varchar(200),
    referencedComponentId varchar(200),
    referencedComponentName varchar(500),
    mapGroup varchar(200),
    mapPriority varchar(200),
    mapRule varchar(200),
    mapAdvice varchar(500),
    mapTarget varchar(200),
    mapTargetName varchar(500),
    correlationId varchar(200),
    mapCategoryId varchar(200),
    mapCategoryName varchar(500)
)
GO
bulk insert zzSNOMEDCT  from '<path>tls_Icd10caHumanReadableMap_INT_20231021.tsv' with( FIELDTERMINATOR = '\t', ROWTERMINATOR ='\n', CODEPAGE = 'RAW' )
--bulk insert zzSNOMEDCT  from 'f:\2024\tls_Icd10caHumanReadableMap_INT_20231021.tsv' with( FIELDTERMINATOR = '\t', ROWTERMINATOR ='\n', CODEPAGE = 'RAW' )
GO
if exists (select * from information_schema.tables where table_name = 'zzSNOMEDCT_valueset')
    drop table zzSNOMEDCT_valueset
GO
Create table zzSNOMEDCT_valueset(
    id varchar(200),
    effectiveTime varchar(200),
    active varchar(200),
    moduleId varchar(200),
    refsetId varchar(200),
    referencedComponentId varchar(200),
    referencedComponentName varchar(500),
    mapGroup varchar(200),
    mapPriority varchar(200),
    mapRule varchar(200),
    mapAdvice varchar(500),
    mapTarget varchar(200),
    mapTargetName varchar(500),
    correlationId varchar(200),
    mapCategoryId varchar(200),
    mapCategoryName varchar(500)
)
GO
bulk insert zzSNOMEDCT_valueset  from '<path>tls_PhcvsHumanReadableMap_INT_20231021.tsv' with( FIELDTERMINATOR = '\t', ROWTERMINATOR ='\n', CODEPAGE = 'RAW' )
--bulk insert zzSNOMEDCT_valueset  from 'f:\2024\tls_PhcvsHumanReadableMap_INT_20231021.tsv' with( FIELDTERMINATOR = '\t', ROWTERMINATOR ='\n', CODEPAGE = 'RAW' )
GO
delete zzSNOMEDCT where id = 'id' and effectiveTime = 'effectiveTime'
GO
delete zzSNOMEDCT_valueset where id = 'id' and effectiveTime = 'effectiveTime'
GO
create index idx_zzSNOMEDCT_referencedComponentId on zzSNOMEDCT(referencedComponentId)
GO
IF  EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[ICD_SNOMEDCT_Diagnosis]') AND name = N'idx_ICD_SNOMEDCT_Diagnosis_CIHIValue') 
DROP INDEX idx_ICD_SNOMEDCT_Diagnosis_CIHIValue ON [dbo].ICD_SNOMEDCT_Diagnosis WITH ( ONLINE = OFF ) 
GO
create index idx_ICD_SNOMEDCT_Diagnosis_CIHIValue on ICD_SNOMEDCT_Diagnosis(CIHIValue)
GO
if exists (select * from information_schema.tables where table_name = 'zzSNOMEDCT_new')
    drop table zzSNOMEDCT_new
GO
if exists (select * from information_schema.tables where table_name = 'zzSNOMEDCT_valueset_new')
    drop table zzSNOMEDCT_valueset_new
GO
select Code = identity(int, 1, 1), z.*
    into zzSNOMEDCT_new
from zzSNOMEDCT z
    left join ICD_SNOMEDCT_Diagnosis d on z.referencedComponentId = d.CIHIValue and z.mapTarget = d.mapTarget
where d.CIHIValue is null and isnull(z.mapTarget, '') <> '' and ISNUMERIC(replace(replace(z.mapTarget, '.', ''), '-', '')) = 0
order by z.referencedComponentId
GO
select Code = identity(int, 1, 1), z.*
    into zzSNOMEDCT_valueset_new
from zzSNOMEDCT_valueset z
    left join ICD_SNOMEDCT_Discharge_Diagnosis d on z.referencedComponentId = d.CIHIValue and z.mapTarget = d.mapTarget
where d.CIHIValue is null and isnull(z.mapTarget, '') <> '' and ISNUMERIC(replace(replace(z.mapTarget, '.', ''), '-', '')) = 0
order by z.referencedComponentId
GO
declare @maxID int
set @maxID = isnull((select max(code) from ICD_SNOMEDCT_Diagnosis where code < 500000), 0)
insert into dbo.ICD_SNOMEDCT_Diagnosis(Code, DisplayCode, UserDescription, DefaultDescription, CIHIValue, ValidFrom, ValidTo, InterfaceIn, HospitalID, LUText,
    mapGroup, mapPriority, mapRule, mapAdvice, mapTarget, mapTargetName, correlationId, mapCategoryId, mapCategoryName)
select @maxID + Code, referencedComponentId + ' = DX Code ' + mapTarget, left(referencedComponentName, 255), left(referencedComponentName, 255), referencedComponentId, 
    '2023/04/01', '2099/03/31', referencedComponentId, 0, '',
    mapGroup, mapPriority, mapRule, mapAdvice, mapTarget, mapTargetName, correlationId, mapCategoryId, mapCategoryName
from zzSNOMEDCT_new
GO
declare @maxID int
set @maxID = isnull((select max(code) from ICD_SNOMEDCT_Discharge_Diagnosis where code < 500000), 0)
insert into dbo.ICD_SNOMEDCT_Discharge_Diagnosis(Code, DisplayCode, UserDescription, DefaultDescription, CIHIValue, ValidFrom, ValidTo, InterfaceIn, HospitalID, LUText,
    mapGroup, mapPriority, mapRule, mapAdvice, mapTarget, mapTargetName, correlationId, mapCategoryId, mapCategoryName)
select @maxID + Code, referencedComponentId + ' = DX Code ' + mapTarget, left(referencedComponentName, 255), left(referencedComponentName, 255), referencedComponentId, 
    '2023/04/01', '2099/03/31', referencedComponentId, 0, '',
    mapGroup, mapPriority, mapRule, mapAdvice, mapTarget, mapTargetName, correlationId, mapCategoryId, mapCategoryName
from zzSNOMEDCT_valueset_new
GO
IF  EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[ICD_SNOMEDCT_Diagnosis]') AND name = N'idx_ICD_SNOMEDCT_Diagnosis_DisplayCode') 
DROP INDEX [idx_ICD_SNOMEDCT_Diagnosis_DisplayCode] ON [dbo].[ICD_SNOMEDCT_Diagnosis] WITH ( ONLINE = OFF ) 
GO
create index [idx_ICD_SNOMEDCT_Diagnosis_DisplayCode] ON [dbo].[ICD_SNOMEDCT_Diagnosis](DisplayCode)
GO
IF  EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[ICD_SNOMEDCT_Discharge_Diagnosis]') AND name = N'idx_ICD_SNOMEDCT_Discharge_Diagnosis_DisplayCode') 
DROP INDEX [idx_ICD_SNOMEDCT_Discharge_Diagnosis_DisplayCode] ON [dbo].[ICD_SNOMEDCT_Discharge_Diagnosis] WITH ( ONLINE = OFF ) 
GO
create index [idx_ICD_SNOMEDCT_Discharge_Diagnosis_DisplayCode] ON [dbo].[ICD_SNOMEDCT_Discharge_Diagnosis](DisplayCode)
GO
--######## Grouper Data ########################
delete from tGrouper_CACS_Aanaesthetic where methodology_year = 2024
GO
delete from tGrouper_CACS_Aanaesthetic_Assign where methodology_year = 2024
GO
delete from tGrouper_CACS_Age_Category where methodology_year = 2024
GO
delete from tGrouper_CACS_Age_Category_Assign where methodology_year = 2024
GO
delete from tGrouper_CACS_Ambulatory_Diag_Assign where methodology_year = 2024
GO
delete from tGrouper_CACS_Ambulatory_Interv_Assign where methodology_year = 2024
GO
delete from tGrouper_CACS_ANAESTHETIC_ASSIGN where methodology_year = 2024
GO
delete from tGrouper_CACS_Base_RIW where methodology_year = 2024
GO
delete from tGrouper_CACS_Branch_Logic where methodology_year = 2024
GO
delete from tGrouper_CACS_CACS where methodology_year = 2024
GO
delete from tGrouper_CACS_ED_Diag_Assign where methodology_year = 2024
GO
delete from tGrouper_CACS_ED_Interv where fiscal_year = 2024
GO
delete from tGrouper_CACS_ED_Interv_Assign where methodology_year = 2024
GO
delete from tGrouper_CACS_ED_INTERV_BRANCH_ASSIGN where methodology_year = 2024
GO
delete from tGrouper_CACS_ED_INTERV_MAP where methodology_year = 2024
GO
delete from tGrouper_CACS_Interaction_Block where methodology_year = 2024
GO
delete from tGrouper_CACS_Interaction_RIW where methodology_year = 2024
GO
delete from tGrouper_CACS_Inv_Tech_Category where methodology_year = 2024
GO
delete from tGrouper_CACS_Inv_Tech_Category_Assign where methodology_year = 2024
GO
delete from tGrouper_CACS_Inv_Tech_RIW where methodology_year = 2024
GO
delete from tGrouper_CACS_Logic where methodology_year = 2024
GO
delete from tGrouper_CACS_Logic_Type where methodology_year = 2024
GO
delete from tGrouper_CACS_MAC where methodology_year = 2024
GO
delete from tGrouper_CACS_Partition where methodology_year = 2024
GO
delete from tGrouper_CACS_Rehab_Program_Aarea_Assign where methodology_year = 2024
GO
delete from tGrouper_CACS_Split where methodology_year = 2024
GO
delete from tGrouper_CACS_Split_Interv where methodology_year = 2024
GO
delete from tGrouper_CACS_SPLIT_MODE_OF_VISIT where methodology_year = 2024
GO
delete from tGrouper_CACS_Split_Type where methodology_year = 2024
GO
delete from tGrouper_CACS_Split_Visit_Disp where methodology_year = 2024
GO
delete from tGrouper_CMG_ATYPICAL_RIW where METHODOLOGY_YEAR = 2024
GO
delete from tGrouper_CMG_ATYPICAL_RIW_2015 where METHODOLOGY_YEAR = 2024
GO
delete from tGrouper_CMG_BASE_ELOS_RIW where methodology_year = 2024
GO
delete from tGrouper_CMG_BRANCH_LOGIC where methodology_year = 2024
GO
delete from tGrouper_CMG_CMG where methodology_year = 2024
GO
delete from tGrouper_CMG_CMG_AGE_CATEGORY where methodology_year = 2024
GO
delete from tGrouper_CMG_CMG_AGE_CATEGORY_ASSIGN where methodology_year = 2024
GO
delete from tGrouper_CMG_CMG_LOGIC where methodology_year = 2024
GO
delete from tGrouper_CMG_CMG_SPLIT where methodology_year = 2024
GO
delete from tGrouper_CMG_CMG_SPLIT_ANAESTHETIC where methodology_year = 2024
GO
delete from tGrouper_CMG_CMG_SPLIT_DIAG where methodology_year = 2024
GO
delete from tGrouper_CMG_CMG_SPLIT_ENTRY where methodology_year = 2024
GO
delete from tGrouper_CMG_CMG_SPLIT_GESTATION where methodology_year = 2024
GO
delete from tGrouper_CMG_CMG_SPLIT_INTERV where methodology_year = 2024
GO
delete from tGrouper_CMG_CMG_WEIGHT_ASSIGN where methodology_year = 2024
GO
delete from tGrouper_CMG_COMORBIDITY_CNT where methodology_year = 2024
GO
delete from tGrouper_CMG_COMORBIDITY_CNT_FACTOR_ASSIGN where methodology_year = 2024
GO
delete from tGrouper_CMG_COMORBIDITY_ELOS_RIW where methodology_year = 2024
GO
delete from tGrouper_CMG_COMORBIDITY_EXCLUSION_PAIR where methodology_year = 2024
GO
delete from tGrouper_CMG_COMORBIDITY_FACTOR_ASSIGN where methodology_year = 2024
GO
delete from tGrouper_CMG_COMORBIDITY_INTERACTION where methodology_year = 2024
GO
delete from tGrouper_CMG_COMORBIDITY_LEVEL where methodology_year = 2024
GO
delete from tGrouper_CMG_COMORBIDITY_LEVEL_ASSIGN where methodology_year = 2024
GO
delete from tGrouper_CMG_EXCEPTION_BRANCH_ASSIGN where methodology_year = 2024
GO
delete from tGrouper_CMG_FACTOR_INTERACTION_ELOS_RIW_2011 where methodology_year = 2024
GO
delete from tGrouper_CMG_FLAGGED_INTERV_ASSIGN where methodology_year = 2024
GO
delete from tGrouper_CMG_FLAGGED_INTERV_CATEGORY where methodology_year = 2024
GO
delete from tGrouper_CMG_FLAGGED_INTERV_ELOS_RIW_2015 where methodology_year = 2024
GO
delete from tGrouper_CMG_FLAGGED_INTERV_EXCLUSION where methodology_year = 2024
GO
delete from tGrouper_CMG_FLAGGED_INTERV_GROUP where methodology_year = 2024
GO
delete from tGrouper_CMG_FLAGGED_INTERV_STATUS where methodology_year = 2024
GO
delete from tGrouper_CMG_FLAGGED_INTERV_STATUS_ASSIGN where methodology_year = 2024
GO
delete from tGrouper_CMG_INPATIENT_CACS_EXCLUSION where methodology_year = 2024
GO
delete from tGrouper_CMG_INTERV_EVENT_CNT where methodology_year = 2024
GO
delete from tGrouper_CMG_INTERV_EVENT_ELOS_RIW where methodology_year = 2024
GO
delete from tGrouper_CMG_INTERV_OOH_ASSIGN where methodology_year = 2024
GO
delete from tGrouper_CMG_INTERV_OOH_CATEGORY where methodology_year = 2024
GO
delete from tGrouper_CMG_INTERV_OOH_RIW where methodology_year = 2024
GO
delete from tGrouper_CMG_IP_FACTOR_INTERACTION_BLOCK where methodology_year = 2024
GO
delete from tGrouper_CMG_IP_RI_LEVEL where methodology_year = 2024
GO
delete from tGrouper_CMG_IP_RIW_ATYPICAL where methodology_year = 2024
GO
delete from tGrouper_CMG_IP_RIW_ATYPICAL_TYPE where methodology_year = 2024
GO
delete from tGrouper_CMG_LOS_GROUP where methodology_year = 2024
GO
delete from tGrouper_CMG_LOS_PERCENTILE where methodology_year = 2024
GO
delete from tGrouper_CMG_MCC_ASSIGN where methodology_year = 2024
GO
delete from tGrouper_CMG_MCC_INTERV where methodology_year = 2024
GO
delete from tGrouper_CMG_MCC_INTERV_PARTITION where methodology_year = 2024
GO
delete from tGrouper_CMG_MCC_LOGIC where methodology_year = 2024
GO
delete from tGrouper_CMG_MCC_PARTITION where methodology_year = 2024
GO
delete from tGrouper_CMG_NEWBORN_BRANCH_ASSIGN where methodology_year = 2024
GO
delete from tGrouper_CMG_PERCENTILE where METHODOLOGY_YEAR = 2024
GO
delete from tGrouper_HIG where methodology_year = 2024
GO
delete from tGrouper_HIG_Age_Category where methodology_year = 2024
GO
delete from tGrouper_HIG_Age_ELOS_Weight where methodology_year = 2024
GO
delete from tGrouper_HIG_Atypical where methodology_year = 2024
GO
delete from tGrouper_HIG_Atypical_Percentile where methodology_year = 2024
GO
delete from tGrouper_HIG_Base_Elos_Weight where methodology_year = 2024
GO
delete from tGrouper_HIG_Branch_Logic where methodology_year = 2024
GO
delete from tGrouper_HIG_Event_Elos_Weight where methodology_year = 2024
GO
delete from tGrouper_HIG_FI_Elos_Weight where methodology_year = 2024
GO
delete from tGrouper_HIG_Logic where methodology_year = 2024
GO
delete from tGrouper_HIG_OOH_Assign where methodology_year = 2024
GO
delete from tGrouper_HIG_OOH_Factor where methodology_year = 2024
GO
delete from tGrouper_HIG_Split where methodology_year = 2024
GO
delete from tGrouper_HIG_Split_Diag where methodology_year = 2024
GO
delete from tGrouper_HIG_Split_Interv where methodology_year = 2024
GO
delete from tGrouper_HIG_Split_Type where methodology_year = 2024
GO
delete from tGrouper_PROV_CACS_BASE_RIW where methodology_year = 2024
GO
delete from tGrouper_PROV_CACS_INTERACTION_RIW where methodology_year = 2024
GO
delete from tGrouper_PROV_CACS_INV_TECH_RIW where methodology_year = 2024
GO
delete from [tGrouper_CACS_ED_CCI_INTERV] where [fiscal_year] = 2024
GO
delete from [tGrouper_CMG_HL_CMG] where methodology_year = 2024
GO
delete from [tGrouper_CMG_HL_CMG_ASSIGN] where methodology_year = 2024
GO
bulk insert tGrouper_CACS_Aanaesthetic from '<path>tGrouper_CACS_Aanaesthetic.txt' with ( fieldterminator = '|', rowterminator = '\n', CODEPAGE  = 'RAW' )  
GO
bulk insert tGrouper_CACS_Aanaesthetic_Assign from '<path>tGrouper_CACS_Aanaesthetic_Assign.txt' with ( fieldterminator = '|', rowterminator = '\n', CODEPAGE  = 'RAW' )  
GO
bulk insert tGrouper_CACS_Age_Category from '<path>tGrouper_CACS_Age_Category.txt' with ( fieldterminator = '|', rowterminator = '\n', CODEPAGE  = 'RAW' )  
GO
bulk insert tGrouper_CACS_Age_Category_Assign from '<path>tGrouper_CACS_Age_Category_Assign.txt' with ( fieldterminator = '|', rowterminator = '\n', CODEPAGE  = 'RAW' )  
GO
bulk insert tGrouper_CACS_Ambulatory_Diag_Assign from '<path>tGrouper_CACS_Ambulatory_Diag_Assign.txt' with ( fieldterminator = '|', rowterminator = '\n', CODEPAGE  = 'RAW' )  
GO
bulk insert tGrouper_CACS_Ambulatory_Interv_Assign from '<path>tGrouper_CACS_Ambulatory_Interv_Assign.txt' with ( fieldterminator = '|', rowterminator = '\n', CODEPAGE  = 'RAW' )  
GO
bulk insert tGrouper_CACS_ANAESTHETIC_ASSIGN from '<path>tGrouper_CACS_ANAESTHETIC_ASSIGN.txt' with ( fieldterminator = '|', rowterminator = '\n', CODEPAGE  = 'RAW' )  
GO
bulk insert tGrouper_CACS_Base_RIW from '<path>tGrouper_CACS_Base_RIW.txt' with ( fieldterminator = '|', rowterminator = '\n', CODEPAGE  = 'RAW' )  
GO
bulk insert tGrouper_CACS_Branch_Logic from '<path>tGrouper_CACS_Branch_Logic.txt' with ( fieldterminator = '|', rowterminator = '\n', CODEPAGE  = 'RAW' )  
GO
bulk insert tGrouper_CACS_CACS from '<path>tGrouper_CACS_CACS.txt' with ( fieldterminator = '|', rowterminator = '\n', CODEPAGE  = 'RAW' )  
GO
bulk insert tGrouper_CACS_ED_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
insert into tGrouper_CMG_MCC_PARTITION_Combination([MCC], [Partition], [CMG])
select a.mcc_code, a.mcc_partition_code, a.cmg_code
from (select distinct [mcc_code], [mcc_partition_code], [cmg_code] from [dbo].[tGrouper_CMG_CMG_LOGIC]) a
left join tGrouper_CMG_MCC_PARTITION_Combination b on a.mcc_code = b.MCC and a.mcc_partition_code = b.Partition and a.cmg_code = b.CMG
where b.CMG is null
order by a.mcc_code, a.mcc_partition_code, a.cmg_code
GO
--End Grouper Data
--Error code for DAD/NACRS
if OBJECT_ID('zz2024NACRSErros') > 0
    drop table zz2024NACRSErros
GO
Create table dbo.zz2024NACRSErros(
     ErrorNumber varchar(10), 
     ErrorMessage varchar(1000)
)
GO
bulk insert zz2024NACRSErros  from '<path>2024NACRSErrors.txt' with ( fieldterminator = '|', rowterminator = '\n' ) 
--bulk insert zz2024NACRSErros  from 'f:\2024\2024NACRSErrors.txt' with ( fieldterminator = '|', rowterminator = '\n', CODEPAGE = 'RAW' ) 
go
Delete from M_AmcareErrors where [BatchYear] = 2024
GO
Insert into M_AmcareErrors([ErrorNumber], [ErrorDescription], [IsError], [BatchYear])
select ErrorNumber, ErrorMessage, (case when ErrorNumber like 'W%' then 0 else 1 end), 2024
from zz2024NACRSErros
GO
if OBJECT_ID('zz2024NACRSErros') > 0
    drop table zz2024NACRSErros
GO
--
if OBJECT_ID('zz2024DADErros') > 0
    drop table zz2024DADErros
GO
Create table dbo.zz2024DADErros(
     Error varchar(10), 
     Groupnumber varchar(10), 
     Fieldnumber varchar(10), 
     Errornumber varchar(10), 
     ErrorMessage varchar(500),
     FrenchMessage varchar(500),
     report varchar(500)
)
GO
bulk insert zz2024DADErros  from '<path>2024DADErrors.txt' with ( fieldterminator = '|', rowterminator = '\n' ) 
--bulk insert zz2024DADErros  from 'f:\2024\2024DADErrors.txt' with ( fieldterminator = '|', rowterminator = '\n', CODEPAGE = 'RAW' ) 
go
Delete from [dbo].[M_DADErrors] where [BatchYear] = 2024
GO
alter table zz2024DADErros add code int identity(1, 1)
GO
delete e
--select e.*
from zz2024DADErros e
inner join
(select Error, Groupnumber, Fieldnumber, Errornumber, max(code) maxid, count(*) cc
from zz2024DADErros
group by Error, Groupnumber, Fieldnumber, Errornumber
having count(*) > 1) d
    on d.Error= e.Error and d.Groupnumber = e.Groupnumber and d.Fieldnumber = e.Fieldnumber and d.Errornumber = e.Errornumber 
where e.code <> d.maxid 
GO
Insert into [dbo].[M_DADErrors]([BatchYear], [ErrorNumber], [EnglishDescription], [FrenchDescription], [IsError])
select 2024,  ErrorNumber = Error + Groupnumber + Fieldnumber + Errornumber, ErrorMessage, FrenchMessage, (case when Error like 'W%' then 0 else 1 end)
from zz2024DADErros
GO
if OBJECT_ID('zz2024DADErros') > 0
    drop table zz2024DADErros
GO