cthorn112 (10/7/2015)
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-- Hartford15874 on QA/Prod
SET @benistar = 15447-- Hartford15447 on QA/Prod
SET @caremark = 28249-- Hartford15918 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
SELECTspe.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 ENDAS 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
FROMalgb_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
WHEREpln.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)
SELECTbdaf.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)
SELECTbdaf.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
WHERECOALESCE(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
SELECTbds.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
FROMalgb_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
SELECTspe.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
FROMalgb_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
WHEREpln.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
SELECTspe.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
FROMalgb_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
WHEREpln.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
SELECTspe.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
FROMalgb_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
WHEREpln.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 BYv.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 (SELECTpln.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 (
SELECTo.subscriber_id, o.subscriber_plan_election_id
FROM
(
SELECTspe.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
FROMalgb_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
WHEREspe.effective_date <= @v_invoice_date AND pln.carrier_org_branch_relationship_id = @hartford
) o
WHERErnk = 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
By far the biggest issue in this code can be found right here: SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
This appears to be a financial or medical benefits application and you are intentionally disregarding accuracy. Do you FULLY understand that hint? Are you ok with missing and/or duplicate data? It can and will happen. The worst part is that it will happen randomly which makes debugging nearly impossible.
When you use the READ UNCOMMITTED isolation level it is the same thing as putting NOLOCK on every single table.
Here are few articles discussing what that really does.
http://sqlblogcasts.com/blogs/tonyrogerson/archive/2006/11/10/1280.aspx
http://blogs.msdn.com/b/davidlean/archive/2009/04/06/sql-server-nolock-hint-other-poor-ideas.aspx
http://www.jasonstrate.com/2012/06/the-side-effect-of-nolock/[/url]
Aside from that this query is over 400 lines long. Do you really expect anybody without the table to be able to decipher this? I can tell you that I pasted into SSMS and it parses fine. The part you state is the issue is listed somewhere around 10 times in here. Nobody is going to parse this manually to try to determine where the problem lies. Especially when you have the line number right there in front of you. Help us to help you by providing us details about the problem instead of dropping 400+ lines of sql on the page and saying "there is an error".
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/