
--Others

--- ***** Remember to keep the customer code validated. Do not touch customer code.
if exists (select * from information_schema.tables where table_name = 'zzICD10_Diag')
    drop table zzICD10_Diag
GO
Create table zzICD10_Diag(
    CIHIValue varchar(10),
    shortDescription varchar(100),
    longDescription varchar(500)
)
GO
bulk insert zzICD10_Diag  from '<path>ICD_Code_Eng_Desc_10CA2017_V1_0.txt' with( FIELDTERMINATOR = '|', ROWTERMINATOR ='\n', CODEPAGE = 'RAW' )
GO
update zzICD10_Diag
set CIHIValue = ltrim(rtrim(CIHIValue)),
    shortDescription = ltrim(rtrim(shortDescription)),
    longDescription = ltrim(rtrim(longDescription))
GO
if exists (select * from information_schema.tables where table_name = 'zzICD10_CCI')
    drop table zzICD10_CCI
GO
Create table zzICD10_CCI(
    CIHIValue varchar(10),
    shortDescription varchar(100),
    longDescription varchar(500)
)
GO
bulk insert zzICD10_CCI  from '<path>CCI_Code_Eng_Desc_CCI2017_V1_0.txt' with( FIELDTERMINATOR = '|', ROWTERMINATOR ='\n', CODEPAGE = 'RAW' )
GO
update zzICD10_CCI
set CIHIValue = ltrim(rtrim(CIHIValue)),
    shortDescription = ltrim(rtrim(shortDescription)),
    longDescription = ltrim(rtrim(longDescription))
GO
Update ICD_diagFile10
set UserDescription = z.longDescription,
    DefaultDescription = z.longDescription,
    ShortDescription = z.shortDescription
-- select z.*, icd.*
from zzICD10_diag z
	inner join ICD_diagFile10 Icd on z.CIHIValue = ICD.CIHIValue
where (icd.DefaultDescription <>  z.longDescription or icd.ShortDescription <> z.shortDescription)
    and icd.code < 500000	--this is for NUNAVUT custom code, do not overwrite description
GO
Update dbo.ICD_ProcFile10
set UserDescription = z.longDescription,
    DefaultDescription = z.longDescription,
    ShortDescription = z.shortDescription
-- select z.*, icd.*
from zzICD10_CCI z
	inner join ICD_ProcFile10 Icd on z.CIHIValue = ICD.CIHIValue
where (icd.DefaultDescription <>  z.longDescription or icd.ShortDescription <> z.shortDescription)
    and icd.code < 500000	--this is for NUNAVUT custom code, do not overwrite description
GO
update ICD_diagFile10
set ValidTo = '2099/03/31'
-- select icd.*
from zzICD10_diag z
	inner join ICD_diagFile10 Icd on z.CIHIValue = ICD.CIHIValue
where icd.validto < '2017/04/01'
GO
update ICD_ProcFile10
set ValidTo = '2099/03/31'
-- select icd.*
from zzICD10_CCI z
	inner join ICD_ProcFile10 Icd on z.CIHIValue = ICD.CIHIValue
where icd.validto < '2017/04/01'
GO
update ICD_diagFile10
set ValidTo = '2017/03/31'
-- select icd.*
from zzICD10_diag z
	right join ICD_diagFile10 Icd on z.CIHIValue = ICD.CIHIValue
where z.cihivalue is null and icd.validto > '2017/03/31' and icd.code < 500000
GO
update ICD_ProcFile10
set ValidTo = '2017/03/31'
-- select icd.*
from zzICD10_CCI z
	right join ICD_ProcFile10 Icd on z.CIHIValue = ICD.CIHIValue
where icd.validto > '2017/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, '2017/04/01', '2099/12/31', 'A', 0, 130, CIHIValue, 0 , ''	
from zzICD10_diag_New
order by code
GO
Update dbo.ICD_DiagFile10
set Displaycode = left(displaycode, 3) + '.' + substring(displaycode, 4, 10)
where len(cihivalue) > 3 and charindex('.', displaycode) = 0 and ISNUMERIC(cihivalue) = 0
GO
declare @MaxCode int
set @MaxCode = (select max(code) from ICD_ProcFile10 where code < 100000)
if @MaxCode is null set @MaxCode = 0

Insert into dbo.ICD_ProcFile10(Code, DisplayCode, UserDescription, DefaultDescription, ShortDescription, CIHIValue, ValidFrom, ValidTo, DiagnosisGender, AgeLow, AgeHigh, InterfaceIn, HospitalID, LUText)
select Code + @MaxCode, CIHIValue, left(longDescription, 255), left(longDescription, 255), left(longDescription, 255), CIHIValue, '2017/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
-------------------------Diagnosis Validation
if exists (select * from information_schema.tables where table_name = 'zzICD10CA_Validation')
    drop table zzICD10CA_Validation
GO
Create table zzICD10CA_Validation(
    CIHIValue varchar(10) COLLATE Latin1_General_CI_AS NOT NULL,
    Sector   varchar(1) COLLATE Latin1_General_CI_AS NOT NULL,
    CIHIFlag   varchar(1) COLLATE Latin1_General_CI_AS NOT NULL,
    MRDxFlag   varchar(1) COLLATE Latin1_General_CI_AS NOT NULL,
    NewBorn   varchar(1) COLLATE Latin1_General_CI_AS NOT NULL,
    DiagType   varchar(1) COLLATE Latin1_General_CI_AS NOT NULL,
    Gender   varchar(1) COLLATE Latin1_General_CI_AS NOT NULL,
    AgeLow   varchar(3) COLLATE Latin1_General_CI_AS NOT NULL,
    AgeHigh   varchar(3) COLLATE Latin1_General_CI_AS NOT NULL
)
GO
bulk insert zzICD10CA_Validation  from '<path>ICD_Code_Validation_10CA2017_V1_0.txt' with( FIELDTERMINATOR = '|', ROWTERMINATOR ='\n')
GO
Update ICD_DiagFile10 set CIHIFlag = v.CIHIFlag
-- select v.*, d.CIHIFlag
from
(select a.CIHIValue, a.Sector, a.CIHIFlag, a.[MRDxFlag], a.[NewBorn], a.[DiagType], a.Gender, a.AgeLow, a.AgeHigh
from 
(select * from zzICD10CA_Validation where Sector = '1') a
inner join
(select * from zzICD10CA_Validation where Sector = 'a') b
on a.cihivalue = b.cihivalue
--where a.CIHIFlag<> b.CIHIFlag
) v
	inner join ICD_DiagFile10 d on v.cihivalue = d.cihivalue   
where isnull(v.CIHIFlag, '@@') <> isnull(d.CIHIFlag, '#') and d.code < 500000
GO
Update ICD_DiagFile10 set MRDxFlag = v.MRDxFlag
--select v.*, d.MRDxFlag
from
(select a.CIHIValue, a.Sector, a.CIHIFlag, MRDxFlag = (case when a.[MRDxFlag] = 'Y' then a.[MRDxFlag] when b.[MRDxFlag] = 'Y' then 'A' else b.[MRDxFlag] end), a.[NewBorn], a.[DiagType], a.Gender, a.AgeLow, a.AgeHigh --, b.MRDxFlag
from 
(select * from zzICD10CA_Validation where Sector = '1') a
inner join
(select * from zzICD10CA_Validation where Sector = 'a') b
on a.cihivalue = b.cihivalue
--where a.MRDxFlag<> b.MRDxFlag
) v
	inner join ICD_DiagFile10 d on v.cihivalue = d.cihivalue   
where isnull(v.MRDxFlag, '@@') <> isnull(d.MRDxFlag, '#') and d.code < 500000
GO
Update ICD_DiagFile10 set NewbornFlag = v.NewBorn
--select v.*, d.NewbornFlag
from
(select a.CIHIValue, a.Sector, a.CIHIFlag, MRDxFlag = (case when a.[MRDxFlag] = 'Y' then a.[MRDxFlag] when b.[MRDxFlag] = 'Y' then 'A' else b.[MRDxFlag] end), a.[NewBorn], a.[DiagType], a.Gender, a.AgeLow, a.AgeHigh --, b.MRDxFlag
from 
(select * from zzICD10CA_Validation where Sector = '1') a
inner join
(select * from zzICD10CA_Validation where Sector = 'a') b
on a.cihivalue = b.cihivalue
--where a.NewBorn<> b.NewBorn
) v
	inner join ICD_DiagFile10 d on v.cihivalue = d.cihivalue   
where isnull(v.NewBorn, '@@') <> isnull(d.NewbornFlag, '#') and d.code < 500000
GO
Update ICD_DiagFile10 set TypeCode = isnull(v.DiagType, '')
--select v.*, d.TypeCode
from
(select a.CIHIValue, a.Sector, a.CIHIFlag, MRDxFlag = (case when a.[MRDxFlag] = 'Y' then a.[MRDxFlag] else b.[MRDxFlag] end), a.[NewBorn], a.[DiagType], a.Gender, a.AgeLow, a.AgeHigh --, b.[DiagType]
from 
(select * from zzICD10CA_Validation where Sector = '1') a
inner join
(select * from zzICD10CA_Validation where Sector = 'a') b
on a.cihivalue = b.cihivalue
--where a.DiagType<> b.DiagType
) v
	inner join ICD_DiagFile10 d on v.cihivalue = d.cihivalue   
where isnull(v.DiagType, '') <> isnull(d.TypeCode, '') and d.code < 500000
GO
Update ICD_DiagFile10 set DiagnosisGender = v.Gender
--select v.*, d.DiagnosisGender
from
(select a.CIHIValue, a.Sector, a.CIHIFlag, a.[MRDxFlag], a.[NewBorn], a.[DiagType], a.Gender, a.AgeLow, a.AgeHigh
from 
(select * from zzICD10CA_Validation where Sector = '1') a
inner join
(select * from zzICD10CA_Validation where Sector = 'a') b
on a.cihivalue = b.cihivalue
--where a.Gender<> b.Gender
) v
	inner join ICD_DiagFile10 d on v.cihivalue = d.cihivalue   
where isnull(v.Gender, '@') <> isnull(d.DiagnosisGender, '#') and d.code < 500000
GO
Update ICD_DiagFile10 set AgeLow = (case when isnull(v.AgeLow, '') = '' then 0 else v.AgeLow end)
--select v.*, d.AgeLow
from
(select a.CIHIValue, a.Sector, a.CIHIFlag, a.[MRDxFlag], a.[NewBorn], a.[DiagType], a.Gender, a.AgeLow, a.AgeHigh
from 
(select * from zzICD10CA_Validation where Sector = '1') a
inner join
(select * from zzICD10CA_Validation where Sector = 'a') b
on a.cihivalue = b.cihivalue
--where a.AgeLow<> b.AgeLow
) v
	inner join ICD_DiagFile10 d on v.cihivalue = d.cihivalue   
where (case when isnull(v.AgeLow, '') = '' then 0 else v.AgeLow end) <> isnull(d.AgeLow, -1) and d.code < 500000
GO
Update ICD_DiagFile10 set AgeHigh = (case when isnull(v.AgeHigh, '') = '' then 0 else v.AgeHigh end)
--select v.*, d.AgeHigh
from
(select a.CIHIValue, a.Sector, a.CIHIFlag, a.[MRDxFlag], a.[NewBorn], a.[DiagType], a.Gender, a.AgeLow, a.AgeHigh--, b.AgeHigh
from 
(select * from zzICD10CA_Validation where Sector = '1') a
inner join
(select * from zzICD10CA_Validation where Sector = 'a') b
on a.cihivalue = b.cihivalue
--where a.AgeHigh<> b.AgeHigh
) v
	inner join ICD_DiagFile10 d on v.cihivalue = d.cihivalue   
where (case when isnull(v.AgeHigh, '') = '' then 0 else v.AgeHigh end) <> isnull(d.AgeHigh, -1) and d.code < 500000
GO
if exists (select * from information_schema.tables where table_name = 'zzICD10_CC1_Validation')
    drop table zzICD10_CC1_Validation
GO
Create table zzICD10_CC1_Validation(
    CIHIValue varchar(10) COLLATE Latin1_General_CI_AS NOT NULL,
    Sector   varchar(1) COLLATE Latin1_General_CI_AS NOT NULL,
    CIHIFlag   varchar(1) COLLATE Latin1_General_CI_AS NOT NULL,
    Gender   varchar(1) COLLATE Latin1_General_CI_AS NOT NULL,
    AgeLow   varchar(6) COLLATE Latin1_General_CI_AS NOT NULL,
    AgeHigh   varchar(6) COLLATE Latin1_General_CI_AS NOT NULL
)
GO
bulk insert zzICD10_CC1_Validation  from '<path>CCI_Code_Validation_CCI2017_V1_0.txt' with( FIELDTERMINATOR = '|', ROWTERMINATOR ='\n')
GO
Update ICD_ProcFile10 set Sector = v.Sector
--select v.*, d.Sector
from
(select a.CIHIValue, a.Sector, a.CIHIFlag, a.Gender, a.AgeLow, a.AgeHigh
from 
(select * from zzICD10_CC1_Validation where Sector = '1') a
) v
	inner join ICD_ProcFile10 d on v.cihivalue = d.cihivalue   
where isnull(v.Sector, '@@') <> isnull(d.Sector, '#') and d.Code < 500000
GO
Update ICD_ProcFile10 set Sector = v.Sector
--select v.*, d.CIHIFlag
from
(select a.CIHIValue, a.Sector, a.CIHIFlag, a.Gender, a.AgeLow, a.AgeHigh
from 
(select * from zzICD10_CC1_Validation where Sector = 'a') a
) v
	inner join ICD_ProcFile10 d on v.cihivalue = d.cihivalue   
where isnull(v.Sector, '') = '' and d.Code < 500000
GO
Update ICD_ProcFile10 set CIHIFlag = v.CIHIFlag
--select v.*, d.CIHIFlag
from
(select a.CIHIValue, a.Sector, a.CIHIFlag, a.Gender, a.AgeLow, a.AgeHigh
from 
(select * from zzICD10_CC1_Validation where Sector = '1') a
inner join
(select * from zzICD10_CC1_Validation where Sector = 'a') b
on a.cihivalue = b.cihivalue
) v
	inner join ICD_ProcFile10 d on v.cihivalue = d.cihivalue   
where isnull(v.CIHIFlag, '@@') <> isnull(d.CIHIFlag, '#') and d.Code < 500000
GO
Update ICD_ProcFile10 set DiagnosisGender = v.Gender
--select v.*, d.DiagnosisGender
from
(select a.CIHIValue, a.Sector, a.CIHIFlag, a.Gender, a.AgeLow, a.AgeHigh
from 
(select * from zzICD10_CC1_Validation where Sector = '1') a
inner join
(select * from zzICD10_CC1_Validation where Sector = 'a') b
on a.cihivalue = b.cihivalue
) v
	inner join ICD_ProcFile10 d on v.cihivalue = d.cihivalue   
where isnull(v.Gender, '@') <> isnull(d.DiagnosisGender, '#') and d.Code < 500000
GO
Update ICD_ProcFile10 set AgeLow = (case when isnull(v.AgeLow, '') = '' then 0 else v.AgeLow end)
--select v.*, d.AgeLow
from
(select a.CIHIValue, a.Sector, a.CIHIFlag, a.Gender, a.AgeLow, a.AgeHigh
from 
(select * from zzICD10_CC1_Validation where Sector = '1') a
inner join
(select * from zzICD10_CC1_Validation where Sector = 'a') b
on a.cihivalue = b.cihivalue
) v
	inner join ICD_ProcFile10 d on v.cihivalue = d.cihivalue   
where (case when isnull(v.AgeLow, '') = '' then 0 else v.AgeLow end) <> isnull(d.AgeLow, -1) and d.Code < 500000
GO
Update ICD_ProcFile10 set AgeHigh = (case when isnull(v.AgeHigh, '') = '' then 0 else v.AgeHigh end)
--select v.*, d.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
-------------------------Intervention Attribute Extent
if exists (select * from information_schema.tables where table_name = 'zzICD10CCI_Extent')
    drop table zzICD10CCI_Extent
GO
Create table zzICD10CCI_Extent(
    CIHIValue varchar(10) COLLATE Latin1_General_CI_AS NOT NULL,
    Sector varchar(10) COLLATE Latin1_General_CI_AS NOT NULL,
    ExtentAttribute varchar(10) COLLATE Latin1_General_CI_AS NOT NULL,
    LinkID varchar(10) COLLATE Latin1_General_CI_AS NOT NULL,
)
GO
bulk insert zzICD10CCI_Extent  from '<path>CCI_Extent_Val_CCI2017_V1_0.txt' with( FIELDTERMINATOR = '|', ROWTERMINATOR ='\n', CODEPAGE = 'RAW' )
GO
Update LU_IntervAttExtent
set validto = '2017/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 > '2017/03/31'
GO
Update LU_IntervAttExtent
set validto = '2017/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 > '2017/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 <= '2017/03/31'
GO
if exists (select * from information_schema.tables where table_name = 'zzICD10CCI_Extent_Desc')
    drop table zzICD10CCI_Extent_Desc
GO
Create table zzICD10CCI_Extent_Desc(
    LinkID varchar(10) COLLATE Latin1_General_CI_AS NOT NULL,
    ExtentAttribute varchar(10) COLLATE Latin1_General_CI_AS NOT NULL,
    UserDescription varchar(255) COLLATE Latin1_General_CI_AS NOT NULL
)
GO
bulk insert zzICD10CCI_Extent_Desc  from '<path>CCI_Extent_Eng_CCI2017_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 > '2017/04/01'
GO
if exists (select * from information_schema.tables where table_name = 'zzICD10CCI_Extent_New')
    drop table zzICD10CCI_Extent_New
GO
select
    Code = identity(int, 1, 1),
    v.CIHIValue,
    v.ExtentAttribute,
    v.LinkID
into zzICD10CCI_Extent_New
from ICD_ProcFile10 icd  
	inner join
    (select * from  zzICD10CCI_Extent where sector = '1') v 
     on v.CIHIValue = icd.CIHIValue 
     left join dbo.LU_IntervAttExtent l on icd.code = l.associationid
     and v.LinkID = l.LinkID and v.ExtentAttribute = l.CIHIValue
where l.CIHIValue is null and v.LinkID <> ''
GO
declare @Maxcode int
set @MaxCode = (select max(code) from LU_IntervAttExtent where code < 100000)
if @MaxCode is null set @MaxCode = 500000

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

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

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

update zzICD10CCI_Class
set CIHIValue = ltrim(rtrim(CIHIValue)),
    shortDescription = ltrim(rtrim(shortDescription)),
    longDescription = ltrim(rtrim(longDescription))
GO
-------------------------Diagnosis Cat -- dbo.ICD_DiagCat
if exists (select * from information_schema.tables where table_name = 'zzICD10CA_Cat')
    drop table zzICD10CA_Cat
GO
Create table zzICD10CA_Cat(
    CIHIValue varchar(10) COLLATE Latin1_General_CI_AS NOT NULL,
    shortDescription varchar(100) COLLATE Latin1_General_CI_AS NOT NULL,
    longDescription varchar(255) COLLATE Latin1_General_CI_AS NOT NULL
)
GO
bulk insert zzICD10CA_Cat  from '<path>ICD_Cat1_Eng_Desc_10CA2017_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

--------------------------------- Populate Intervention Clinician Friendly Short Title --------------------------------------
--2.x-0634 
if exists (select * from information_schema.tables where table_name = 'ICD_IntervClinicianTitle')
    drop table ICD_IntervClinicianTitle
GO
CREATE TABLE [dbo].ICD_IntervClinicianTitle(
	CCICode [varchar](10) NOT NULL,
	CCI_SortTitle [varchar](255) NOT NULL,
	CCI_LongTitle [varchar](255) NOT NULL,
	CCI_ClinicianTitle [varchar](255) NOT NULL,
    CONSTRAINT [PK_ICD_IntervClinicianTitle] PRIMARY KEY CLUSTERED (CCICode ASC)
)
GO
if exists (select * from information_schema.tables where table_name = 'zzCCI_Clinic_Desc')
    drop table zzCCI_Clinic_Desc
GO
CREATE TABLE [dbo].zzCCI_Clinic_Desc(
	CCICode [varchar](10) ,
	CCI_SortTitle [varchar](255) ,
	CCI_LongTitle [varchar](1000) ,
	CCI_ClinicianTitle [varchar](255) 
)
GO

bulk insert zzCCI_Clinic_Desc  from '<path>zzCCI_Clinic_Desc.txt' with( FIELDTERMINATOR = '|', ROWTERMINATOR ='\n')
GO

insert into [dbo].ICD_IntervClinicianTitle(	CCICode, CCI_SortTitle, CCI_LongTitle, CCI_ClinicianTitle)
select ltrim(rtrim(CCICode)), ltrim(rtrim(CCI_SortTitle)), ltrim(rtrim(CCI_LongTitle)), ltrim(rtrim(CCI_ClinicianTitle))
from zzCCI_Clinic_Desc
GO
if exists (select * from information_schema.tables where table_name = 'zzCCI_Clinic_Desc')
    drop table zzCCI_Clinic_Desc
GO


--CCRS Medicine
if exists (select * from information_schema.tables where table_name = 'zz2017CCRS_Medicine')
    drop table zz2017CCRS_Medicine
GO
create table zz2017CCRS_Medicine(
    Classification varchar(500)  COLLATE Latin1_General_CI_AS,
	Chemical varchar(500) COLLATE Latin1_General_CI_AS,
    CommonBrandName varchar(500) COLLATE Latin1_General_CI_AS
)
GO
--bulk insert zz2017CCRS_Medicine  from '<path>2017CCRS_Medicine.txt' with ( fieldterminator = '|', rowterminator = '\n', CODEPAGE  = 'RAW' ) 
bulk insert zz2017CCRS_Medicine  from '<path>2017CCRS_Medicine.txt' with ( fieldterminator = '|', rowterminator = '\n', CODEPAGE  = 'RAW' ) 
GO
Update zz2017CCRS_Medicine
set Classification = ltrim(rtrim(Classification)),
	Chemical = ltrim(rtrim(Chemical)),
	CommonBrandName = ltrim(rtrim(CommonBrandName))
GO
Update LLU_MedicationList_MDS20
set Validto = '2017/03/31'
--select a.*
from LLU_MedicationList_MDS20 a
    left join zz2017CCRS_Medicine z on replace(replace(a.Classification, ' ', ''), ',', '') = replace(replace(z.Classification, ' ', ''), ',', '')
			and replace(replace(a.Chemical, ' ', ''), ',', '') = replace(replace(z.Chemical, ' ', ''), ',', '')
			and replace(replace(a.CommonBrandName, ' ', ''), ',', '') = replace(replace(z.CommonBrandName, ' ', ''), ',', '')
where z.Classification is null and a.validto > '2017/04/01'
GO
Update LLU_MedicationList_MDS20
set Validto = '2099/03/31'
--select a.*
from LLU_MedicationList_MDS20 a
    inner join zz2017CCRS_Medicine z on replace(replace(a.Classification, ' ', ''), ',', '') = replace(replace(z.Classification, ' ', ''), ',', '')
			and replace(replace(a.Chemical, ' ', ''), ',', '') = replace(replace(z.Chemical, ' ', ''), ',', '')
			and replace(replace(a.CommonBrandName, ' ', ''), ',', '') = replace(replace(z.CommonBrandName, ' ', ''), ',', '')
where a.validto < '2099/03/31'
GO
if exists (select * from information_schema.tables where table_name = 'zz2017CCRS_Medicine_new')
    drop table zz2017CCRS_Medicine_new
GO
select code = identity(int, 1, 1), z.* into zz2017CCRS_Medicine_new
from LLU_MedicationList_MDS20 a
    right join zz2017CCRS_Medicine z on replace(replace(a.Classification, ' ', ''), ',', '') = replace(replace(z.Classification, ' ', ''), ',', '')
			and replace(replace(a.Chemical, ' ', ''), ',', '') = replace(replace(z.Chemical, ' ', ''), ',', '')
			and replace(replace(a.CommonBrandName, ' ', ''), ',', '') = replace(replace(z.CommonBrandName, ' ', ''), ',', '')
where a.code is null
GO
declare @MaxID int
set @MaxID = isnull((select max(code) from [dbo].[LLU_MedicationList_MDS20]), 0) + 1

Insert into [dbo].[LLU_MedicationList_MDS20](code, Classification, Chemical, CommonBrandName, ValidFrom, ValidTo)
select @MaxID + code, Classification, Chemical, CommonBrandName, ValidFrom = '2017/04/01', ValidTo = '2099/03/31'
from zz2017CCRS_Medicine_new
GO
if exists (select * from information_schema.tables where table_name = 'zz2017CCRS_Medicine_new')
    drop table zz2017CCRS_Medicine_new
GO
if exists (select * from information_schema.tables where table_name = 'zz2017CCRS_Medicine')
    drop table zz2017CCRS_Medicine
GO

--ED presenting complaint 
if exists (select * from information_schema.tables where table_name = 'zz2017ED_PCL')
    drop table zz2017ED_PCL
GO
create table zz2017ED_PCL(
	UserDescription varchar(500) COLLATE Latin1_General_CI_AS,
    CIHIValue varchar(500) COLLATE Latin1_General_CI_AS
)
GO
--bulk insert zz2017ED_PCL  from '<path>2017PLC.txt' with ( fieldterminator = '|', rowterminator = '\n', CODEPAGE  = 'RAW' ) 
bulk insert zz2017ED_PCL  from '<path>2017PLC.txt' with ( fieldterminator = '|', rowterminator = '\n', CODEPAGE  = 'RAW' ) 
GO
Update zz2017ED_PCL
set UserDescription = ltrim(rtrim(UserDescription)),
	CIHIValue = ltrim(rtrim(CIHIValue))
GO
update lu_codefile
set UserDescription = z.UserDescription,
    DefaultDescription = z.UserDescription
-- select z.*, a.*
from lu_codefile a
    inner join zz2017ED_PCL z on a.cihivalue = z.cihivalue and a.lookupid = 1100
where a.userdescription <> z.UserDescription
GO
update lu_codefile
set Validto = '2017/03/31'
-- select z.*, a.*
from lu_codefile a
    left join zz2017ED_PCL z on a.cihivalue = z.cihivalue and a.lookupid = 1100
where z.UserDescription is null and a.Validto > '2017/04/01' and a.lookupid = 1100
GO
if exists (select * from information_schema.tables where table_name = 'zz2017ED_PCL_new')
    drop table zz2017ED_PCL_new
GO
select z.*, code = identity(int, 1, 1) into zz2017ED_PCL_new
from lu_codefile a
    right join zz2017ED_PCL z on a.cihivalue = z.cihivalue and a.lookupid = 1100
where a.code is null
order by z.CIHIValue
GO
declare @MaxID int
set @MaxID = 203167
if exists (select * from lu_codefile where code = 203168)
begin
    set @maxID = (select max(code) from lu_codefile where code > 500000 and code < 600000)
    if @maxID is null set @maxID = 500000
end
Insert into lu_codefile(Code, DisplayCode, UserDescription, DefaultDescription, CIHIValue, ValidFrom, ValidTo, InterfaceIn, LookupID, HospitalID, LUText)
select @MaxID+code, CIHIValue, UserDescription, UserDescription, CIHIValue, '2017/04/01', '2099/03/31', CIHIValue, 1100, 0, ''
from zz2017ED_PCL_new
GO
if exists (select * from information_schema.tables where table_name = 'zz2017ED_PCL')
    drop table zz2017ED_PCL
GO
if exists (select * from information_schema.tables where table_name = 'zz2017ED_PCL_new')
    drop table zz2017ED_PCL_new
GO
--MIS Code
if exists (select * from information_schema.tables where table_name = 'zz2017MISCode')
    drop table zz2017MISCode
GO
create table zz2017MISCode(
	FUNCTIONAL_CENTRE_CODE	varchar(500) COLLATE Latin1_General_CI_AS,
    FUNCTIONAL_CENTRE_SERIES_CODE	varchar(500) COLLATE Latin1_General_CI_AS,
    FUNCT_CENTRE_SERIES_E_DESC	varchar(500) COLLATE Latin1_General_CI_AS,
    AMCARE_TYPE_CODE	varchar(500) COLLATE Latin1_General_CI_AS,
    AMCARE_TYPE_E_DESC	varchar(500) COLLATE Latin1_General_CI_AS,
    AMCARE_GROUP_CODE	varchar(500) COLLATE Latin1_General_CI_AS,
    AMCARE_GROUP_E_DESC varchar(500) COLLATE Latin1_General_CI_AS
)
GO
--bulk insert zz2017MISCode  from '<path>2017MISCode.txt' with ( fieldterminator = '|', rowterminator = '\n', CODEPAGE  = 'RAW' ) 
bulk insert zz2017MISCode  from '<path>2017MISCode.txt' with ( fieldterminator = '|', rowterminator = '\n', CODEPAGE  = 'RAW' ) 
GO
Update zz2017MISCode
set FUNCTIONAL_CENTRE_CODE = ltrim(rtrim(FUNCTIONAL_CENTRE_CODE)),
	FUNCTIONAL_CENTRE_SERIES_CODE = ltrim(rtrim(FUNCTIONAL_CENTRE_SERIES_CODE)),
	FUNCT_CENTRE_SERIES_E_DESC = ltrim(rtrim(FUNCT_CENTRE_SERIES_E_DESC)),
	AMCARE_TYPE_CODE = ltrim(rtrim(AMCARE_TYPE_CODE)),
	AMCARE_TYPE_E_DESC = ltrim(rtrim(AMCARE_TYPE_E_DESC)),
	AMCARE_GROUP_CODE = ltrim(rtrim(AMCARE_GROUP_CODE)),
	AMCARE_GROUP_E_DESC = ltrim(rtrim(AMCARE_GROUP_E_DESC))
GO
update lu_miscode
set DefaultDescription = z.FUNCT_CENTRE_SERIES_E_DESC
-- select z.*, a.*
from lu_miscode a
    inner join zz2017MISCode z on a.cihivalue = z.FUNCTIONAL_CENTRE_CODE 
where a.DefaultDescription <> z.FUNCT_CENTRE_SERIES_E_DESC and [AmCareGroup] is not null
GO
update lu_miscode
set Validto = '2017/03/31'
-- select z.*, a.*
from lu_miscode a
    left join zz2017MISCode z on a.cihivalue = z.FUNCTIONAL_CENTRE_CODE 
where z.FUNCT_CENTRE_SERIES_E_DESC is null and a.Validto > '2017/04/01' and [AmCareGroup] is not null
GO
update lu_miscode
set Validto = '2099/03/31'
--select z.*, a.*
from lu_miscode a
    inner join zz2017MISCode z on a.cihivalue = z.FUNCTIONAL_CENTRE_CODE 
where a.validto < '2017/04/01' and [AmCareGroup] is not null
GO

--Provider Service
if exists (select * from information_schema.tables where table_name = 'zz2017ProviderService')
    drop table zz2017ProviderService
GO
create table zz2017ProviderService(
	CIHIValue	varchar(500) COLLATE Latin1_General_CI_AS,
    UserDescription varchar(500) COLLATE Latin1_General_CI_AS
)
GO
--bulk insert zz2017ProviderService  from '<path>2017ProviderService.txt' with ( fieldterminator = '|', rowterminator = '\n', CODEPAGE  = 'RAW' ) 
bulk insert zz2017ProviderService  from '<path>2017ProviderService.txt' with ( fieldterminator = '|', rowterminator = '\n', CODEPAGE  = 'RAW' ) 
GO
--For NACRS
update lu_providerspecialty
set DefaultDescription = z.userdescription
-- select z.*, a.*
from lu_providerspecialty a
    inner join zz2017ProviderService z on a.cihivalue = z.cihivalue 
where a.defaultdescription <> z.userdescription 
GO
update lu_providerspecialty
set Validto = '2017/03/31'
-- select z.*, a.*
from lu_providerspecialty a
    left join zz2017ProviderService z on a.cihivalue = z.cihivalue 
where z.userdescription is null and a.Validto > '2017/04/01' 
GO
update lu_providerspecialty
set Validto = '2099/03/31'
--select z.*, a.*
from lu_providerspecialty a
    inner join zz2017ProviderService z on a.cihivalue = z.cihivalue 
where a.validto < '2017/04/01' 
GO
if exists (select * from information_schema.tables where table_name = 'zz2017ProviderService_new')
    drop table zz2017ProviderService_new
GO
select z.*, code = identity(int, 1, 1) into zz2017ProviderService_new
from lu_providerspecialty a
    right join zz2017ProviderService z on a.cihivalue = z.cihivalue 
where a.code is null
GO
declare @maxid int
set @maxid = 187
if exists (select * from lu_providerspecialty where code = 188)
begin
    set @maxid = (select max(code) from lu_providerspecialty) + 1
    if @maxid < 500000 
            set @maxId = 500001
    insert into [dbo].[LU_ProviderSpecialty](Code, DisplayCode, UserDescription, DefaultDescription, CIHIValue, ValidFrom, ValidTo, InterfaceIn, HospitalID, LUText)
    select code + @maxId, CIHIValue, UserDescription, UserDescription, CIHIValue, '2017/04/01', '2099/03/31', CIHIValue, 0, ''
    from zz2017ProviderService_new
end    
GO
if exists (select * from information_schema.tables where table_name = 'zz2017ProviderService_new')
    drop table zz2017ProviderService_new
GO
if exists (select * from information_schema.tables where table_name = 'zz2017ProviderService')
    drop table zz2017ProviderService
GO
--DAD Error File
if exists (select * from information_schema.tables where table_name = 'zz2017DADError')
    drop table zz2017DADError
GO
create table zz2017DADError(
	IsError	varchar(500) COLLATE Latin1_General_CI_AS,
    Groupnumber	varchar(500) COLLATE Latin1_General_CI_AS,
    Fieldnumber	varchar(500) COLLATE Latin1_General_CI_AS,
    Errornumber	varchar(500) COLLATE Latin1_General_CI_AS,
    Englisherror	varchar(500) COLLATE Latin1_General_CI_AS,
    Frencherror	varchar(500) COLLATE Latin1_General_CI_AS,
    Associatedfile varchar(500) COLLATE Latin1_General_CI_AS
)
GO
--bulk insert zz2017DADError  from '<path>2017_DADError.txt' with ( fieldterminator = '|', rowterminator = '\n', CODEPAGE  = 'RAW' ) 
bulk insert zz2017DADError  from '<path>2017_DADError.txt' with ( fieldterminator = '|', rowterminator = '\n', CODEPAGE  = 'RAW' ) 
GO
--0725 
Delete from [dbo].[M_DADErrors] where batchyear = 2017
GO
alter table zz2017DADError add code int identity(1, 1)
GO
Insert into [dbo].[M_DADErrors](BatchYear, ErrorNumber, EnglishDescription, FrenchDescription, IsError)
select BatchYear = 2017, d.ErrorNumber, z.Englisherror, z.Frencherror, IsError = (case when z.IsError = 'Y' then 1 else 0 end)
from 
(select ErrorNumber = IsError + Groupnumber + Fieldnumber + isnull(Errornumber, ''), count(*) cc, min(code) mincode
from zz2017DADError
where Englisherror is not null
group by  IsError + Groupnumber + Fieldnumber + isnull(Errornumber, '')
) d
inner join zz2017DADError z on d.mincode = z.code 
GO
if exists (select * from information_schema.tables where table_name = 'zz2017DADError')
    drop table zz2017DADError
GO
--NACRS Error File
if exists (select * from information_schema.tables where table_name = 'zz2017NACRSError')
    drop table zz2017NACRSError
GO
create table zz2017NACRSError(
    Errornumber	varchar(500) COLLATE Latin1_General_CI_AS,
    Englisherror	varchar(500) COLLATE Latin1_General_CI_AS
)
GO
--bulk insert zz2017NACRSError  from '<path>2017_NACRSError.txt' with ( fieldterminator = '|', rowterminator = '\n', CODEPAGE  = 'RAW' ) 
bulk insert zz2017NACRSError  from '<path>2017_NACRSError.txt' with ( fieldterminator = '|', rowterminator = '\n', CODEPAGE  = 'RAW' ) 
GO
--0725 
Delete from [dbo].[M_AmcareErrors] where batchyear = 2017
GO
Insert into [dbo].[M_AmcareErrors](ErrorNumber, ErrorDescription, IsError, BatchYear)
select z.ErrorNumber, z.Englisherror, IsError = (case when z.ErrorNumber like 'w%' then 0 else 1 end), BatchYear = 2017
from zz2017NACRSError z
GO
if exists (select * from information_schema.tables where table_name = 'zz2017NACRSError')
    drop table zz2017NACRSError
GO
--CCRS Pick List
if exists (select * from information_schema.tables where table_name = 'zz2017CCRSPickList')
    drop table zz2017CCRSPickList
GO
create table zz2017CCRSPickList(
    CIHIValue	varchar(500) COLLATE Latin1_General_CI_AS,
    UserDescription	varchar(500) COLLATE Latin1_General_CI_AS
)
GO
--bulk insert zz2017CCRSPickList  from '<path>2017_CCRSPickList.txt' with ( fieldterminator = '|', rowterminator = '\n', CODEPAGE  = 'RAW' ) 
bulk insert zz2017CCRSPickList  from '<path>2017_CCRSPickList.txt' with ( fieldterminator = '|', rowterminator = '\n', CODEPAGE  = 'RAW' ) 
GO
Update zz2017CCRSPickList
set CIHIValue = ltrim(rtrim(CIHIValue)),
    UserDescription = ltrim(rtrim(UserDescription))
GO
update [dbo].[ICD_DiagFileMDS]
set [UserDescription] = z.UserDescription,
    [DefaultDescription] = z.UserDescription
--select z.*, a.*
from [dbo].[ICD_DiagFileMDS] a
    inner join zz2017CCRSPickList z on a.cihivalue = z.cihivalue
where a.userdescription <> z.userdescription
GO
update [dbo].[ICD_DiagFileMDS]
set ValidTo = '2017/03/31'
--select z.*, a.*
from [dbo].[ICD_DiagFileMDS] a
    left join zz2017CCRSPickList z on a.cihivalue = z.cihivalue
where z.cihivalue is null and a.validto > '2017/03/31'
GO
if exists (select * from information_schema.tables where table_name = 'zz2017CCRSPickList')
    drop table zz2017CCRSPickList
GO

Delete from tGrouper_CACS_ANAESTHETIC_ASSIGN where methodology_year in (2016, 2106)
GO
Insert into [dbo].[tGrouper_CACS_ANAESTHETIC_ASSIGN](methodology_year, anaesthetic_technique_code, cacs_anaesthetic_code) values(2016, 0, 8)
GO
Insert into [dbo].[tGrouper_CACS_ANAESTHETIC_ASSIGN](methodology_year, anaesthetic_technique_code, cacs_anaesthetic_code) values(2016, 1, 1)
GO
Insert into [dbo].[tGrouper_CACS_ANAESTHETIC_ASSIGN](methodology_year, anaesthetic_technique_code, cacs_anaesthetic_code) values(2016, 2, 1)
GO
Insert into [dbo].[tGrouper_CACS_ANAESTHETIC_ASSIGN](methodology_year, anaesthetic_technique_code, cacs_anaesthetic_code) values(2016, 3, 1)
GO
Insert into [dbo].[tGrouper_CACS_ANAESTHETIC_ASSIGN](methodology_year, anaesthetic_technique_code, cacs_anaesthetic_code) values(2016, 4, 1)
GO
Insert into [dbo].[tGrouper_CACS_ANAESTHETIC_ASSIGN](methodology_year, anaesthetic_technique_code, cacs_anaesthetic_code) values(2016, 5, 2)
GO
Insert into [dbo].[tGrouper_CACS_ANAESTHETIC_ASSIGN](methodology_year, anaesthetic_technique_code, cacs_anaesthetic_code) values(2016, 6, 2)
GO
Insert into [dbo].[tGrouper_CACS_ANAESTHETIC_ASSIGN](methodology_year, anaesthetic_technique_code, cacs_anaesthetic_code) values(2016, 7, 4)
GO
Insert into [dbo].[tGrouper_CACS_ANAESTHETIC_ASSIGN](methodology_year, anaesthetic_technique_code, cacs_anaesthetic_code) values(2016, 8, 8)
GO
Insert into [dbo].[tGrouper_CACS_ANAESTHETIC_ASSIGN](methodology_year, anaesthetic_technique_code, cacs_anaesthetic_code) values(2016, 9, 3)
GO
Insert into [dbo].[tGrouper_CACS_ANAESTHETIC_ASSIGN](methodology_year, anaesthetic_technique_code, cacs_anaesthetic_code) values(2016, 'C', 1)
GO
Insert into [dbo].[tGrouper_CACS_ANAESTHETIC_ASSIGN](methodology_year, anaesthetic_technique_code, cacs_anaesthetic_code) values(2016, 'U', 8)
GO
----2017 Grouper
delete from tGrouper_CACS_Aanaesthetic where methodology_year = 2017 
GO
delete from tGrouper_CACS_Aanaesthetic_Assign where methodology_year = 2017 
GO
delete from tGrouper_CACS_Age_Category where methodology_year = 2017 
GO
delete from tGrouper_CACS_Age_Category_Assign where methodology_year = 2017 
GO
delete from tGrouper_CACS_Ambulatory_Diag_Assign where methodology_year = 2017 
GO
delete from tGrouper_CACS_Ambulatory_Interv_Assign where methodology_year = 2017 
GO
delete from tGrouper_CACS_ANAESTHETIC_ASSIGN where methodology_year = 2017 
GO
delete from tGrouper_CACS_Base_RIW where methodology_year = 2017 
GO
delete from tGrouper_CACS_Branch_Logic where methodology_year = 2017 
GO
delete from tGrouper_CACS_CACS where methodology_year = 2017 
GO
delete from tGrouper_CACS_ED_Diag_Assign where methodology_year = 2017 
GO
delete from tGrouper_CACS_ED_Interv_Assign where methodology_year = 2017 
GO
delete from tGrouper_CACS_Interaction_Block where methodology_year = 2017 
GO
delete from tGrouper_CACS_Interaction_RIW where methodology_year = 2017 
GO
delete from tGrouper_CACS_Inv_Tech_Category where methodology_year = 2017 
GO
delete from tGrouper_CACS_Inv_Tech_Category_Assign where methodology_year = 2017 
GO
delete from tGrouper_CACS_Inv_Tech_RIW where methodology_year = 2017 
GO
delete from tGrouper_CACS_Logic where methodology_year = 2017 
GO
delete from tGrouper_CACS_Logic_Type where methodology_year = 2017 
GO
delete from tGrouper_CACS_MAC where methodology_year = 2017 
GO
delete from tGrouper_CACS_Partition where methodology_year = 2017 
GO
delete from tGrouper_CACS_Rehab_Program_Aarea_Assign where methodology_year = 2017 
GO
delete from tGrouper_CACS_Split where methodology_year = 2017 
GO
delete from tGrouper_CACS_Split_Interv where methodology_year = 2017 
GO
delete from tGrouper_CACS_Split_Type where methodology_year = 2017 
GO
delete from tGrouper_CACS_Split_Visit_Disp where methodology_year = 2017 
GO
delete from tGrouper_CMG_ATYPICAL_RIW_2015 where methodology_year = 2017 
GO
delete from tGrouper_CMG_BASE_ELOS_RIW where methodology_year = 2017 
GO
delete from tGrouper_CMG_BRANCH_LOGIC where methodology_year = 2017 
GO
delete from tGrouper_CMG_CMG where methodology_year = 2017 
GO
delete from tGrouper_CMG_CMG_AGE_CATEGORY where methodology_year = 2017 
GO
delete from tGrouper_CMG_CMG_AGE_CATEGORY_ASSIGN where methodology_year = 2017 
GO
delete from tGrouper_CMG_CMG_LOGIC where methodology_year = 2017 
GO
delete from tGrouper_CMG_CMG_SPLIT where methodology_year = 2017 
GO
delete from tGrouper_CMG_CMG_SPLIT_ANAESTHETIC where methodology_year = 2017 
GO
delete from tGrouper_CMG_CMG_SPLIT_DIAG where methodology_year = 2017 
GO
delete from tGrouper_CMG_CMG_SPLIT_ENTRY where methodology_year = 2017 
GO
delete from tGrouper_CMG_CMG_SPLIT_GESTATION where methodology_year = 2017 
GO
delete from tGrouper_CMG_CMG_SPLIT_INTERV where methodology_year = 2017 
GO
delete from tGrouper_CMG_CMG_WEIGHT_ASSIGN where methodology_year = 2017 
GO
delete from tGrouper_CMG_COMORBIDITY_CNT where methodology_year = 2017 
GO
delete from tGrouper_CMG_COMORBIDITY_CNT_FACTOR_ASSIGN where methodology_year = 2017 
GO
delete from tGrouper_CMG_COMORBIDITY_ELOS_RIW where methodology_year = 2017 
GO
delete from tGrouper_CMG_COMORBIDITY_EXCLUSION_PAIR where methodology_year = 2017 
GO
delete from tGrouper_CMG_COMORBIDITY_FACTOR_ASSIGN where methodology_year = 2017 
GO
delete from tGrouper_CMG_COMORBIDITY_INTERACTION where methodology_year = 2017 
GO
delete from tGrouper_CMG_COMORBIDITY_LEVEL where methodology_year = 2017 
GO
delete from tGrouper_CMG_COMORBIDITY_LEVEL_ASSIGN where methodology_year = 2017 
GO
delete from tGrouper_CMG_EXCEPTION_BRANCH_ASSIGN where methodology_year = 2017 
GO
delete from tGrouper_CMG_FACTOR_INTERACTION_ELOS_RIW_2011 where methodology_year = 2017 
GO
delete from tGrouper_CMG_FLAGGED_INTERV_ASSIGN where methodology_year = 2017 
GO
delete from tGrouper_CMG_FLAGGED_INTERV_CATEGORY where methodology_year = 2017 
GO
delete from tGrouper_CMG_FLAGGED_INTERV_ELOS_RIW_2015 where methodology_year = 2017 
GO
delete from tGrouper_CMG_FLAGGED_INTERV_EXCLUSION where methodology_year = 2017 
GO
delete from tGrouper_CMG_FLAGGED_INTERV_GROUP where methodology_year = 2017 
GO
delete from tGrouper_CMG_FLAGGED_INTERV_STATUS where methodology_year = 2017 
GO
delete from tGrouper_CMG_FLAGGED_INTERV_STATUS_ASSIGN where methodology_year = 2017 
GO
delete from tGrouper_CMG_INTERV_EVENT_CNT where methodology_year = 2017 
GO
delete from tGrouper_CMG_INTERV_EVENT_ELOS_RIW where methodology_year = 2017 
GO
delete from tGrouper_CMG_INTERV_OOH_ASSIGN where methodology_year = 2017 
GO
delete from tGrouper_CMG_INTERV_OOH_CATEGORY where methodology_year = 2017 
GO
delete from tGrouper_CMG_INTERV_OOH_RIW where methodology_year = 2017 
GO
delete from tGrouper_CMG_IP_FACTOR_INTERACTION_BLOCK where methodology_year = 2017 
GO
delete from tGrouper_CMG_IP_RI_LEVEL where methodology_year = 2017 
GO
delete from tGrouper_CMG_IP_RIW_ATYPICAL where methodology_year = 2017 
GO
delete from tGrouper_CMG_IP_RIW_ATYPICAL_TYPE where methodology_year = 2017 
GO
delete from tGrouper_CMG_LOS_GROUP where methodology_year = 2017 
GO
delete from tGrouper_CMG_LOS_PERCENTILE where methodology_year = 2017 
GO
delete from tGrouper_CMG_MCC_ASSIGN where methodology_year = 2017 
GO
delete from tGrouper_CMG_MCC_INTERV where methodology_year = 2017 
GO
delete from tGrouper_CMG_MCC_INTERV_PARTITION where methodology_year = 2017 
GO
delete from tGrouper_CMG_MCC_LOGIC where methodology_year = 2017 
GO
delete from tGrouper_CMG_MCC_PARTITION where methodology_year = 2017 
GO
delete from tGrouper_CMG_NEWBORN_BRANCH_ASSIGN where methodology_year = 2017 
GO
delete from tGrouper_CMG_PERCENTILE where methodology_year = 2017 
GO
delete from tGrouper_HIG where methodology_year = 2017 
GO
delete from tGrouper_HIG_Age_Category where methodology_year = 2017 
GO
delete from tGrouper_HIG_Age_ELOS_Weight where methodology_year = 2017 
GO
delete from tGrouper_HIG_Atypical where methodology_year = 2017 
GO
delete from tGrouper_HIG_Atypical_Percentile where methodology_year = 2017 
GO
delete from tGrouper_HIG_Base_Elos_Weight where methodology_year = 2017 
GO
delete from tGrouper_HIG_Branch_Logic where methodology_year = 2017 
GO
delete from tGrouper_HIG_Event_Elos_Weight where methodology_year = 2017 
GO
delete from tGrouper_HIG_FI_Elos_Weight where methodology_year = 2017 
GO
delete from tGrouper_HIG_Logic where methodology_year = 2017 
GO
delete from tGrouper_HIG_OOH_Assign where methodology_year = 2017 
GO
delete from tGrouper_HIG_OOH_Factor where methodology_year = 2017 
GO
delete from tGrouper_HIG_Split where methodology_year = 2017 
GO
delete from tGrouper_HIG_Split_Diag where methodology_year = 2017 
GO
delete from tGrouper_HIG_Split_Interv where methodology_year = 2017 
GO
delete from tGrouper_HIG_Split_Type where methodology_year = 2017 
GO
delete from tGrouper_PROV_CACS_BASE_RIW where methodology_year = 2017 
GO
delete from tGrouper_PROV_CACS_INTERACTION_RIW where methodology_year = 2017 
GO
delete from tGrouper_PROV_CACS_INV_TECH_RIW where methodology_year = 2017 
GO
delete from tGrouper_CACS_Age
GO
delete from tGrouper_CACS_Anaesthetic_Technique
GO
delete from tGrouper_CACS_Inv_Tech_cnt
GO
delete from tGrouper_CACS_Program_Area
GO
delete from tGrouper_CACS_VISIT_DISP
GO
delete from tGrouper_CMG_AGE
GO
delete from tGrouper_CMG_ANESTHETIC_TECHNIQUE
GO
delete from tGrouper_CMG_CLASSIFICATION
GO
delete from tGrouper_CMG_CMG_SPLIT_TYPE
GO
delete from tGrouper_CMG_CMG_WEIGHT_CATEGORY
GO
delete from tGrouper_CMG_COMORBIDITY_ASSIGN
GO
delete from tGrouper_CMG_COMORBIDITY_RULE
GO
delete from tGrouper_CMG_DAGGER_ASTERISK
GO
delete from tGrouper_CMG_ENTRY
GO
delete from tGrouper_CMG_FLAGGED_INTERV_CNT
GO
delete from tGrouper_CMG_LOGIC_TYPE
GO
delete from tGrouper_CMG_MCC_PARTITION_Combination
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_ASSIGN from '<path>tGrouper_CACS_ANAESTHETIC_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_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_CACS_Split_Visit_Disp from '<path>tGrouper_CACS_Split_Visit_Disp.txt' with ( fieldterminator = '|', rowterminator = '\n', CODEPAGE  = 'RAW' )  
GO
bulk insert tGrouper_CACS_VISIT_DISP from '<path>tGrouper_CACS_VISIT_DISP.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_2015 from '<path>tGrouper_CMG_ATYPICAL_RIW_2015.txt' with ( fieldterminator = '|', rowterminator = '\n', CODEPAGE  = 'RAW' )  
GO
bulk insert tGrouper_CMG_BASE_ELOS_RIW from '<path>tGrouper_CMG_BASE_ELOS_RIW.txt' with ( fieldterminator = '|', rowterminator = '\n', CODEPAGE  = 'RAW' )  
GO
bulk insert tGrouper_CMG_BRANCH_LOGIC from '<path>tGrouper_CMG_BRANCH_LOGIC.txt' with ( fieldterminator = '|', rowterminator = '\n', CODEPAGE  = 'RAW' )  
GO
bulk insert tGrouper_CMG_CLASSIFICATION from '<path>tGrouper_CMG_CLASSIFICATION.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_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_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_2011 from '<path>tGrouper_CMG_FACTOR_INTERACTION_ELOS_RIW_2011.txt' with ( fieldterminator = '|', rowterminator = '\n', CODEPAGE  = 'RAW' )  
GO
bulk insert tGrouper_CMG_FLAGGED_INTERV_ASSIGN from '<path>tGrouper_CMG_FLAGGED_INTERV_ASSIGN.txt' with ( fieldterminator = '|', rowterminator = '\n', CODEPAGE  = 'RAW' )  
GO
bulk insert tGrouper_CMG_FLAGGED_INTERV_CATEGORY from '<path>tGrouper_CMG_FLAGGED_INTERV_CATEGORY.txt' with ( fieldterminator = '|', rowterminator = '\n', CODEPAGE  = 'RAW' )  
GO
bulk insert tGrouper_CMG_FLAGGED_INTERV_CNT from '<path>tGrouper_CMG_FLAGGED_INTERV_CNT.txt' with ( fieldterminator = '|', rowterminator = '\n', CODEPAGE  = 'RAW' )  
GO
bulk insert tGrouper_CMG_FLAGGED_INTERV_ELOS_RIW_2015 from '<path>tGrouper_CMG_FLAGGED_INTERV_ELOS_RIW_2015.txt' with ( fieldterminator = '|', rowterminator = '\n', CODEPAGE  = 'RAW' )  
GO
bulk insert tGrouper_CMG_FLAGGED_INTERV_EXCLUSION from '<path>tGrouper_CMG_FLAGGED_INTERV_EXCLUSION.txt' with ( fieldterminator = '|', rowterminator = '\n', CODEPAGE  = 'RAW' )  
GO
bulk insert tGrouper_CMG_FLAGGED_INTERV_GROUP from '<path>tGrouper_CMG_FLAGGED_INTERV_GROUP.txt' with ( fieldterminator = '|', rowterminator = '\n', CODEPAGE  = 'RAW' )  
GO
bulk insert tGrouper_CMG_FLAGGED_INTERV_STATUS from '<path>tGrouper_CMG_FLAGGED_INTERV_STATUS.txt' with ( fieldterminator = '|', rowterminator = '\n', CODEPAGE  = 'RAW' )  
GO
bulk insert tGrouper_CMG_FLAGGED_INTERV_STATUS_ASSIGN from '<path>tGrouper_CMG_FLAGGED_INTERV_STATUS_ASSIGN.txt' with ( fieldterminator = '|', rowterminator = '\n', CODEPAGE  = 'RAW' )  
GO
bulk insert tGrouper_CMG_INTERV_EVENT_CNT from '<path>tGrouper_CMG_INTERV_EVENT_CNT.txt' with ( fieldterminator = '|', rowterminator = '\n', CODEPAGE  = 'RAW' )  
GO
bulk insert tGrouper_CMG_INTERV_EVENT_ELOS_RIW from '<path>tGrouper_CMG_INTERV_EVENT_ELOS_RIW.txt' with ( fieldterminator = '|', rowterminator = '\n', CODEPAGE  = 'RAW' )  
GO
bulk insert tGrouper_CMG_INTERV_OOH_ASSIGN from '<path>tGrouper_CMG_INTERV_OOH_ASSIGN.txt' with ( fieldterminator = '|', rowterminator = '\n', CODEPAGE  = 'RAW' )  
GO
bulk insert tGrouper_CMG_INTERV_OOH_CATEGORY from '<path>tGrouper_CMG_INTERV_OOH_CATEGORY.txt' with ( fieldterminator = '|', rowterminator = '\n', CODEPAGE  = 'RAW' )  
GO
bulk insert tGrouper_CMG_INTERV_OOH_RIW from '<path>tGrouper_CMG_INTERV_OOH_RIW.txt' with ( fieldterminator = '|', rowterminator = '\n', CODEPAGE  = 'RAW' )  
GO
bulk insert tGrouper_CMG_IP_FACTOR_INTERACTION_BLOCK from '<path>tGrouper_CMG_IP_FACTOR_INTERACTION_BLOCK.txt' with ( fieldterminator = '|', rowterminator = '\n', CODEPAGE  = 'RAW' )  
GO
bulk insert tGrouper_CMG_IP_RI_LEVEL from '<path>tGrouper_CMG_IP_RI_LEVEL.txt' with ( fieldterminator = '|', rowterminator = '\n', CODEPAGE  = 'RAW' )  
GO
bulk insert tGrouper_CMG_IP_RIW_ATYPICAL from '<path>tGrouper_CMG_IP_RIW_ATYPICAL.txt' with ( fieldterminator = '|', rowterminator = '\n', CODEPAGE  = 'RAW' )  
GO
bulk insert tGrouper_CMG_IP_RIW_ATYPICAL_TYPE from '<path>tGrouper_CMG_IP_RIW_ATYPICAL_TYPE.txt' with ( fieldterminator = '|', rowterminator = '\n', CODEPAGE  = 'RAW' )  
GO
bulk insert tGrouper_CMG_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_HIG from '<path>tGrouper_HIG.txt' with ( fieldterminator = '|', rowterminator = '\n', CODEPAGE  = 'RAW' )  
GO
bulk insert tGrouper_HIG_Age_Category from '<path>tGrouper_HIG_Age_Category.txt' with ( fieldterminator = '|', rowterminator = '\n', CODEPAGE  = 'RAW' )  
GO
bulk insert tGrouper_HIG_Age_ELOS_Weight from '<path>tGrouper_HIG_Age_ELOS_Weight.txt' with ( fieldterminator = '|', rowterminator = '\n', CODEPAGE  = 'RAW' )  
GO
bulk insert tGrouper_HIG_Atypical from '<path>tGrouper_HIG_Atypical.txt' with ( fieldterminator = '|', rowterminator = '\n', CODEPAGE  = 'RAW' )  
GO
bulk insert tGrouper_HIG_Atypical_Percentile from '<path>tGrouper_HIG_Atypical_Percentile.txt' with ( fieldterminator = '|', rowterminator = '\n', CODEPAGE  = 'RAW' )  
GO
bulk insert tGrouper_HIG_Base_Elos_Weight from '<path>tGrouper_HIG_Base_Elos_Weight.txt' with ( fieldterminator = '|', rowterminator = '\n', CODEPAGE  = 'RAW' )  
GO
bulk insert tGrouper_HIG_Branch_Logic from '<path>tGrouper_HIG_Branch_Logic.txt' with ( fieldterminator = '|', rowterminator = '\n', CODEPAGE  = 'RAW' )  
GO
bulk insert tGrouper_HIG_Event_Elos_Weight from '<path>tGrouper_HIG_Event_Elos_Weight.txt' with ( fieldterminator = '|', rowterminator = '\n', CODEPAGE  = 'RAW' )  
GO
bulk insert tGrouper_HIG_FI_Elos_Weight from '<path>tGrouper_HIG_FI_Elos_Weight.txt' with ( fieldterminator = '|', rowterminator = '\n', CODEPAGE  = 'RAW' )  
GO
bulk insert tGrouper_HIG_Logic from '<path>tGrouper_HIG_Logic.txt' with ( fieldterminator = '|', rowterminator = '\n', CODEPAGE  = 'RAW' )  
GO
bulk insert tGrouper_HIG_OOH_Assign from '<path>tGrouper_HIG_OOH_Assign.txt' with ( fieldterminator = '|', rowterminator = '\n', CODEPAGE  = 'RAW' )  
GO
bulk insert tGrouper_HIG_OOH_Factor from '<path>tGrouper_HIG_OOH_Factor.txt' with ( fieldterminator = '|', rowterminator = '\n', CODEPAGE  = 'RAW' )  
GO
bulk insert tGrouper_HIG_Split from '<path>tGrouper_HIG_Split.txt' with ( fieldterminator = '|', rowterminator = '\n', CODEPAGE  = 'RAW' )  
GO
bulk insert tGrouper_HIG_Split_Diag from '<path>tGrouper_HIG_Split_Diag.txt' with ( fieldterminator = '|', rowterminator = '\n', CODEPAGE  = 'RAW' )  
GO
bulk insert tGrouper_HIG_Split_Interv from '<path>tGrouper_HIG_Split_Interv.txt' with ( fieldterminator = '|', rowterminator = '\n', CODEPAGE  = 'RAW' )  
GO
bulk insert tGrouper_HIG_Split_Type from '<path>tGrouper_HIG_Split_Type.txt' with ( fieldterminator = '|', rowterminator = '\n', CODEPAGE  = 'RAW' )  
GO
bulk insert tGrouper_PROV_CACS_BASE_RIW from '<path>tGrouper_PROV_CACS_BASE_RIW.txt' with ( fieldterminator = '|', rowterminator = '\n', CODEPAGE  = 'RAW' )  
GO
bulk insert tGrouper_PROV_CACS_INTERACTION_RIW from '<path>tGrouper_PROV_CACS_INTERACTION_RIW.txt' with ( fieldterminator = '|', rowterminator = '\n', CODEPAGE  = 'RAW' )  
GO
bulk insert tGrouper_PROV_CACS_INV_TECH_RIW from '<path>tGrouper_PROV_CACS_INV_TECH_RIW.txt' with ( fieldterminator = '|', rowterminator = '\n', CODEPAGE  = 'RAW' )  
GO
--End Grouper
--********************** NRS ICD Diagnosis and Intervention
if exists (select * from information_schema.tables where table_name = 'zzICD10_diagNRS')
    drop table zzICD10_diagNRS
GO
Create table zzICD10_diagNRS(
    CIHIValue varchar(10),
    shortDescription varchar(100),
    longDescription varchar(1000)
)
GO
bulk insert zzICD10_diagNRS  from '<path>NRS_ICD10CA_Picklist_FY2017-2018_v1.0.txt' with( FIELDTERMINATOR = '|', ROWTERMINATOR ='\n', CODEPAGE = 'RAW' )
GO
update zzICD10_diagNRS
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_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_CCI_Picklist_FY2017-2018_v1.0.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
Update ICD_DiagFileNRS
set UserDescription = left(z.longDescription, 255),
    DefaultDescription = left(z.longDescription, 255),
    ShortDescription = z.shortDescription
-- select z.*, icd.*
from zzICD10_diagNRS z
	inner join ICD_DiagFileNRS Icd on z.CIHIValue = ICD.CIHIValue
where (icd.DefaultDescription <>  left(z.longDescription, 255) or icd.ShortDescription <> z.shortDescription)
    and icd.code < 500000	--this is for NUNAVUT custom code, do not overwrite description
GO
Update dbo.ICD_ProcFileNRS
set UserDescription = left(z.longDescription, 255),
    DefaultDescription = left(z.longDescription, 255),
    ShortDescription = z.shortDescription
-- select z.*, icd.*
from zzICD10_CCI_NRS z
	inner join ICD_ProcFileNRS Icd on z.CIHIValue = ICD.CIHIValue
where (icd.DefaultDescription <>  left(z.longDescription, 255) or icd.ShortDescription <> z.shortDescription)
    and icd.code < 500000	--this is for NUNAVUT custom code, do not overwrite description
GO
update ICD_DiagFileNRS
set ValidTo = '2099/03/31'
-- select icd.*
from zzICD10_diagNRS z
	inner join ICD_DiagFileNRS Icd on z.CIHIValue = ICD.CIHIValue
where icd.validto < '2017/04/01'
GO
update ICD_ProcFileNRS
set ValidTo = '2099/03/31'
-- select icd.*
from zzICD10_CCI_NRS z
	inner join ICD_ProcFileNRS Icd on z.CIHIValue = ICD.CIHIValue
where icd.validto < '2017/04/01'
GO
update ICD_DiagFileNRS
set ValidTo = '2017/03/31'
-- select icd.*
from zzICD10_diagNRS z
	right join ICD_DiagFileNRS Icd on z.CIHIValue = ICD.CIHIValue
where z.cihivalue is null and icd.validto > '2017/03/31' and icd.code < 500000
GO
update ICD_ProcFileNRS
set ValidTo = '2017/03/31'
-- select icd.*
from zzICD10_CCI_NRS z
	right join ICD_ProcFileNRS Icd on z.CIHIValue = ICD.CIHIValue
where icd.validto > '2017/03/31' and  z.cihivalue is null and icd.code < 500000
GO

-------------------------NRS Diagnosis Validation
if exists (select * from information_schema.tables where table_name = 'zzICD10CA_NRS_Validation')
    drop table zzICD10CA_NRS_Validation
GO
Create table zzICD10CA_NRS_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(10) COLLATE Latin1_General_CI_AS NOT NULL
)
GO
bulk insert zzICD10CA_NRS_Validation  from '<path>NRS_ICD10CA_Validation_FY2017-2018_v1.0.txt' with( FIELDTERMINATOR = '|', ROWTERMINATOR ='\n')
GO
Update ICD_DiagFileNRS set DiagnosisGender = v.Gender
--select v.*, d.DiagnosisGender
from zzICD10CA_NRS_Validation v
	inner join ICD_DiagFileNRS d on v.cihivalue = d.cihivalue   
where isnull(v.Gender, '@') <> isnull(d.DiagnosisGender, '#') and d.code < 500000
GO
Update ICD_DiagFileNRS set AgeLow = (case when isnull(v.AgeLow, '') = '' then 0 else v.AgeLow end)
--select v.*, d.AgeLow
from zzICD10CA_NRS_Validation v
	inner join ICD_DiagFileNRS 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_DiagFileNRS set AgeHigh = (case when isnull(v.AgeHigh, '') = '' then 0 else v.AgeHigh end)
--select v.*, d.AgeHigh
from zzICD10CA_NRS_Validation v
	inner join ICD_DiagFileNRS d on v.cihivalue = d.cihivalue   
where (case when isnull(v.AgeHigh, '') = '' then 0 else v.AgeHigh end) <> isnull(d.AgeHigh, -1) and d.code < 500000
GO
if exists (select * from information_schema.tables where table_name = 'zzICD10_CC1_NRS_Validation')
    drop table zzICD10_CC1_NRS_Validation
GO
Create table zzICD10_CC1_NRS_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_NRS_Validation  from '<path>NRS_CCI_Validation_FY2017-2018_v1.0.txt' with( FIELDTERMINATOR = '|', ROWTERMINATOR ='\n')
GO
Update ICD_ProcFileNRS set DiagnosisGender = v.Gender
--select v.*, d.DiagnosisGender
from zzICD10_CC1_NRS_Validation v
	inner join ICD_ProcFileNRS d on v.cihivalue = d.cihivalue   
where isnull(v.Gender, '@') <> isnull(d.DiagnosisGender, '#') and d.Code < 500000
GO
Update ICD_ProcFileNRS set AgeLow = (case when isnull(v.AgeLow, '') = '' then 0 else v.AgeLow end)
--select v.*, d.AgeLow
from zzICD10_CC1_NRS_Validation v
	inner join ICD_ProcFileNRS 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_ProcFileNRS set AgeHigh = (case when isnull(v.AgeHigh, '') = '' then 0 else v.AgeHigh end)
--select v.*, d.AgeLow
from zzICD10_CC1_NRS_Validation v
	inner join ICD_ProcFileNRS d on v.cihivalue = d.cihivalue   
where (case when isnull(v.AgeHigh, '') = '' then 0 else v.AgeHigh end) <> isnull(d.AgeHigh, -1) and d.Code < 500000
GO

if exists (select * from information_schema.tables where table_name = 'zzICD10_diagNRS')
    drop table zzICD10_diagNRS
GO
if exists (select * from information_schema.tables where table_name = 'zzICD10_CCI_NRS')
    drop table zzICD10_CCI_NRS
GO
if exists (select * from information_schema.tables where table_name = 'zzICD10CA_NRS_Validation')
    drop table zzICD10CA_NRS_Validation
GO
if exists (select * from information_schema.tables where table_name = 'zzICD10_CC1_NRS_Validation')
    drop table zzICD10_CC1_NRS_Validation
