October 19, 2018 at 3:38 pm
My OCD kicked in and I rewrote as best I could. Not perfect, but performs much better!
-- Notes: We would not need to include dbo.vwOPPCoverage if the database were designed correctly.
-- No indexes on any table! ?????
-- The current database has no referential integrity or normalization. It is a garbage bin with duplicate rows and haphazard design.
-- Because of this, many hours or work and jumping through hoops needs to be done in order to return any data that makes sense.
-- SSN numbers need to be hashed. Why would you allow garbage data into the database? GIGO
-- EEID lines
select distinct
'EEID' As LineType
,e.EEID
,ISNULL(cm.SSN, 0) As SSN
,cm.First_name
,cm.MI
,cm.Last_name
,cm.Gender
,cm.DOB
FROM
gmstrscma.tbl_OPPEnrollMain cm
join
gmstrscma.tbl_OPP_ACA_Elig e
on
e.EEID = cm.id
join
dbo.vwOPPCoverage c -- View contains row_number on records, CTE with partition by.
on
c.OPP_ID = e.EEID
and
c.RowNum = 1
select distinct
'OFFR' As LineType
,e.EEID
,CAST(FORMAT(GETDATE(), 'MMddyyyyhhmmss') As NVarChar(25))
,'AE'
,case when cm.[Start_Date] < CAST('1/1/' + CAST(DATEPART(YEAR, getdate()) As NVarChar(4)) As Date) then FORMAT(CAST('1/1/' + CAST(DATEPART(YEAR, getdate()) As NVarChar(4)) As Date), 'MM/dd/yyyy') else FORMAT(CAST(cm.[Start_Date] As Date), 'MM/dd/yyyy') end
,case when cm.[Start_Date] < CAST('1/1/' + CAST(DATEPART(YEAR, getdate()) As NVarChar(4)) As Date) then FORMAT(CAST('1/1/' + CAST(DATEPART(YEAR, getdate()) As NVarChar(4)) As Date), 'MM/dd/yyyy') else FORMAT(CAST(cm.[Start_Date] As Date), 'MM/dd/yyyy') end
,FORMAT(CAST('1/1/' + CAST(DATEPART(YEAR, getdate()) As NVarChar(4)) As Date), 'MM/dd/yyyy')
,FORMAT(CAST('12/31/' + CAST(DATEPART(YEAR, getdate()) As NVarChar(4)) As Date), 'MM/dd/yyyy')
,CAST(FORMAT(cm.dtmAdded, 'MM/dd/yyyy hh:mm:ss.ffffff tt') As NVarChar(50))
FROM
gmstrscma.tbl_OPPEnrollMain cm
join
gmstrscma.tbl_OPP_ACA_Elig e
on
e.EEID = cm.id
join
dbo.vwOPPCoverage c
on
c.OPP_ID = e.EEID
and
c.RowNum = 1
-- ELIG lines
select distinct
pc.[LineType]
,CAST(e.EEID As NVarChar(15)) As EEID
,CAST(FORMAT(GETDATE(), 'MMddyyyyhhmmss') As NVarChar(25))
,pc.[PlanCode]
,pc.[PlanDesc]
,pc.[PlanLevel]
,pc.[LevelDesc]
,pc.[LevelFlag]
,pc.[CostE]
,pc.[CostU]
,pc.[MinCov]
,pc.[MinPlan]
,pc.[DepCov]
,pc.[SpouseCov]
,pc.[SelfIns]
,pc.[ACAVP]
,pc.[Wait]
,pc.[Waived]
FROM
gmstrscma.tbl_OPPEnrollMain cm
join
gmstrscma.tbl_OPP_ACA_Elig e
on
e.EEID = cm.id
join
dbo.vwOPPCoverage c
on
c.OPP_ID = e.EEID
and
c.RowNum = 1
OUTER APPLY
dbo.ACAPlanCost pc -- No hard coded smallmoney vars.
-- We would not need this step if the dependents where in a table joined to the enrollees instead of flat file style.
;with DependentTruthTable (OPP_ID, SpouseMed, ChildrenMed, ChildrenDent, ChildrenVis)
As
(
select distinct
cov.OPP_ID
,cov.partner_med As SpouseMed
,case when cov.child1_med + cov.child2_med + cov.child3_med + cov.child4_med + cov.child5_med > 0 then 1 else 0 end As ChildrenMed
,case when cov.child1_dent + cov.child2_dent + cov.child3_dent + cov.child4_dent + cov.child5_dent > 0 then 1 else 0 end As ChildrenDent
,case when cov.child1_vis + cov.child2_vis + cov.child3_vis + cov.child4_vis + cov.child5_vis > 0 then 1 else 0 end As ChildrenVis
FROM
dbo.vwOPPCoverage cov
join
gmstrscma.tbl_OPP_ACA_Elig e
on
e.EEID = cov.OPP_ID
where
cov.RowNum = 1
)
-- COVG lines
select distinct
'COVG' As LineType
,e.EEID
,'AE' As PlanAbbv
,FORMAT(CAST(cm.[Start_Date] As Date), 'MM/dd/yyyy') As PlanStart
,pc.PlanCode
,pc.PlanDesc
,pc.PlanLevel
,pc.LevelDesc
,pc.CostE
,pc.CostU
,FORMAT(CAST(cm.[Start_Date] As Date), 'MM/dd/yyyy') As PlanStartB
,FORMAT(CAST(isnull(cm.Cov_end_date, '12/31/' + CAST(DATEPART(YEAR, getdate()) As nvarchar(4))) As Date), 'MM/dd/yyyy') As CovEnd
,'' As B1
,'' As B2
,'' As B3
,isnull(c.waive_med, 'Y') As Waive
,case isnull(c.waive_med, 'Y') when 'Y' then '06' else '' end As WaiveCode
,case isnull(c.waive_med, 'Y') when 'Y' then 'Waive' else '' end As WaiveText
,FORMAT(cm.dtmAdded, 'MM/dd/yyyy hh:mm:ss.ffffff tt') As DateAdded
,e.EEID
FROM
gmstrscma.tbl_OPPEnrollMain cm
join
gmstrscma.tbl_OPP_ACA_Elig e
on
e.EEID = cm.id
join
dbo.vwOPPCoverage c
on
c.OPP_ID = e.EEID
and
c.RowNum = 1
join
DependentTruthTable d
on
d.OPP_ID = e.EEID
join
dbo.ACAPlanCost pc
on
pc.PlanCode = c.Enroll_med
and
pc.Spouse = d.SpouseMed
and
pc.Child = d.ChildrenMed
-- Unpivot the damned dependents! Why would you store them all in one @#@#%$^ row?
-- We would not need this step if the database were designed correctly.
-- New DB build will normalize Enrollees and dependents.
;with Dependents (OPP_ID, ID_Count, EnrolledBit, SSN, FirstName, MiddleName, LastName, TypeCode, SpouseBit, DOB, Gender, Start, EndCovDate)
As
(
select distinct
c.OPP_ID
,c.OPP_ID + '-' + CAST(d.ID As NVarchar(2))
,d.EnrolledBit
,d.SSN
,d.FirstName
,d.MiddleName
,d.LastName
,d.TypeCode
,d.SpouseBit
,FORMAT(d.DOB, 'MM/dd/yyyy')
,d.Gender
,FORMAT(CAST(isnull(d.Start, '01/01/' + CAST(DATEPART(YEAR, getdate()) As nvarchar(4))) As Date), 'MM/dd/yyyy')
,FORMAT(CAST(isnull(d.EndCovDate, '12/31/' + CAST(DATEPART(YEAR, getdate()) As nvarchar(4))) As Date), 'MM/dd/yyyy')
from
dbo.vwOPPCoverage c
join
gmstrscma.tbl_OPP_ACA_Elig e
on
e.EEID = c.OPP_ID
CROSS APPLY
(
select
case c.Partner_med when 0 then 0 else 1 end As ID
,c.Partner_med As EnrolledBit
,c.SSN_dp As SSN
,c.First_Name_dp As FirstName
,c.MI_dp As MiddleName
,c.Last_Name_dp As LastName
,'Spouse' As TypeCode
,'Y' As SpouseBit
,c.DOB_dp As DOB
,c.gender_dp As Gender
,c.start_dp As Start
,c.end_dp As EndCovDate
union all
select
case when c.Partner_med = 0 then case when c.child1_med = 0 then 0 else 1 end else case when c.child1_med = 0 then 0 else 2 end end As ID
,c.child1_med As EnrolledBit
,c.SSN_child1 As SSN
,c.First_Name_child1 As FirstName
,c.MI_child1 As MiddleName
,c.Last_Name_child1 As LastName
,'Child' As TypeCode
,'N' As SpouseBit
,c.DOB_child1 As DOB
,c.gender_child1 As Gender
,c.start_child1 As Start
,c.end_child1 As EndCovDate
union all
select
case when c.Partner_med = 0 then case when c.child2_med = 0 then 0 else 2 end else case when c.child2_med = 0 then 0 else 3 end end As ID
,c.child2_med As EnrolledBit
,c.SSN_child2 As SSN
,c.First_Name_child2 As FirstName
,c.MI_child2 As MiddleName
,c.Last_Name_child2 As LastName
,'Child' As TypeCode
,'N' As SpouseBit
,c.DOB_child2 As DOB
,c.gender_child2 As Gender
,c.start_child2 As Start
,c.end_child2 As EndCovDate
union all
select
case when c.Partner_med = 0 then case when c.child3_med = 0 then 0 else 3 end else case when c.child3_med = 0 then 0 else 4 end end As ID
,c.child3_med As EnrolledBit
,c.SSN_child3 As SSN
,c.First_Name_child3 As FirstName
,c.MI_child3 As MiddleName
,c.Last_Name_child3 As LastName
,'Child' As TypeCode
,'N' As SpouseBit
,c.DOB_child3 As DOB
,c.gender_child3 As Gender
,c.start_child3 as Start
,c.end_child3 As EndCovDate
union all
select
case when c.Partner_med = 0 then case when c.child4_med = 0 then 0 else 4 end else case when c.child4_med = 0 then 0 else 5 end end As ID
,c.child4_med As EnrolledBit
,c.SSN_child4 As SSN
,c.First_Name_child4 As FirstName
,c.MI_child4 As MiddleName
,c.Last_Name_child4 As LastName
,'Child' As TypeCode
,'N' As SpouseBit
,c.DOB_child4 As DOB
,c.gender_child4 As Gender
,c.start_child4 As Start
,c.end_child4 As EndCovDate
union all
select
case when c.Partner_med = 0 then case when c.child5_med = 0 then 0 else 5 end else case when c.child5_med = 0 then 0 else 6 end end As ID
,c.child5_med As EnrolledBit
,c.SSN_child5 As SSN
,c.First_Name_child5 As FirstName
,c.MI_child5 As MiddleName
,c.Last_Name_child5 As LastName
,'Child' As TypeCode
,'N' As SpouseBit
,c.DOB_child5 As DOB
,c.gender_child5 As Gender
,c.start_child5 As Start
,c.end_child5 As EndCovDate
) d (ID, EnrolledBit, SSN, FirstName, MiddleName, LastName, TypeCode, SpouseBit, DOB, Gender, Start, EndCovDate)
where
c.RowNum = 1
)
select
'DEPI' As LineType
,OPP_ID
,ID_Count
,EnrolledBit
,SSN
,FirstName
,MiddleName
,LastName
,TypeCode
,SpouseBit
,DOB
,Gender
,Start
,EndCovDate
,'' As B1
,OPP_ID
from Dependents where EnrolledBit = 1
October 19, 2018 at 4:28 pm
Looks like you are having fun:D
I do have a comment - avoid FORMAT if the same formatting can be achieved with convert - check https://sqlperformance.com/2015/06/t-sql-queries/format-is-nice-and-all-but
October 19, 2018 at 4:56 pm
frederico_fonseca - Friday, October 19, 2018 4:28 PMLooks like you are having fun:DI do have a comment - avoid FORMAT if the same formatting can be achieved with convert - check https://sqlperformance.com/2015/06/t-sql-queries/format-is-nice-and-all-but
Thank you!
October 19, 2018 at 5:03 pm
I do prefer convert due to the extra functionality over cast. And from a standard point of view I think its better to stick with a single format type unless there are significant performance differences between them.
October 21, 2018 at 8:53 am
frederico_fonseca - Friday, October 19, 2018 5:03 PMI do prefer convert due to the extra functionality over cast. And from a standard point of view I think its better to stick with a single format type unless there are significant performance differences between them.
Also, internally SQL Server will change a CAST to a CONVERT.
If you try this and look at the execution plan SELECT CAST(C AS bigint) FROM (VALUES (1),(2),(3)) T(C)
you will see this within the execution plan:
<ScalarOperator ScalarString="CONVERT(bigint,[Union1003],0)">
<Convert DataType="bigint" Style="0" Implicit="false">
Viewing 5 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply