October 19, 2018 at 3:05 pm
I'm going through some scripts that my coworker wrote, for a good laugh. The HR guy calls him the "SQL Master".
He's coding against a very wide and flat OLTP table that he concocted. He just piles rows upon rows of what he calls "history" into the working table to boot, so you have to get the latest row to work with. I would use a CTE and partition by Id (whatever that may be, I can't tell) and grab a row_number then ditch the cursors, but He's the SQL Master. Love the use of hard coded, smallmoney vars!
Can you figure out what the end result is? Good luck!
--Note for 2018
--AHMO_C is being sent for terms when ADP is expecting AHMO_E to match the offering in the COVG line.
--*** Declare Global Variables
DECLARE @EEIDCount int
DECLARE @Tier varchar(5)
DECLARE @selfm int
DECLARE @partnerm int
DECLARE @c1m int
DECLARE @c2m int
DECLARE @c3m int
DECLARE @c4m int
DECLARE @c5m int
DECLARE @Depsm int
DECLARE @selfd int
DECLARE @partnerd int
DECLARE @c1d int
DECLARE @c2d int
DECLARE @c3d int
DECLARE @c4d int
DECLARE @c5d int
DECLARE @Depsd int
DECLARE @selfv int
DECLARE @partnerv int
DECLARE @c1v int
DECLARE @c2v int
DECLARE @c3v int
DECLARE @c4v int
DECLARE @c5v int
DECLARE @Depsv int
DECLARE @c1fname varchar(30)
DECLARE @c1mname varchar(1)
DECLARE @c1lname varchar(30)
DECLARE @c1gen varchar(1)
DECLARE @c1dob smalldatetime
DECLARE @c1ssn varchar(12)
DECLARE @c1start smalldatetime
DECLARE @c1end smalldatetime
DECLARE @c2fname varchar(30)
DECLARE @c2mname varchar(1)
DECLARE @c2lname varchar(30)
DECLARE @c2gen varchar(1)
DECLARE @c2dob smalldatetime
DECLARE @c2ssn varchar(12)
DECLARE @c2start smalldatetime
DECLARE @c2end smalldatetime
DECLARE @c3fname varchar(30)
DECLARE @c3mname varchar(1)
DECLARE @c3lname varchar(30)
DECLARE @c3gen varchar(1)
DECLARE @c3dob smalldatetime
DECLARE @c3ssn varchar(12)
DECLARE @c3start smalldatetime
DECLARE @c3end smalldatetime
DECLARE @c4fname varchar(30)
DECLARE @c4mname varchar(1)
DECLARE @c4lname varchar(30)
DECLARE @c4gen varchar(1)
DECLARE @c4dob smalldatetime
DECLARE @c4ssn varchar(12)
DECLARE @c4start smalldatetime
DECLARE @c4end smalldatetime
DECLARE @c5fname varchar(30)
DECLARE @c5mname varchar(1)
DECLARE @c5lname varchar(30)
DECLARE @c5gen varchar(1)
DECLARE @c5dob smalldatetime
DECLARE @c5ssn varchar(12)
DECLARE @c5start smalldatetime
DECLARE @c5end smalldatetime
DECLARE @partnerfname varchar(30)
DECLARE @partnermname varchar(1)
DECLARE @partnerlname varchar(30)
DECLARE @partnergen varchar(1)
DECLARE @partnerdob smalldatetime
DECLARE @partnerssn varchar(12)
DECLARE @partnerstart smalldatetime
DECLARE @partnerend smalldatetime
DECLARE @covlevcode varchar(4)
DECLARE @covlevdesc varchar(100)
DECLARE @covemponly varchar(1)
DECLARE @depcount int
DECLARE @INS1_HMOE_Emp smallmoney = 63.41
DECLARE @INS1_HMOU_Emp smallmoney = 359.30
--/318.64/62.97/Employee only
DECLARE @INS1_HMOE_Sp smallmoney = 185.99
DECLARE @INS1_HMOU_Sp smallmoney = 743.96
--/675.40/164.13/Employee +Sp
DECLARE @INS1_HMOE_Ch smallmoney = 152.17
DECLARE @INS1_HMOU_Ch smallmoney = 608.68
--/552.59/134.28/Employee + Ch (ren)
DECLARE @INS1_HMOE_Fam smallmoney = 262.18
DECLARE @INS1_HMOU_Fam smallmoney = 1048.17
--/951.72/231.27/Family
DECLARE @INS1_POSE_Emp smallmoney = 90.02
DECLARE @INS1_POSU_Emp smallmoney = 510.14
--/452.41/89.40/Employee only
DECLARE @INS1_POSE_Sp smallmoney = 212.45
DECLARE @INS1_POSU_Sp smallmoney = 849.81
--/771.50/187.48/Employee +Sp
DECLARE @INS1_POSE_Ch smallmoney = 201.65
DECLARE @INS1_POSU_Ch smallmoney = 806.59
--/732.26/177.95/Employee + Ch (ren)
DECLARE @INS1_POSE_Fam smallmoney = 284.47
DECLARE @INS1_POSU_Fam smallmoney = 1137.88
--/1033.03/251.03/Family
DECLARE @INS2E_Emp smallmoney = 62.67
DECLARE @INS2U_Emp smallmoney = 355.12
--/310.96/61.45/Employee only
DECLARE @INS2E_Sp smallmoney = 158.76
DECLARE @INS2U_Sp smallmoney = 635.03
--/569.24/138.33/Employee +SP
DECLARE @INS2E_Ch smallmoney = 150.40
DECLARE @INS2U_Ch smallmoney = 601.61
--/539.28/131.05/Employee + CH (ren)
DECLARE @INS2E_Fam smallmoney = 217.25
DECLARE @INS2U_Fam smallmoney = 868.99
--/778.96/189.29/ Family
DECLARE @PlanValueSelectedE smallmoney = 0
DECLARE @PlanValueSelectedU smallmoney = 0
DECLARE @PlanValue101LowE smallmoney = 0
DECLARE @PlanValue101LowU smallmoney = 0
DECLARE @PlanValue102LowE smallmoney = 0
DECLARE @PlanValue102LowU smallmoney = 0
DECLARE @PlanValue103LowE smallmoney = 0
DECLARE @PlanValue103LowU smallmoney = 0
DECLARE @OFFERID varchar(3) = ''
DECLARE @01start varchar(15)
DECLARE @01end varchar(15)
DECLARE @02start varchar(15)
DECLARE @02end varchar(15)
DECLARE @03start varchar(15)
DECLARE @03end varchar(15)
DECLARE @04start varchar(15)
DECLARE @04end varchar(15)
--*** Declare Variables for eligible EEID
DECLARE @EEID varchar(10)
DECLARE @CovEndDate varchar(15)
DECLARE @Waive varchar(1)
DECLARE @WaiveCode varchar(2)
DECLARE @WaiveDesc varchar(100)
DECLARE @SSN varchar(12)
DECLARE @FirstName varchar(30)
DECLARE @LastName varchar(30)
DECLARE @MidName varchar(1)
DECLARE @Gender varchar(1)
DECLARE @EEDOB smalldatetime
DECLARE @StartDate smalldatetime
DECLARE @dtmAdded smalldatetime
DECLARE @add1 varchar(50)
DECLARE @add2 varchar(50)
DECLARE @addcity varchar(50)
DECLARE @addstate varchar(50)
DECLARE @addzip varchar(12)
DECLARE @FEIN varchar(20)
--*** Declare Variables for Coverage record
DECLARE @CovID int
DECLARE @Enroll_med varchar(6)
DECLARE @Enroll_dent varchar(6)
DECLARE @Enroll_vis varchar(6)
DECLARE @Cov_end_date smalldatetime
DECLARE @PlanName varchar(15)
DECLARE @defaultcovenddate varchar(10)
DECLARE @planstartdate varchar(10)
DECLARE @planenddate varchar(10)
DECLARE @offrstartdate varchar(10)
DECLARE @offrname varchar(14)
DECLARE @currentSSN varchar(20)
SET @EEIDCount = 0
SET @defaultcovenddate = '12/31/2018'
SET @planstartdate = '1/1/2018'
SET @planenddate = '12/31/2018'
SET @offrstartdate = @planstartdate
SET @offrname = convert(varchar,FORMAT(GETDATE(), 'MMddyyyyhhmmss'))
SET @Waive = 'N'
--*********************************
--Header Record (always required)
--*********************************
Print 'HEAD' + '|' + ----- + '|' + 'UNIV'
--*** Grab Eligible EEID from tbl_OPPEnrollMain for 2015
--*** Eligible EEID will come from list generated from Invoices sent during 2015
--*** EEID list will host in own table and run against EF table
DECLARE OPP_Eligible CURSOR FOR
/**
SELECT convert(int,[Column 1]),
(SELECT TOP 1 [Column 11] FROM [gmstrscma].[tbl_OPP_aca_final_covg] WHERE [Column 1] = q.[Column 1]) AS enddate,
(SELECT TOP 1 [Column 15] FROM [gmstrscma].[tbl_OPP_aca_final_covg] WHERE [Column 1] = q.[Column 1]) AS Waive,
(SELECT TOP 1 [Column 16] FROM [gmstrscma].[tbl_OPP_aca_final_covg] WHERE [Column 1] = q.[Column 1]) AS waivecode,
(SELECT TOP 1 [Column 17] FROM [gmstrscma].[tbl_OPP_aca_final_covg] WHERE [Column 1] = q.[Column 1]) AS waivedesc,
(SELECT TOP 1 [Column 11] FROM [gmstrscma].[tbl_OPP_aca_fin_depi] WHERE [Column 1] = q.[Column 1] AND [Column 2] = '01') AS [01start],
(SELECT TOP 1 [Column 12] FROM [gmstrscma].[tbl_OPP_aca_fin_depi] WHERE [Column 1] = q.[Column 1] AND [Column 2] = '01') AS [01end],
(SELECT TOP 1 [Column 11] FROM [gmstrscma].[tbl_OPP_aca_fin_depi] WHERE [Column 1] = q.[Column 1] AND [Column 2] = '02') AS [02start],
(SELECT TOP 1 [Column 12] FROM [gmstrscma].[tbl_OPP_aca_fin_depi] WHERE [Column 1] = q.[Column 1] AND [Column 2] = '02') AS [02end],
(SELECT TOP 1 [Column 11] FROM [gmstrscma].[tbl_OPP_aca_fin_depi] WHERE [Column 1] = q.[Column 1] AND [Column 2] = '03') AS [03start],
(SELECT TOP 1 [Column 12] FROM [gmstrscma].[tbl_OPP_aca_fin_depi] WHERE [Column 1] = q.[Column 1] AND [Column 2] = '03') AS [03end],
(SELECT TOP 1 [Column 11] FROM [gmstrscma].[tbl_OPP_aca_fin_depi] WHERE [Column 1] = q.[Column 1] AND [Column 2] = '04') AS [04start],
(SELECT TOP 1 [Column 12] FROM [gmstrscma].[tbl_OPP_aca_fin_depi] WHERE [Column 1] = q.[Column 1] AND [Column 2] = '04') AS [04end]
FROM [gmstrscma].[tbl_OPP_aca_fin_list_ids] as q
WHERE [Column 1] = (SELECT ID FROM gmstrscma.tbl_OPPEnrollMain WHERE ID = q.[Column 1] AND GPA_bill = 'NO')
ORDER BY [Column 1]
**/
--SELECT [Column 1]
--FROM [gmstrscma].[tbl_OPP_aca_fin_list_ids] as q
--WHERE [Column 1] = (SELECT ID FROM gmstrscma.tbl_OPPEnrollMain WHERE ID = q.[Column 1] AND GPA_bill = 'NO')
--ORDER BY [Column 1]
--SELECT DISTINCT [column 1]
--FROM gmstrscma.tbl_OPP_aca_fin_list_ids as q
--ORDER BY [Column 1]
SELECT DISTINCT EEID
FROM [gmstrscma].[tbl_OPP_ACA_Elig] as q
ORDER BY EEID
--SELECT EEID FROM [gmstrscma].[tbl_OPP_ACA_Elig] ORDER BY EEID
--SELECT ID, SSN, First_name, MI, Last_name, Gender, DOB FROM gmstrscma.tbl_OPPEnrollMain WHERE terminated is null or terminated = 'N' --temp query for testing
OPEN OPP_Eligible
--*** Loop through EEID list
FETCH NEXT FROM OPP_Eligible
INTO @EEID
--INTO @EEID, @CovEndDate, @Waive, @WaiveCode, @WaiveDesc, @01start, @01end, @02start, @02end, @03start, @03end, @04start, @04end
--INTO @EEID, @SSN, @FirstName, @MidName, @LastName, @Gender, @EEDOB
SET @EEID = LTRIM(@EEID)
-- Check @@FETCH_STATUS to see if there are any more rows to fetch.
WHILE (@@FETCH_STATUS = 0)
BEGIN
--print @EEID
SET @EEIDCount =@EEIDCount + 1
SET @depcount = 0
--*** Grab Most Recent EF Record from tbl_OPPEnrollMain
DECLARE OPP_EF CURSOR FOR
SELECT isnull(SSN,''), First_name, MI, Last_name, Gender, DOB, [Start_date], dtmAdded, Cov_end_date, Address1, Address2, City, State, Zip, FEIN FROM gmstrscma.tbl_OPPEnrollMain WHERE ID = @EEID
OPEN OPP_EF
FETCH NEXT FROM OPP_EF
INTO @SSN, @FirstName, @MidName, @LastName, @Gender, @EEDOB, @StartDate, @dtmAdded, @Cov_end_date, @add1, @add2, @addcity, @addstate, @addzip, @FEIN
CLOSE OPP_EF
DEALLOCATE OPP_EF
--*** Grab Most Recent Coverage Record from tbl_OPPCoverage
DECLARE OPP_Coverage CURSOR FOR
SELECT TOP 1 CASE
WHEN isnull(self_med,'N') = 'Y'
THEN 1
ELSE 0
END AS self_med,
CASE
WHEN isnull(dp_med,'N') = 'Y'
THEN 1
ELSE 0
END AS partner_med,
CASE
WHEN isnull(child1_med,'N') = 'Y'
THEN 1
ELSE 0
END AS child1_med,
CASE
WHEN isnull(child2_med,'N') = 'Y'
THEN 1
ELSE 0
END AS child2_med,
CASE
WHEN isnull(child3_med,'N') = 'Y'
THEN 1
ELSE 0
END AS child3_med,
CASE
WHEN isnull(child4_med,'N') = 'Y'
THEN 1
ELSE 0
END AS child4_med,
CASE
WHEN isnull(child5_med,'N') = 'Y'
THEN 1
ELSE 0
END AS child5_med,
CASE
WHEN isnull(self_dent,'N') = 'Y'
THEN 1
ELSE 0
END AS self_dent,
CASE
WHEN isnull(dp_dent,'N') = 'Y'
THEN 1
ELSE 0
END AS partner_dent,
CASE
WHEN isnull(child1_dent,'N') = 'Y'
THEN 1
ELSE 0
END AS child1_dent,
CASE
WHEN isnull(child2_dent,'N') = 'Y'
THEN 1
ELSE 0
END AS child2_dent,
CASE
WHEN isnull(child3_dent,'N') = 'Y'
THEN 1
ELSE 0
END AS child3_dent,
CASE
WHEN isnull(child4_dent,'N') = 'Y'
THEN 1
ELSE 0
END AS child4_dent,
CASE
WHEN isnull(child5_dent,'N') = 'Y'
THEN 1
ELSE 0
END AS child5_dent,
CASE
WHEN isnull(self_vis,'N') = 'Y'
THEN 1
ELSE 0
END AS self_vis,
CASE
WHEN isnull(dp_vis,'N') = 'Y'
THEN 1
ELSE 0
END AS partner_vis,
CASE
WHEN isnull(child1_vis,'N') = 'Y'
THEN 1
ELSE 0
END AS child1_vis,
CASE
WHEN isnull(child2_vis,'N') = 'Y'
THEN 1
ELSE 0
END AS child2_vis,
CASE
WHEN isnull(child3_vis,'N') = 'Y'
THEN 1
ELSE 0
END AS child3_vis,
CASE
WHEN isnull(child4_vis,'N') = 'Y'
THEN 1
ELSE 0
END AS child4_vis,
CASE
WHEN isnull(child5_vis,'N') = 'Y'
THEN 1
ELSE 0
END AS child5_vis,
CASE
WHEN isnull(waive_med,'N') = 'W'
THEN 'Y'
ELSE 'N'
END AS waive_med,
isnull(Enroll_med,''), isnull(Enroll_dent,''), isnull(Enroll_vis,''), intID, First_name_child1, MI_child1, Last_name_child1, gender_child1, DOB_child1, SSN_child1, start_child1, end_child1, First_name_child2, MI_child2, Last_name_child2, gender_child2, DOB_child2, SSN_child2, start_child2, end_child2, First_name_child3, MI_child3, Last_name_child3, gender_child3, DOB_child3, SSN_child3, start_child3, end_child3, First_name_child4, MI_child4, Last_name_child4, gender_child4, DOB_child4, SSN_child4, start_child4, end_child4, First_name_child5, MI_child5, Last_name_child5, gender_child5, DOB_child5, SSN_child5, start_child5, end_child5, First_name_dp, MI_dp, Last_name_dp, gender_dp, dob_dp, SSN_dp, start_dp, end_dp
FROM gmstrscma.tbl_OPPCoverage WHERE OPP_ID = @EEID and dtmDate < '2018-12-20' AND Open_enroll is null ORDER BY intID Desc
OPEN OPP_Coverage
FETCH NEXT FROM OPP_Coverage
INTO @selfm, @partnerm, @c1m, @c2m, @c3m, @c4m, @c5m, @selfd, @partnerd, @c1d, @c2d, @c3d, @c4d, @c5d, @selfv, @partnerv, @c1v, @c2v, @c3v, @c4v, @c5v,@Waive, @Enroll_med, @Enroll_dent, @Enroll_vis, @CovID, @c1fname,
@c1mname,
@c1lname,
@c1gen,
@c1dob,
@c1ssn,
@c1start,
@c1end,
@c2fname,
@c2mname,
@c2lname,
@c2gen,
@c2dob,
@c2ssn,
@c2start,
@c2end,
@c3fname,
@c3mname,
@c3lname,
@c3gen,
@c3dob,
@c3ssn,
@c3start,
@c3end,
@c4fname,
@c4mname,
@c4lname,
@c4gen,
@c4dob,
@c4ssn,
@c4start,
@c4end,
@c5fname,
@c5mname,
@c5lname,
@c5gen,
@c5dob,
@c5ssn,
@c5start,
@c5end,
@partnerfname,
@partnermname,
@partnerlname,
@partnergen,
@partnerdob,
@partnerssn,
@partnerstart,
@partnerend
CLOSE OPP_Coverage
DEALLOCATE OPP_Coverage
--GPA--Medical Plan Coverage Level Code-- always use " EE" , "EES', "EEC","FAM"
--*** Determine Dependents
--*** Set default vNalue to 0
SET @Depsm = 0
SET @covlevdesc = ''
SET @covemponly = 'N'
SET @PlanName = ''
SET @Depsm = @selfm + @partnerm + @c1m + @c2m + @c3m + @c4m + @c5m --*** Medical Dependents
IF (@Depsm > 1)
BEGIN
IF (@Depsm > 2)
BEGIN
IF (@partnerm = 1)
BEGIN
SET @covlevcode = 'FAM'
SET @covlevdesc = 'Employee Plus Family'
END
ELSE
BEGIN
SET @covlevcode = 'EEC'
SET @covlevdesc = 'Employee Plus Child(ren)'
END
END
ELSE
BEGIN
IF (@partnerm = 1)
BEGIN
SET @covlevcode = 'EES'
SET @covlevdesc = 'Employee Plus Spouse'
END
ELSE
BEGIN
SET @covlevcode = 'EEC'
SET @covlevdesc = 'Employee Plus Child(ren)'
END
END
END
ELSE
BEGIN
SET @covlevcode = 'EE'
SET @covlevdesc = 'Employee Only'
END
IF (@covlevcode = 'EE')
BEGIN
SET @covemponly = 'Y'
END
IF CHARINDEX('HMO', @Enroll_med) > 0
BEGIN
SET @OFFERID = '101'
SET @PlanName = 'INS1 HMO'
END
IF CHARINDEX('PPO', @Enroll_med) > 0
BEGIN
SET @OFFERID = '102'
SET @PlanName = 'INS1 PPO'
END
IF CHARINDEX('INS2', @Enroll_med) > 0
BEGIN
SET @OFFERID = '103'
SET @PlanName = 'INS2'
END
--Determine COVG VALUES
IF @covlevcode = 'EE'
BEGIN
SET @PlanValue101LowE = @INS1_HMOE_Emp
SET @PlanValue101LowU = @INS1_HMOU_Emp
SET @PlanValue102LowE = @INS1_POSE_Emp
SET @PlanValue102LowU = @INS1_POSU_Emp
SET @PlanValue103LowE = @INS2E_Emp
SET @PlanValue103LowU = @INS2U_Emp
IF @PlanName = 'INS1 HMO'
BEGIN
SET @PlanValueSelectedE = @INS1_HMOE_Emp
SET @PlanValueSelectedU = @INS1_HMOU_Emp
END
IF @PlanName = 'INS1 PPO'
BEGIN
SET @PlanValueSelectedE = @INS1_POSE_Emp
SET @PlanValueSelectedU = @INS1_POSU_Emp
END
IF @PlanName = 'INS2'
BEGIN
SET @PlanValueSelectedE = @INS2E_Emp
SET @PlanValueSelectedU = @INS2U_Emp
END
END
IF @covlevcode = 'EES'
BEGIN
SET @PlanValue101LowE = @INS1_HMOE_Sp
SET @PlanValue101LowU = @INS1_HMOU_Sp
SET @PlanValue102LowE = @INS1_POSE_Sp
SET @PlanValue102LowU = @INS1_POSU_Sp
SET @PlanValue103LowE = @INS2E_Sp
SET @PlanValue103LowU = @INS2U_Sp
IF @PlanName = 'INS1 HMO'
BEGIN
SET @PlanValueSelectedE = @INS1_HMOE_Sp
SET @PlanValueSelectedU = @INS1_HMOU_Sp
END
IF @PlanName = 'INS1 PPO'
BEGIN
SET @PlanValueSelectedE = @INS1_POSE_Sp
SET @PlanValueSelectedU = @INS1_POSU_Sp
END
IF @PlanName = 'INS2'
BEGIN
SET @PlanValueSelectedE = @INS2E_Sp
SET @PlanValueSelectedU = @INS2U_Sp
END
END
IF @covlevcode = 'EEC'
BEGIN
SET @PlanValue101LowE = @INS1_HMOE_Ch
SET @PlanValue101LowU = @INS1_HMOU_Ch
SET @PlanValue102LowE = @INS1_POSE_Ch
SET @PlanValue102LowU = @INS1_POSU_Ch
SET @PlanValue103LowE = @INS2E_Ch
SET @PlanValue103LowU = @INS2U_Ch
IF @PlanName = 'INS1 HMO'
BEGIN
SET @PlanValueSelectedE = @INS1_HMOE_Ch
SET @PlanValueSelectedU = @INS1_HMOU_Ch
END
IF @PlanName = 'INS1 PPO'
BEGIN
SET @PlanValueSelectedE = @INS1_POSE_Ch
SET @PlanValueSelectedU = @INS1_POSU_Ch
END
IF @PlanName = 'INS2'
BEGIN
SET @PlanValueSelectedE = @INS2E_Ch
SET @PlanValueSelectedU = @INS2U_Ch
END
END
IF @covlevcode = 'FAM'
BEGIN
SET @PlanValue101LowE = @INS1_HMOE_Fam
SET @PlanValue101LowU = @INS1_HMOU_Fam
SET @PlanValue102LowE = @INS1_POSE_Fam
SET @PlanValue102LowU = @INS1_POSU_Fam
SET @PlanValue103LowE = @INS2E_Fam
SET @PlanValue103LowU = @INS2U_Fam
IF @PlanName = 'INS1 HMO'
BEGIN
SET @PlanValueSelectedE = @INS1_HMOE_Fam
SET @PlanValueSelectedU = @INS1_HMOU_Fam
END
IF @PlanName = 'INS1 PPO'
BEGIN
SET @PlanValueSelectedE = @INS1_POSE_Fam
SET @PlanValueSelectedU = @INS1_POSU_Fam
END
IF @PlanName = 'INS2'
BEGIN
SET @PlanValueSelectedE = @INS2E_Fam
SET @PlanValueSelectedU = @INS2U_Fam
END
END
--If employee start date is prior to plan year start date then set OFFR Start and Coverage date equal to plan year start date
If convert(smalldatetime,@StartDate,101) < convert(smalldatetime,@planstartdate,101)
BEGIN
SET @offrstartdate = convert(varchar,@planstartdate,101)
END
ELSE
BEGIN
SET @offrstartdate = convert(varchar,@StartDate,101)
END
--If waive_med = y then "Waive Desc" will show up on the COVG line. Please verify that it is indeed a waive. If not remove desc and change Y to N
IF @Waive = 'Y'
BEGIN
SET @WaiveCode = '06'
SET @WaiveDesc = 'Waive'
END
ELSE
BEGIN
SET @WaiveCode = ''
SET @WaiveDesc = ''
END
--comment out below when done
--print convert(varchar,@EEID)
--*********************************
--Employee Identifier Record (always required)
--*********************************
PRINT
--Record Type
'EEID' + '|' +
--Participant Identifier
convert(varchar,@EEID) + '|' +
--Participant SSN
convert(varchar,@SSN) + '|' +
--Participant First Name
@FirstName + '|' +
--Participant Middle Name
@MidName + '|' +
--Participant Last Name
@LastName + '|' +
--Gender
@Gender + '|' +
--Date of Birth
convert(varchar,@EEDOB,101)
--*********************************
--Offer Record (required when eligible event triggers) 3 records thus far all hard coded 101(INS1 HMO), 102(INS1 PPO), 103(INS2)
--*********************************
--101(INS1 HMO)
--*********************************
PRINT
--Record Type
'OFFR' + '|' +
--Participant Identifier @EEID
convert(varchar,@EEID) + '|' +
--Offer Identifier
@offrname + '|' +
--Event Reason
'AE' + '|' +
--Event Date
convert(varchar,@offrstartdate,101) + '|' +
--Coverage Start Date
convert(varchar,@offrstartdate,101) + '|' +
--Plan Year Start Date
--'1/1/' + convert(varchar,year(getDate())) + '|' +
@planstartdate + '|' +
--Plan Year End Date
--'12/31/' + convert(varchar,year(getDate())) + '|' +
@planenddate + '|' +
--Transaction Date
convert(varchar,FORMAT(@dtmAdded, 'MM/dd/yyyy hh:mm:ss.ffffff tt'))
--*********************************
--Eligibility Record (required when reporting eligible plans) 3 plans avail 101(INS1 HMO), 102(INS1 PPO), 103(INS2)
--*********************************
--101(INS1 HMO) Self
--*********************************
PRINT
--Record Type
'ELIG' + '|' +
--Participant Identifier @EEID
convert(varchar,@EEID) + '|' +
--Offer Identifier
@offrname + '|' +
--Medical Plan Code
'AHMO_E' + '|' +
--Medical Plan Description
-- change value based on column above(Medical Plan Code)
--'INS1 HMO' + '|' +
'INS1 HMO' + '|' +
--Medical Plan Coverage Level Code
'EE' + '|' +
--Medical Plan Coverage Level Description
'Employee Only' + '|' +
--Employee Only Coverage Level Flag
'Y' + '|' +
--Monthly Employee Cost
convert(varchar,@INS1_HMOE_Emp) + '|' +
--Monthly Employer Cost
convert(varchar,@INS1_HMOU_Emp) + '|' +
--Minimum Essential Coverage
'Y' + '|' +
--Minimum Value Plan
'Y' + '|' +
--Dependent Coverage Available
'Y' + '|' +
--Spouse Coverage Available
'Y' + '|' +
--Self-Insured Plan
'N' + '|' +
--ACA Base Plan Acturial Value Percentage
'' + '|' +
--Wait Period Indicator
'Y' + '|' +
--Waived Coverage
''
--*********************************
--101(INS1 HMO) Spouse
--*********************************
PRINT
--Record Type
'ELIG' + '|' +
--Participant Identifier @EEID
convert(varchar,@EEID) + '|' +
--Offer Identifier
@offrname + '|' +
--Medical Plan Code
'AHMO_E' + '|' +
--Medical Plan Description
-- change value based on column above(Medical Plan Code)
--'INS1 HMO' + '|' +
'INS1 HMO' + '|' +
--Medical Plan Coverage Level Code
'EES' + '|' +
--Medical Plan Coverage Level Description
'Employee Plus Spouse' + '|' +
--Employee Only Coverage Level Flag
'N' + '|' +
--Monthly Employee Cost
convert(varchar,@INS1_HMOE_Sp) + '|' +
--Monthly Employer Cost
convert(varchar,@INS1_HMOU_Sp) + '|' +
--Minimum Essential Coverage
'Y' + '|' +
--Minimum Value Plan
'Y' + '|' +
--Dependent Coverage Available
'Y' + '|' +
--Spouse Coverage Available
'Y' + '|' +
--Self-Insured Plan
'N' + '|' +
--ACA Base Plan Acturial Value Percentage
'' + '|' +
--Wait Period Indicator
'Y' + '|' +
--Waived Coverage
''
--*********************************
--101(INS1 HMO) Child
--*********************************
PRINT
--Record Type
'ELIG' + '|' +
--Participant Identifier @EEID
convert(varchar,@EEID) + '|' +
--Offer Identifier
@offrname + '|' +
--Medical Plan Code
'AHMO_E' + '|' +
--Medical Plan Description
-- change value based on column above(Medical Plan Code)
--'INS1 HMO' + '|' +
'INS1 HMO' + '|' +
--Medical Plan Coverage Level Code
'EEC' + '|' +
--Medical Plan Coverage Level Description
'Employee Plus Child(ren)' + '|' +
--Employee Only Coverage Level Flag
'N' + '|' +
--Monthly Employee Cost
convert(varchar,@INS1_HMOE_Ch) + '|' +
--Monthly Employer Cost
convert(varchar,@INS1_HMOU_Ch) + '|' +
--Minimum Essential Coverage
'Y' + '|' +
--Minimum Value Plan
'Y' + '|' +
--Dependent Coverage Available
'Y' + '|' +
--Spouse Coverage Available
'Y' + '|' +
--Self-Insured Plan
'N' + '|' +
--ACA Base Plan Acturial Value Percentage
'' + '|' +
--Wait Period Indicator
'Y' + '|' +
--Waived Coverage
''
--*********************************
--101(INS1 HMO) Family
--*********************************
PRINT
--Record Type
'ELIG' + '|' +
--Participant Identifier @EEID
convert(varchar,@EEID) + '|' +
--Offer Identifier
@offrname + '|' +
--Medical Plan Code
'AHMO_E' + '|' +
--Medical Plan Description
-- change value based on column above(Medical Plan Code)
--'INS1 HMO' + '|' +
'INS1 HMO' + '|' +
--Medical Plan Coverage Level Code
'FAM' + '|' +
--Medical Plan Coverage Level Description
'Employee Plus Family' + '|' +
--Employee Only Coverage Level Flag
'N' + '|' +
--Monthly Employee Cost
convert(varchar,@INS1_HMOE_Fam) + '|' +
--Monthly Employer Cost
convert(varchar,@INS1_HMOU_Fam) + '|' +
--Minimum Essential Coverage
'Y' + '|' +
--Minimum Value Plan
'Y' + '|' +
--Dependent Coverage Available
'Y' + '|' +
--Spouse Coverage Available
'Y' + '|' +
--Self-Insured Plan
'N' + '|' +
--ACA Base Plan Acturial Value Percentage
'' + '|' +
--Wait Period Indicator
'Y' + '|' +
--Waived Coverage
''
--*********************************
--102(INS1 PPO) Self
--*********************************
PRINT
--Record Type
'ELIG' + '|' +
--Participant Identifier @EEID
convert(varchar,@EEID) + '|' +
--Offer Identifier
@offrname + '|' +
--Medical Plan Code
'APPO_E' + '|' +
--Medical Plan Description
-- change value based on column above(Medical Plan Code)
--'INS1 HMO' + '|' +
'INS1 PPO' + '|' +
--Medical Plan Coverage Level Code
'EE' + '|' +
--Medical Plan Coverage Level Description
'Employee Only' + '|' +
--Employee Only Coverage Level Flag
'Y' + '|' +
--Monthly Employee Cost
convert(varchar, @INS1_POSE_Emp) + '|' +
--Monthly Employer Cost
convert(varchar, @INS1_POSU_Emp) + '|' +
--Minimum Essential Coverage
'Y' + '|' +
--Minimum Value Plan
'Y' + '|' +
--Dependent Coverage Available
'Y' + '|' +
--Spouse Coverage Available
'Y' + '|' +
--Self-Insured Plan
'N' + '|' +
--ACA Base Plan Acturial Value Percentage
'' + '|' +
--Wait Period Indicator
'Y' + '|' +
--Waived Coverage
''
--*********************************
--102(INS1 PPO) Spouse
--*********************************
PRINT
--Record Type
'ELIG' + '|' +
--Participant Identifier @EEID
convert(varchar,@EEID) + '|' +
--Offer Identifier
@offrname + '|' +
--Medical Plan Code
'APPO_E' + '|' +
--Medical Plan Description
-- change value based on column above(Medical Plan Code)
--'INS1 HMO' + '|' +
'INS1 PPO' + '|' +
--Medical Plan Coverage Level Code
'EES' + '|' +
--Medical Plan Coverage Level Description
'Employee Plus Spouse' + '|' +
--Employee Only Coverage Level Flag
'N' + '|' +
--Monthly Employee Cost
convert(varchar, @INS1_POSE_Sp) + '|' +
--Monthly Employer Cost
convert(varchar, @INS1_POSU_Sp) + '|' +
--Minimum Essential Coverage
'Y' + '|' +
--Minimum Value Plan
'Y' + '|' +
--Dependent Coverage Available
'Y' + '|' +
--Spouse Coverage Available
'Y' + '|' +
--Self-Insured Plan
'N' + '|' +
--ACA Base Plan Acturial Value Percentage
'' + '|' +
--Wait Period Indicator
'Y' + '|' +
--Waived Coverage
''
--*********************************
--102(INS1 PPO) Child
--*********************************
PRINT
--Record Type
'ELIG' + '|' +
--Participant Identifier @EEID
convert(varchar,@EEID) + '|' +
--Offer Identifier
@offrname + '|' +
--Medical Plan Code
'APPO_E' + '|' +
--Medical Plan Description
-- change value based on column above(Medical Plan Code)
--'INS1 HMO' + '|' +
'INS1 PPO' + '|' +
--Medical Plan Coverage Level Code
'EEC' + '|' +
--Medical Plan Coverage Level Description
'Employee Plus Child(ren)' + '|' +
--Employee Only Coverage Level Flag
'N' + '|' +
--Monthly Employee Cost
convert(varchar, @INS1_POSE_Ch) + '|' +
--Monthly Employer Cost
convert(varchar, @INS1_POSU_Ch) + '|' +
--Minimum Essential Coverage
'Y' + '|' +
--Minimum Value Plan
'Y' + '|' +
--Dependent Coverage Available
'Y' + '|' +
--Spouse Coverage Available
'Y' + '|' +
--Self-Insured Plan
'N' + '|' +
--ACA Base Plan Acturial Value Percentage
'' + '|' +
--Wait Period Indicator
'Y' + '|' +
--Waived Coverage
''
--*********************************
--102(INS1 PPO) Family
--*********************************
PRINT
--Record Type
'ELIG' + '|' +
--Participant Identifier @EEID
convert(varchar,@EEID) + '|' +
--Offer Identifier
@offrname + '|' +
--Medical Plan Code
'APPO_E' + '|' +
--Medical Plan Description
-- change value based on column above(Medical Plan Code)
--'INS1 HMO' + '|' +
'INS1 PPO' + '|' +
--Medical Plan Coverage Level Code
'FAM' + '|' +
--Medical Plan Coverage Level Description
'Employee Plus Family' + '|' +
--Employee Only Coverage Level Flag
'N' + '|' +
--Monthly Employee Cost
convert(varchar, @INS1_POSE_Fam) + '|' +
--Monthly Employer Cost
convert(varchar, @INS1_POSU_Fam) + '|' +
--Minimum Essential Coverage
'Y' + '|' +
--Minimum Value Plan
'Y' + '|' +
--Dependent Coverage Available
'Y' + '|' +
--Spouse Coverage Available
'Y' + '|' +
--Self-Insured Plan
'N' + '|' +
--ACA Base Plan Acturial Value Percentage
'' + '|' +
--Wait Period Indicator
'Y' + '|' +
--Waived Coverage
''
--*********************************
--103(INS2) Self
--*********************************
PRINT
--Record Type
'ELIG' + '|' +
--Participant Identifier @EEID
convert(varchar,@EEID) + '|' +
--Offer Identifier
@offrname + '|' +
--Medical Plan Code
'INS2' + '|' +
--Medical Plan Description
-- change value based on column above(Medical Plan Code)
--'INS1 HMO' + '|' +
'INS2' + '|' +
--Medical Plan Coverage Level Code
'EE' + '|' +
--Medical Plan Coverage Level Description
'Employee Only' + '|' +
--Employee Only Coverage Level Flag
'Y' + '|' +
--Monthly Employee Cost
convert(varchar,@INS2E_Emp) + '|' +
--Monthly Employer Cost
convert(varchar,@INS2U_Emp) + '|' +
--Minimum Essential Coverage
'Y' + '|' +
--Minimum Value Plan
'Y' + '|' +
--Dependent Coverage Available
'Y' + '|' +
--Spouse Coverage Available
'Y' + '|' +
--Self-Insured Plan
'N' + '|' +
--ACA Base Plan Acturial Value Percentage
'' + '|' +
--Wait Period Indicator
'Y' + '|' +
--Waived Coverage
''
--*********************************
--103(INS2) Spouse
--*********************************
PRINT
--Record Type
'ELIG' + '|' +
--Participant Identifier @EEID
convert(varchar,@EEID) + '|' +
--Offer Identifier
@offrname + '|' +
--Medical Plan Code
'INS2' + '|' +
--Medical Plan Description
-- change value based on column above(Medical Plan Code)
--'INS1 HMO' + '|' +
'INS2' + '|' +
--Medical Plan Coverage Level Code
'EES' + '|' +
--Medical Plan Coverage Level Description
'Employee Plus Spouse' + '|' +
--Employee Only Coverage Level Flag
'N' + '|' +
--Monthly Employee Cost
convert(varchar,@INS2E_Sp) + '|' +
--Monthly Employer Cost
convert(varchar,@INS2U_Sp) + '|' +
--Minimum Essential Coverage
'Y' + '|' +
--Minimum Value Plan
'Y' + '|' +
--Dependent Coverage Available
'Y' + '|' +
--Spouse Coverage Available
'Y' + '|' +
--Self-Insured Plan
'N' + '|' +
--ACA Base Plan Acturial Value Percentage
'' + '|' +
--Wait Period Indicator
'Y' + '|' +
--Waived Coverage
''
--*********************************
--103(INS2) Child
--*********************************
PRINT
--Record Type
'ELIG' + '|' +
--Participant Identifier @EEID
convert(varchar,@EEID) + '|' +
--Offer Identifier
@offrname + '|' +
--Medical Plan Code
'INS2' + '|' +
--Medical Plan Description
-- change value based on column above(Medical Plan Code)
--'INS1 HMO' + '|' +
'INS2' + '|' +
--Medical Plan Coverage Level Code
'EEC' + '|' +
--Medical Plan Coverage Level Description
'Employee Plus Child(ren)' + '|' +
--Employee Only Coverage Level Flag
'N' + '|' +
--Monthly Employee Cost
convert(varchar,@INS2E_Ch) + '|' +
--Monthly Employer Cost
convert(varchar,@INS2U_Ch) + '|' +
--Minimum Essential Coverage
'Y' + '|' +
--Minimum Value Plan
'Y' + '|' +
--Dependent Coverage Available
'Y' + '|' +
--Spouse Coverage Available
'Y' + '|' +
--Self-Insured Plan
'N' + '|' +
--ACA Base Plan Acturial Value Percentage
'' + '|' +
--Wait Period Indicator
'Y' + '|' +
--Waived Coverage
''
--*********************************
--103(INS2) Family
--*********************************
PRINT
--Record Type
'ELIG' + '|' +
--Participant Identifier @EEID
convert(varchar,@EEID) + '|' +
--Offer Identifier
@offrname + '|' +
--Medical Plan Code
'INS2' + '|' +
--Medical Plan Description
-- change value based on column above(Medical Plan Code)
--'INS1 HMO' + '|' +
'INS2' + '|' +
--Medical Plan Coverage Level Code
'FAM' + '|' +
--Medical Plan Coverage Level Description
'Employee Plus Family' + '|' +
--Employee Only Coverage Level Flag
'N' + '|' +
--Monthly Employee Cost
convert(varchar,@INS2E_Fam) + '|' +
--Monthly Employer Cost
convert(varchar,@INS2U_Fam) + '|' +
--Minimum Essential Coverage
'Y' + '|' +
--Minimum Value Plan
'Y' + '|' +
--Dependent Coverage Available
'Y' + '|' +
--Spouse Coverage Available
'Y' + '|' +
--Self-Insured Plan
'N' + '|' +
--ACA Base Plan Acturial Value Percentage
'' + '|' +
--Wait Period Indicator
'Y' + '|' +
--Waived Coverage
''
--*********************************
--Coverage Record (required when reporting coverage)
--*********************************
PRINT
--Record Type
'COVG' + '|' +
--Participant Identifier @EEID
convert(varchar,@EEID) + '|' +
--Event Reason
'AE' + '|' +
--Event Date
convert(varchar,@StartDate,101) + '|' +
--Medical Plan Code
@Enroll_med + '|' +
--Medical Plan Description
--'INS1 HMO' + '|' +
@PlanName + '|' +
--Medical Plan Coverage Level Code
@covlevcode + '|' +
--Medical Plan Coverage Level Description
@covlevdesc + '|' +
--Monthly Employee Cost
convert(varchar,@PlanValueSelectedE) + '|' +
--Monthly Employer Cost
convert(varchar,@PlanValueSelectedU) + '|' +
--Coverage Start Date
convert(varchar,@startdate,101) + '|' +
--Coverage End Date
--convert(varchar,isnull(@Cov_end_date,''),101) + '|' +
isnull(convert(varchar,@Cov_end_date,101), @defaultcovenddate) + '|' +
--Self-Insured Plan
'' + '|' +
--Minimum Essential Coverage
'' + '|' +
--Minimum Value Plan
'' + '|' +
--Waived Coverage check to make sure this is working
@Waive + '|' +
--Reason for Waiver Code this one too
@WaiveCode + '|' +
--Reason for Waiver Description this one too
@WaiveDesc + '|' +
--Transaction Date
convert(varchar,FORMAT(@dtmAdded, 'MM/dd/yyyy hh:mm:ss.ffffff tt')) + '|' +
--Coverage Identifier
convert(varchar,@EEID)
--*********************************
--Dependent Record (required when reporting dependents)
--*********************************
--Spouse
--PRINT convert(varchar,isnull(@partnerdob,''),101) + '|'
SET @currentSSN = replace(replace(isnull(@partnerssn,''),' ',''),'-','')
IF LEN(@currentSSN) > 0
BEGIN
IF ISNUMERIC(@currentSSN) <> 1
BEGIN
SET @currentSSN = 'BADSSN'
END
END
IF (@partnerm = 1)
BEGIN
SET @depcount = @depcount + 1
PRINT
--Record Type
'DEPI' + '|' +
--Participant Identifier @EEID
convert(varchar,@EEID) + '|' +
--Dependent Identifier
convert(varchar,@EEID) + '-0' + convert(varchar,@depcount) + '|' +
--Dependent SSN
--isnull(@partnerssn,'') + '|' +
@currentSSN + '|' +
--Dependent First Name
isnull(@partnerfname,'') + '|' +
--Dependent Middle Name
isnull(@partnermname,'') + '|' +
--Dependent Last Name
isnull(@partnerlname,'') + '|' +
--Relationship
'Spouse' + '|' +
--Spouse Indicator
'Y' + '|' +
--Dependent Date of Birth
convert(varchar,isnull(@partnerdob,''),101) + '|' +
--Gender
isnull(@partnergen,'N/A') + '|' +
--Coverage Start Date
--'GPA--Coverage Start Date' + '|' +
isnull(convert(varchar,@partnerstart,101),@planstartdate) + '|' +
--Coverage End Date (removed doc 3.0)
--'GPA--Coverage End Date' + '|' +
isnull(convert(varchar,@partnerend,101),@defaultcovenddate) + '|' +
--Status
'' + '|' +
--Coverage Identifier (removed doc 3.0)
convert(varchar,@EEID)
END
--Child 1
IF @c1m = 1
BEGIN
SET @currentSSN = replace(replace(isnull(@c1ssn,''),' ',''),'-','')
IF LEN(@currentSSN) > 0
BEGIN
IF ISNUMERIC(@currentSSN) <> 1
BEGIN
SET @currentSSN = 'BADSSN'
END
END
SET @depcount = @depcount + 1
/**
IF (@partnerm <> 1)
BEGIN
SET @04start = @03start
SET @04end = @03end
SET @03start = @02start
SET @03end = @03end
SET @02start = @01start
SET @02end = @01end
END
**/
PRINT
--Record Type
'DEPI' + '|' +
--Participant Identifier @EEID
convert(varchar,@EEID) + '|' +
--Dependent Identifier
convert(varchar,@EEID) + '-0' + convert(varchar,@depcount) + '|' +
--Dependent SSN
--isnull(@c1ssn,'') + '|' +
@currentSSN + '|' +
--Dependent First Name
isnull(@c1fname,'') + '|' +
--Dependent Middle Name
isnull(@c1mname,'') + '|' +
--Dependent Last Name
isnull(@c1lname,'') + '|' +
--Relationship
'Child' + '|' +
--Spouse Indicator
'N' + '|' +
--Dependent Date of Birth
convert(varchar,isnull(@c1dob,''),101) + '|' +
--Gender
isnull(@c1gen,'N/A') + '|' +
--Coverage Start Date
isnull(convert(varchar,@c1start,101),@planstartdate) + '|' +
--Coverage End Date
isnull(convert(varchar,@c1end,101),@defaultcovenddate) + '|' +
--Status
'' + '|' +
--Coverage Identifier
convert(varchar,@EEID)
END
--Child 2
IF @c2m = 1
BEGIN
SET @currentSSN = replace(replace(isnull(@c2ssn,''),' ',''),'-','')
IF LEN(@currentSSN) > 0
BEGIN
IF ISNUMERIC(@currentSSN) <> 1
BEGIN
SET @currentSSN = 'BADSSN'
END
END
SET @depcount = @depcount + 1
PRINT
--Record Type
'DEPI' + '|' +
--Participant Identifier @EEID
convert(varchar,@EEID) + '|' +
--Dependent Identifier
convert(varchar,@EEID) + '-0' + convert(varchar,@depcount) + '|' +
--Dependent SSN
--isnull(@c2ssn,'') + '|' +
@currentSSN + '|' +
--Dependent First Name
isnull(@c2fname,'') + '|' +
--Dependent Middle Name
isnull(@c2mname,'') + '|' +
--Dependent Last Name
isnull(@c2lname,'') + '|' +
--Relationship
'Child' + '|' +
--Spouse Indicator
'N' + '|' +
--Dependent Date of Birth
convert(varchar,isnull(@c2dob,''),101) + '|' +
--Gender
isnull(@c2gen,'N/A') + '|' +
--Coverage Start Date
isnull(convert(varchar,@c2start,101),@planstartdate) + '|' +
--Coverage End Date
isnull(convert(varchar,@c2end,101),@defaultcovenddate) + '|' +
--Status
'' + '|' +
--Coverage Identifier
convert(varchar,@EEID)
END
--Child 3
IF @c3m = 1
BEGIN
SET @depcount = @depcount + 1
SET @currentSSN = replace(replace(isnull(@c3ssn,''),' ',''),'-','')
IF LEN(@currentSSN) > 0
BEGIN
IF ISNUMERIC(@currentSSN) <> 1
BEGIN
SET @currentSSN = 'BADSSN'
END
END
PRINT
--Record Type
'DEPI' + '|' +
--Participant Identifier @EEID
convert(varchar,@EEID) + '|' +
--Dependent Identifier
convert(varchar,@EEID) + '-0' + convert(varchar,@depcount) + '|' +
--Dependent SSN
--isnull(@c3ssn,'') + '|' +
@currentSSN + '|' +
--Dependent First Name
isnull(@c3fname,'') + '|' +
--Dependent Middle Name
isnull(@c3mname,'') + '|' +
--Dependent Last Name
isnull(@c3lname,'') + '|' +
--Relationship
'Child' + '|' +
--Spouse Indicator
'N' + '|' +
--Dependent Date of Birth
convert(varchar,isnull(@c3dob,''),101) + '|' +
--Gender
isnull(@c3gen,'N/A') + '|' +
--Coverage Start Date
isnull(convert(varchar,@c3start,101),@planstartdate) + '|' +
--Coverage End Date
isnull(convert(varchar,@c3end,101),@defaultcovenddate) + '|' +
--Status
'' + '|' +
--Coverage Identifier
convert(varchar,@EEID)
END
--Child 4
IF @c4m = 1
BEGIN
SET @currentSSN = replace(replace(isnull(@c4ssn,''),' ',''),'-','')
IF LEN(@currentSSN) > 0
BEGIN
IF ISNUMERIC(@currentSSN) <> 1
BEGIN
SET @currentSSN = 'BADSSN'
END
END
SET @depcount = @depcount + 1
PRINT
--Record Type
'DEPI' + '|' +
--Participant Identifier @EEID
convert(varchar,@EEID) + '|' +
--Dependent Identifier
convert(varchar,@EEID) + '-0' + convert(varchar,@depcount) + '|' +
--Dependent SSN
--isnull(@c4ssn,'') + '|' +
@currentSSN + '|' +
--Dependent First Name
isnull(@c4fname,'') + '|' +
--Dependent Middle Name
isnull(@c4mname,'') + '|' +
--Dependent Last Name
isnull(@c4lname,'') + '|' +
--Relationship
'Child' + '|' +
--Spouse Indicator
'N' + '|' +
--Dependent Date of Birth
convert(varchar,isnull(@c4dob,''),101) + '|' +
--Gender
isnull(@c4gen,'N/A') + '|' +
--Coverage Start Date
isnull(convert(varchar,@c4start,101),@planstartdate) + '|' +
--Coverage End Date
isnull(convert(varchar,@c4end,101),@defaultcovenddate) + '|' +
--Status
'' + '|' +
--Coverage Identifier
convert(varchar,@EEID)
END
--Child 5
IF @c5m = 1
BEGIN
SET @currentSSN = replace(replace(isnull(@c5ssn,''),' ',''),'-','')
IF LEN(@currentSSN) > 0
BEGIN
IF ISNUMERIC(@currentSSN) <> 1
BEGIN
SET @currentSSN = 'BADSSN'
END
END
SET @depcount = @depcount + 1
PRINT
--Record Type
'DEPI' + '|' +
--Participant Identifier @EEID
convert(varchar,@EEID) + '|' +
--Dependent Identifier
convert(varchar,@EEID) + '-0' + convert(varchar,@depcount) + '|' +
--Dependent SSN
--isnull(@c5ssn,'') + '|' +
@currentSSN + '|' +
--Dependent First Name
isnull(@c5fname,'') + '|' +
--Dependent Middle Name
isnull(@c5mname,'') + '|' +
--Dependent Last Name
isnull(@c5lname,'') + '|' +
--Relationship
'Child' + '|' +
--Spouse Indicator
'N' + '|' +
--Dependent Date of Birth
convert(varchar,isnull(@c5dob,''),101) + '|' +
--Gender
isnull(@c5gen,'N/A') + '|' +
--Coverage Start Date
isnull(convert(varchar,@c5start,101),@planstartdate) + '|' +
--Coverage End Date
isnull(convert(varchar,@c5end,101),@defaultcovenddate) + '|' +
--Status
'' + '|' +
--Coverage Identifier
convert(varchar,@EEID)
END
FETCH NEXT FROM OPP_Eligible
--INTO @EEID, @SSN, @FirstName, @MidName, @LastName, @Gender, @EEDOB
INTO @EEID
--INTO @EEID, @CovEndDate, @Waive, @WaiveCode, @WaiveDesc, @01start, @01end, @02start, @02end, @03start, @03end, @04start, @04end
END
CLOSE OPP_Eligible
DEALLOCATE OPP_Eligible
--*********************************
--Footer Record (required)
--*********************************
Print 'FOOT' + '|' + convert(varchar,@EEIDCount)
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">
October 24, 2018 at 4:15 pm
This was removed by the editor as SPAM
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply