Greetings Everyone,
I scoured the forum and internet trying to figure out what is wrong with this query. Aside from it being hard on the eyes I cannot pin point the error. I know it is complaining about the 2 columns adminfee.fixed_amount and adminfee.percentage_amount. Please note that I'm trying to update an existing query by adding the two columns that are causing me grief. Any help would be appreciated. Thanks in advance!
-Chris
USE [AmLink_GB]
/****** Object: StoredProcedure [dbo].[ALGB_RPT_DATA_HSM_PREMIUM_DETAIL] Script Date: 10/7/2015 10:46:32 AM ******/
DECLARE
@p_premium_detail_date DATETIME = NULL,
@p_account_build_id INT = 0,
@p_migration_status INT = 0,
--@p_bill_group_id INT,
@p_carrier_org_branch_relationship_id INT = 0,
@p_product_id INT = 0,
@p_plan_id INT = 0
--@p_report_id INT = 0
IF @p_premium_detail_date IS NULL
BEGIN
DECLARE @tmp_date DATETIME
SET @tmp_date = GETDATE()
SET @p_premium_detail_date = CONVERT(VARCHAR(10),DATEADD(d,-DATEPART(d,@tmp_date)+20,@tmp_date),101)
END
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
DECLARE @v_invoice_date DATETIME
SET @v_invoice_date = DATEADD(d,(DATEPART(d,@p_premium_detail_date)*-1) + 1, @p_premium_detail_date)
DECLARE @v_pd_start DATETIME
DECLARE @v_pd_end DATETIME
SET @v_pd_start = DATEADD(dd, 1,DATEADD(mm, -1, @p_premium_detail_date))
SET @v_pd_end = DATEADD(ss, -1, DATEADD(DD, 1, @p_premium_detail_date))
DECLARE @v_adjustment_start_date DATETIME
SET @v_adjustment_start_date = DATEADD(d, 1, DATEADD(mm, -1, @p_premium_detail_date))
-- Orgs
DECLARE @hartford AS INT
DECLARE @benistar AS INT
DECLARE @caremark AS INT
SET @hartford = 18490 -- Hartford 15874 on QA/Prod
SET @benistar = 15447 -- Hartford 15447 on QA/Prod
SET @caremark = 28249 -- Hartford 15918 on QA, not on prod
DECLARE @sterling INT
SET @sterling = 15469
SELECT
x.bill_group_id,
ab.account_build_id,
appe_pln.plan_id,
appe_pln.account_product_plan_election_id,
appe_pln.product_id,
appe_pln.carrier_org_branch_relationship_id,
appe_pln.policy_number + COALESCE(' - ' + appe_pln.policy_suffix,'') + ' - Mode:' + CONVERT(CHAR(2),bmml.period_Months) AS policy_number,
(hmn.last_name + ', ' + hmn.first_name) AS subscriber_name,
REPLACE(hmn.social_security_number, '-', '') AS social_security_number,
spe.dependent_election_type_code AS dep_status,
act.account_name,
CASE seperator
WHEN 0 THEN appe_pln.policy_number
ELSE LEFT(appe_pln.policy_number, seperator-1)
END AS policy,
appe_pln.policy_suffix AS suffix,
CONVERT(VARCHAR(10),x.period_begin_date,101) AS invoice_date,
bmml.period_Months AS bill_mode,
x.har_amount AS tot_premium,
x.adm_amount AS tot_admin,
x.den_amount AS DENTAL_TOT_ADMIN,
x.cam_amount AS CAREMARK_TOT_RX,
x.ben_amount AS BENISTAR_TOT_RX,
x.ptd_amount AS medd_tot_tx,
x.life_count AS lives_count,
x.adjustment_reason_code,
x.adjustment_date,
CASE
WHEN appe_pln.account_product_plan_election_id = 9796 THEN x.life_count * 8.00
WHEN appe_pln.account_product_plan_election_id IN (3560) THEN x.life_count * 7.00
WHEN appe_pln.account_product_plan_election_id IN (8025,8026,9289,9386,9377,9601) THEN x.life_count * 8.00
ELSE CASE WHEN appe_pln.NAIC = 1 THEN x.har_amount * 0.06 ELSE x.life_count * 7.00 END
END AS due_nebco,
adminfee.fixed_amount,
--adminfee.percentage_amount,
spe.subscriber_plan_election_id,
x.subscriber_plan_election_id,
ox.subscriber_plan_election_id
FROM (
SELECT
v.subscriber_id,
v.period_begin_date,
SUM(har_amount) AS har_amount,
SUM(adm_amount) AS adm_amount,
SUM(den_amount) AS den_amount,
SUM(cam_amount) AS cam_amount,
SUM(ben_amount) AS ben_amount,
SUM(ptd_amount) AS ptd_amount,
SUM(life_count) AS life_count,
v.adjustment_reason_code,
v.adjustment_date,
v.adjustment_id,
v.account_build_id,
v.bill_group_id,
v.fixed_amount,
MAX(v.subscriber_plan_election_id) AS subscriber_plan_election_id
FROM
(
-- Hartford Medical Premiums
SELECT spe.subscriber_id,
bd.period_begin_date,
pd.premium_amount AS har_amount,
0.00 AS adm_amount,
0.00 AS den_amount,
0.00 AS cam_amount,
0.00 AS ben_amount,
0.00 AS ptd_amount,
CASE WHEN pd.premium_amount < 0 THEN -1 ELSE 1 END AS life_count,
COALESCE(adjr.adjustment_reason_code, '') AS adjustment_reason_code,
adj.effective_date AS adjustment_date,
adj.adjustment_id,
ab.account_build_id,
bbg.bill_group_id,
adminfee.fixed_amount,
spe.subscriber_plan_election_id
FROM algb_account_build ab
INNER JOIN algb_account_group ag ON ab.account_build_id = ag.account_build_id
INNER JOIN algb_account_group_plan_election agpe ON ag.account_group_id = agpe.account_group_id
INNER JOIN algb_subscriber_plan_election spe ON spe.group_plan_election_id = agpe.group_plan_election_id
INNER JOIN algb_account_product_plan_election appe ON agpe.account_product_plan_election_id = appe.account_product_plan_election_id
INNER JOIN algb_account_product ap ON appe.account_product_id = ap.account_product_id
INNER JOIN algb_account_build_bill_group bbg ON bbg.account_build_id = ag.account_build_id AND bbg.bill_group_id IN (5,11)-- @p_bill_group_id
INNER JOIN algb_plan pln ON appe.plan_id = pln.plan_id
INNER JOIN algb_premium_detail pd ON spe.subscriber_plan_election_id = pd.subscriber_plan_election_id AND pd.ENABLED = 1
INNER JOIN algb_billing_detail bd ON bd.billing_detail_id = pd.billing_detail_id
INNER JOIN algb_administrative_fee adminfee ON adminfee.account_build_id = ab.account_build_id
LEFT JOIN algb_billing_detail_adjustment bda ON bd.billing_detail_id = bda.billing_detail_id
LEFT JOIN algb_adjustment adj ON bda.adjustment_id = adj.adjustment_id
LEFT JOIN algb_adjustment_type adjt ON adj.adjustment_type_id = adjt.adjustment_type_id
LEFT JOIN algb_adjustment_reason adjr ON adj.adjustment_reason_id = adjr.adjustment_reason_id
WHERE pln.carrier_org_branch_relationship_id = @hartford
AND @p_carrier_org_branch_relationship_id IN (@hartford, 0)
AND @p_product_id IN (ap.product_id, 0)
AND @p_plan_id IN (0, appe.account_product_plan_election_id)
AND pd.premium_detail_date BETWEEN @p_premium_detail_date AND @v_pd_end
AND @p_account_build_id IN (0, ab.account_build_id)
AND @p_migration_status IN (CASE WHEN ab.migration_status_id IN (16080,16137) THEN 1 ELSE 2 END,0)
UNION ALL
-- Admin fees (associated with plans)
SELECT bdaf.entity_id AS subscriber_id,
bd.period_begin_date,
0 AS har_amount,
bd.amount AS adm_amount,
0.00 AS den_amount,
0.00 AS cam_amount,
0.00 AS ben_amount,
0.00 AS ptd_amount,
0 AS life_count,
COALESCE(adjr.adjustment_reason_code, '') AS adjustment_reason_code,
adj.effective_date AS adjustment_date,
adj.adjustment_id,
ab.account_build_id,
bbg.bill_group_id,
adminfee.fixed_amount,
NULL AS subscriber_plan_election_id
FROM algb_billing_detail_subscriber bds
INNER JOIN algb_subscriber sub ON bds.subscriber_id = sub.subscriber_id
INNER JOIN algb_account_group ag ON sub.account_group_id = ag.account_group_id
INNER JOIN algb_account_build ab ON ag.account_build_id = ab.account_build_id
INNER JOIN algb_billing_detail bd ON bds.billing_detail_id = bd.billing_detail_id
INNER JOIN algb_administrative_fee adminfee ON adminfee.account_build_id = ab.account_build_id
INNER JOIN algb_account_build_bill_group bbg ON bbg.account_build_id = ag.account_build_id AND bbg.bill_group_id IN (5,11)-- @p_bill_group_id
INNER JOIN algb_billing_detail_admin_fee bdaf ON bd.billing_detail_id = bdaf.billing_detail_id
INNER JOIN algb_administrative_fee af ON af.entity_level_attribute_lookup_id = 15687 AND af.administrative_fee_id = bdaf.administrative_fee_id
INNER JOIN algb_account_product_plan_election appe ON af.entity_id = appe.account_product_plan_election_id
INNER JOIN algb_plan pln ON pln.plan_id = appe.plan_id AND pln.premium_type_id = 15863
INNER JOIN algb_invoice_status istat ON bds.invoice_id = istat.invoice_id AND istat.ENABLED = 1 AND istat.status_attribute_lookup_id NOT IN (15746) -- JPM isspe -> bds
INNER JOIN algb_invoice inv ON bds.invoice_id = inv.invoice_id
LEFT JOIN algb_billing_detail_adjustment bda ON bd.billing_detail_id = bda.billing_detail_id
LEFT JOIN algb_adjustment adj ON bda.adjustment_id = adj.adjustment_id
LEFT JOIN algb_adjustment_type adjt ON adj.adjustment_type_id = adjt.adjustment_type_id
LEFT JOIN algb_adjustment_reason adjr ON adj.adjustment_reason_id = adjr.adjustment_reason_id
WHERE @p_account_build_id IN (0, ab.account_build_id)
AND @p_migration_status IN (CASE WHEN ab.migration_status_id IN (16080,16137) THEN 1 ELSE 2 END,0)
AND
COALESCE(bds.premium_detail_date, adj.accounting_date, CASE WHEN inv.create_date <= bd.period_begin_date THEN DATEADD(dd, 19, bd.period_begin_date) ELSE inv.create_date END) BETWEEN @v_pd_start AND @v_pd_end
UNION ALL
-- Admin fees (not so associated with plans)
SELECT bdaf.entity_id AS subscriber_id,
bd.period_begin_date,
0 AS har_amount,
bd.amount AS adm_amount,
0.00 AS den_amount,
0.00 AS cam_amount,
0.00 AS ben_amount,
0.00 AS ptd_amount,
0 AS life_count,
COALESCE(adjr.adjustment_reason_code, '') AS adjustment_reason_code,
adj.effective_date AS adjustment_date,
adj.adjustment_id,
ab.account_build_id,
bbg.bill_group_id,
adminfee.fixed_amount,
NULL AS subscriber_plan_election_id
FROM algb_billing_detail_subscriber bds
INNER JOIN algb_subscriber sub ON bds.subscriber_id = sub.subscriber_id
INNER JOIN algb_account_group ag ON sub.account_group_id = ag.account_group_id
INNER JOIN algb_account_build ab ON ag.account_build_id = ab.account_build_id
INNER JOIN algb_billing_detail bd ON bds.billing_detail_id = bd.billing_detail_id
INNER JOIN algb_administrative_fee adminfee ON adminfee.account_build_id = ab.account_build_id
INNER JOIN algb_account_build_bill_group bbg ON bbg.account_build_id = ag.account_build_id AND bbg.bill_group_id IN (5,11)
INNER JOIN algb_billing_detail_admin_fee bdaf ON bdaf.billing_detail_id = bd.billing_detail_id
INNER JOIN algb_administrative_fee af ON af.entity_level_attribute_lookup_id IS NULL AND af.administrative_fee_id = bdaf.administrative_fee_id
INNER JOIN algb_invoice_status istat ON bds.invoice_id = istat.invoice_id AND istat.ENABLED = 1 AND istat.status_attribute_lookup_id NOT IN (15746)
INNER JOIN algb_invoice inv ON bds.invoice_id = inv.invoice_id
LEFT JOIN algb_billing_detail_adjustment bda ON bd.billing_detail_id = bda.billing_detail_id
LEFT JOIN algb_adjustment adj ON bda.adjustment_id = adj.adjustment_id
LEFT JOIN algb_adjustment_type adjt ON adj.adjustment_type_id = adjt.adjustment_type_id
LEFT JOIN algb_adjustment_reason adjr ON adj.adjustment_reason_id = adjr.adjustment_reason_id
WHERE COALESCE(af.is_rx_admin_fee,0) = 1
AND @p_account_build_id IN (0, ab.account_build_id)
AND @p_migration_status IN (CASE WHEN ab.migration_status_id IN (16080,16137) THEN 1 ELSE 2 END,0)
AND COALESCE(bds.premium_detail_date, adj.accounting_date, CASE WHEN inv.create_date <= bd.period_begin_date THEN DATEADD(dd, 19, bd.period_begin_date) ELSE inv.create_date END) BETWEEN @v_pd_start AND @v_pd_end
UNION ALL
SELECT bds.subscriber_id,
bd.period_begin_date,
0 AS har_amount,
0.00 AS adm_amount,
bd.amount AS den_amount,
0.00 AS cam_amount,
0.00 AS ben_amount,
0.00 AS ptd_amount,
0 AS life_count,
COALESCE(adjr.adjustment_reason_code, '') AS adjustment_reason_code,
adj.effective_date AS adjustment_date,
adj.adjustment_id,
ab.account_build_id,
bbg.bill_group_id,
adminfee.fixed_amount,
NULL AS subscriber_plan_election_id
FROM algb_billing_detail_subscriber bds
INNER JOIN algb_subscriber sub ON bds.subscriber_id = sub.subscriber_id
INNER JOIN algb_billing_detail bd ON bds.billing_detail_id = bd.billing_detail_id
INNER JOIN algb_premium_detail pd ON bd.billing_detail_id = pd.billing_detail_id AND pd.ENABLED = 1
INNER JOIN algb_subscriber_plan_election spe ON pd.subscriber_plan_election_id = spe.subscriber_plan_election_id
INNER JOIN algb_account_group_plan_election agpe ON spe.group_plan_election_id = agpe.group_plan_election_id
INNER JOIN algb_account_group ag ON ag.account_group_id = agpe.account_group_id
INNER JOIN algb_account_build_bill_group bbg ON bbg.account_build_id = ag.account_build_id AND bbg.bill_group_id IN (5,11)-- @p_bill_group_id
INNER JOIN algb_account_build ab ON ab.account_build_id = ag.account_build_id
INNER JOIN algb_administrative_fee adminfee ON adminfee.account_build_id = ab.account_build_id
INNER JOIN algb_account_product_plan_election appe ON agpe.account_product_plan_election_id = appe.account_product_plan_election_id
INNER JOIN algb_plan pln ON appe.plan_id = pln.plan_id AND pln.premium_type_id = 15861
INNER JOIN algb_invoice_status istat ON bds.invoice_id = istat.invoice_id AND istat.ENABLED = 1 AND istat.status_attribute_lookup_id NOT IN (15746)
INNER JOIN algb_invoice inv ON bds.invoice_id = inv.invoice_id
LEFT JOIN algb_billing_detail_adjustment bda ON bd.billing_detail_id = bda.billing_detail_id
LEFT JOIN algb_adjustment adj ON bda.adjustment_id = adj.adjustment_id
LEFT JOIN algb_adjustment_type adjt ON adj.adjustment_type_id = adjt.adjustment_type_id
LEFT JOIN algb_adjustment_reason adjr ON adj.adjustment_reason_id = adjr.adjustment_reason_id
WHERE @p_account_build_id IN (0, ab.account_build_id)
AND @p_migration_status IN (CASE WHEN ab.migration_status_id IN (16080,16137) THEN 1 ELSE 2 END,0)
AND pd.premium_detail_date BETWEEN @p_premium_detail_date AND @v_pd_end
AND bd.billing_detail_type_attribute_lookup_id != 15732
UNION ALL
-- Caremark Rx Premiums
SELECT spe.subscriber_id,
bd.period_begin_date,
0.00 AS har_amount,
0.00 AS adm_amount,
0.00 AS den_amount,
pd.premium_amount AS cam_amount,
0.00 AS ben_amount,
0.00 AS ptd_amount,
0 AS life_count,
COALESCE(adjr.adjustment_reason_code, '') AS adjustment_reason_code,
adj.effective_date AS adjustment_date,
adj.adjustment_id,
ab.account_build_id,
bbg.bill_group_id,
adminfee.fixed_amount,
NULL AS subscriber_plan_election_id
FROM algb_account_build ab
INNER JOIN algb_account_group ag ON ab.account_build_id = ag.account_build_id
INNER JOIN algb_account_build_bill_group bbg ON bbg.account_build_id = ag.account_build_id AND bbg.bill_group_id IN (5,11)-- @p_bill_group_id
INNER JOIN algb_account_group_plan_election agpe ON ag.account_group_id = agpe.account_group_id
INNER JOIN algb_subscriber_plan_election spe ON spe.group_plan_election_id = agpe.group_plan_election_id
INNER JOIN algb_account_product_plan_election appe ON agpe.account_product_plan_election_id = appe.account_product_plan_election_id
INNER JOIN algb_account_product ap ON appe.account_product_id = ap.account_product_id
INNER JOIN algb_plan pln ON appe.plan_id = pln.plan_id
INNER JOIN algb_premium_detail pd ON spe.subscriber_plan_election_id = pd.subscriber_plan_election_id AND pd.ENABLED = 1
INNER JOIN algb_billing_detail bd ON bd.billing_detail_id = pd.billing_detail_id
INNER JOIN algb_administrative_fee adminfee ON adminfee.account_build_id = ab.account_build_id
LEFT JOIN algb_billing_detail_adjustment bda ON bd.billing_detail_id = bda.billing_detail_id
LEFT JOIN algb_adjustment adj ON bda.adjustment_id = adj.adjustment_id
LEFT JOIN algb_adjustment_type adjt ON adj.adjustment_type_id = adjt.adjustment_type_id
LEFT JOIN algb_adjustment_reason adjr ON adj.adjustment_reason_id = adjr.adjustment_reason_id
WHERE pln.carrier_org_branch_relationship_id = @caremark
AND @p_carrier_org_branch_relationship_id IN (@caremark, 0)
AND @p_product_id IN (ap.product_id, 0)
AND @p_plan_id IN (0, appe.account_product_plan_election_id)
AND pd.premium_detail_date BETWEEN @p_premium_detail_date AND @v_pd_end
AND @p_account_build_id IN (0, ab.account_build_id)
AND @p_migration_status IN (CASE WHEN ab.migration_status_id IN (16080,16137) THEN 1 ELSE 2 END,0)
UNION ALL
-- Benistar Rx Premiums
SELECT spe.subscriber_id,
bd.period_begin_date,
0.00 AS har_amount,
0.00 AS adm_amount,
0.00 AS den_amount,
0.00 AS cam_amount,
pd.premium_amount AS ben_amount,
0.00 AS ptd_amount,
0 AS life_count,
COALESCE(adjr.adjustment_reason_code, '') AS adjustment_reason_code,
adj.effective_date AS adjustment_date,
adj.adjustment_id,
ab.account_build_id,
bbg.bill_group_id,
adminfee.fixed_amount,
NULL AS subscriber_plan_election_id
FROM algb_account_build ab
INNER JOIN algb_account_group ag ON ab.account_build_id = ag.account_build_id
INNER JOIN algb_account_build_bill_group bbg ON bbg.account_build_id = ag.account_build_id AND bbg.bill_group_id IN (5,11)-- @p_bill_group_id
INNER JOIN algb_account_group_plan_election agpe ON ag.account_group_id = agpe.account_group_id
INNER JOIN algb_subscriber_plan_election spe ON spe.group_plan_election_id = agpe.group_plan_election_id
INNER JOIN algb_account_product_plan_election appe ON agpe.account_product_plan_election_id = appe.account_product_plan_election_id
INNER JOIN algb_account_product ap ON appe.account_product_id = ap.account_product_id
INNER JOIN algb_plan pln ON appe.plan_id = pln.plan_id
INNER JOIN algb_premium_detail pd ON spe.subscriber_plan_election_id = pd.subscriber_plan_election_id AND pd.ENABLED = 1
INNER JOIN algb_billing_detail bd ON bd.billing_detail_id = pd.billing_detail_id
INNER JOIN algb_administrative_fee adminfee ON adminfee.account_build_id = ab.account_build_id
LEFT JOIN algb_billing_detail_adjustment bda ON bd.billing_detail_id = bda.billing_detail_id
LEFT JOIN algb_adjustment adj ON bda.adjustment_id = adj.adjustment_id
LEFT JOIN algb_adjustment_type adjt ON adj.adjustment_type_id = adjt.adjustment_type_id
LEFT JOIN algb_adjustment_reason adjr ON adj.adjustment_reason_id = adjr.adjustment_reason_id
WHERE pln.carrier_org_branch_relationship_id = @benistar
AND @p_carrier_org_branch_relationship_id IN (@benistar, 0)
AND @p_product_id IN (ap.product_id, 0)
AND @p_plan_id IN (0, appe.account_product_plan_election_id)
AND pd.premium_detail_date BETWEEN @p_premium_detail_date AND @v_pd_end
AND @p_account_build_id IN (0, ab.account_build_id)
AND @p_migration_status IN (CASE WHEN ab.migration_status_id IN (16080,16137) THEN 1 ELSE 2 END,0)
UNION ALL
-- Part D Rx Premiums
SELECT spe.subscriber_id,
bd.period_begin_date,
0.00 AS har_amount,
0.00 AS adm_amount,
0.00 AS den_amount,
0.00 AS cam_amount,
0.00 AS ben_amount,
pd.premium_amount AS ptd_amount,
0 AS life_count,
COALESCE(adjr.adjustment_reason_code, '') AS adjustment_reason_code,
adj.effective_date AS adjustment_date,
adj.adjustment_id,
ab.account_build_id,
bbg.bill_group_id,
adminfee.fixed_amount,
NULL AS subscriber_plan_election_id
FROM algb_account_build ab
INNER JOIN algb_account_group ag ON ab.account_build_id = ag.account_build_id
INNER JOIN algb_account_build_bill_group bbg ON bbg.account_build_id = ag.account_build_id AND bbg.bill_group_id IN (5,11)-- @p_bill_group_id
INNER JOIN algb_account_group_plan_election agpe ON ag.account_group_id = agpe.account_group_id
INNER JOIN algb_subscriber_plan_election spe ON spe.group_plan_election_id = agpe.group_plan_election_id
INNER JOIN algb_account_product_plan_election appe ON agpe.account_product_plan_election_id = appe.account_product_plan_election_id
INNER JOIN algb_account_product ap ON appe.account_product_id = ap.account_product_id
INNER JOIN algb_plan pln ON appe.plan_id = pln.plan_id
INNER JOIN algb_premium_detail pd ON spe.subscriber_plan_election_id = pd.subscriber_plan_election_id AND pd.ENABLED = 1
INNER JOIN algb_billing_detail bd ON bd.billing_detail_id = pd.billing_detail_id
INNER JOIN algb_administrative_fee adminfee ON adminfee.account_build_id = ab.account_build_id
LEFT JOIN algb_billing_detail_adjustment bda ON bd.billing_detail_id = bda.billing_detail_id
LEFT JOIN algb_adjustment adj ON bda.adjustment_id = adj.adjustment_id
LEFT JOIN algb_adjustment_type adjt ON adj.adjustment_type_id = adjt.adjustment_type_id
LEFT JOIN algb_adjustment_reason adjr ON adj.adjustment_reason_id = adjr.adjustment_reason_id
WHERE pln.medicare_part_d = 1
AND pd.premium_detail_date BETWEEN @p_premium_detail_date AND @v_pd_end
AND pln.carrier_org_branch_relationship_id = @sterling
AND @p_account_build_id IN (0, ab.account_build_id)
AND @p_migration_status IN (CASE WHEN ab.migration_status_id IN (16080,16137) THEN 1 ELSE 2 END,0)
) v
GROUP BY v.subscriber_id, v.period_begin_date, v.adjustment_reason_code, v.adjustment_date,
v.adjustment_id, v.account_build_id, v.bill_group_id, v.fixed_amount
) x
INNER JOIN algb_subscriber sub ON x.subscriber_id = sub.subscriber_id
INNER JOIN algb_account_build ab ON x.account_build_id = ab.account_build_id
INNER JOIN algb_account act ON ab.account_id = act.account_id
INNER JOIN algb_human hmn ON sub.human_id = hmn.human_id
INNER JOIN algb_subscriber_plan_election spe ON sub.subscriber_id = spe.subscriber_id
INNER JOIN algb_bill_mode_months_lookup bmml ON spe.bill_mode_attribute_lookup_id = bmml.bill_mode_attribute_lookup_id
INNER JOIN (SELECT pln.plan_id,
appe.account_product_id,
REPLACE(REPLACE(COALESCE(agpe.carrier_group_number, appe.carrier_group_number, pln.carrier_group_number), 'AGP ', ''),'AGP-','') AS policy_number,
CHARINDEX('-',REPLACE(REPLACE(COALESCE(agpe.carrier_group_number, appe.carrier_group_number, pln.carrier_group_number), 'AGP ', ''),'AGP-','')) AS seperator,
agpe.sub_group_number AS policy_suffix,
appe.account_product_plan_election_id,
agpe.group_plan_election_id,
pln.carrier_org_branch_relationship_id,
premium_type_id,
ap.product_id,
CASE WHEN COALESCE(hpe.is_med_supp,0) = 1 THEN 1 ELSE COALESCE(hpe.NAIC,0) END AS NAIC
FROM algb_plan pln
INNER JOIN algb_account_product_plan_election appe ON pln.plan_id = appe.plan_id
INNER JOIN algb_account_group_plan_election agpe ON appe.account_product_plan_election_id = agpe.account_product_plan_election_id
INNER JOIN algb_account_product ap ON appe.account_product_id = ap.account_product_id
LEFT JOIN dbo.algb_hartford_plan_election hpe ON appe.account_product_plan_election_id = hpe.account_product_plan_election_id
WHERE pln.carrier_org_branch_relationship_id = @hartford
) appe_pln ON spe.group_plan_election_id = appe_pln.group_plan_election_id
INNER JOIN (
SELECT o.subscriber_id, o.subscriber_plan_election_id
FROM
(
SELECT spe.subscriber_id,
spe.subscriber_plan_election_id,
DENSE_RANK() OVER (PARTITION BY subscriber_id ORDER BY COALESCE(spe.termination_date,GETDATE()) DESC, spe.effective_date DESC ) AS rnk
FROM algb_subscriber_plan_election spe
INNER JOIN algb_account_group_plan_election agpe ON spe.group_plan_election_id = agpe.group_plan_election_id
INNER JOIN algb_hartford_plan_election hpe ON agpe.account_product_plan_election_id = hpe.account_product_plan_election_id
INNER JOIN algb_account_product_plan_election appe ON hpe.account_product_plan_election_id = appe.account_product_plan_election_id
INNER JOIN algb_plan pln ON appe.plan_id = pln.plan_id
WHERE spe.effective_date <= @v_invoice_date AND pln.carrier_org_branch_relationship_id = @hartford
) o
WHERE rnk = 1
) ox ON x.subscriber_id = ox.subscriber_id
WHERE
(spe.subscriber_plan_election_id = x.subscriber_plan_election_id)
OR (x.subscriber_plan_election_id IS NULL AND ox.subscriber_plan_election_id = spe.subscriber_plan_election_id)
ORDER BY ab.account_build_id, subscriber_name
GO