GO
--GRouper v1.1
if exists (select * from information_schema.tables where table_name = 'zz2017Grouper_cmg_BRANCH_LOGIC')
DROP TABLE zz2017Grouper_cmg_BRANCH_LOGIC
GO
CREATE TABLE zz2017Grouper_cmg_BRANCH_LOGIC ( 
methodology_year varchar(255) COLLATE Latin1_General_CI_AS 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
)
GO
bulk insert zz2017Grouper_cmg_BRANCH_LOGIC from '<path>2017_CMG_branch_logic.csv' with (fieldterminator = '","', rowterminator = '\n', CODEPAGE  = 'RAW' )  
GO
UPdate zz2017Grouper_cmg_BRANCH_LOGIC
set methodology_year = replace(methodology_year, '"', ''),
	branch_long_f_desc = replace(branch_long_f_desc, '''''', ''''),
	branch_short_f_desc = replace(branch_long_f_desc, '''''', ''''),
	exception_flag = replace(replace(exception_flag, '"', ''), '''''', '''')
GO
delete from tGrouper_CMG_BRANCH_LOGIC where methodology_year = 2017
GO
Insert into tGrouper_CMG_BRANCH_LOGIC(methodology_year, mcc_code, mcc_partition_code, mcc_subpartition_code, branch_code, branch_rank, number_of_splits, cmg_split1_code, cmg_split2_code, cmg_split3_code, cmg_split4_code, cmg_split5_code, cmg_split6_code, cmg_split7_code, branch_long_e_desc, branch_long_f_desc, branch_short_e_desc, branch_short_f_desc, exception_flag)
select methodology_year, mcc_code, mcc_partition_code, mcc_subpartition_code, branch_code, branch_rank, number_of_splits, cmg_split1_code, cmg_split2_code, cmg_split3_code, cmg_split4_code, cmg_split5_code, cmg_split6_code, cmg_split7_code, branch_long_e_desc, branch_long_f_desc, branch_short_e_desc, branch_short_f_desc, exception_flag
from [dbo].[zz2017Grouper_cmg_BRANCH_LOGIC]
GO
if exists (select * from information_schema.tables where table_name = 'zz2017Grouper_CMG_CACS')
DROP TABLE zz2017Grouper_CMG_CACS
GO
CREATE TABLE zz2017Grouper_CMG_CACS ( 
methodology_year VARCHAR(255) COLLATE Latin1_General_CI_AS 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
)
GO
bulk insert zz2017Grouper_CMG_CACS from '<path>2017_CMG_cacs.csv' with (fieldterminator = '","', rowterminator = '\n', CODEPAGE  = 'RAW' )  
GO
UPdate zz2017Grouper_CMG_CACS
set methodology_year = replace(methodology_year, '"', ''),
	cacs_long_f_desc = replace(cacs_long_f_desc, '''''', ''''),
	cacs_short_f_desc = replace(replace(cacs_short_f_desc, '"', ''), '''''', '''')
GO
delete from [dbo].[tGrouper_CACS_CACS] where methodology_year = 2017
GO
insert into [dbo].[tGrouper_CACS_CACS](methodology_year, cacs_code, cacs_long_e_desc, cacs_long_f_desc, cacs_short_e_desc, cacs_short_f_desc)
select methodology_year, cacs_code, cacs_long_e_desc, cacs_long_f_desc, cacs_short_e_desc, cacs_short_f_desc
from zz2017Grouper_CMG_CACS
GO
if exists (select * from information_schema.tables where table_name = 'zz2017Grouper_CMG_CACS_BRANCH_LOGIC')
DROP TABLE zz2017Grouper_CMG_CACS_BRANCH_LOGIC
GO
CREATE TABLE zz2017Grouper_CMG_CACS_BRANCH_LOGIC ( 
methodology_year 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,
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
)
GO
bulk insert zz2017Grouper_CMG_CACS_BRANCH_LOGIC from '<path>2017_CMG_cacs_branch_logic.csv' with (fieldterminator = '","', rowterminator = '\n', CODEPAGE  = 'RAW' )  
GO
UPdate zz2017Grouper_CMG_CACS_BRANCH_LOGIC
set methodology_year = replace(methodology_year, '"', ''),
	branch_long_f_desc = replace(branch_long_f_desc, '''''', ''''),
	branch_short_f_desc = replace(branch_short_f_desc, '''''', ''''),
	number_of_splits = replace(number_of_splits, '"', '')
GO
delete from [dbo].tGrouper_CACS_Branch_Logic where methodology_year = 2017
GO
insert into [dbo].tGrouper_CACS_Branch_Logic(methodology_year, logic_type_code, cacs_partition_code, branch_code, branch_rank, branch_long_e_desc, branch_long_f_desc, branch_short_e_desc, branch_short_f_desc, cacs_split1_code, cacs_split2_code, cacs_split3_code, cacs_split4_code, number_of_splits)
select methodology_year, logic_type_code, cacs_partition_code, branch_code, branch_rank, branch_long_e_desc, branch_long_f_desc, branch_short_e_desc, branch_short_f_desc, cacs_split1_code, cacs_split2_code, cacs_split3_code, cacs_split4_code, number_of_splits
from zz2017Grouper_CMG_CACS_BRANCH_LOGIC
GO
if exists (select * from information_schema.tables where table_name = 'zz2017Grouper_cmg_CMG')
DROP TABLE zz2017Grouper_cmg_CMG
GO
CREATE TABLE zz2017Grouper_cmg_CMG ( 
methodology_year VARCHAR(255) COLLATE Latin1_General_CI_AS 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
)
GO
bulk insert zz2017Grouper_cmg_CMG from '<path>2017_CMG_cmg.csv' with (fieldterminator = '","', rowterminator = '\n', CODEPAGE  = 'RAW' )  
GO
UPdate zz2017Grouper_cmg_CMG
set methodology_year = replace(methodology_year, '"', ''),
	cmg_long_f_desc = replace(cmg_long_f_desc, '''''', ''''),
	cmg_short_e_desc = replace(cmg_short_e_desc, '''''', ''''),
	comorbidity_assign_code = replace(comorbidity_assign_code, '"', '')
GO
delete from [dbo].[tGrouper_CMG_CMG] where methodology_year = 2017
GO
insert into [dbo].[tGrouper_CMG_CMG](methodology_year, cmg_code, cmg_long_e_desc, cmg_long_f_desc, cmg_short_e_desc, cmg_short_f_desc, comorbidity_assign_code)
select methodology_year, cmg_code, cmg_long_e_desc, cmg_long_f_desc, cmg_short_e_desc, cmg_short_f_desc, comorbidity_assign_code
from zz2017Grouper_cmg_CMG
GO

if exists (select * from information_schema.tables where table_name = 'zz2017Grouper_cmg_CMG_SPLIT')
DROP TABLE zz2017Grouper_cmg_CMG_SPLIT
GO
CREATE TABLE zz2017Grouper_cmg_CMG_SPLIT ( 
methodology_year VARCHAR(255) COLLATE Latin1_General_CI_AS 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
)
GO
bulk insert zz2017Grouper_cmg_CMG_SPLIT from '<path>2017_CMG_cmg_split.csv' with (fieldterminator = '","', rowterminator = '\n', CODEPAGE  = 'RAW' )  
GO
UPdate zz2017Grouper_cmg_CMG_SPLIT
set methodology_year = replace(methodology_year, '"', ''),
	cmg_split_f_desc = replace(cmg_split_f_desc, '''''', ''''),
	cmg_split_type_code = replace(cmg_split_type_code, '"', '')
GO
delete from [dbo].[tGrouper_CMG_CMG_SPLIT] where methodology_year = 2017
GO
insert into [dbo].[tGrouper_CMG_CMG_SPLIT](methodology_year, cmg_split_code, cmg_split_e_desc, cmg_split_f_desc, cmg_split_type_code)
select methodology_year, cmg_split_code, cmg_split_e_desc, cmg_split_f_desc, cmg_split_type_code
from zz2017Grouper_cmg_CMG_SPLIT
GO
if exists (select * from information_schema.tables where table_name = 'zz2017Grouper_cmg_CMG_SPLIT_TYPE')
DROP TABLE zz2017Grouper_cmg_CMG_SPLIT_TYPE
GO
CREATE TABLE zz2017Grouper_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
)
GO
bulk insert zz2017Grouper_cmg_CMG_SPLIT_TYPE from '<path>2017_CMG_cmg_split_type.csv' with (fieldterminator = '","', rowterminator = '\n', CODEPAGE  = 'RAW' )  
GO
UPdate zz2017Grouper_cmg_CMG_SPLIT_TYPE
set cmg_split_type_code = replace(cmg_split_type_code, '"', ''),
	cmg_split_type_f_desc = replace(replace(cmg_split_type_f_desc, '"', ''), '''''', '''')
GO
delete from [dbo].[tGrouper_CMG_CMG_SPLIT_TYPE]
GO
insert into [dbo].[tGrouper_CMG_CMG_SPLIT_TYPE](cmg_split_type_code, cmg_split_type_e_desc, cmg_split_type_f_desc)
select cmg_split_type_code, cmg_split_type_e_desc, cmg_split_type_f_desc
from zz2017Grouper_cmg_CMG_SPLIT_TYPE
GO

if exists (select * from information_schema.tables where table_name = 'zz2017Grouper_cmg_CMG_WEIGHT_CATEGORY')
DROP TABLE zz2017Grouper_cmg_CMG_WEIGHT_CATEGORY
GO
CREATE TABLE zz2017Grouper_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
)
GO
bulk insert zz2017Grouper_cmg_CMG_WEIGHT_CATEGORY from '<path>2017_CMG_cmg_weight_category.csv' with (fieldterminator = '","', rowterminator = '\n', CODEPAGE  = 'RAW' )  
GO
UPdate zz2017Grouper_cmg_CMG_WEIGHT_CATEGORY
set cmg_weight_category_code = replace(cmg_weight_category_code, '"', ''),
	cmg_weight_category_f_desc = replace(replace(cmg_weight_category_f_desc, '"', ''), '''''', '''')
GO
delete from [dbo].[tGrouper_CMG_CMG_WEIGHT_CATEGORY]
GO
insert into [dbo].[tGrouper_CMG_CMG_WEIGHT_CATEGORY](cmg_weight_category_code, cmg_weight_category_e_desc, cmg_weight_category_f_desc)
select cmg_weight_category_code, cmg_weight_category_e_desc, cmg_weight_category_f_desc
from zz2017Grouper_cmg_CMG_WEIGHT_CATEGORY
GO

if exists (select * from information_schema.tables where table_name = 'zz2017Grouper_cmg_ENTRY')
DROP TABLE zz2017Grouper_cmg_ENTRY
GO
CREATE TABLE zz2017Grouper_cmg_ENTRY ( 
entry_code VARCHAR(255) 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
)
GO
bulk insert zz2017Grouper_cmg_ENTRY from '<path>2017_CMG_entry.csv' with (fieldterminator = '","', rowterminator = '\n', CODEPAGE  = 'RAW' )  
GO
UPdate zz2017Grouper_cmg_ENTRY
set entry_code = replace(entry_code, '"', ''),
	entry_f_desc = replace(replace(entry_f_desc, '"', ''), '''''', '''')
GO
delete from [dbo].[tGrouper_CMG_ENTRY]
GO
insert into [dbo].[tGrouper_CMG_ENTRY](entry_code, entry_e_desc, entry_f_desc)
select entry_code, entry_e_desc, entry_f_desc
from zz2017Grouper_cmg_ENTRY
GO
if exists (select * from information_schema.tables where table_name = 'zz2017Grouper_cmg_FLAGGED_INTERV_CATEGORY')
DROP TABLE zz2017Grouper_cmg_FLAGGED_INTERV_CATEGORY
GO
CREATE TABLE zz2017Grouper_cmg_FLAGGED_INTERV_CATEGORY ( 
methodology_year VARCHAR(255) COLLATE Latin1_General_CI_AS 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,
flagged_interv_group_code VARCHAR(255) COLLATE Latin1_General_CI_AS NULL
)
GO
bulk insert zz2017Grouper_cmg_FLAGGED_INTERV_CATEGORY from '<path>2017_CMG_flagged_interv_category.csv' with (fieldterminator = '","', rowterminator = '\n', CODEPAGE  = 'RAW' )  
GO
UPdate zz2017Grouper_cmg_FLAGGED_INTERV_CATEGORY
set methodology_year = replace(methodology_year, '"', ''),
	flagged_interv_category_f_desc = replace(flagged_interv_category_f_desc, '''''', ''''),
	flagged_interv_group_code = replace(flagged_interv_group_code, '"', '')
GO
delete from [dbo].[tGrouper_CMG_FLAGGED_INTERV_CATEGORY] where methodology_year = 2017
GO
insert into [dbo].[tGrouper_CMG_FLAGGED_INTERV_CATEGORY](methodology_year, flagged_interv_category_code, flagged_interv_category_e_desc, flagged_interv_category_f_desc, flagged_interv_group_code)
select methodology_year, flagged_interv_category_code, flagged_interv_category_e_desc, flagged_interv_category_f_desc, flagged_interv_group_code
from zz2017Grouper_cmg_FLAGGED_INTERV_CATEGORY
GO
if exists (select * from information_schema.tables where table_name = 'zz2017Grouper_cmg_INTERV_EVENT_CNT')
DROP TABLE zz2017Grouper_cmg_INTERV_EVENT_CNT
GO
CREATE TABLE zz2017Grouper_cmg_INTERV_EVENT_CNT ( 
methodology_year VARCHAR(255) COLLATE Latin1_General_CI_AS 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
)
GO
bulk insert zz2017Grouper_cmg_INTERV_EVENT_CNT from '<path>2017_CMG_interv_event_cnt.csv' with (fieldterminator = '","', rowterminator = '\n', CODEPAGE  = 'RAW' )  
GO
UPdate zz2017Grouper_cmg_INTERV_EVENT_CNT
set methodology_year = replace(methodology_year, '"', ''),
	interv_event_cnt_f_desc = replace(replace(interv_event_cnt_f_desc, '"', ''), '''''', '''')
GO
delete from [dbo].[tGrouper_CMG_INTERV_EVENT_CNT] where methodology_year = 2017
GO
insert into [dbo].[tGrouper_CMG_INTERV_EVENT_CNT](methodology_year, interv_event_cnt_code, interv_event_cnt_e_desc, interv_event_cnt_f_desc)
select methodology_year, interv_event_cnt_code, interv_event_cnt_e_desc, interv_event_cnt_f_desc
from zz2017Grouper_cmg_INTERV_EVENT_CNT
GO
if exists (select * from information_schema.tables where table_name = 'zz2017Grouper_cmg_IP_RIW_ATYPICAL')
DROP TABLE zz2017Grouper_cmg_IP_RIW_ATYPICAL
GO
CREATE TABLE zz2017Grouper_cmg_IP_RIW_ATYPICAL ( 
methodology_year VARCHAR(255) COLLATE Latin1_General_CI_AS NULL,
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,
ip_riw_atypical_type_code VARCHAR(255) COLLATE Latin1_General_CI_AS NULL
)
GO
bulk insert zz2017Grouper_cmg_IP_RIW_ATYPICAL from '<path>2017_CMG_ip_riw_atypical.csv' with (fieldterminator = '","', rowterminator = '\n', CODEPAGE  = 'RAW' )  
GO
UPdate zz2017Grouper_cmg_IP_RIW_ATYPICAL
set methodology_year = replace(methodology_year, '"', ''),
	ip_riw_atypical_f_desc = replace(ip_riw_atypical_f_desc, '''''', ''''),
	ip_riw_atypical_type_code = replace(ip_riw_atypical_type_code, '"', '')
GO
delete from [dbo].[tGrouper_CMG_IP_RIW_ATYPICAL] where methodology_year = 2017
GO
insert into [dbo].[tGrouper_CMG_IP_RIW_ATYPICAL](ip_riw_atypical_code, ip_riw_atypical_e_desc, ip_riw_atypical_f_desc, methodology_year, ip_riw_atypical_type_code)
select ip_riw_atypical_code, ip_riw_atypical_e_desc, ip_riw_atypical_f_desc, methodology_year, ip_riw_atypical_type_code
from zz2017Grouper_cmg_IP_RIW_ATYPICAL
GO
if exists (select * from information_schema.tables where table_name = 'zz2017Grouper_cmg_IP_RIW_ATYPICAL_TYPE')
DROP TABLE zz2017Grouper_cmg_IP_RIW_ATYPICAL_TYPE
GO
CREATE TABLE zz2017Grouper_cmg_IP_RIW_ATYPICAL_TYPE ( 
methodology_year VARCHAR(255) COLLATE Latin1_General_CI_AS 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
)
GO
bulk insert zz2017Grouper_cmg_IP_RIW_ATYPICAL_TYPE from '<path>2017_CMG_ip_riw_atypical_type.csv' with (fieldterminator = '","', rowterminator = '\n', CODEPAGE  = 'RAW' )  
GO
UPdate zz2017Grouper_cmg_IP_RIW_ATYPICAL_TYPE
set methodology_year = replace(methodology_year, '"', ''),
	ip_riw_atypical_type_f_desc = replace(replace(ip_riw_atypical_type_f_desc, '"', ''), '''''', '''')
GO
delete from [dbo].[tGrouper_CMG_IP_RIW_ATYPICAL_TYPE] where methodology_year = 2017
GO
insert into [dbo].[tGrouper_CMG_IP_RIW_ATYPICAL_TYPE](methodology_year, ip_riw_atypical_type_code, ip_riw_atypical_type_e_desc, ip_riw_atypical_type_f_desc)
select methodology_year, ip_riw_atypical_type_code, ip_riw_atypical_type_e_desc, ip_riw_atypical_type_f_desc
from zz2017Grouper_cmg_IP_RIW_ATYPICAL_TYPE
GO
if exists (select * from information_schema.tables where table_name = 'zz2017Grouper_cmg_MAC')
DROP TABLE zz2017Grouper_cmg_MAC
GO
CREATE TABLE zz2017Grouper_cmg_MAC ( 
methodology_year VARCHAR(255) COLLATE Latin1_General_CI_AS 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
)
GO
bulk insert zz2017Grouper_cmg_MAC from '<path>2017_CMG_mac.csv' with (fieldterminator = '","', rowterminator = '\n', CODEPAGE  = 'RAW' )  
GO
UPdate zz2017Grouper_cmg_MAC
set methodology_year = replace(methodology_year, '"', ''),
	mac_long_f_desc = replace(mac_long_f_desc, '''''', ''''),
	mac_short_f_desc = replace(replace(mac_short_f_desc, '"', ''), '''''', '''')
GO
delete from [dbo].[tGrouper_CACS_MAC] where methodology_year = 2017
GO
insert into [dbo].[tGrouper_CACS_MAC](methodology_year, mac_code, mac_long_e_desc, mac_long_f_desc, mac_short_e_desc, mac_short_f_desc)
select methodology_year, mac_code, mac_long_e_desc, mac_long_f_desc, mac_short_e_desc, mac_short_f_desc
from zz2017Grouper_cmg_MAC
GO
if exists (select * from information_schema.tables where table_name = 'zz2017Grouper_cmg_MCC_LOGIC')
DROP TABLE zz2017Grouper_cmg_MCC_LOGIC
GO
CREATE TABLE zz2017Grouper_cmg_MCC_LOGIC ( 
methodology_year VARCHAR(255) COLLATE Latin1_General_CI_AS 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
)
GO
bulk insert zz2017Grouper_cmg_MCC_LOGIC from '<path>2017_CMG_mcc_logic.csv' with (fieldterminator = '","', rowterminator = '\n', CODEPAGE  = 'RAW' )  
GO
UPdate zz2017Grouper_cmg_MCC_LOGIC
set methodology_year = replace(methodology_year, '"', ''),
	mcc_long_f_desc = replace(mcc_long_f_desc, '''''', ''''),
	mcc_short_f_desc = replace(mcc_short_f_desc, '''''', ''''),
	logic_type_code = replace(logic_type_code, '"', '')
GO
delete from [dbo].[tGrouper_CMG_MCC_LOGIC] where methodology_year = 2017
GO
insert into [dbo].[tGrouper_CMG_MCC_LOGIC](methodology_year, mcc_code, mcc_long_e_desc, mcc_long_f_desc, mcc_short_e_desc, mcc_short_f_desc, logic_type_code)
select methodology_year, mcc_code, mcc_long_e_desc, mcc_long_f_desc, mcc_short_e_desc, mcc_short_f_desc, logic_type_code
from zz2017Grouper_cmg_MCC_LOGIC
GO
if exists (select * from information_schema.tables where table_name = 'zz2017Grouper_cmg_VISIT_DISP')
DROP TABLE zz2017Grouper_cmg_VISIT_DISP
GO
CREATE TABLE zz2017Grouper_cmg_VISIT_DISP ( 
visit_disp_code VARCHAR(255) COLLATE Latin1_General_CI_AS NULL,
visit_disp_e_desc VARCHAR(255) COLLATE Latin1_General_CI_AS NULL,
visit_disp_f_desc VARCHAR(255) COLLATE Latin1_General_CI_AS NULL
)
GO
bulk insert zz2017Grouper_cmg_VISIT_DISP from '<path>2017_CMG_visit_disp.csv' with (fieldterminator = '","', rowterminator = '\n', CODEPAGE  = 'RAW' )  
GO
UPdate zz2017Grouper_cmg_VISIT_DISP
set visit_disp_code = replace(visit_disp_code, '"', ''),
	visit_disp_f_desc = replace(replace(visit_disp_f_desc, '"', ''), '''''', '''')
GO
delete from [dbo].[tGrouper_CACS_VISIT_DISP]
GO
insert into [dbo].[tGrouper_CACS_VISIT_DISP](visit_disp_code, visit_disp_e_desc, visit_disp_f_desc)
select visit_disp_code, visit_disp_e_desc, visit_disp_f_desc
from zz2017Grouper_cmg_VISIT_DISP
GO
if exists (select * from information_schema.tables where table_name = 'zz2017Grouper_cmg_BRANCH_LOGIC')
DROP TABLE zz2017Grouper_cmg_BRANCH_LOGIC
GO
if exists (select * from information_schema.tables where table_name = 'zz2017Grouper_CMG_CACS')
DROP TABLE zz2017Grouper_CMG_CACS
GO
if exists (select * from information_schema.tables where table_name = 'zz2017Grouper_CMG_CACS_BRANCH_LOGIC')
DROP TABLE zz2017Grouper_CMG_CACS_BRANCH_LOGIC
GO
if exists (select * from information_schema.tables where table_name = 'zz2017Grouper_cmg_CMG')
DROP TABLE zz2017Grouper_cmg_CMG
GO
if exists (select * from information_schema.tables where table_name = 'zz2017Grouper_cmg_CMG_SPLIT')
DROP TABLE zz2017Grouper_cmg_CMG_SPLIT
GO
if exists (select * from information_schema.tables where table_name = 'zz2017Grouper_cmg_CMG_SPLIT_TYPE')
DROP TABLE zz2017Grouper_cmg_CMG_SPLIT_TYPE
GO
if exists (select * from information_schema.tables where table_name = 'zz2017Grouper_cmg_CMG_WEIGHT_CATEGORY')
DROP TABLE zz2017Grouper_cmg_CMG_WEIGHT_CATEGORY
GO
if exists (select * from information_schema.tables where table_name = 'zz2017Grouper_cmg_ENTRY')
DROP TABLE zz2017Grouper_cmg_ENTRY
GO
if exists (select * from information_schema.tables where table_name = 'zz2017Grouper_cmg_FLAGGED_INTERV_CATEGORY')
DROP TABLE zz2017Grouper_cmg_FLAGGED_INTERV_CATEGORY
GO
if exists (select * from information_schema.tables where table_name = 'zz2017Grouper_cmg_INTERV_EVENT_CNT')
DROP TABLE zz2017Grouper_cmg_INTERV_EVENT_CNT
GO
if exists (select * from information_schema.tables where table_name = 'zz2017Grouper_cmg_IP_RIW_ATYPICAL')
DROP TABLE zz2017Grouper_cmg_IP_RIW_ATYPICAL
GO
if exists (select * from information_schema.tables where table_name = 'zz2017Grouper_cmg_IP_RIW_ATYPICAL_TYPE')
DROP TABLE zz2017Grouper_cmg_IP_RIW_ATYPICAL_TYPE
GO
if exists (select * from information_schema.tables where table_name = 'zz2017Grouper_cmg_MAC')
DROP TABLE zz2017Grouper_cmg_MAC
GO
if exists (select * from information_schema.tables where table_name = 'zz2017Grouper_cmg_MCC_LOGIC')
DROP TABLE zz2017Grouper_cmg_MCC_LOGIC
GO
if exists (select * from information_schema.tables where table_name = 'zz2017Grouper_cmg_VISIT_DISP')
DROP TABLE zz2017Grouper_cmg_VISIT_DISP
GO
