-------------------------------------------------------------------------------------------------
--ED - 02468
if exists (select * from information_schema.tables where table_name = 'zzMISCode2012')
    drop table  zzMISCode2012
GO
if exists (select * from information_schema.tables where table_name = 'zzMISCode2012_new')
    drop table  zzMISCode2012_new
GO
Create table zzMISCode2012(
    FUNCTIONAL_CENTRE_CODE varchar(20),
    FUNCTIONAL_CENTRE_SERIES_CODE varchar(20),
    FUNCTIONAL_CENTRE_SERIES_DESC varchar(200),
    AMCARE_TYPE_CODE varchar(10),
    AMCARE_TYPE_DESC varchar(100),
    AMCARE_GROUP_CODE varchar(10),
    AMCARE_GROUP_DESC varchar(100)
)
GO
bulk insert zzMISCode2012 from '<path>MISCode2012.txt' with ( fieldterminator = '|', rowterminator = '\n', CODEPAGE  = 'RAW' ) 
GO
Update lu_miscode
set UserDescription = FUNCTIONAL_CENTRE_SERIES_DESC,
    DefaultDescription = FUNCTIONAL_CENTRE_SERIES_DESC
from zzMISCode2012 z
    inner join lu_miscode m on z.FUNCTIONAL_CENTRE_CODE = m.cihivalue
where isnull(m.AmCareTypeCode, '') <> ''
    and FUNCTIONAL_CENTRE_SERIES_DESC <> m.DefaultDescription
GO
Update lu_miscode
set ValidTo = '2012/03/31'
from zzMISCode2012 z
    right join lu_miscode m on z.FUNCTIONAL_CENTRE_CODE = m.cihivalue
where isnull(m.AmCareTypeCode, '') <> ''
    and z.FUNCTIONAL_CENTRE_CODE is null
    and m.validto >= '2012/04/01'
GO
select z.* into zzMISCode2012_new
from zzMISCode2012 z
    left join lu_miscode m on z.FUNCTIONAL_CENTRE_CODE = m.cihivalue and isnull(m.AmCareTypeCode, '') <> ''
where m.cihivalue is null
GO
alter table zzMISCode2012_new add code int identity(1, 1)
GO
declare @maxCode int
set @maxcode = (select max(code) from lu_miscode)
if isnull(@maxcode, 0) < 500000 set @maxcode = 500000
insert into dbo.LU_MISCode (Code, DisplayCode, UserDescription, DefaultDescription, CIHIValue, ValidFrom, ValidTo, InterfaceIn, HospitalID, LUText, AmCareTypeCode, AmCareGroup)   
select code + @maxcode, FUNCTIONAL_CENTRE_CODE, FUNCTIONAL_CENTRE_SERIES_DESC, FUNCTIONAL_CENTRE_SERIES_DESC, FUNCTIONAL_CENTRE_CODE,
    '2012/04/01', '2099/03/31', FUNCTIONAL_CENTRE_CODE, 0, '', AMCARE_TYPE_CODE, AMCARE_GROUP_CODE
from zzMISCode2012_new
GO
if exists (select * from information_schema.tables where table_name = 'zzMISCode2012')
    drop table  zzMISCode2012
GO
if exists (select * from information_schema.tables where table_name = 'zzMISCode2012_new')
    drop table  zzMISCode2012_new
GO

---------Grouper ------------------------
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tGrouper_CACS_Aanaesthetic]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tGrouper_CACS_Aanaesthetic]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tGrouper_CACS_Aanaesthetic_Assign]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tGrouper_CACS_Aanaesthetic_Assign]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tGrouper_CACS_Age]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tGrouper_CACS_Age]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tGrouper_CACS_Age_Category]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tGrouper_CACS_Age_Category]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tGrouper_CACS_Age_Category_Assign]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tGrouper_CACS_Age_Category_Assign]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tGrouper_CACS_Ambulatory_Diag_Assign]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tGrouper_CACS_Ambulatory_Diag_Assign]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tGrouper_CACS_Ambulatory_Interv_Assign]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tGrouper_CACS_Ambulatory_Interv_Assign]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tGrouper_CACS_Anaesthetic_Technique]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tGrouper_CACS_Anaesthetic_Technique]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tGrouper_CACS_Base_RIW]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tGrouper_CACS_Base_RIW]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tGrouper_CACS_Branch_Logic]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tGrouper_CACS_Branch_Logic]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tGrouper_CACS_CACS]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tGrouper_CACS_CACS]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tGrouper_CACS_ED_Diag_Assign]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tGrouper_CACS_ED_Diag_Assign]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tGrouper_CACS_ED_Interv_Assign]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tGrouper_CACS_ED_Interv_Assign]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tGrouper_CACS_Interaction_Block]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tGrouper_CACS_Interaction_Block]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tGrouper_CACS_Interaction_RIW]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tGrouper_CACS_Interaction_RIW]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tGrouper_CACS_Inv_Tech_Category]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tGrouper_CACS_Inv_Tech_Category]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tGrouper_CACS_Inv_Tech_Category_Assign]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tGrouper_CACS_Inv_Tech_Category_Assign]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tGrouper_CACS_Inv_Tech_RIW]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tGrouper_CACS_Inv_Tech_RIW]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tGrouper_CACS_Inv_Tech_cnt]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tGrouper_CACS_Inv_Tech_cnt]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tGrouper_CACS_Logic]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tGrouper_CACS_Logic]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tGrouper_CACS_Logic_Type]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tGrouper_CACS_Logic_Type]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tGrouper_CACS_MAC]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tGrouper_CACS_MAC]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tGrouper_CACS_PAC10]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tGrouper_CACS_PAC10]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tGrouper_CACS_PAC10_2007]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tGrouper_CACS_PAC10_2007]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tGrouper_CACS_PAC10_MITCode]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tGrouper_CACS_PAC10_MITCode]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tGrouper_CACS_Partition]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tGrouper_CACS_Partition]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tGrouper_CACS_Program_Area]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tGrouper_CACS_Program_Area]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tGrouper_CACS_Rehab_Program_Aarea_Assign]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tGrouper_CACS_Rehab_Program_Aarea_Assign]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tGrouper_CACS_Split]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tGrouper_CACS_Split]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tGrouper_CACS_Split_Interv]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tGrouper_CACS_Split_Interv]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tGrouper_CACS_Split_Type]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tGrouper_CACS_Split_Type]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tGrouper_CMG_AGE]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tGrouper_CMG_AGE]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tGrouper_CMG_ANESTHETIC_TECHNIQUE]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tGrouper_CMG_ANESTHETIC_TECHNIQUE]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tGrouper_CMG_ATYPICAL_RIW]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tGrouper_CMG_ATYPICAL_RIW]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tGrouper_CMG_BASE_ELOS_RIW]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tGrouper_CMG_BASE_ELOS_RIW]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tGrouper_CMG_BRANCH_LOGIC]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tGrouper_CMG_BRANCH_LOGIC]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tGrouper_CMG_CLASSIFICATION]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tGrouper_CMG_CLASSIFICATION]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tGrouper_CMG_CMG]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tGrouper_CMG_CMG]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tGrouper_CMG_CMG_AGE_CATEGORY]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tGrouper_CMG_CMG_AGE_CATEGORY]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tGrouper_CMG_CMG_AGE_CATEGORY_ASSIGN]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tGrouper_CMG_CMG_AGE_CATEGORY_ASSIGN]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tGrouper_CMG_CMG_LOGIC]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tGrouper_CMG_CMG_LOGIC]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tGrouper_CMG_CMG_SPLIT]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tGrouper_CMG_CMG_SPLIT]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tGrouper_CMG_CMG_SPLIT_ANAESTHETIC]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tGrouper_CMG_CMG_SPLIT_ANAESTHETIC]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tGrouper_CMG_CMG_SPLIT_DIAG]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tGrouper_CMG_CMG_SPLIT_DIAG]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tGrouper_CMG_CMG_SPLIT_ENTRY]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tGrouper_CMG_CMG_SPLIT_ENTRY]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tGrouper_CMG_CMG_SPLIT_GESTATION]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tGrouper_CMG_CMG_SPLIT_GESTATION]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tGrouper_CMG_CMG_SPLIT_INTERV]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tGrouper_CMG_CMG_SPLIT_INTERV]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tGrouper_CMG_CMG_SPLIT_TYPE]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tGrouper_CMG_CMG_SPLIT_TYPE]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tGrouper_CMG_CMG_WEIGHT_ASSIGN]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tGrouper_CMG_CMG_WEIGHT_ASSIGN]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tGrouper_CMG_CMG_WEIGHT_CATEGORY]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tGrouper_CMG_CMG_WEIGHT_CATEGORY]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tGrouper_CMG_COMORBIDITY_ASSIGN]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tGrouper_CMG_COMORBIDITY_ASSIGN]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tGrouper_CMG_COMORBIDITY_CNT]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tGrouper_CMG_COMORBIDITY_CNT]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tGrouper_CMG_COMORBIDITY_CNT_FACTOR_ASSIGN]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tGrouper_CMG_COMORBIDITY_CNT_FACTOR_ASSIGN]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tGrouper_CMG_COMORBIDITY_ELOS_RIW]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tGrouper_CMG_COMORBIDITY_ELOS_RIW]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tGrouper_CMG_COMORBIDITY_EXCLUSION_PAIR]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tGrouper_CMG_COMORBIDITY_EXCLUSION_PAIR]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tGrouper_CMG_COMORBIDITY_FACTOR_ASSIGN]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tGrouper_CMG_COMORBIDITY_FACTOR_ASSIGN]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tGrouper_CMG_COMORBIDITY_FI_EXCLUSION]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tGrouper_CMG_COMORBIDITY_FI_EXCLUSION]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tGrouper_CMG_COMORBIDITY_INTERACTION]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tGrouper_CMG_COMORBIDITY_INTERACTION]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tGrouper_CMG_COMORBIDITY_LEVEL]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tGrouper_CMG_COMORBIDITY_LEVEL]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tGrouper_CMG_COMORBIDITY_LEVEL_ASSIGN]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tGrouper_CMG_COMORBIDITY_LEVEL_ASSIGN]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tGrouper_CMG_COMORBIDITY_RULE]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tGrouper_CMG_COMORBIDITY_RULE]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tGrouper_CMG_DAGGER_ASTERISK]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tGrouper_CMG_DAGGER_ASTERISK]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tGrouper_CMG_DPG]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tGrouper_CMG_DPG]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tGrouper_CMG_DPG_ANESTHETIC_EXCLUSION]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tGrouper_CMG_DPG_ANESTHETIC_EXCLUSION]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tGrouper_CMG_DPG_ASSIGN]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tGrouper_CMG_DPG_ASSIGN]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tGrouper_CMG_DPG_SPLIT_ANESTHETIC]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tGrouper_CMG_DPG_SPLIT_ANESTHETIC]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tGrouper_CMG_ENTRY]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tGrouper_CMG_ENTRY]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tGrouper_CMG_EXCEPTION_BRANCH_ASSIGN]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tGrouper_CMG_EXCEPTION_BRANCH_ASSIGN]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tGrouper_CMG_FACTOR_INTERACTION_ELOS_RIW]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tGrouper_CMG_FACTOR_INTERACTION_ELOS_RIW]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tGrouper_CMG_FACTOR_INTERACTION_ELOS_RIW_2011]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tGrouper_CMG_FACTOR_INTERACTION_ELOS_RIW_2011]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tGrouper_CMG_FACTOR_INTERACTION_ELOS_RIW_OLD]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tGrouper_CMG_FACTOR_INTERACTION_ELOS_RIW_OLD]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tGrouper_CMG_FLAGGED_INTERV_ASSIGN]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tGrouper_CMG_FLAGGED_INTERV_ASSIGN]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tGrouper_CMG_FLAGGED_INTERV_CATEGORY]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tGrouper_CMG_FLAGGED_INTERV_CATEGORY]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tGrouper_CMG_FLAGGED_INTERV_CNT]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tGrouper_CMG_FLAGGED_INTERV_CNT]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tGrouper_CMG_FLAGGED_INTERV_ELOS_RIW]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tGrouper_CMG_FLAGGED_INTERV_ELOS_RIW]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tGrouper_CMG_FLAGGED_INTERV_EXCLUSION]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tGrouper_CMG_FLAGGED_INTERV_EXCLUSION]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tGrouper_CMG_INTERVENTION_EVENT_CNT]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tGrouper_CMG_INTERVENTION_EVENT_CNT]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tGrouper_CMG_INTERVENTION_OOH_CNT]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tGrouper_CMG_INTERVENTION_OOH_CNT]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tGrouper_CMG_INTERV_EVENT_CNT]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tGrouper_CMG_INTERV_EVENT_CNT]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tGrouper_CMG_INTERV_EVENT_ELOS_RIW]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tGrouper_CMG_INTERV_EVENT_ELOS_RIW]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tGrouper_CMG_INTERV_OOH_ASSIGN]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tGrouper_CMG_INTERV_OOH_ASSIGN]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tGrouper_CMG_INTERV_OOH_CATEGORY]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tGrouper_CMG_INTERV_OOH_CATEGORY]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tGrouper_CMG_INTERV_OOH_RIW]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tGrouper_CMG_INTERV_OOH_RIW]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tGrouper_CMG_IP_FACTOR_INTERACTION_BLOCK]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tGrouper_CMG_IP_FACTOR_INTERACTION_BLOCK]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tGrouper_CMG_IP_RIW_ATYPICAL]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tGrouper_CMG_IP_RIW_ATYPICAL]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tGrouper_CMG_IP_RIW_ATYPICAL_TYPE]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tGrouper_CMG_IP_RIW_ATYPICAL_TYPE]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tGrouper_CMG_IP_RI_LEVEL]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tGrouper_CMG_IP_RI_LEVEL]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tGrouper_CMG_LOGIC_TYPE]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tGrouper_CMG_LOGIC_TYPE]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tGrouper_CMG_LOS_GROUP]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tGrouper_CMG_LOS_GROUP]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tGrouper_CMG_LOS_PERCENTILE]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tGrouper_CMG_LOS_PERCENTILE]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tGrouper_CMG_MCC_ASSIGN]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tGrouper_CMG_MCC_ASSIGN]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tGrouper_CMG_MCC_INTERV]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tGrouper_CMG_MCC_INTERV]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tGrouper_CMG_MCC_INTERV_PARTITION]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tGrouper_CMG_MCC_INTERV_PARTITION]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tGrouper_CMG_MCC_LOGIC]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tGrouper_CMG_MCC_LOGIC]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tGrouper_CMG_MCC_PARTITION]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tGrouper_CMG_MCC_PARTITION]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tGrouper_CMG_MCC_PARTITION_Combination]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tGrouper_CMG_MCC_PARTITION_Combination]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tGrouper_CMG_NEWBORN_BRANCH_ASSIGN]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tGrouper_CMG_NEWBORN_BRANCH_ASSIGN]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tGrouper_CMG_PERCENTILE]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tGrouper_CMG_PERCENTILE]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tGrouper_CMG_PRIMARY_DPG]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tGrouper_CMG_PRIMARY_DPG]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tGrouper_CMG_classification_2008]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tGrouper_CMG_classification_2008]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tGrouper_PROV_CACS_BASE_RIW]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tGrouper_PROV_CACS_BASE_RIW]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tGrouper_PROV_CACS_INTERACTION_RIW]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tGrouper_PROV_CACS_INTERACTION_RIW]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tGrouper_PROV_CACS_INV_TECH_RIW]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tGrouper_PROV_CACS_INV_TECH_RIW]
GO

