October 7, 2015 at 2:57 pm
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
October 7, 2015 at 3:31 pm
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/
October 7, 2015 at 5:08 pm
Hi cthorn112,
I think that's ok to set the isolation level to READ UNCOMMITTED if you know what to expect.. dirty reads and all other side effects (personally I've never been in a situation to use the isolation level). If you want to "fix" the bad query design(slow running query-report) and reduce waits etc... the NOLOCK seems like a bad idea.
As Sean pointed out, it's simply hard to understand your query and help you find the possible logical/syntax problems.
If I may, I'll suggest you to decouple the query (you may use Inline TVFs, intermediate results #tables ...) and than it will be easier for you to test the result sets and find the problem (also it may help the Query Optimizer to come up with the better plan 🙂
Dean
D.Mincic
😀
MCTS Sql Server 2008, Database Development
October 8, 2015 at 7:05 am
I would love to see the execution plan for this query.
I suspect that you're trying to add the columns by referencing the table name when you need to be using the alias, x. I could be wrong, that's a huge amount of stuff to read through, but I think that's the issue.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
October 8, 2015 at 7:17 am
Dean Mincic (10/7/2015)
I think that's ok to set the isolation level to READ UNCOMMITTED if you know what to expect.. dirty reads and all other side effects (personally I've never been in a situation to use the isolation level).
It certainly NOT ok if this is, as I suspect, a financial or benefits application. Accuracy is critical for this type of thing and that is anything but accurate. I have been in a situation as a consultant where the customers senior DBA mandated NOLOCK on every single query across the board to "improve performance". This was a benefits application that also had attached debit cards. We would reject claims when the account had money and we would approve claims when there was no money all because of that hint. It had taken almost 3 months to get that hint added to everything, then it cost them thousands and thousands of dollars for approved claims that had no funds. Finally they bit the bullet and spent another 3 months removing all those hints.
There are times and places where that hint may be acceptable but they have no business in an application dealing with money or anything else where accuracy is highly important.
_______________________________________________________________
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/
October 8, 2015 at 7:54 am
You have a main query, a subquery, and a sub-subquery. The table adminfee is only specified within the sub-subquery, but you are trying to reference it in the main query.
I highly recommend using CTEs. They are equivalent to subqueries, but are much easier for most people to follow.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
October 8, 2015 at 4:31 pm
Hi Sean,
I didn't have enough time to go through the query and to understand the query logic to be able to "assume" what's that all about. The two things caught my attention though; Read uncommitted session setting and Order by. The "assumption" was that we have a reasonably bad structured query that is a kind of a report that does not care about accuracy.(?) . The question was about syntax error. I couldn't help myself not to mention the nolock (and I totally agree with your previous post) mostly to highlight the possible problem.
It's almost unbelievable what you described happened with the finance app. I am seeing rbar queries, # tables flying through the zillion nested sps and so on (mostly bad programming) but noting like that..
Dean
D.Mincic
😀
MCTS Sql Server 2008, Database Development
October 9, 2015 at 7:23 am
Well maybe the OP will show back up and we can help them resolve this issue. It seems there a number of people willing to help but the OP is missing. 😛
_______________________________________________________________
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/
October 9, 2015 at 8:15 am
Isn't the first reference to adminfee wrong? The table alias is "x" on this scope, and you don't join anything else that is aliased or called adminfee, so you can't just drop "adminfee.fixed_amount" into the SELECT statement.
The SQL does look like a disaster waiting to happen...
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply