SQLTeam.com | Weblogs | Forums

The multi-part identifier could not be bound


#1

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

#2

Replace the first instance of adminfee.fixed_amount with x.fixed_amount. Leave the other instances unchanged.


#3

@JamesK that worked!!! Thank you so much :grin:

-Chris