CREATE TABLE [dbo].[tGrouper_CACS_Aanaesthetic] (
	[methodology_year] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[cacs_anaesthetic_code] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[cacs_anaesthetic_e_desc] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[cacs_anaesthetic_f_desc] [varchar] (255) COLLATE Latin1_General_CI_AS NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[tGrouper_CACS_Aanaesthetic_Assign] (
	[methodology_year] [int] NULL ,
	[anaesthetic_technique_code] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[cacs_anaesthetic_code] [varchar] (255) COLLATE Latin1_General_CI_AS NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[tGrouper_CACS_Age] (
	[age_code] [char] (1) COLLATE Latin1_General_CI_AS NULL ,
	[age_num] [varchar] (10) COLLATE Latin1_General_CI_AS NULL ,
	[age_e_desc] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[age_f_desc] [varchar] (255) COLLATE Latin1_General_CI_AS NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[tGrouper_CACS_Age_Category] (
	[methodology_year] [int] NULL ,
	[cacs_age_category_code] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[cacs_age_category_e_desc] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[cacs_age_category_f_desc] [varchar] (255) COLLATE Latin1_General_CI_AS NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[tGrouper_CACS_Age_Category_Assign] (
	[methodology_year] [int] NULL ,
	[age_code] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[age_num] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[cacs_age_category_code] [varchar] (255) COLLATE Latin1_General_CI_AS NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[tGrouper_CACS_Ambulatory_Diag_Assign] (
	[methodology_year] [int] NULL ,
	[classification_code] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[logic_type_code] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[diag_code] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[gender_code] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[cacs_partition_code] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[branch_code] [varchar] (255) COLLATE Latin1_General_CI_AS NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[tGrouper_CACS_Ambulatory_Interv_Assign] (
	[methodology_year] [int] NULL ,
	[classification_code] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[logic_type_code] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[interv_code] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[status_attribute_code] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[location_attribute_code] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[extent_attribute_code] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[cacs_partition_code] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[branch_code] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[branch_rank] [varchar] (255) COLLATE Latin1_General_CI_AS NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[tGrouper_CACS_Anaesthetic_Technique] (
	[anaesthetic_technique_code] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[anaesthetic_technique_e_desc] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[anaesthetic_technique_f_desc] [varchar] (255) COLLATE Latin1_General_CI_AS NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[tGrouper_CACS_Base_RIW] (
	[methodology_year] [int] NULL ,
	[cacs_code] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[cacs_age_category_code] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[cacs_anaesthetic_code] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[cacs_base_riw] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[num_of_cases] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[print_suppression_ind_code] [varchar] (255) COLLATE Latin1_General_CI_AS NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[tGrouper_CACS_Branch_Logic] (
	[methodology_year] [int] NULL ,
	[logic_type_code] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[cacs_partition_code] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[branch_code] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[branch_rank] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[branch_long_e_desc] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[branch_long_f_desc] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[branch_short_e_desc] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[branch_short_f_desc] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[cacs_split1_code] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[cacs_split2_code] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[cacs_split3_code] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[cacs_split4_code] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[number_of_splits] [varchar] (255) COLLATE Latin1_General_CI_AS NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[tGrouper_CACS_CACS] (
	[methodology_year] [int] NULL ,
	[cacs_code] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[cacs_long_e_desc] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[cacs_long_f_desc] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[cacs_short_e_desc] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[cacs_short_f_desc] [varchar] (255) COLLATE Latin1_General_CI_AS NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[tGrouper_CACS_ED_Diag_Assign] (
	[methodology_year] [int] NULL ,
	[classification_code] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[logic_type_code] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[cacs_partition_code] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[diag_code] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[gender_code] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[branch_code] [varchar] (255) COLLATE Latin1_General_CI_AS NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[tGrouper_CACS_ED_Interv_Assign] (
	[methodology_year] [int] NULL ,
	[classification_code] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[logic_type_code] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[interv_code] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[status_attribute_code] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[location_attribute_code] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[extent_attribute_code] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[cacs_partition_code] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[branch_code] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[branch_rank] [varchar] (255) COLLATE Latin1_General_CI_AS NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[tGrouper_CACS_Interaction_Block] (
	[methodology_year] [int] NULL ,
	[block_num] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[variable1] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[variable2] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[variable3] [varchar] (255) COLLATE Latin1_General_CI_AS NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[tGrouper_CACS_Interaction_RIW] (
	[methodology_year] [int] NULL ,
	[block_num] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[cacs_code] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[value1] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[value2] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[value3] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[cacs_interaction_riw_factor] [varchar] (255) COLLATE Latin1_General_CI_AS NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[tGrouper_CACS_Inv_Tech_Category] (
	[methodology_year] [int] NULL ,
	[cacs_inv_tech_category_code] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[cacs_inv_tech_category_e_desc] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[cacs_inv_tech_category_f_desc] [varchar] (255) COLLATE Latin1_General_CI_AS NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[tGrouper_CACS_Inv_Tech_Category_Assign] (
	[methodology_year] [int] NULL ,
	[classification_code] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[interv_code] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[status_attribute_code] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[location_attribute_code] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[extent_attribute_code] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[cacs_inv_tech_category_code] [varchar] (255) COLLATE Latin1_General_CI_AS NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[tGrouper_CACS_Inv_Tech_RIW] (
	[methodology_year] [int] NULL ,
	[cacs_code] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[cacs_age_category_code] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[cacs_inv_tech_category_code] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[cacs_inv_tech_cnt_code] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[cacs_inv_tech_riw_factor] [varchar] (255) COLLATE Latin1_General_CI_AS NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[tGrouper_CACS_Inv_Tech_cnt] (
	[cacs_inv_tech_cnt_code] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[cacs_inv_tech_cnt_e_desc] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[cacs_inv_tech_cnt_f_desc] [varchar] (255) COLLATE Latin1_General_CI_AS NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[tGrouper_CACS_Logic] (
	[methodology_year] [int] NULL ,
	[logic_type_code] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[cacs_partition_code] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[branch_code] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[cacs_split1_code] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[cacs_split2_code] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[cacs_split3_code] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[cacs_split4_code] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[cacs_code] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[mac_code] [varchar] (255) COLLATE Latin1_General_CI_AS NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[tGrouper_CACS_Logic_Type] (
	[methodology_year] [int] NULL ,
	[logic_type_code] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[logic_type_e_desc] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[logic_type_f_desc] [varchar] (255) COLLATE Latin1_General_CI_AS NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[tGrouper_CACS_MAC] (
	[methodology_year] [int] NULL ,
	[mac_code] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[mac_long_e_desc] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[mac_long_f_desc] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[mac_short_e_desc] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[mac_short_f_desc] [varchar] (255) COLLATE Latin1_General_CI_AS NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[tGrouper_CACS_PAC10] (
	[CACS_Year] [varchar] (4) COLLATE Latin1_General_CI_AS NULL ,
	[CACS] [varchar] (4) COLLATE Latin1_General_CI_AS NULL ,
	[VisitDisposition] [varchar] (2) COLLATE Latin1_General_CI_AS NULL ,
	[CACSPAC10] [decimal](6, 4) NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[tGrouper_CACS_PAC10_2007] (
	[CACS_Year] [int] NOT NULL ,
	[CACS] [varchar] (4) COLLATE Latin1_General_CI_AS NOT NULL ,
	[VisitDisposition] [varchar] (2) COLLATE Latin1_General_CI_AS NOT NULL ,
	[MITCode] [int] NOT NULL ,
	[VisitType] [varchar] (2) COLLATE Latin1_General_CI_AS NOT NULL ,
	[LookupID] [varchar] (9) COLLATE Latin1_General_CI_AS NULL ,
	[CACSPAC10] [decimal](6, 4) NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[tGrouper_CACS_PAC10_MITCode] (
	[CACS_Year] [int] NOT NULL ,
	[InterventionCode] [varchar] (10) COLLATE Latin1_General_CI_AS NOT NULL ,
	[MITCode] [int] NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[tGrouper_CACS_Partition] (
	[methodology_year] [int] NULL ,
	[cacs_partition_code] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[cacs_partition_e_desc] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[cacs_partition_f_desc] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[cacs_partition_rank] [varchar] (255) COLLATE Latin1_General_CI_AS NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[tGrouper_CACS_Program_Area] (
	[program_area_code] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[program_area_e_desc] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[program_area_f_desc] [varchar] (255) COLLATE Latin1_General_CI_AS NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[tGrouper_CACS_Rehab_Program_Aarea_Assign] (
	[methodology_year] [int] NULL ,
	[logic_type_code] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[cacs_partition_code] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[program_area_code] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[branch_code] [varchar] (255) COLLATE Latin1_General_CI_AS NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[tGrouper_CACS_Split] (
	[methodology_year] [int] NULL ,
	[cacs_split_code] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[cacs_split_e_desc] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[cacs_split_f_desc] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[cacs_split_type_code] [varchar] (255) COLLATE Latin1_General_CI_AS NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[tGrouper_CACS_Split_Interv] (
	[methodology_year] [int] NULL ,
	[classification_code] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[cacs_split_code] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[interv_code] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[status_attribute_code] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[location_attribute_code] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[extent_attribute_code] [varchar] (255) COLLATE Latin1_General_CI_AS NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[tGrouper_CACS_Split_Type] (
	[methodology_year] [int] NULL ,
	[cacs_split_type_code] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[cacs_split_type_e_desc] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[cacs_split_type_f_desc] [varchar] (255) COLLATE Latin1_General_CI_AS NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[tGrouper_CMG_AGE] (
	[age_code] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[age_units] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[age_e_desc] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[age_f_desc] [varchar] (255) COLLATE Latin1_General_CI_AS NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[tGrouper_CMG_ANESTHETIC_TECHNIQUE] (
	[anesthetic_technique_code] [char] (1) COLLATE Latin1_General_CI_AS NULL ,
	[anesthetic_technique_e_desc] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[anesthetic_technique_f_desc] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[anesthetic_technique_rank] [varchar] (10) COLLATE Latin1_General_CI_AS NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[tGrouper_CMG_ATYPICAL_RIW] (
	[METHODOLOGY_YEAR] [int] NULL ,
	[MCC_PARTITION_CODE] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[CMG_AGE_CATEGORY_CODE] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[IP_RIW_ATYPICAL_CODE] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[LOS_GROUP_DAYS] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[ATYPICAL_RIW_FACTOR] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[ATYPICAL_PD_FACTOR] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[ATYPICAL_OPD_FACTOR] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[COMORBIDITY_LEVEL_CODE] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[FLAGGED_INTERV_CNT_CODE] [varchar] (255) COLLATE Latin1_General_CI_AS NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[tGrouper_CMG_BASE_ELOS_RIW] (
	[methodology_year] [int] NULL ,
	[cmg_code] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[cmg_age_category_code] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[base_elos_days] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[base_trim_days] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[base_ip_riw] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[base_pd] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[base_opd] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[dad_num_of_cases] [varchar] (255) COLLATE Latin1_General_CI_AS NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[tGrouper_CMG_BRANCH_LOGIC] (
	[methodology_year] [int] NULL ,
	[mcc_code] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[mcc_partition_code] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[mcc_subpartition_code] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[branch_code] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[branch_rank] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[number_of_splits] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[cmg_split1_code] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[cmg_split2_code] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[cmg_split3_code] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[cmg_split4_code] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[cmg_split5_code] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[cmg_split6_code] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[cmg_split7_code] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[branch_long_e_desc] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[branch_long_f_desc] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[branch_short_e_desc] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[branch_short_f_desc] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[exception_flag] [varchar] (255) COLLATE Latin1_General_CI_AS NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[tGrouper_CMG_CLASSIFICATION] (
	[classification_code] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[classification_e_desc] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[classification_f_desc] [varchar] (255) COLLATE Latin1_General_CI_AS NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[tGrouper_CMG_CMG] (
	[methodology_year] [int] NULL ,
	[cmg_code] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[cmg_long_e_desc] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[cmg_long_f_desc] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[cmg_short_e_desc] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[cmg_short_f_desc] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[comorbidity_assign_code] [varchar] (255) COLLATE Latin1_General_CI_AS NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[tGrouper_CMG_CMG_AGE_CATEGORY] (
	[methodology_year] [int] NULL ,
	[cmg_age_category_code] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[cmg_age_category_e_desc] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[cmg_age_category_f_desc] [varchar] (255) COLLATE Latin1_General_CI_AS NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[tGrouper_CMG_CMG_AGE_CATEGORY_ASSIGN] (
	[methodology_year] [int] NULL ,
	[mcc_code] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[age_code] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[age_units] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[cmg_age_category_code] [varchar] (255) COLLATE Latin1_General_CI_AS NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[tGrouper_CMG_CMG_LOGIC] (
	[methodology_year] [int] NULL ,
	[mcc_code] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[mcc_partition_code] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[mcc_subpartition_code] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[branch_code] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[cmg_split1_code] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[cmg_split2_code] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[cmg_split3_code] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[cmg_split4_code] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[cmg_split5_code] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[cmg_split6_code] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[cmg_split7_code] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[cmg_code] [varchar] (255) COLLATE Latin1_General_CI_AS NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[tGrouper_CMG_CMG_SPLIT] (
	[methodology_year] [int] NULL ,
	[cmg_split_code] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[cmg_split_e_desc] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[cmg_split_f_desc] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[cmg_split_type_code] [varchar] (255) COLLATE Latin1_General_CI_AS NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[tGrouper_CMG_CMG_SPLIT_ANAESTHETIC] (
	[methodology_year] [int] NULL ,
	[cmg_split_code] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[anaesthetic_technique_code] [varchar] (255) COLLATE Latin1_General_CI_AS NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[tGrouper_CMG_CMG_SPLIT_DIAG] (
	[methodology_year] [int] NULL ,
	[classification_code] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[cmg_split_code] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[diag_code] [varchar] (255) COLLATE Latin1_General_CI_AS NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[tGrouper_CMG_CMG_SPLIT_ENTRY] (
	[methodology_year] [int] NULL ,
	[cmg_split_code] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[entry_code] [varchar] (255) COLLATE Latin1_General_CI_AS NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[tGrouper_CMG_CMG_SPLIT_GESTATION] (
	[methodology_year] [int] NULL ,
	[cmg_split_code] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[gestational_age] [varchar] (255) COLLATE Latin1_General_CI_AS NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[tGrouper_CMG_CMG_SPLIT_INTERV] (
	[methodology_year] [int] NULL ,
	[classification_code] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[cmg_split_code] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[interv_code] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[status_attribute_code] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[location_attribute_code] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[extent_attribute_code] [varchar] (255) COLLATE Latin1_General_CI_AS NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[tGrouper_CMG_CMG_SPLIT_TYPE] (
	[cmg_split_type_code] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[cmg_split_type_e_desc] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[cmg_split_type_f_desc] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[code] [int] IDENTITY (1, 1) NOT NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[tGrouper_CMG_CMG_WEIGHT_ASSIGN] (
	[methodology_year] [int] NULL ,
	[weight_min] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[weight_max] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[cmg_weight_category_code] [varchar] (255) COLLATE Latin1_General_CI_AS NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[tGrouper_CMG_CMG_WEIGHT_CATEGORY] (
	[cmg_weight_category_code] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[cmg_weight_category_e_desc] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[cmg_weight_category_f_desc] [varchar] (255) COLLATE Latin1_General_CI_AS NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[tGrouper_CMG_COMORBIDITY_ASSIGN] (
	[comorbidity_assign_code] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[comorbidity_assign_e_desc] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[comorbidity_assign_f_desc] [varchar] (255) COLLATE Latin1_General_CI_AS NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[tGrouper_CMG_COMORBIDITY_CNT] (
	[methodology_year] [int] NULL ,
	[comorbidity_cnt_code] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[comorbidity_cnt_e_desc] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[comorbidity_cnt_f_desc] [varchar] (255) COLLATE Latin1_General_CI_AS NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[tGrouper_CMG_COMORBIDITY_CNT_FACTOR_ASSIGN] (
	[methodology_year] [int] NULL ,
	[mcc_code] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[comorbidity_cnt_code] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[comorbidity_cnt_factor] [varchar] (255) COLLATE Latin1_General_CI_AS NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[tGrouper_CMG_COMORBIDITY_ELOS_RIW] (
	[methodology_year] [int] NULL ,
	[cmg_code] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[cmg_age_category_code] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[comorbidity_level_code] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[comorbidity_elos_factor] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[comorbidity_ip_riw_factor] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[comorbidity_pd_factor] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[comorbidity_opd_factor] [varchar] (255) COLLATE Latin1_General_CI_AS NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[tGrouper_CMG_COMORBIDITY_EXCLUSION_PAIR] (
	[methodology_year] [int] NULL ,
	[classification_code] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[comorbidity_rule_code] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[comorbidity1_code] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[comorbidity2_code] [varchar] (255) COLLATE Latin1_General_CI_AS NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[tGrouper_CMG_COMORBIDITY_FACTOR_ASSIGN] (
	[methodology_year] [int] NULL ,
	[classification_code] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[mcc_code] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[diag_code] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[comorbidity_factor] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[comorbidity_exclusion_flag] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[comorbidity_interaction_flag] [varchar] (255) COLLATE Latin1_General_CI_AS NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[tGrouper_CMG_COMORBIDITY_FI_EXCLUSION] (
	[methodology_year] [int] NULL ,
	[classification_code] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[diag_code] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[flagged_interv_category_code] [varchar] (255) COLLATE Latin1_General_CI_AS NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[tGrouper_CMG_COMORBIDITY_INTERACTION] (
	[methodology_year] [int] NULL ,
	[classification_code] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[comorbidity_interaction1_code] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[comorbidity_interaction2_code] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[mcc_code] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[comorbidity_interaction_factor] [varchar] (255) COLLATE Latin1_General_CI_AS NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[tGrouper_CMG_COMORBIDITY_LEVEL] (
	[methodology_year] [int] NULL ,
	[comorbidity_level_code] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[comorbidity_level_e_desc] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[comorbidity_level_f_desc] [varchar] (255) COLLATE Latin1_General_CI_AS NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[tGrouper_CMG_COMORBIDITY_LEVEL_ASSIGN] (
	[methodology_year] [int] NULL ,
	[mcc_code] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[mcc_partition_code] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[comorbidity_factor_min] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[comorbidity_factor_max] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[comorbidity_level_code] [varchar] (255) COLLATE Latin1_General_CI_AS NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[tGrouper_CMG_COMORBIDITY_RULE] (
	[comorbidity_rule_code] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[comorbidity_rule_e_desc] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[comorbidity_rule_f_desc] [varchar] (255) COLLATE Latin1_General_CI_AS NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[tGrouper_CMG_DAGGER_ASTERISK] (
	[dagger_asterisk_code] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[dagger_asterisk_e_desc] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[dagger_asterisk_f_desc] [varchar] (255) COLLATE Latin1_General_CI_AS NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[tGrouper_CMG_DPG] (
	[methodology_year] [int] NULL ,
	[dpg_code] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[dpg_long_e_desc] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[dpg_long_f_desc] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[dpg_short_e_desc] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[dpg_short_f_desc] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[dpg_riw] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[dpg_return_code] [varchar] (255) COLLATE Latin1_General_CI_AS NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[tGrouper_CMG_DPG_ANESTHETIC_EXCLUSION] (
	[methodology_year] [int] NULL ,
	[classification_code] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[interv_code] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[anesthetic_technique_code] [varchar] (255) COLLATE Latin1_General_CI_AS NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[tGrouper_CMG_DPG_ASSIGN] (
	[methodology_year] [int] NULL ,
	[classification_code] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[interv_code] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[status_attribute_code] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[location_attribute_code] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[extent_attribute_code] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[primary_dpg_code] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[anesthetic_flag] [varchar] (255) COLLATE Latin1_General_CI_AS NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[tGrouper_CMG_DPG_SPLIT_ANESTHETIC] (
	[methodology_year] [int] NULL ,
	[primary_dpg_code] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[anesthetic_technique_code] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[dpg_code] [varchar] (255) COLLATE Latin1_General_CI_AS NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[tGrouper_CMG_ENTRY] (
	[entry_code] [char] (1) COLLATE Latin1_General_CI_AS NULL ,
	[entry_e_desc] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[entry_f_desc] [varchar] (255) COLLATE Latin1_General_CI_AS NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[tGrouper_CMG_EXCEPTION_BRANCH_ASSIGN] (
	[methodology_year] [int] NULL ,
	[mcc_code] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[classification_code] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[diag_code] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[exception_branch_code] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[dagger_asterisk_code] [varchar] (255) COLLATE Latin1_General_CI_AS NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[tGrouper_CMG_FACTOR_INTERACTION_ELOS_RIW] (
	[methodology_year] [int] NULL ,
	[block_num] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[rule_num] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[variable1] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[value1] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[variable2] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[value2] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[variable3] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[value3] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[variable4] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[value4] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[variable5] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[value5] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[variable6] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[value6] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[variable7] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[value7] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[variable8] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[value8] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[variable9] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[value9] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[interaction_elos_factor] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[interaction_ip_riw_factor] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[interaction_pd_factor] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[interaction_opd_factor] [varchar] (255) COLLATE Latin1_General_CI_AS NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[tGrouper_CMG_FACTOR_INTERACTION_ELOS_RIW_2011] (
	[methodology_year] [int] NULL ,
	[block_num] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[cmg_code] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[value1] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[value2] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[value3] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[interaction_elos_factor] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[interaction_ip_riw_factor] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[interaction_pd_factor] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[interaction_opd_factor] [varchar] (255) COLLATE Latin1_General_CI_AS NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[tGrouper_CMG_FACTOR_INTERACTION_ELOS_RIW_OLD] (
	[BLOCK_NUM] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[RULE_NUM] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[VARIABLE1] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[VALUE1] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[VARIABLE2] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[VALUE2] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[VARIABLE3] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[VALUE3] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[VARIABLE4] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[VALUE4] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[VARIABLE5] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[VALUE5] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[VARIABLE6] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[VALUE6] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[VARIABLE7] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[VALUE7] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[VARIABLE8] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[VALUE8] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[VARIABLE9] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[VALUE9] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[INTERACTION_ELOS_FACTOR] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[INTERACTION_IP_RIW_FACTOR] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[INTERACTION_PD_FACTOR] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[INTERACTION_OPD_FACTOR] [varchar] (255) COLLATE Latin1_General_CI_AS NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[tGrouper_CMG_FLAGGED_INTERV_ASSIGN] (
	[methodology_year] [int] NULL ,
	[classification_code] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[interv_code] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[status_attribute_code] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[location_attribute_code] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[extent_attribute_code] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[flagged_interv_category_code] [varchar] (255) COLLATE Latin1_General_CI_AS NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[tGrouper_CMG_FLAGGED_INTERV_CATEGORY] (
	[methodology_year] [int] NULL ,
	[flagged_interv_category_code] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[flagged_interv_category_e_desc] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[flagged_interv_category_f_desc] [varchar] (255) COLLATE Latin1_General_CI_AS NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[tGrouper_CMG_FLAGGED_INTERV_CNT] (
	[METHODOLOGY_YEAR] [int] NULL ,
	[FLAGGED_INTERV_CODE] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[FLAGGED_INTERV_E_DESC] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[FLAGGED_INTERV_F_DESC] [varchar] (255) COLLATE Latin1_General_CI_AS NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[tGrouper_CMG_FLAGGED_INTERV_ELOS_RIW] (
	[methodology_year] [int] NULL ,
	[cmg_code] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[cmg_age_category_code] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[flagged_interv_category_code] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[flagged_interv_elos_factor] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[flagged_interv_ip_riw_factor] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[flagged_interv_pd_factor] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[flagged_interv_opd_factor] [varchar] (255) COLLATE Latin1_General_CI_AS NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[tGrouper_CMG_FLAGGED_INTERV_EXCLUSION] (
	[methodology_year] [int] NULL ,
	[cmg_code] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[flagged_interv_category_code] [varchar] (255) COLLATE Latin1_General_CI_AS NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[tGrouper_CMG_INTERVENTION_EVENT_CNT] (
	[METHODOLOGY_YEAR] [int] NULL ,
	[INTERV_EVENT_CNT_CODE] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[INTERV_EVENT_CNT_E_DESC] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[INTERV_EVENT_CNT_F_DESC] [varchar] (255) COLLATE Latin1_General_CI_AS NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[tGrouper_CMG_INTERVENTION_OOH_CNT] (
	[METHODOLOGY_YEAR] [int] NULL ,
	[INTERV_OOH_CNT_CODE] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[INTERV_OOH_CNT_E_DESC] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[INTERV_OOH_CNT_F_DESC] [varchar] (255) COLLATE Latin1_General_CI_AS NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[tGrouper_CMG_INTERV_EVENT_CNT] (
	[methodology_year] [int] NULL ,
	[interv_event_cnt_code] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[interv_event_cnt_e_desc] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[interv_event_cnt_f_desc] [varchar] (255) COLLATE Latin1_General_CI_AS NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[tGrouper_CMG_INTERV_EVENT_ELOS_RIW] (
	[methodology_year] [int] NULL ,
	[cmg_code] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[cmg_age_category_code] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[interv_event_cnt_code] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[interv_event_elos_factor] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[interv_event_ip_riw_factor] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[interv_event_pd_factor] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[interv_event_opd_factor] [varchar] (255) COLLATE Latin1_General_CI_AS NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[tGrouper_CMG_INTERV_OOH_ASSIGN] (
	[methodology_year] [int] NULL ,
	[cmg_code] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[classification_code] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[interv_code] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[interv_ooh_category_code] [varchar] (255) COLLATE Latin1_General_CI_AS NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[tGrouper_CMG_INTERV_OOH_CATEGORY] (
	[methodology_year] [int] NULL ,
	[interv_ooh_category_code] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[interv_ooh_category_e_desc] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[interv_ooh_category_f_desc] [varchar] (255) COLLATE Latin1_General_CI_AS NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[tGrouper_CMG_INTERV_OOH_RIW] (
	[methodology_year] [int] NULL ,
	[cmg_code] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[cmg_age_category_code] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[interv_ooh_category_code] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[interv_ooh_ip_riw_factor] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[interv_ooh_pd_factor] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[interv_ooh_opd_factor] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[interv_ooh_elos_factor] [varchar] (255) COLLATE Latin1_General_CI_AS NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[tGrouper_CMG_IP_FACTOR_INTERACTION_BLOCK] (
	[methodology_year] [int] NULL ,
	[block_num] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[variable1] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[variable2] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[variable3] [varchar] (255) COLLATE Latin1_General_CI_AS NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[tGrouper_CMG_IP_RIW_ATYPICAL] (
	[ip_riw_atypical_code] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[ip_riw_atypical_e_desc] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[ip_riw_atypical_f_desc] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[methodology_year] [varchar] (255) COLLATE Latin1_General_CI_AS NOT NULL ,
	[ip_riw_atypical_type_code] [varchar] (255) COLLATE Latin1_General_CI_AS NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[tGrouper_CMG_IP_RIW_ATYPICAL_TYPE] (
	[methodology_year] [int] NULL ,
	[ip_riw_atypical_type_code] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[ip_riw_atypical_type_e_desc] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[ip_riw_atypical_type_f_desc] [varchar] (255) COLLATE Latin1_General_CI_AS NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[tGrouper_CMG_IP_RI_LEVEL] (
	[methodology_year] [int] NULL ,
	[ip_ri_total_factor_min] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[ip_ri_total_factor_max] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[ip_ri_level_code] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[ip_ri_level_e_desc] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[ip_ri_level_f_desc] [varchar] (255) COLLATE Latin1_General_CI_AS NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[tGrouper_CMG_LOGIC_TYPE] (
	[logic_type_code] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[logic_type_e_desc] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[logic_type_f_desc] [varchar] (255) COLLATE Latin1_General_CI_AS NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[tGrouper_CMG_LOS_GROUP] (
	[methodology_year] [int] NULL ,
	[mcc_partition_code] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[ip_riw_atypical_code] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[total_los_days_min] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[total_los_days_max] [varchar] (255) COLLATE Latin1_General_CI_AS NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[tGrouper_CMG_LOS_PERCENTILE] (
	[methodology_year] [int] NULL ,
	[cmg_code] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[ip_ri_level_code] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[dad_num_of_cases] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[average_acute_los] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[acute_los_10_percentile] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[acute_los_25_percentile] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[acute_los_50_percentile] [varchar] (255) COLLATE Latin1_General_CI_AS NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[tGrouper_CMG_MCC_ASSIGN] (
	[methodology_year] [int] NULL ,
	[classification_code] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[diag_code] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[gender_code] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[mcc_code] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[branch_code] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[mcc_subpartition_code] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[dagger_asterisk_code] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[hiv_flag] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[mcc_partition_code] [varchar] (255) COLLATE Latin1_General_CI_AS NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[tGrouper_CMG_MCC_INTERV] (
	[methodology_year] [int] NULL ,
	[mcc_code] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[mcc_subpartition_code] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[classification_code] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[interv_code] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[status_attribute_code] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[location_attribute_code] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[extent_attribute_code] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[branch_code] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[mcc_partition_code] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[branch_rank] [varchar] (255) COLLATE Latin1_General_CI_AS NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[tGrouper_CMG_MCC_INTERV_PARTITION] (
	[methodology_year] [int] NULL ,
	[classification_code] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[interv_code] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[status_attribute_code] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[location_attribute_code] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[extent_attribute_code] [varchar] (255) COLLATE Latin1_General_CI_AS NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[tGrouper_CMG_MCC_LOGIC] (
	[methodology_year] [int] NULL ,
	[mcc_code] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[mcc_long_e_desc] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[mcc_long_f_desc] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[mcc_short_e_desc] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[mcc_short_f_desc] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[logic_type_code] [varchar] (255) COLLATE Latin1_General_CI_AS NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[tGrouper_CMG_MCC_PARTITION] (
	[methodology_year] [int] NULL ,
	[mcc_partition_code] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[mcc_partition_e_desc] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[mcc_partition_f_desc] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[mcc_partition_seq_num] [varchar] (255) COLLATE Latin1_General_CI_AS NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[tGrouper_CMG_MCC_PARTITION_Combination] (
	[code] [int] NOT NULL ,
	[MCC] [varchar] (2) COLLATE Latin1_General_CI_AS NULL ,
	[Partition] [varchar] (1) COLLATE Latin1_General_CI_AS NULL ,
	[CMG] [varchar] (3) COLLATE Latin1_General_CI_AS NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[tGrouper_CMG_NEWBORN_BRANCH_ASSIGN] (
	[methodology_year] [int] NULL ,
	[mcc_code] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[mcc_subpartition_code] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[cmg_weight_category_code] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[exception_branch_code] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[branch_code] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[mcc_partition_code] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[newborn_interv_flag] [varchar] (255) COLLATE Latin1_General_CI_AS NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[tGrouper_CMG_PERCENTILE] (
	[METHODOLOGY_YEAR] [int] NULL ,
	[CMG_CODE] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[IP_RI_LEVEL_CODE] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[VolumeCases] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[AverageAcuteLOS] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[CMGTenthPercent] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[CMGTwentyFifthPercent] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[CMGFiftiethPercent] [varchar] (255) COLLATE Latin1_General_CI_AS NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[tGrouper_CMG_PRIMARY_DPG] (
	[methodology_year] [int] NULL ,
	[primary_dpg_code] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[primary_dpg_long_e_desc] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[primary_dpg_long_f_desc] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[primary_dpg_short_e_desc] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[primary_dpg_short_f_desc] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[primary_dpg_rank] [varchar] (255) COLLATE Latin1_General_CI_AS NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[tGrouper_CMG_classification_2008] (
	[classification_code] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[classification_e_desc] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[classification_f_desc] [varchar] (255) COLLATE Latin1_General_CI_AS NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[tGrouper_PROV_CACS_BASE_RIW] (
	[methodology_year] [int] NULL ,
	[cacs_code] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[cacs_age_category_code] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[cacs_anaesthetic_code] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[prov_cacs_base_riw] [varchar] (255) COLLATE Latin1_General_CI_AS NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[tGrouper_PROV_CACS_INTERACTION_RIW] (
	[methodology_year] [int] NULL ,
	[block_num] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[cacs_code] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[value1] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[value2] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[value3] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[prov_cacs_interaction_factor] [varchar] (255) COLLATE Latin1_General_CI_AS NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[tGrouper_PROV_CACS_INV_TECH_RIW] (
	[methodology_year] [int] NULL ,
	[cacs_code] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[cacs_inv_tech_category_code] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[cacs_inv_tech_cnt_code] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[prov_cacs_inv_tech_riw_factor] [varchar] (255) COLLATE Latin1_General_CI_AS NULL 
) ON [PRIMARY]
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 from '<path>tGrouper_CACS_Age.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_Technique from '<path>tGrouper_CACS_Anaesthetic_Technique.txt' with ( fieldterminator = '|', rowterminator = '\n', CODEPAGE  = 'RAW' ) 
GO
bulk insert tGrouper_CACS_Base_RIW from '<path>tGrouper_CACS_Base_RIW.txt' with ( fieldterminator = '|', rowterminator = '\n', CODEPAGE  = 'RAW' ) 
GO
bulk insert tGrouper_CACS_Branch_Logic from '<path>tGrouper_CACS_Branch_Logic.txt' with ( fieldterminator = '|', rowterminator = '\n', CODEPAGE  = 'RAW' ) 
GO
bulk insert tGrouper_CACS_CACS from '<path>tGrouper_CACS_CACS.txt' with ( fieldterminator = '|', rowterminator = '\n', CODEPAGE  = 'RAW' ) 
GO
bulk insert tGrouper_CACS_ED_Diag_Assign from '<path>tGrouper_CACS_ED_Diag_Assign.txt' with ( fieldterminator = '|', rowterminator = '\n', CODEPAGE  = 'RAW' ) 
GO
bulk insert tGrouper_CACS_ED_Interv_Assign from '<path>tGrouper_CACS_ED_Interv_Assign.txt' with ( fieldterminator = '|', rowterminator = '\n', CODEPAGE  = 'RAW' ) 
GO
bulk insert tGrouper_CACS_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_cnt from '<path>tGrouper_CACS_Inv_Tech_cnt.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_PAC10 from '<path>tGrouper_CACS_PAC10.txt' with ( fieldterminator = '|', rowterminator = '\n', CODEPAGE  = 'RAW' ) 
GO
bulk insert tGrouper_CACS_PAC10_2007 from '<path>tGrouper_CACS_PAC10_2007.txt' with ( fieldterminator = '|', rowterminator = '\n', CODEPAGE  = 'RAW' ) 
GO
bulk insert tGrouper_CACS_PAC10_MITCode from '<path>tGrouper_CACS_PAC10_MITCode.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_Program_Area from '<path>tGrouper_CACS_Program_Area.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_CMG_AGE from '<path>tGrouper_CMG_AGE.txt' with ( fieldterminator = '|', rowterminator = '\n', CODEPAGE  = 'RAW' ) 
GO
bulk insert tGrouper_CMG_ANESTHETIC_TECHNIQUE from '<path>tGrouper_CMG_ANESTHETIC_TECHNIQUE.txt' with ( fieldterminator = '|', rowterminator = '\n', CODEPAGE  = 'RAW' ) 
GO
bulk insert tGrouper_CMG_ATYPICAL_RIW from '<path>tGrouper_CMG_ATYPICAL_RIW.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_CLASSIFICATION from '<path>tGrouper_CMG_CLASSIFICATION.txt' with ( fieldterminator = '|', rowterminator = '\n', CODEPAGE  = 'RAW' ) 
GO
bulk insert tGrouper_CMG_classification_2008 from '<path>tGrouper_CMG_classification_2008.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_SPLIT_TYPE from '<path>tGrouper_CMG_CMG_SPLIT_TYPE.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_CMG_WEIGHT_CATEGORY from '<path>tGrouper_CMG_CMG_WEIGHT_CATEGORY.txt' with ( fieldterminator = '|', rowterminator = '\n', CODEPAGE  = 'RAW' ) 
GO
bulk insert tGrouper_CMG_COMORBIDITY_ASSIGN from '<path>tGrouper_CMG_COMORBIDITY_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_FI_EXCLUSION from '<path>tGrouper_CMG_COMORBIDITY_FI_EXCLUSION.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_COMORBIDITY_RULE from '<path>tGrouper_CMG_COMORBIDITY_RULE.txt' with ( fieldterminator = '|', rowterminator = '\n', CODEPAGE  = 'RAW' ) 
GO
bulk insert tGrouper_CMG_DAGGER_ASTERISK from '<path>tGrouper_CMG_DAGGER_ASTERISK.txt' with ( fieldterminator = '|', rowterminator = '\n', CODEPAGE  = 'RAW' ) 
GO
bulk insert tGrouper_CMG_DPG from '<path>tGrouper_CMG_DPG.txt' with ( fieldterminator = '|', rowterminator = '\n', CODEPAGE  = 'RAW' ) 
GO
bulk insert tGrouper_CMG_DPG_ANESTHETIC_EXCLUSION from '<path>tGrouper_CMG_DPG_ANESTHETIC_EXCLUSION.txt' with ( fieldterminator = '|', rowterminator = '\n', CODEPAGE  = 'RAW' ) 
GO
bulk insert tGrouper_CMG_DPG_ASSIGN from '<path>tGrouper_CMG_DPG_ASSIGN.txt' with ( fieldterminator = '|', rowterminator = '\n', CODEPAGE  = 'RAW' ) 
GO
bulk insert tGrouper_CMG_DPG_SPLIT_ANESTHETIC from '<path>tGrouper_CMG_DPG_SPLIT_ANESTHETIC.txt' with ( fieldterminator = '|', rowterminator = '\n', CODEPAGE  = 'RAW' ) 
GO
bulk insert tGrouper_CMG_ENTRY from '<path>tGrouper_CMG_ENTRY.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 from '<path>tGrouper_CMG_FACTOR_INTERACTION_ELOS_RIW.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_FACTOR_INTERACTION_ELOS_RIW_OLD from '<path>tGrouper_CMG_FACTOR_INTERACTION_ELOS_RIW_OLD.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_CNT from '<path>tGrouper_CMG_FLAGGED_INTERV_CNT.txt' with ( fieldterminator = '|', rowterminator = '\n', CODEPAGE  = 'RAW' ) 
GO
bulk insert tGrouper_CMG_FLAGGED_INTERV_ELOS_RIW from '<path>tGrouper_CMG_FLAGGED_INTERV_ELOS_RIW.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_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_INTERVENTION_EVENT_CNT from '<path>tGrouper_CMG_INTERVENTION_EVENT_CNT.txt' with ( fieldterminator = '|', rowterminator = '\n', CODEPAGE  = 'RAW' ) 
GO
bulk insert tGrouper_CMG_INTERVENTION_OOH_CNT from '<path>tGrouper_CMG_INTERVENTION_OOH_CNT.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_LOGIC_TYPE from '<path>tGrouper_CMG_LOGIC_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_MCC_PARTITION_Combination from '<path>tGrouper_CMG_MCC_PARTITION_Combination.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_CMG_PRIMARY_DPG from '<path>tGrouper_CMG_PRIMARY_DPG.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


 CREATE  INDEX [IX_tGrouper_CACS_Aanaesthetic] ON [dbo].[tGrouper_CACS_Aanaesthetic]([methodology_year], [cacs_anaesthetic_code]) WITH  FILLFACTOR = 90 ON [PRIMARY]
GO

 CREATE  INDEX [IX_tGrouper_CACS_Aanaesthetic_Assign] ON [dbo].[tGrouper_CACS_Aanaesthetic_Assign]([methodology_year], [anaesthetic_technique_code]) WITH  FILLFACTOR = 90 ON [PRIMARY]
GO

 CREATE  INDEX [IX_tGrouper_CACS_Age_Category] ON [dbo].[tGrouper_CACS_Age_Category]([methodology_year], [cacs_age_category_code]) WITH  FILLFACTOR = 90 ON [PRIMARY]
GO

 CREATE  INDEX [IX_tGrouper_CACS_Age_Category_Assign] ON [dbo].[tGrouper_CACS_Age_Category_Assign]([methodology_year], [age_code]) WITH  FILLFACTOR = 90 ON [PRIMARY]
GO

 CREATE  INDEX [IX_tGrouper_CACS_Ambulatory_Diag_Assign] ON [dbo].[tGrouper_CACS_Ambulatory_Diag_Assign]([methodology_year], [logic_type_code], [diag_code]) WITH  FILLFACTOR = 90 ON [PRIMARY]
GO

 CREATE  INDEX [IX_tGrouper_CACS_Ambulatory_Interv_Assign] ON [dbo].[tGrouper_CACS_Ambulatory_Interv_Assign]([methodology_year], [logic_type_code], [interv_code]) WITH  FILLFACTOR = 90 ON [PRIMARY]
GO

 CREATE  INDEX [IX_tGrouper_CACS_Anaesthetic_Technique] ON [dbo].[tGrouper_CACS_Anaesthetic_Technique]([anaesthetic_technique_code]) WITH  FILLFACTOR = 90 ON [PRIMARY]
GO

 CREATE  INDEX [IX_tGrouper_CACS_Base_RIW] ON [dbo].[tGrouper_CACS_Base_RIW]([methodology_year], [cacs_code], [cacs_age_category_code], [cacs_anaesthetic_code]) WITH  FILLFACTOR = 90 ON [PRIMARY]
GO

 CREATE  INDEX [IX_tGrouper_CACS_Branch_Logic] ON [dbo].[tGrouper_CACS_Branch_Logic]([methodology_year], [logic_type_code], [cacs_partition_code], [branch_code], [branch_rank]) WITH  FILLFACTOR = 90 ON [PRIMARY]
GO

 CREATE  INDEX [IX_tGrouper_CACS_CACS] ON [dbo].[tGrouper_CACS_CACS]([methodology_year], [cacs_code]) WITH  FILLFACTOR = 90 ON [PRIMARY]
GO

 CREATE  INDEX [IX_tGrouper_CACS_ED_Diag_Assign] ON [dbo].[tGrouper_CACS_ED_Diag_Assign]([methodology_year], [logic_type_code], [cacs_partition_code], [diag_code]) WITH  FILLFACTOR = 90 ON [PRIMARY]
GO

 CREATE  INDEX [IX_tGrouper_CACS_ED_Interv_Assign] ON [dbo].[tGrouper_CACS_ED_Interv_Assign]([methodology_year], [logic_type_code], [interv_code]) WITH  FILLFACTOR = 90 ON [PRIMARY]
GO

 CREATE  INDEX [IX_tGrouper_CACS_Interaction_RIW] ON [dbo].[tGrouper_CACS_Interaction_RIW]([methodology_year], [block_num], [cacs_code]) WITH  FILLFACTOR = 90 ON [PRIMARY]
GO

 CREATE  INDEX [IX_tGrouper_CACS_Inv_Tech_Category] ON [dbo].[tGrouper_CACS_Inv_Tech_Category]([methodology_year], [cacs_inv_tech_category_code]) WITH  FILLFACTOR = 90 ON [PRIMARY]
GO

 CREATE  INDEX [IX_tGrouper_CACS_Inv_Tech_Category_Assign] ON [dbo].[tGrouper_CACS_Inv_Tech_Category_Assign]([methodology_year], [interv_code]) WITH  FILLFACTOR = 90 ON [PRIMARY]
GO

 CREATE  INDEX [IX_tGrouper_CACS_Inv_Tech_RIW] ON [dbo].[tGrouper_CACS_Inv_Tech_RIW]([methodology_year], [cacs_code], [cacs_age_category_code], [cacs_inv_tech_category_code], [cacs_inv_tech_cnt_code]) WITH  FILLFACTOR = 90 ON [PRIMARY]
GO

 CREATE  INDEX [IX_tGrouper_CACS_Logic] ON [dbo].[tGrouper_CACS_Logic]([methodology_year], [logic_type_code], [cacs_partition_code], [branch_code]) WITH  FILLFACTOR = 90 ON [PRIMARY]
GO

 CREATE  INDEX [IX_tGrouper_CACS_Logic_Type] ON [dbo].[tGrouper_CACS_Logic_Type]([methodology_year], [logic_type_code]) WITH  FILLFACTOR = 90 ON [PRIMARY]
GO

 CREATE  INDEX [IX_tGrouper_CACS_MAC] ON [dbo].[tGrouper_CACS_MAC]([methodology_year], [mac_code]) WITH  FILLFACTOR = 90 ON [PRIMARY]
GO

 CREATE  INDEX [IX_tGrouper_CACS_Partition] ON [dbo].[tGrouper_CACS_Partition]([methodology_year], [cacs_partition_code]) WITH  FILLFACTOR = 90 ON [PRIMARY]
GO

 CREATE  INDEX [IX_tGrouper_CACS_Rehab_Program_Aarea_Assign] ON [dbo].[tGrouper_CACS_Rehab_Program_Aarea_Assign]([methodology_year], [logic_type_code], [cacs_partition_code], [program_area_code]) WITH  FILLFACTOR = 90 ON [PRIMARY]
GO

 CREATE  INDEX [IX_tGrouper_CACS_Split] ON [dbo].[tGrouper_CACS_Split]([methodology_year], [cacs_split_code]) WITH  FILLFACTOR = 90 ON [PRIMARY]
GO

 CREATE  INDEX [IX_tGrouper_CACS_Split_Interv] ON [dbo].[tGrouper_CACS_Split_Interv]([methodology_year], [cacs_split_code], [interv_code]) ON [PRIMARY]
GO

 CREATE  INDEX [IX_tGrouper_CMG_ATYPICAL_RIW] ON [dbo].[tGrouper_CMG_ATYPICAL_RIW]([METHODOLOGY_YEAR], [MCC_PARTITION_CODE], [IP_RIW_ATYPICAL_CODE], [LOS_GROUP_DAYS]) ON [PRIMARY]
GO

 CREATE  INDEX [IX_tGrouper_CMG_BASE_ELOS_RIW] ON [dbo].[tGrouper_CMG_BASE_ELOS_RIW]([methodology_year], [cmg_code], [cmg_age_category_code]) ON [PRIMARY]
GO

 CREATE  INDEX [IX_tGrouper_CMG_BRANCH_LOGIC] ON [dbo].[tGrouper_CMG_BRANCH_LOGIC]([methodology_year], [mcc_code], [mcc_partition_code], [mcc_subpartition_code]) ON [PRIMARY]
GO

 CREATE  INDEX [IX_tGrouper_CMG_CMG] ON [dbo].[tGrouper_CMG_CMG]([methodology_year], [cmg_code]) ON [PRIMARY]
GO

 CREATE  INDEX [IX_tGrouper_CMG_CMG_AGE_CATEGORY] ON [dbo].[tGrouper_CMG_CMG_AGE_CATEGORY]([methodology_year], [cmg_age_category_code]) ON [PRIMARY]
GO

 CREATE  INDEX [IX_tGrouper_CMG_CMG_AGE_CATEGORY_ASSIGN] ON [dbo].[tGrouper_CMG_CMG_AGE_CATEGORY_ASSIGN]([methodology_year], [mcc_code], [age_code], [age_units]) ON [PRIMARY]
GO

 CREATE  INDEX [IX_tGrouper_CMG_CMG_LOGIC] ON [dbo].[tGrouper_CMG_CMG_LOGIC]([methodology_year], [mcc_code], [mcc_partition_code], [mcc_subpartition_code], [branch_code]) ON [PRIMARY]
GO

 CREATE  INDEX [IX_tGrouper_CMG_CMG_SPLIT] ON [dbo].[tGrouper_CMG_CMG_SPLIT]([methodology_year], [cmg_split_code]) ON [PRIMARY]
GO

 CREATE  INDEX [IX_tGrouper_CMG_CMG_SPLIT_ANAESTHETIC] ON [dbo].[tGrouper_CMG_CMG_SPLIT_ANAESTHETIC]([methodology_year], [cmg_split_code]) ON [PRIMARY]
GO

 CREATE  INDEX [IX_tGrouper_CMG_CMG_SPLIT_DIAG] ON [dbo].[tGrouper_CMG_CMG_SPLIT_DIAG]([methodology_year], [cmg_split_code], [diag_code]) ON [PRIMARY]
GO

 CREATE  INDEX [IX_tGrouper_CMG_CMG_SPLIT_ENTRY] ON [dbo].[tGrouper_CMG_CMG_SPLIT_ENTRY]([methodology_year], [cmg_split_code], [entry_code]) ON [PRIMARY]
GO

 CREATE  INDEX [IX_tGrouper_CMG_CMG_SPLIT_GESTATION] ON [dbo].[tGrouper_CMG_CMG_SPLIT_GESTATION]([methodology_year], [cmg_split_code], [gestational_age]) ON [PRIMARY]
GO

 CREATE  INDEX [IX_tGrouper_CMG_CMG_SPLIT_INTERV] ON [dbo].[tGrouper_CMG_CMG_SPLIT_INTERV]([methodology_year], [classification_code], [cmg_split_code], [interv_code]) ON [PRIMARY]
GO

 CREATE  INDEX [IX_tGrouper_CMG_CMG_WEIGHT_ASSIGN] ON [dbo].[tGrouper_CMG_CMG_WEIGHT_ASSIGN]([methodology_year], [weight_min], [weight_max]) ON [PRIMARY]
GO

 CREATE  INDEX [IX_tGrouper_CMG_COMORBIDITY_CNT_FACTOR_ASSIGN] ON [dbo].[tGrouper_CMG_COMORBIDITY_CNT_FACTOR_ASSIGN]([methodology_year], [mcc_code], [comorbidity_cnt_code]) ON [PRIMARY]
GO

 CREATE  INDEX [IX_tGrouper_CMG_COMORBIDITY_ELOS_RIW] ON [dbo].[tGrouper_CMG_COMORBIDITY_ELOS_RIW]([methodology_year], [cmg_code], [cmg_age_category_code], [comorbidity_level_code]) ON [PRIMARY]
GO

 CREATE  INDEX [IX_tGrouper_CMG_COMORBIDITY_EXCLUSION_PAIR] ON [dbo].[tGrouper_CMG_COMORBIDITY_EXCLUSION_PAIR]([methodology_year], [comorbidity_rule_code], [comorbidity1_code], [comorbidity2_code]) ON [PRIMARY]
GO

 CREATE  INDEX [IX_tGrouper_CMG_COMORBIDITY_FACTOR_ASSIGN] ON [dbo].[tGrouper_CMG_COMORBIDITY_FACTOR_ASSIGN]([methodology_year], [mcc_code], [diag_code]) ON [PRIMARY]
GO

 CREATE  INDEX [IX_tGrouper_CMG_COMORBIDITY_FI_EXCLUSION] ON [dbo].[tGrouper_CMG_COMORBIDITY_FI_EXCLUSION]([methodology_year], [diag_code], [flagged_interv_category_code]) ON [PRIMARY]
GO

 CREATE  INDEX [IX_tGrouper_CMG_COMORBIDITY_INTERACTION] ON [dbo].[tGrouper_CMG_COMORBIDITY_INTERACTION]([methodology_year], [classification_code], [comorbidity_interaction1_code], [comorbidity_interaction2_code], [mcc_code]) ON [PRIMARY]
GO

 CREATE  INDEX [IX_tGrouper_CMG_COMORBIDITY_LEVEL] ON [dbo].[tGrouper_CMG_COMORBIDITY_LEVEL]([methodology_year], [comorbidity_level_code]) ON [PRIMARY]
GO

 CREATE  INDEX [IX_tGrouper_CMG_COMORBIDITY_LEVEL_ASSIGN] ON [dbo].[tGrouper_CMG_COMORBIDITY_LEVEL_ASSIGN]([methodology_year], [mcc_code]) ON [PRIMARY]
GO

 CREATE  INDEX [IX_tGrouper_CMG_EXCEPTION_BRANCH_ASSIGN] ON [dbo].[tGrouper_CMG_EXCEPTION_BRANCH_ASSIGN]([methodology_year], [mcc_code], [classification_code], [diag_code]) ON [PRIMARY]
GO

 CREATE  INDEX [IX_tGrouper_CMG_FACTOR_INTERACTION_ELOS_RIW] ON [dbo].[tGrouper_CMG_FACTOR_INTERACTION_ELOS_RIW]([methodology_year], [block_num], [rule_num]) ON [PRIMARY]
GO

 CREATE  INDEX [IX_tGrouper_CMG_FACTOR_INTERACTION_ELOS_RIW_2011] ON [dbo].[tGrouper_CMG_FACTOR_INTERACTION_ELOS_RIW_2011]([methodology_year], [block_num], [cmg_code], [value1], [value2], [value3]) ON [PRIMARY]
GO

 CREATE  INDEX [IX_tGrouper_CMG_FLAGGED_INTERV_ASSIGN] ON [dbo].[tGrouper_CMG_FLAGGED_INTERV_ASSIGN]([methodology_year], [classification_code], [interv_code]) ON [PRIMARY]
GO

 CREATE  INDEX [IX_tGrouper_CMG_FLAGGED_INTERV_CATEGORY] ON [dbo].[tGrouper_CMG_FLAGGED_INTERV_CATEGORY]([methodology_year], [flagged_interv_category_code]) ON [PRIMARY]
GO

 CREATE  INDEX [IX_tGrouper_CMG_FLAGGED_INTERV_CNT] ON [dbo].[tGrouper_CMG_FLAGGED_INTERV_CNT]([METHODOLOGY_YEAR], [FLAGGED_INTERV_CODE]) ON [PRIMARY]
GO

 CREATE  INDEX [IX_tGrouper_CMG_FLAGGED_INTERV_ELOS_RIW] ON [dbo].[tGrouper_CMG_FLAGGED_INTERV_ELOS_RIW]([methodology_year], [cmg_code], [cmg_age_category_code], [flagged_interv_category_code]) ON [PRIMARY]
GO

 CREATE  INDEX [IX_tGrouper_CMG_FLAGGED_INTERV_EXCLUSION] ON [dbo].[tGrouper_CMG_FLAGGED_INTERV_EXCLUSION]([methodology_year], [cmg_code], [flagged_interv_category_code]) ON [PRIMARY]
GO

 CREATE  INDEX [IX_tGrouper_CMG_INTERVENTION_EVENT_CNT] ON [dbo].[tGrouper_CMG_INTERVENTION_EVENT_CNT]([METHODOLOGY_YEAR], [INTERV_EVENT_CNT_CODE]) ON [PRIMARY]
GO

 CREATE  INDEX [IX_tGrouper_CMG_INTERVENTION_OOH_CNT] ON [dbo].[tGrouper_CMG_INTERVENTION_OOH_CNT]([METHODOLOGY_YEAR], [INTERV_OOH_CNT_CODE]) ON [PRIMARY]
GO

 CREATE  INDEX [IX_tGrouper_CMG_INTERV_EVENT_CNT] ON [dbo].[tGrouper_CMG_INTERV_EVENT_CNT]([methodology_year], [interv_event_cnt_code]) ON [PRIMARY]
GO

 CREATE  INDEX [IX_tGrouper_CMG_INTERV_EVENT_ELOS_RIW] ON [dbo].[tGrouper_CMG_INTERV_EVENT_ELOS_RIW]([methodology_year], [cmg_code], [cmg_age_category_code], [interv_event_cnt_code]) ON [PRIMARY]
GO

 CREATE  INDEX [IX_tGrouper_CMG_INTERV_OOH_ASSIGN] ON [dbo].[tGrouper_CMG_INTERV_OOH_ASSIGN]([methodology_year], [cmg_code], [interv_code]) ON [PRIMARY]
GO

 CREATE  INDEX [IX_tGrouper_CMG_INTERV_OOH_CATEGORY] ON [dbo].[tGrouper_CMG_INTERV_OOH_CATEGORY]([methodology_year], [interv_ooh_category_code]) ON [PRIMARY]
GO

 CREATE  INDEX [IX_tGrouper_CMG_INTERV_OOH_RIW] ON [dbo].[tGrouper_CMG_INTERV_OOH_RIW]([methodology_year], [cmg_code], [cmg_age_category_code], [interv_ooh_category_code]) ON [PRIMARY]
GO

 CREATE  INDEX [IX_tGrouper_CMG_IP_FACTOR_INTERACTION_BLOCK] ON [dbo].[tGrouper_CMG_IP_FACTOR_INTERACTION_BLOCK]([methodology_year], [block_num]) ON [PRIMARY]
GO

 CREATE  INDEX [IX_tGrouper_CMG_IP_RIW_ATYPICAL_TYPE] ON [dbo].[tGrouper_CMG_IP_RIW_ATYPICAL_TYPE]([methodology_year], [ip_riw_atypical_type_code]) ON [PRIMARY]
GO

 CREATE  INDEX [IX_tGrouper_CMG_IP_RI_LEVEL] ON [dbo].[tGrouper_CMG_IP_RI_LEVEL]([methodology_year], [ip_ri_total_factor_min], [ip_ri_total_factor_max]) ON [PRIMARY]
GO

 CREATE  INDEX [IX_tGrouper_CMG_LOS_GROUP] ON [dbo].[tGrouper_CMG_LOS_GROUP]([methodology_year], [mcc_partition_code], [ip_riw_atypical_code]) ON [PRIMARY]
GO

 CREATE  INDEX [IX_tGrouper_CMG_LOS_PERCENTILE] ON [dbo].[tGrouper_CMG_LOS_PERCENTILE]([methodology_year], [cmg_code], [ip_ri_level_code]) ON [PRIMARY]
GO

 CREATE  INDEX [IX_tGrouper_CMG_MCC_ASSIGN] ON [dbo].[tGrouper_CMG_MCC_ASSIGN]([methodology_year], [classification_code], [diag_code], [gender_code]) ON [PRIMARY]
GO

 CREATE  INDEX [IX_tGrouper_CMG_MCC_INTERV] ON [dbo].[tGrouper_CMG_MCC_INTERV]([methodology_year], [mcc_code], [mcc_subpartition_code], [classification_code], [interv_code]) ON [PRIMARY]
GO

 CREATE  INDEX [IX_tGrouper_CMG_MCC_INTERV_PARTITION] ON [dbo].[tGrouper_CMG_MCC_INTERV_PARTITION]([methodology_year], [classification_code], [interv_code]) ON [PRIMARY]
GO

 CREATE  INDEX [IX_tGrouper_CMG_MCC_LOGIC] ON [dbo].[tGrouper_CMG_MCC_LOGIC]([methodology_year], [mcc_code]) ON [PRIMARY]
GO

 CREATE  INDEX [IX_tGrouper_CMG_MCC_PARTITION] ON [dbo].[tGrouper_CMG_MCC_PARTITION]([methodology_year], [mcc_partition_code]) ON [PRIMARY]
GO

 CREATE  INDEX [IX_tGrouper_CMG_NEWBORN_BRANCH_ASSIGN] ON [dbo].[tGrouper_CMG_NEWBORN_BRANCH_ASSIGN]([methodology_year], [mcc_code], [mcc_subpartition_code], [cmg_weight_category_code], [exception_branch_code]) ON [PRIMARY]
GO

 CREATE  INDEX [IX_tGrouper_CMG_PERCENTILE_Key] ON [dbo].[tGrouper_CMG_PERCENTILE]([METHODOLOGY_YEAR], [CMG_CODE], [IP_RI_LEVEL_CODE]) WITH  FILLFACTOR = 90 ON [PRIMARY]
GO

 CREATE  INDEX [IX_tGrouper_CMG_PERCENTILE] ON [dbo].[tGrouper_CMG_PERCENTILE]([METHODOLOGY_YEAR], [CMG_CODE], [IP_RI_LEVEL_CODE]) ON [PRIMARY]
GO

 CREATE  INDEX [IX_tGrouper_PROV_CACS_BASE_RIW] ON [dbo].[tGrouper_PROV_CACS_BASE_RIW]([methodology_year], [cacs_code], [cacs_age_category_code], [cacs_anaesthetic_code]) ON [PRIMARY]
GO

 CREATE  INDEX [IX_tGrouper_PROV_CACS_INTERACTION_RIW] ON [dbo].[tGrouper_PROV_CACS_INTERACTION_RIW]([methodology_year], [block_num], [cacs_code]) ON [PRIMARY]
GO

 CREATE  INDEX [IX_tGrouper_PROV_CACS_INV_TECH_RIW] ON [dbo].[tGrouper_PROV_CACS_INV_TECH_RIW]([methodology_year], [cacs_code], [cacs_inv_tech_category_code], [cacs_inv_tech_cnt_code]) ON [PRIMARY]
GO



------------End grouper --------------------------------------

---ICD Tables -------------
--- ***** Remember to keep the customer code validated. Do not touch customer code.
--ED - 02403
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_Desc2012_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_Desc2012_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
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
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'
from zzICD10_diag z
	inner join ICD_diagFile10 Icd on z.CIHIValue = ICD.CIHIValue
where icd.validto < '2012/04/01'
GO
update ICD_ProcFile10
set ValidTo = '2099/03/31'
from zzICD10_CCI z
	inner join ICD_ProcFile10 Icd on z.CIHIValue = ICD.CIHIValue
where icd.validto < '2012/04/01'
GO
update ICD_diagFile10
set ValidTo = '2012/03/31'
from zzICD10_diag z
	right join ICD_diagFile10 Icd on z.CIHIValue = ICD.CIHIValue
where z.cihivalue is null and icd.validto > '2012/03/31' and icd.code < 500000
GO
update ICD_ProcFile10
set ValidTo = '2012/03/31'
from zzICD10_CCI z
	inner join ICD_ProcFile10 Icd on z.CIHIValue = ICD.CIHIValue
where icd.validto > '2012/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, '2012/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
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, '2012/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 len(cihivalue) >= 5
GO
delete from icd_procfile10 where cihivalue = 'closure by'
GO
-------------------------dbo.ICD_DiagFileNRS
if exists (select * from information_schema.tables where table_name = 'zzICD10_CA_NRS')
    drop table zzICD10_CA_NRS
GO
Create table zzICD10_CA_NRS(
    CIHIValue varchar(10),
    --shortDescription varchar(100),
    longDescription varchar(1000)
)
GO
bulk insert zzICD10_CA_NRS from '<path>NRS 2012-2013_ICD-10-CA Picklist_Final.txt' with( FIELDTERMINATOR = '|', ROWTERMINATOR ='\n', CODEPAGE = 'RAW' )
GO
update zzICD10_CA_NRS 
set CIHIValue = ltrim(rtrim(replace(CIHIValue, '.', ''))),
    --shortDescription = ltrim(rtrim(shortDescription)),
    longDescription = ltrim(rtrim(longDescription))
GO
--Diagnosis
UPdate ICD_DiagFileNRS 
set ValidTo = '2012/03/31'
-- select z.*, d.*
from ICD_DiagFileNRS d
	left join zzICD10_CA_NRS z on z.CIHIValue = d.CIHIValue
where 	z.cihivalue is null and d.validto > '2012/04/01'
GO
Update dbo.ICD_DiagFileNRS
set UserDescription = left(z.longDescription, 255),
	DefaultDescription = left(z.longDescription, 255),
	ShortDescription = left(z.longDescription, 255) --z.shortDescription
-- select z.*, d.*
from ICD_DiagFileNRS d
	inner join zzICD10_CA_NRS z on z.CIHIValue = d.CIHIValue
where 	left(z.LongDescription, 255) <> d.DefaultDescription
GO
if exists (select * from information_schema.tables where table_name = 'zzICD10_CA_NRS_New')
    drop table zzICD10_CA_NRS_New
GO
select code = identity(int, 1, 1), z.*
	into zzICD10_CA_NRS_New
from ICD_DiagFileNRS d
	right join zzICD10_CA_NRS z on z.CIHIValue = d.CIHIValue
where d.CIHIValue is null
order by z.cihivalue
GO
declare @MaxCode int
set @MaxCode = (select max(code) from ICD_DiagFileNRS where code < 100000)
if @MaxCode is null set @MaxCode = 0

Insert into dbo.ICD_DiagFileNRS(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, '2012/04/01', '2099/12/31', 'A', 0, 130, CIHIValue, 0 , ''	
from zzICD10_CA_NRS_New
order by code
GO
Update dbo.ICD_DiagFileNRS
set Displaycode = left(displaycode, 3) + '.' + substring(displaycode, 4, 10)
where len(cihivalue) > 3 and charindex('.', displaycode) = 0
GO

-------------------------dbo.ICD_ProcFileNRS
if exists (select * from information_schema.tables where table_name = 'zzICD10_CCI_NRS')
    drop table zzICD10_CCI_NRS
GO
Create table zzICD10_CCI_NRS(
    CIHIValue varchar(10),
    --shortDescription varchar(100),
    longDescription varchar(1000)
)
GO
bulk insert zzICD10_CCI_NRS from '<path>NRS 2012-2013_CCI Picklist_Final.txt' with( FIELDTERMINATOR = '|', ROWTERMINATOR ='\n', CODEPAGE = 'RAW' )
GO
update zzICD10_CCI_NRS 
set CIHIValue = ltrim(rtrim(CIHIValue)),
    --shortDescription = ltrim(rtrim(shortDescription)),
    longDescription = ltrim(rtrim(longDescription))
GO
--Intervention code
UPdate ICD_ProcFileNRS 
set ValidTo = '2012/03/31'
-- select z.*, d.*
from ICD_ProcFileNRS d
	left join zzICD10_CCI_NRS z on z.CIHIValue = d.CIHIValue
where 	z.cihivalue is null and d.validto > '2012/04/01'
GO
Update dbo.ICD_ProcFileNRS
set UserDescription = left(z.longDescription, 255),
	DefaultDescription = left(z.longDescription, 25) --,
	--ShortDescription = z.shortDescription
-- select z.*, d.*
from ICD_ProcFileNRS d
	inner join zzICD10_CCI_NRS z on z.CIHIValue = d.CIHIValue
where 	left(z.LongDescription, 255) <> d.DefaultDescription
GO
if exists (select * from information_schema.tables where table_name = 'zzICD10_CCI_NRS_New')
    drop table zzICD10_CCI_NRS_New
GO
select code = identity(int, 1, 1), z.*
	into zzICD10_CCI_NRS_New
from ICD_ProcFileNRS d
	right join zzICD10_CCI_NRS z on z.CIHIValue = d.CIHIValue
where d.CIHIValue is null
order by z.cihivalue
GO
declare @MaxCode int
set @MaxCode = (select max(code) from ICD_ProcFileNRS where code < 100000)
if @MaxCode is null set @MaxCode = 0

Insert into dbo.ICD_ProcFileNRS(Code, DisplayCode, UserDescription, DefaultDescription, ShortDescription, CIHIValue, ValidFrom, ValidTo, DiagnosisGender, AgeLow, AgeHigh, InterfaceIn, HospitalID, LUText)
select Code + @MaxCode, CIHIValue, left(longDescription, 255), left(longDescription, 255), '', CIHIValue, '2012/04/01', '2099/12/31', 'A', 0, 130, CIHIValue, 0 , ''	
from zzICD10_CCI_NRS_New
order by code
GO
--ED - 02243
-------------------------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_Desc2012_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_Desc2012_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_Desc2012_V1_0.txt' with( FIELDTERMINATOR = '|', ROWTERMINATOR ='\n')
GO
update zzICD10CA_Cat
set CIHIValue = ltrim(rtrim(CIHIValue)),
    shortDescription = ltrim(rtrim(shortDescription)),
    longDescription = ltrim(rtrim(longDescription))
GO
--Add Diag Cat
Update dbo.ICD_DiagCat
set DiagCatDescription = z.LongDescription
from ICD_DiagCat d
	inner join zzICD10CA_Cat z on z.CIHIValue = d.DiagCat
where 	z.LongDescription <> d.DiagCatDescription
GO
Insert into dbo.ICD_DiagCat(DiagCat, DiagCatDescription)
select z.CIHIValue, z.LongDescription
from ICD_DiagCat d
	right join zzICD10CA_Cat z on z.CIHIValue = d.DiagCat
where d.DiagCat is null
GO
--Add Interv Cat
Update dbo.ICD_IntervCat
set IntervCatDescription = z.LongDescription
from ICD_IntervCat d
	inner join zzICD10CCI_Cat z on z.CIHIValue = d.IntervCat
where 	z.LongDescription <> d.IntervCatDescription
GO
Insert into dbo.ICD_IntervCat(IntervCat, IntervCatDescription)
select z.CIHIValue, z.LongDescription
from ICD_IntervCat d
	right join zzICD10CCI_Cat z on z.CIHIValue = d.IntervCat
where d.IntervCat is null
GO
--Add Interv Class
Update dbo.ICD_IntervClass
set IntervClassDescription = z.LongDescription
from ICD_IntervClass d
	inner join zzICD10CCI_Class z on z.CIHIValue = d.IntervClass
where 	z.LongDescription <> d.IntervClassDescription
GO
Insert into dbo.ICD_IntervClass(IntervClass, IntervClassDescription)
select z.CIHIValue, z.LongDescription
from ICD_IntervClass d
	right join zzICD10CCI_Class z on z.CIHIValue = d.IntervClass
where d.IntervClass is null
GO
--ED - 02243
-------------------------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_Val2012_V1_0.txt' with( FIELDTERMINATOR = '|', ROWTERMINATOR ='\n', CODEPAGE = 'RAW' )
GO
Update LU_IntervAttExtent
set validto = '2012/03/31'
-- select l.*
from 
    (select * from  zzICD10CCI_Extent where sector = '1') v
    inner join ICD_ProcFile10 icd on v.CIHIValue = icd.CIHIValue
    inner join dbo.LU_IntervAttExtent l on icd.code = l.associationid
where v.LinkID = '' and l.validto > '2012/03/31'
GO
Update LU_IntervAttExtent
set validto = '2012/03/31'
--select l.*
from dbo.LU_IntervAttExtent l
	inner join ICD_ProcFile10 icd on icd.code = l.associationid
	left join
    (select * from  zzICD10CCI_Extent where sector = '1') v 
     on v.CIHIValue = icd.CIHIValue and v.LinkID = l.LinkID and v.ExtentAttribute = l.CIHIValue
where v.CIHIValue is null and l.validto > '2012/03/31'
GO

Update LU_IntervAttExtent
set validto = '2099/03/31' 
-- select l.*
from 
    (select * from  zzICD10CCI_Extent where sector = '1') v
    inner join ICD_ProcFile10 icd on v.CIHIValue = icd.CIHIValue
    inner join dbo.LU_IntervAttExtent l on icd.code = l.associationid and v.LinkID = l.LinkID
        and v.ExtentAttribute = l.CIHIValue
    inner join (select CIHIValue, AssociationID, LinkID, max(ValidTo) as maxDate from LU_IntervAttExtent group by CIHIValue, AssociationID, LinkID) d
        on l.CIHIValue = d.CIHIValue and l.AssociationID = d.AssociationID and l.LinkID = d.LinkID and l.Validto = d.maxdate
where v.LinkID <> '' and l.validto < '2012/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_Eng2012_V1_0.txt' with( FIELDTERMINATOR = '|', ROWTERMINATOR ='\n', CODEPAGE = 'RAW' )
GO
Update LU_IntervAttExtent
set UserDescription = d.UserDescription, DefaultDescription = d.UserDescription
-- select d.*, l.*
from 
    (select * from  zzICD10CCI_Extent where sector = '1') v
    inner join ICD_ProcFile10 icd on v.CIHIValue = icd.CIHIValue
    inner join dbo.LU_IntervAttExtent l on icd.code = l.associationid and v.LinkID = l.LinkID
        and v.ExtentAttribute = l.CIHIValue
    inner join zzICD10CCI_Extent_Desc d on v.LinkID = d.LinkID and v.ExtentAttribute = d.ExtentAttribute
where l.DefaultDescription <> d.UserDescription and v.LinkID <> ''
    and l.CIHIValue <> '' and l.ValidTo > '2012/04/01'
GO
--ED - 02403
-------------------------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_Loc_Val2012_V1_0.txt' with( FIELDTERMINATOR = '|', ROWTERMINATOR ='\n', CODEPAGE = 'RAW' )
GO
Update LU_IntervAttLocation
set validto = '2012/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 > '2012/03/31'
GO
Update LU_IntervAttLocation
set validto = '2012/03/31'
--select l.*
from dbo.LU_IntervAttLocation l
	inner join ICD_ProcFile10 icd on icd.code = l.associationid
	left join
    (select * from  zzICD10CCI_Location where sector = '1') v 
     on v.CIHIValue = icd.CIHIValue and v.LinkID = l.LinkID and v.LocationAttribute = l.CIHIValue
where v.CIHIValue is null and l.validto > '2012/03/31'
GO

Update LU_IntervAttLocation
set validto = '2099/03/31' -- select l.*
from 
    (select * from  zzICD10CCI_Location where sector = '1') v
    inner join ICD_ProcFile10 icd on v.CIHIValue = icd.CIHIValue
    inner join dbo.LU_IntervAttLocation l on icd.code = l.associationid and v.LinkID = l.LinkID
        and v.LocationAttribute = l.CIHIValue
    inner join (select CIHIValue, AssociationID, LinkID, max(ValidTo) as maxDate from LU_IntervAttLocation group by CIHIValue, AssociationID, LinkID) d
        on l.CIHIValue = d.CIHIValue and l.AssociationID = d.AssociationID and l.LinkID = d.LinkID and l.Validto = d.maxdate
where v.LinkID <> '' and l.validto < '2012/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_Loc_Eng2012_V1_0.txt' with( FIELDTERMINATOR = '|', ROWTERMINATOR ='\n', CODEPAGE = 'RAW' )
GO
Update LU_IntervAttLocation
set UserDescription = d.UserDescription, DefaultDescription = d.UserDescription
-- select d.*, l.*
from 
    (select * from  zzICD10CCI_Location where sector = '1') v
    inner join ICD_ProcFile10 icd on v.CIHIValue = icd.CIHIValue
    inner join dbo.LU_IntervAttLocation l on icd.code = l.associationid and v.LinkID = l.LinkID
        and v.LocationAttribute = l.CIHIValue
    inner join zzICD10CCI_Location_Desc d on v.LinkID = d.LinkID and v.LocationAttribute = d.LocationAttribute
where l.DefaultDescription <> d.UserDescription and v.LinkID <> ''
    and l.CIHIValue <> '' and l.ValidTo > '2012/04/01'
GO
--ED - 02403
-------------------------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_Val2012_V1_0.txt' with( FIELDTERMINATOR = '|', ROWTERMINATOR ='\n', CODEPAGE = 'RAW' )
GO
Update LU_IntervAttStatus
set validto = '2012/03/31'
--select v.*, l.*
from 
    (select * from  zzICD10CCI_Status where sector = '1') v
    inner join ICD_ProcFile10 icd on v.CIHIValue = icd.CIHIValue
    inner join dbo.LU_IntervAttStatus l on icd.code = l.associationid
where v.LinkID = '' and l.validto > '2012/03/31'
GO
Update LU_IntervAttStatus
set validto = '2012/03/31'
--select l.*
from dbo.LU_IntervAttStatus l
	inner join ICD_ProcFile10 icd on icd.code = l.associationid
	left join
    (select * from  zzICD10CCI_Status where sector = '1') v 
     on v.CIHIValue = icd.CIHIValue and v.LinkID = l.LinkID and v.StatusAttribute = l.CIHIValue
where v.CIHIValue is null and l.validto > '2012/03/31'
GO

Update LU_IntervAttStatus
set validto = '2099/03/31' -- select l.*
from 
    (select * from  zzICD10CCI_Status where sector = '1') v
    inner join ICD_ProcFile10 icd on v.CIHIValue = icd.CIHIValue
    inner join dbo.LU_IntervAttStatus l on icd.code = l.associationid and v.LinkID = l.LinkID
        and v.StatusAttribute = l.CIHIValue
    inner join (select CIHIValue, AssociationID, LinkID, max(ValidTo) as maxDate from LU_IntervAttStatus group by CIHIValue, AssociationID, LinkID) d
        on l.CIHIValue = d.CIHIValue and l.AssociationID = d.AssociationID and l.LinkID = d.LinkID and l.Validto = d.maxdate
where v.LinkID <> '' and l.validto < '2012/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_Eng2012_V1_0.txt' with( FIELDTERMINATOR = '|', ROWTERMINATOR ='\n', CODEPAGE = 'RAW' )
GO
Update LU_IntervAttStatus
set UserDescription = d.UserDescription, DefaultDescription = d.UserDescription
-- select d.*, l.*
from 
    (select * from  zzICD10CCI_Status where sector = '1') v
    inner join ICD_ProcFile10 icd on v.CIHIValue = icd.CIHIValue
    inner join dbo.LU_IntervAttStatus l on icd.code = l.associationid and v.LinkID = l.LinkID
        and v.StatusAttribute = l.CIHIValue
    inner join zzICD10CCI_Status_Desc d on v.LinkID = d.LinkID and v.StatusAttribute = d.StatusAttribute
where l.DefaultDescription <> d.UserDescription and v.LinkID <> ''
    and l.CIHIValue <> '' and l.ValidTo > '2012/04/01'
GO
--- ***** Remember to keep the customer code validated. Do not touch customer code.
-------------------------Diagnosis Cat -- dbo.ICD_DiagCat
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_Validation2012_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

--- ***** Remember to keep the customer code validated. Do not touch customer code.
--ED - 02403
-------------------------Procedure ICD_ProcFile10
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_Validation2012_V1_0.txt' with( FIELDTERMINATOR = '|', ROWTERMINATOR ='\n')
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.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
--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
if exists (select * from information_schema.tables where table_name = 'zzICD10CCI_Extent_New')
    drop table zzICD10CCI_Extent_New
GO
select
    Code = identity(int, 1, 1),
    v.CIHIValue,
    v.ExtentAttribute,
    v.LinkID
into zzICD10CCI_Extent_New
from ICD_ProcFile10 icd  
	inner join
    (select * from  zzICD10CCI_Extent where sector = '1') v 
     on v.CIHIValue = icd.CIHIValue 
     left join dbo.LU_IntervAttExtent l on icd.code = l.associationid
     and v.LinkID = l.LinkID and v.ExtentAttribute = l.CIHIValue
where l.CIHIValue is null and v.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 = '2012/04/01', ValidTo = '2099/03/31', 
    AssociationID = icd.Code, LinkID = n.LinkID, 
    InterfaceIn = ltrim(rtrim(n.ExtentAttribute)), Sector = '3', HospitalID = 0
from zzICD10CCI_Extent_New n
    inner join ICD_ProcFile10 icd on n.CIHIValue = icd.CIHIValue
    inner join zzICD10CCI_Extent_Desc d on n.LinkID = d.LinkID and d.ExtentAttribute = n.ExtentAttribute
    left  join LU_IntervAttExtent l on icd.code = l.associationid and n.linkid = l.linkid and n.ExtentAttribute = l.cihivalue
where l.associationid is null
GO
if exists (select * from information_schema.tables where table_name = 'zzICD10CCI_Location_New')
    drop table zzICD10CCI_Location_New
GO
select
    Code = identity(int, 1, 1),
    v.CIHIValue,
    v.LocationAttribute,
    v.LinkID
into zzICD10CCI_Location_New
from ICD_ProcFile10 icd  
	inner join
    (select * from  zzICD10CCI_Location where sector = '1') v 
     on v.CIHIValue = icd.CIHIValue 
     left join dbo.LU_IntervAttLocation l on icd.code = l.associationid
     and v.LinkID = l.LinkID and v.LocationAttribute = l.CIHIValue
where l.CIHIValue is null and v.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 = '2012/04/01', ValidTo = '2099/03/31', 
    AssociationID = icd.Code, LinkID = n.LinkID, 
    InterfaceIn = ltrim(rtrim(n.LocationAttribute)), Sector = '3', HospitalID = 0
from zzICD10CCI_Location_New n
    inner join ICD_ProcFile10 icd on n.CIHIValue = icd.CIHIValue
    inner join zzICD10CCI_Location_Desc d on n.LinkID = d.LinkID and d.LocationAttribute = n.LocationAttribute
    left  join LU_IntervAttLocation l on icd.code = l.associationid and n.linkid = l.linkid and n.LocationAttribute = l.cihivalue
where l.associationid is null
GO
if exists (select * from information_schema.tables where table_name = 'zzICD10CCI_Status_New')
    drop table zzICD10CCI_Status_New
GO
select
    Code = identity(int, 1, 1),
    v.CIHIValue,
    v.StatusAttribute,
    v.LinkID
into zzICD10CCI_Status_New
from ICD_ProcFile10 icd  
	inner join
    (select * from  zzICD10CCI_Status where sector = '1') v 
     on v.CIHIValue = icd.CIHIValue 
     left join dbo.LU_IntervAttStatus l on icd.code = l.associationid
     and v.LinkID = l.LinkID and v.StatusAttribute = l.CIHIValue
where l.CIHIValue is null and v.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 = '2012/04/01', ValidTo = '2099/03/31', 
    AssociationID = icd.Code, LinkID = n.LinkID, 
    InterfaceIn = ltrim(rtrim(n.StatusAttribute)), Sector = '3', HospitalID = 0
from zzICD10CCI_Status_New n
    inner join ICD_ProcFile10 icd on n.CIHIValue = icd.CIHIValue
    inner join zzICD10CCI_Status_Desc d on n.LinkID = d.LinkID and d.StatusAttribute = n.StatusAttribute
    left  join LU_IntervAttStatus l on icd.code = l.associationid and n.linkid = l.linkid and n.StatusAttribute = l.cihivalue
where l.associationid is null
GO
--ED - 02403
if exists (select * from information_schema.tables where table_name = 'zzICD10_Diag_French')
    drop table zzICD10_Diag_French
GO
Create table zzICD10_Diag_French(
    CIHIValue varchar(10),
    shortDescription varchar(100),
    longDescription varchar(500)
)
GO
bulk insert zzICD10_Diag_French  from '<path>ICD_Code_Fra_Desc2012_V1_0.txt' with( FIELDTERMINATOR = '|', ROWTERMINATOR ='\n', CODEPAGE = 'RAW' )
GO
update zzICD10_Diag_French
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_French')
    drop table zzICD10_CCI_French
GO
Create table zzICD10_CCI_French(
    CIHIValue varchar(10),
    shortDescription varchar(100),
    longDescription varchar(500)
)
GO
bulk insert zzICD10_CCI_French from '<path>CCI_Code_Fra_Desc2012_V1_0.txt' with( FIELDTERMINATOR = '|', ROWTERMINATOR ='\n', CODEPAGE = 'RAW' )
GO
update zzICD10_CCI_French
set CIHIValue = ltrim(rtrim(CIHIValue)),
    shortDescription = ltrim(rtrim(shortDescription)),
    longDescription = ltrim(rtrim(longDescription))
GO
if exists (select * from information_schema.tables where table_name = 'ICD_diagFile10Fr')
begin
    declare @sql varchar(1000)
    set @sql = '
    Update ICD_diagFile10Fr
    set UserDescription = z.longDescription,
        DefaultDescription = z.longDescription
    from zzICD10_Diag_French z
	    inner join ICD_diagFile10Fr Icd on z.CIHIValue = ICD.CIHIValue
    where icd.DefaultDescription <>  z.longDescription' --or icd.ShortDescription <> z.shortDescription
    exec(@sql)
end    
GO
if exists (select * from information_schema.tables where table_name = 'ICD_ProcFile10Fr')
begin
    declare @sql varchar(1000)
    set @sql = '
    Update dbo.ICD_ProcFile10Fr
    set UserDescription = z.longDescription,
        DefaultDescription = z.longDescription
    from zzICD10_CCI_French z
	    inner join ICD_ProcFile10Fr Icd on z.CIHIValue = ICD.CIHIValue
    where icd.DefaultDescription <>  z.longDescription' --or icd.ShortDescription <> z.shortDescription
    exec(@sql)
end    
GO
if exists (select * from information_schema.tables where table_name = 'ICD_diagFile10Fr')
    update ICD_diagFile10Fr
    set ValidTo = '2099/03/31'
    from zzICD10_diag_French z
	    inner join ICD_diagFile10Fr Icd on z.CIHIValue = ICD.CIHIValue
    where icd.validto < '2012/04/01'
GO
if exists (select * from information_schema.tables where table_name = 'ICD_ProcFile10Fr')
    update ICD_ProcFile10Fr
    set ValidTo = '2099/03/31'
    from zzICD10_CCI_French z
	    inner join ICD_ProcFile10Fr Icd on z.CIHIValue = ICD.CIHIValue
    where icd.validto < '2012/04/01'
GO
if exists (select * from information_schema.tables where table_name = 'ICD_diagFile10Fr')
    update ICD_diagFile10Fr
    set ValidTo = '2012/03/31'
    from zzICD10_diag_French z
	    right join ICD_diagFile10Fr Icd on z.CIHIValue = ICD.CIHIValue
    where z.cihivalue is null and icd.validto > '2012/03/31' and icd.code < 500000
GO
if exists (select * from information_schema.tables where table_name = 'ICD_ProcFile10Fr')
    update ICD_ProcFile10Fr
    set ValidTo = '2012/03/31'
    from zzICD10_CCI_French z
	    inner join ICD_ProcFile10Fr Icd on z.CIHIValue = ICD.CIHIValue
    where icd.validto > '2012/03/31' and  z.cihivalue is null and icd.code < 500000
GO
if exists (select * from information_schema.tables where table_name = 'zzICD10_diag_French_New')
    drop table zzICD10_diag_French_New
GO
if exists (select * from information_schema.tables where table_name = 'zzICD10_CCI_French_New')
    drop table zzICD10_CCI_French_New
GO
if exists (select * from information_schema.tables where table_name = 'ICD_diagFile10Fr')
    select  z.CIHIValue, z.shortDescription, z.longDescription, code = identity(int, 1, 1)
        into zzICD10_diag_French_New
    from zzICD10_Diag_French z
	    left join ICD_diagFile10Fr Icd on z.CIHIValue = ICD.CIHIValue
    where icd.cihivalue is null
    order by z.CIHIValue
GO
if exists (select * from information_schema.tables where table_name = 'ICD_diagFile10Fr')
    select  z.CIHIValue, z.shortDescription, z.longDescription, code = identity(int, 1, 1)
        into zzICD10_CCI_French_New
    from zzICD10_CCI_French z
	    left join ICD_ProcFile10Fr Icd on z.CIHIValue = ICD.CIHIValue
    where icd.cihivalue is null
    order by z.CIHIValue
GO
if exists (select * from information_schema.tables where table_name = 'ICD_diagFile10Fr')
    update ICD_diagFile10Fr
    set ValidTo = '2099/03/31'
    from zzICD10_diag_French z
	    left join ICD_diagFile10Fr Icd on z.CIHIValue = ICD.CIHIValue
    where icd.validto < '2012/04/01'
GO
if exists (select * from information_schema.tables where table_name = 'ICD_diagFile10Fr')
    update ICD_ProcFile10
    set ValidTo = '2099/03/31'
    from zzICD10_CCI_French z
	    inner join ICD_ProcFile10 Icd on z.CIHIValue = ICD.CIHIValue
    where icd.validto < '2012/04/01'
GO
if exists (select * from information_schema.tables where table_name = 'ICD_diagFile10Fr')
begin
    declare @MaxCode int
    set @MaxCode = (select max(code) from ICD_DiagFile10Fr where code < 100000)
    if @MaxCode is null set @MaxCode = 0

    Insert into dbo.ICD_DiagFile10Fr(Code, DisplayCode, UserDescription, DefaultDescription, CIHIValue, ValidFrom, ValidTo, DiagnosisGender, AgeLow, AgeHigh, InterfaceIn)
    select Code + @MaxCode, CIHIValue, left(longDescription, 255), left(longDescription, 255), CIHIValue, '2012/04/01', '2099/12/31', 'A', 0, 130, CIHIValue
    from zzICD10_diag_French_New
    order by code
end
GO
if exists (select * from information_schema.tables where table_name = 'ICD_diagFile10Fr')
    Update dbo.ICD_DiagFile10Fr
    set Displaycode = left(displaycode, 3) + '.' + substring(displaycode, 4, 10)
    where len(cihivalue) > 3 and charindex('.', displaycode) = 0
GO
if exists (select * from information_schema.tables where table_name = 'ICD_diagFile10Fr')
begin
    declare @MaxCode int
    set @MaxCode = (select max(code) from ICD_ProcFile10Fr where code < 100000)
    if @MaxCode is null set @MaxCode = 0
    set IDENTITY_INSERT ICD_ProcFile10Fr on

    Insert into dbo.ICD_ProcFile10Fr(Code, DisplayCode, UserDescription, DefaultDescription, CIHIValue, ValidFrom, ValidTo, DiagnosisGender, AgeLow, AgeHigh, InterfaceIn)
    select Code + @MaxCode, CIHIValue, left(longDescription, 255), left(longDescription, 255), CIHIValue, '2012/04/01', '2099/12/31', 'A', 0, 130, CIHIValue
    from zzICD10_CCI_French_New
    where cihivalue <> ''
    order by code
end
GO
if exists (select * from information_schema.tables where table_name = 'ICD_diagFile10Fr')
    set IDENTITY_INSERT ICD_ProcFile10Fr off
GO
if exists (select * from information_schema.tables where table_name = 'ICD_diagFile10Fr')
    Update dbo.ICD_ProcFile10Fr
    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 len(cihivalue) >= 5
GO
if exists (select * from information_schema.tables where table_name = 'ICD_DiagFile10Fr')
    Update ICD_DiagFile10Fr 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_DiagFile10Fr d on v.cihivalue = d.cihivalue   
    where isnull(v.CIHIFlag, '@@') <> isnull(d.CIHIFlag, '#')
GO
if exists (select * from information_schema.tables where table_name = 'ICD_DiagFile10Fr')
    Update ICD_DiagFile10Fr 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_DiagFile10Fr d on v.cihivalue = d.cihivalue   
    where isnull(v.MRDxFlag, '@@') <> isnull(d.MRDxFlag, '#')
GO
if exists (select * from information_schema.tables where table_name = 'ICD_DiagFile10Fr')
    Update ICD_DiagFile10Fr 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_DiagFile10Fr d on v.cihivalue = d.cihivalue   
    where isnull(v.NewBorn, '@@') <> isnull(d.NewbornFlag, '#')
GO
if exists (select * from information_schema.tables where table_name = 'ICD_DiagFile10Fr')
    Update ICD_DiagFile10Fr 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_DiagFile10Fr d on v.cihivalue = d.cihivalue   
    where isnull(v.DiagType, '') <> isnull(d.TypeCode, '')
GO
if exists (select * from information_schema.tables where table_name = 'ICD_DiagFile10Fr')
    Update ICD_DiagFile10Fr 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_DiagFile10Fr d on v.cihivalue = d.cihivalue   
    where isnull(v.Gender, '@') <> isnull(d.DiagnosisGender, '#')
GO
if exists (select * from information_schema.tables where table_name = 'ICD_DiagFile10Fr')
    Update ICD_DiagFile10Fr 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_DiagFile10Fr d on v.cihivalue = d.cihivalue   
    where (case when isnull(v.AgeLow, '') = '' then 0 else v.AgeLow end) <> isnull(d.AgeLow, -1)
GO
if exists (select * from information_schema.tables where table_name = 'ICD_DiagFile10Fr')
    Update ICD_DiagFile10Fr 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_DiagFile10Fr d on v.cihivalue = d.cihivalue   
    where (case when isnull(v.AgeHigh, '') = '' then 0 else v.AgeHigh end) <> isnull(d.AgeHigh, -1)
GO
if exists (select * from information_schema.tables where table_name = 'ICD_ProcFile10Fr')
    Update ICD_ProcFile10Fr 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_ProcFile10Fr d on v.cihivalue = d.cihivalue   
    where isnull(v.CIHIFlag, '@@') <> isnull(d.CIHIFlag, '#')
GO
if exists (select * from information_schema.tables where table_name = 'ICD_ProcFile10Fr')
    Update ICD_ProcFile10Fr 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_ProcFile10Fr d on v.cihivalue = d.cihivalue   
    where isnull(v.Gender, '@') <> isnull(d.DiagnosisGender, '#')
GO
if exists (select * from information_schema.tables where table_name = 'ICD_ProcFile10Fr')
    Update ICD_ProcFile10Fr 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_ProcFile10Fr d on v.cihivalue = d.cihivalue   
    where (case when isnull(v.AgeLow, '') = '' then 0 else v.AgeLow end) <> isnull(d.AgeLow, -1)
GO
if exists (select * from information_schema.tables where table_name = 'ICD_ProcFile10Fr')
    Update ICD_ProcFile10Fr set AgeHigh = (case when isnull(v.AgeHigh, '') = '' then 0 else v.AgeHigh 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
    --where a.AgeHigh<> b.AgeHigh
    ) v
	    inner join ICD_ProcFile10Fr d on v.cihivalue = d.cihivalue   
    where (case when isnull(v.AgeHigh, '') = '' then 0 else v.AgeHigh end) <> isnull(d.AgeHigh, -1)
GO
--ED - 02403
-------------------------Intervention Attribute Extent
if exists (select * from information_schema.tables where table_name = 'zzICD10CCI_Extent_French')
    drop table zzICD10CCI_Extent_French
GO
Create table zzICD10CCI_Extent_French(
    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_French from '<path>CCI_Extent_Val2012_V1_0.txt' with( FIELDTERMINATOR = '|', ROWTERMINATOR ='\n')
GO
if exists (select * from information_schema.tables where table_name = 'ICD_ProcFile10Fr')
    Update LU_IntervAttExtentFr
    set validto = '2012/03/31'
    -- select l.*
    from 
        (select * from  zzICD10CCI_Extent_French where sector = '1') v
        inner join ICD_ProcFile10Fr icd on v.CIHIValue = icd.CIHIValue
        inner join dbo.LU_IntervAttExtentFr l on icd.code = l.associationid
    where v.LinkID = '' and l.validto > '2012/03/31'
GO
if exists (select * from information_schema.tables where table_name = 'ICD_ProcFile10Fr')
    Update LU_IntervAttExtentFr
    set validto = '2012/03/31'
    --select l.*
    from dbo.LU_IntervAttExtentFr l
	    inner join ICD_ProcFile10Fr icd on icd.code = l.associationid
	    left join
        (select * from  zzICD10CCI_Extent_French where sector = '1') v 
         on v.CIHIValue = icd.CIHIValue and v.LinkID = l.LinkID and v.ExtentAttribute = l.CIHIValue
    where v.CIHIValue is null and l.validto > '2012/03/31'
GO
if exists (select * from information_schema.tables where table_name = 'ICD_ProcFile10Fr')
    Update LU_IntervAttExtentFr
    set validto = '2099/03/31' 
    -- select l.*
    from 
        (select * from  zzICD10CCI_Extent_French where sector = '1') v
        inner join ICD_ProcFile10Fr icd on v.CIHIValue = icd.CIHIValue
        inner join dbo.LU_IntervAttExtentFr l on icd.code = l.associationid and v.LinkID = l.LinkID
            and v.ExtentAttribute = l.CIHIValue
        inner join (select CIHIValue, AssociationID, LinkID, max(ValidTo) as maxDate from LU_IntervAttExtentFr group by CIHIValue, AssociationID, LinkID) d
            on l.CIHIValue = d.CIHIValue and l.AssociationID = d.AssociationID and l.LinkID = d.LinkID and l.Validto = d.maxdate
    where v.LinkID <> '' and l.validto < '2012/03/31'
GO
if exists (select * from information_schema.tables where table_name = 'zzICD10CCI_Extent_French_Desc')
    drop table zzICD10CCI_Extent_French_Desc
GO
Create table zzICD10CCI_Extent_French_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_French_Desc from '<path>CCI_Extent_Fra2012_V1_0.txt' with( FIELDTERMINATOR = '|', ROWTERMINATOR ='\n', CODEPAGE = 'RAW' )
GO
if exists (select * from information_schema.tables where table_name = 'ICD_ProcFile10Fr')
    Update LU_IntervAttExtentFr
    set UserDescription = d.UserDescription, DefaultDescription = d.UserDescription
    -- select d.*, l.*
    from 
        (select * from  zzICD10CCI_Extent_French where sector = '1') v
        inner join ICD_ProcFile10Fr icd on v.CIHIValue = icd.CIHIValue
        inner join dbo.LU_IntervAttExtentFr l on icd.code = l.associationid and v.LinkID = l.LinkID
            and v.ExtentAttribute = l.CIHIValue
        inner join zzICD10CCI_Extent_French_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 > '2012/04/01'
GO

--2012 new code
if exists (select * from information_schema.tables where table_name = 'zzICD10CCI_Extent_French_New')
    drop table zzICD10CCI_Extent_French_New
GO
if exists (select * from information_schema.tables where table_name = 'ICD_ProcFile10Fr')
    select
        Code = identity(int, 1, 1),
        v.CIHIValue,
        v.ExtentAttribute,
        v.LinkID
    into zzICD10CCI_Extent_French_New
    from ICD_ProcFile10Fr icd  
	    inner join
        (select * from  zzICD10CCI_Extent_French where sector = '1') v 
         on v.CIHIValue = icd.CIHIValue 
         left join dbo.LU_IntervAttExtentFr l on icd.code = l.associationid
         and v.LinkID = l.LinkID and v.ExtentAttribute = l.CIHIValue
    where l.CIHIValue is null and v.ExtentAttribute <> ''
GO
if exists (select * from information_schema.tables where table_name = 'ICD_ProcFile10Fr')
begin
    declare @Maxcode int
    set @MaxCode = (select max(code) from LU_IntervAttExtentFr where code < 100000)
    if @MaxCode is null set @MaxCode = 500000

    insert into dbo.LU_IntervAttExtentFr(Code, DisplayCode, UserDescription, DefaultDescription, CIHIValue, ValidFrom, ValidTo, AssociationID, LinkID, InterfaceIn, Sector)
    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 = '2012/04/01', ValidTo = '2099/03/31', 
        AssociationID = icd.Code, LinkID = n.LinkID, 
        InterfaceIn = ltrim(rtrim(n.ExtentAttribute)), Sector = '3'
    from zzICD10CCI_Extent_French_New n
        inner join ICD_ProcFile10Fr icd on n.CIHIValue = icd.CIHIValue
        inner join zzICD10CCI_Extent_French_Desc d on n.LinkID = d.LinkID and d.ExtentAttribute = n.ExtentAttribute
        left  join LU_IntervAttExtentFr l on icd.code = l.associationid and n.linkid = l.linkid and n.ExtentAttribute = l.cihivalue
    where l.associationid is null
end
GO
--ED - 02243
-------------------------Intervention Attribute Location
if exists (select * from information_schema.tables where table_name = 'zzICD10CCI_LocationFr')
    drop table zzICD10CCI_LocationFr
GO
Create table zzICD10CCI_LocationFr(
    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_LocationFr from '<path>CCI_Loc_Val2012_V1_0.txt' with( FIELDTERMINATOR = '|', ROWTERMINATOR ='\n')
GO
if exists (select * from information_schema.tables where table_name = 'ICD_ProcFile10Fr')
    Update LU_IntervAttLocationFr
    set validto = '2012/03/31'
    --select v.*, l.*
    from 
        (select * from  zzICD10CCI_LocationFr where sector = '1') v
        inner join ICD_ProcFile10Fr icd on v.CIHIValue = icd.CIHIValue
        inner join dbo.LU_IntervAttLocationFr l on icd.code = l.associationid
    where v.LinkID = '' and l.validto > '2012/03/31'
GO
if exists (select * from information_schema.tables where table_name = 'ICD_ProcFile10Fr')
    Update LU_IntervAttLocationFr
    set validto = '2012/03/31'
    --select l.*
    from dbo.LU_IntervAttLocationFr l
	    inner join ICD_ProcFile10Fr icd on icd.code = l.associationid
	    left join
        (select * from  zzICD10CCI_LocationFr where sector = '1') v 
         on v.CIHIValue = icd.CIHIValue and v.LinkID = l.LinkID and v.LocationAttribute = l.CIHIValue
    where v.CIHIValue is null and l.validto > '2012/03/31'
GO
if exists (select * from information_schema.tables where table_name = 'ICD_ProcFile10Fr')
    Update LU_IntervAttLocationFr
    set validto = '2099/03/31' -- select l.*
    from 
        (select * from  zzICD10CCI_LocationFr where sector = '1') v
        inner join ICD_ProcFile10Fr icd on v.CIHIValue = icd.CIHIValue
        inner join dbo.LU_IntervAttLocationFr l on icd.code = l.associationid and v.LinkID = l.LinkID
            and v.LocationAttribute = l.CIHIValue
        inner join (select CIHIValue, AssociationID, LinkID, max(ValidTo) as maxDate from LU_IntervAttLocationFr group by CIHIValue, AssociationID, LinkID) d
            on l.CIHIValue = d.CIHIValue and l.AssociationID = d.AssociationID and l.LinkID = d.LinkID and l.Validto = d.maxdate
    where v.LinkID <> '' and l.validto < '2012/03/31'
GO
if exists (select * from information_schema.tables where table_name = 'zzICD10CCI_LocationFr_Desc')
    drop table zzICD10CCI_LocationFr_Desc
GO
Create table zzICD10CCI_LocationFr_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_LocationFr_Desc from '<path>CCI_Loc_Fra2012_V1_0.txt' with( FIELDTERMINATOR = '|', ROWTERMINATOR ='\n', CODEPAGE = 'RAW')
GO
if exists (select * from information_schema.tables where table_name = 'ICD_ProcFile10Fr')
    Update LU_IntervAttLocationFr
    set UserDescription = d.UserDescription, DefaultDescription = d.UserDescription
    -- select d.*, l.*
    from 
        (select * from  zzICD10CCI_LocationFr where sector = '1') v
        inner join ICD_ProcFile10Fr icd on v.CIHIValue = icd.CIHIValue
        inner join dbo.LU_IntervAttLocationFr l on icd.code = l.associationid and v.LinkID = l.LinkID
            and v.LocationAttribute = l.CIHIValue
        inner join zzICD10CCI_LocationFr_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 > '2012/04/01'
GO

--2012  new code
if exists (select * from information_schema.tables where table_name = 'zzICD10CCI_LocationFr_New')
    drop table zzICD10CCI_LocationFr_New
GO
if exists (select * from information_schema.tables where table_name = 'ICD_ProcFile10Fr')
select
    Code = identity(int, 1, 1),
    v.CIHIValue,
    v.LocationAttribute,
    v.LinkID
into zzICD10CCI_LocationFr_New
from ICD_ProcFile10Fr icd  
	inner join
    (select * from  zzICD10CCI_LocationFr where sector = '1') v 
     on v.CIHIValue = icd.CIHIValue 
     left join dbo.LU_IntervAttLocationFr l on icd.code = l.associationid
     and v.LinkID = l.LinkID and v.LocationAttribute = l.CIHIValue
where l.CIHIValue is null and v.LocationAttribute <> ''
GO
if exists (select * from information_schema.tables where table_name = 'ICD_ProcFile10Fr')
begin
    declare @Maxcode int
    set @MaxCode = (select max(code) from LU_IntervAttLocationFr )
    if @MaxCode is null set @MaxCode = 500000
    if exists (select * from information_schema.tables where table_name = 'ICD_ProcFile10Fr')
    insert into dbo.LU_IntervAttLocationFr(Code, DisplayCode, UserDescription, DefaultDescription, CIHIValue, ValidFrom, ValidTo, AssociationID, LinkID, InterfaceIn, Sector)
    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 = '2012/04/01', ValidTo = '2099/03/31', 
        AssociationID = icd.Code, LinkID = n.LinkID, 
        InterfaceIn = ltrim(rtrim(n.LocationAttribute)), Sector = '3'
    from zzICD10CCI_LocationFr_New n
        inner join ICD_ProcFile10Fr icd on n.CIHIValue = icd.CIHIValue
        inner join zzICD10CCI_LocationFr_Desc d on n.LinkID = d.LinkID and d.LocationAttribute = n.LocationAttribute
        left  join LU_IntervAttLocationFr l on icd.code = l.associationid and n.linkid = l.linkid and n.LocationAttribute = l.cihivalue
    where l.associationid is null
end
GO
--ED - 02403
-------------------------Intervention Attribute Status
if exists (select * from information_schema.tables where table_name = 'zzICD10CCI_Status_French')
    drop table zzICD10CCI_Status_French
GO
Create table zzICD10CCI_Status_French(
    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_French from '<path>CCI_Status_Val2012_V1_0.txt' with( FIELDTERMINATOR = '|', ROWTERMINATOR ='\n')
GO
if exists (select * from information_schema.tables where table_name = 'ICD_ProcFile10Fr')
    Update LU_IntervAttStatusFr
    set validto = '2012/03/31'
    --select v.*, l.*
    from 
        (select * from  zzICD10CCI_Status_French where sector = '1') v
        inner join ICD_ProcFile10Fr icd on v.CIHIValue = icd.CIHIValue
        inner join dbo.LU_IntervAttStatusFr l on icd.code = l.associationid
    where v.LinkID = '' and l.validto > '2012/03/31'
GO
if exists (select * from information_schema.tables where table_name = 'ICD_ProcFile10Fr')
    Update LU_IntervAttStatusFr
    set validto = '2012/03/31'
    --select l.*
    from dbo.LU_IntervAttStatusFr l
	    inner join ICD_ProcFile10Fr icd on icd.code = l.associationid
	    left join
        (select * from  zzICD10CCI_Status_French where sector = '1') v 
         on v.CIHIValue = icd.CIHIValue and v.LinkID = l.LinkID and v.StatusAttribute = l.CIHIValue
    where v.CIHIValue is null and l.validto > '2012/03/31'
GO
if exists (select * from information_schema.tables where table_name = 'ICD_ProcFile10Fr')
    Update LU_IntervAttStatusFr
    set validto = '2099/03/31' -- select l.*
    from 
        (select * from  zzICD10CCI_Status_French where sector = '1') v
        inner join ICD_ProcFile10Fr icd on v.CIHIValue = icd.CIHIValue
        inner join dbo.LU_IntervAttStatusFr l on icd.code = l.associationid and v.LinkID = l.LinkID
            and v.StatusAttribute = l.CIHIValue
        inner join (select CIHIValue, AssociationID, LinkID, max(ValidTo) as maxDate from LU_IntervAttStatusFr group by CIHIValue, AssociationID, LinkID) d
            on l.CIHIValue = d.CIHIValue and l.AssociationID = d.AssociationID and l.LinkID = d.LinkID and l.Validto = d.maxdate
    where v.LinkID <> '' and l.validto < '2012/03/31'
GO
if exists (select * from information_schema.tables where table_name = 'zzICD10CCI_Status_French_Desc')
    drop table zzICD10CCI_Status_French_Desc
GO
Create table zzICD10CCI_Status_French_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_French_Desc from '<path>CCI_Status_Fra2012_V1_0.txt' with( FIELDTERMINATOR = '|', ROWTERMINATOR ='\n', CODEPAGE = 'RAW')
GO
if exists (select * from information_schema.tables where table_name = 'ICD_ProcFile10Fr')
    Update LU_IntervAttStatusFr
    set UserDescription = d.UserDescription, DefaultDescription = d.UserDescription
    -- select d.*, l.*
    from 
        (select * from  zzICD10CCI_Status_French where sector = '1') v
        inner join ICD_ProcFile10Fr icd on v.CIHIValue = icd.CIHIValue
        inner join dbo.LU_IntervAttStatusFr l on icd.code = l.associationid and v.LinkID = l.LinkID
            and v.StatusAttribute = l.CIHIValue
        inner join zzICD10CCI_Status_French_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 > '2012/04/01'
GO

--2012 new code
if exists (select * from information_schema.tables where table_name = 'zzICD10CCI_Status_French_New')
    drop table zzICD10CCI_Status_French_New
GO
if exists (select * from information_schema.tables where table_name = 'ICD_ProcFile10Fr')
select
    Code = identity(int, 1, 1),
    v.CIHIValue,
    v.StatusAttribute,
    v.LinkID
into zzICD10CCI_Status_French_New
from ICD_ProcFile10Fr icd  
	inner join
    (select * from  zzICD10CCI_Status_French where sector = '1') v 
     on v.CIHIValue = icd.CIHIValue 
     left join dbo.LU_IntervAttStatusFr l on icd.code = l.associationid
     and v.LinkID = l.LinkID and v.StatusAttribute = l.CIHIValue
where l.CIHIValue is null and v.StatusAttribute <> ''
GO
if exists (select * from information_schema.tables where table_name = 'ICD_ProcFile10Fr')
begin
    declare @Maxcode int
    set @MaxCode = (select count(*) from LU_IntervAttStatusFr where code between 76430 and 78442)
    set @MaxCode = (select max(code) from LU_IntervAttStatusFr )
    if @MaxCode is null set @MaxCode = 500000

    insert into dbo.LU_IntervAttStatusFr(Code, DisplayCode, UserDescription, DefaultDescription, CIHIValue, ValidFrom, ValidTo, AssociationID, LinkID, InterfaceIn, Sector)
    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 = '2012/04/01', ValidTo = '2099/03/31', 
        AssociationID = icd.Code, LinkID = n.LinkID, 
        InterfaceIn = ltrim(rtrim(n.StatusAttribute)), Sector = '3'
    from zzICD10CCI_Status_French_New n
        inner join ICD_ProcFile10Fr icd on n.CIHIValue = icd.CIHIValue
        inner join zzICD10CCI_Status_French_Desc d on n.LinkID = d.LinkID and d.StatusAttribute = n.StatusAttribute
        left  join LU_IntervAttStatusFr l on icd.code = l.associationid and n.linkid = l.linkid and n.StatusAttribute = l.cihivalue
    where l.associationid is null
end   
GO
--------- END ICD Tables ----------------
--------- Error Files -------------------
-------------------------DAD Error File -----------------------------------------------------
if exists (select * from information_schema.tables where table_name = 'zzErrorFile_DAD_2012')
    drop table zzErrorFile_DAD_2012
GO
Create table zzErrorFile_DAD_2012(
    IsError varchar(1),
    GroupNumber varchar(2),
    FieldNumber varchar(2),
    ErrorNumber varchar(2),
    EnglishErrorMessage varchar(1000),
    FrenchErrorMessage varchar(1000)
)
GO
bulk insert zzErrorFile_DAD_2012 from '<path>2012_ErrorFile_DAD.txt' with( FIELDTERMINATOR = '|', ROWTERMINATOR ='\n', CODEPAGE = 'RAW')
GO
Delete from dbo.M_DADErrors where batchyear = 2012
GO
Insert into dbo.M_DADErrors(BatchYear, ErrorNumber, EnglishDescription, FrenchDescription, IsError)
select 2012, IsError + GroupNumber + FieldNumber + ErrorNumber, EnglishErrorMessage, FrenchErrorMessage,
    (case when IsError = 'E' then 1 else 0 end)
from zzErrorFile_DAD_2012
GO
if exists (select * from information_schema.tables where table_name = 'zzErrorFile_DAD_2012')
    drop table zzErrorFile_DAD_2012
GO
-------------------------NACRS Error File -----------------------------------------------------
if exists (select * from information_schema.tables where table_name = 'zzErrorFile_NACRS_2012')
    drop table zzErrorFile_NACRS_2012
GO
Create table zzErrorFile_NACRS_2012(
    ErrorNumber varchar(10),
    EnglishErrorMessage varchar(1000)
)
GO
bulk insert zzErrorFile_NACRS_2012 from '<path>2012_ErrorFile_NACRS.txt' with( FIELDTERMINATOR = '|', ROWTERMINATOR ='\n', CODEPAGE = 'RAW')
GO
Delete from dbo.M_AmcareErrors where batchyear = 2012
GO
Insert into dbo.M_AmcareErrors(ErrorNumber, ErrorDescription, IsError, BatchYear)
select ErrorNumber, EnglishErrorMessage, 
    (case when ErrorNumber like 'W%' then 0 else 1 end), 2012
from zzErrorFile_NACRS_2012
GO
if exists (select * from information_schema.tables where table_name = 'zzErrorFile_NACRS_2012')
    drop table zzErrorFile_NACRS_2012
GO
--------- END Files -------------------