September 21, 2015 at 1:16 pm
Greetings All,
It's been a while since I last posted on here, about 4 years maybe more. Anyway I'm happy to be back. I have an existing query that I needed to add two columns to. I did that but now my dilemma is to have the query NOT return NULL values in a particular column (pd.premium_amount). I tried adding WHERE pd.premium_amount IS NOT NULL and that did not do the trick. I was thinking about trying to use CASE statement but didn't really see that as being an option. Below is my code. Any help would be greatly appreciated. Thank you all and have a great rest of your day!
-Chris
USE [AmLink_DW]
DECLARE @Bil_Grps TABLE (bil_grp_id int, bil_grp_sname varchar(50))
INSERT INTO @Bil_Grps (bil_grp_id, bil_grp_sname)
SELECT *
FROM (
VALUES
(1,'NPRIT'),
(2,'BCBS'),
(4,'LGA'),
(5,'HSM'),
(11,'HSM TPA'),
(13,'IHP'),
(15,'LM'),
(17,'SGA'),
(18,'CCSA')) AS vtable
([bill_group_id],[bill_group_short_name])
SELECT DISTINCTpd.premium_detail_id
,d.Account_build_id
,Bill_group_id
,account_name
,d.group_id
,group_sub_id
,addr.state_cd AS [Group State] --newly added column
,plan_code
,subscriber_first_name
,subscriber_last_name
,resident_state
,CONVERT(VARCHAR(10),d.effective_date,101) AS effective_date
,hic_number
,social_security_number
,CASE WHEN appe.plan_id IN (2238,2239,2240,2241,2242,2244,2246,2247,2248,2249,2250,2251,2565,2566)
THEN SUM(pd.premium_amount)
--WHEN (pd.premium_amount) IS NULL THEN 0
--ELSE (pd.premium_amount)
END AS [Fidelity Premium] --newly added column
,net_pdp_premium
,net_enhanced_premium
,net_self_funded
,total_billed_premium
,pdp_cms_capitation_with_lis
,enhanced_cms_capitation_rate
,lis_offset
,total_pdp_premium
,total_enhanced_premium
,CASE WHEN COALESCE(adjustment_apply_month, '1/1/1900') < '1/1/2000'
THEN ''
ELSE CONVERT(VARCHAR(10),adjustment_apply_month ,101)
END AS adjustment_apply_month
,adjustment_code
,CONVERT(VARCHAR(10),invoice_month,101) AS invoice_month
,lives_pdp
,lives_enhanced
,commission_plan_id
,pdp_fees_and_commissions
,enhanced_fees_and_commissions
,aggregate_coverage_fee
,ISNULL(CONVERT(VARCHAR(20),late_enrollment_penalty),'') as late_enrollment_penalty
,d.Subscriber_id
,CONVERT(VARCHAR(10),Report_month,101) AS Report_month
,appe.plan_id
,appe.plan_election_name
FROM AMLINK_GB.AmLink_GB.dbo.algb_premium_detail pd
INNER JOIN AMLINK_GB.AmLink_GB.dbo.algb_subscriber_plan_election spe ON spe.subscriber_plan_election_id = pd.subscriber_plan_election_id
INNER JOIN AMLINK_GB.AmLink_GB.dbo.algb_account_group_plan_election agpe ON agpe.group_plan_election_id = spe.group_plan_election_id
INNER JOIN AMLINK_GB.AmLink_GB.dbo.algb_account_product_plan_election appe ON appe.account_product_plan_election_id = agpe.account_product_plan_election_id
INNER JOIN AMLINK_GB.AmLink_GB.dbo.algb_plan p ON p.plan_id = appe.plan_id
INNER JOIN DW_GB_STERLING_PART_D d ON d.commission_plan_id = appe.carrier_plan_id AND pd.Subscriber_id = d.Subscriber_id
INNER JOIN AMLINK_GB.AmLink_GB.dbo.algb_subscriber s ON s.subscriber_id = d.Subscriber_id AND s.[enabled] = 1
INNER JOIN AMLINK_GB.AmLink_GB.dbo.algb_account_group ag ON ag.account_group_id = s.account_group_id AND ag.enabled = 1
INNER JOIN AMLINK_GB.AmLink_GB.dbo.algb_group_address ga ON ga.group_id = ag.group_id AND ga.enabled = 1
INNER JOIN AMLINK_GB.AmLink_GB.dbo.algb_address addr ON addr.address_id = ga.address_id AND addr.enabled = 1
WHERE 1 = 1
AND pd.premium_detail_date = '08/20/2015'
AND d.Report_month = '08/20/2015'
AND d.social_security_number <> '258-30-0237'
AND pd.premium_amount IS NOT NULL --tried this but didn't change the number of results
GROUP BYpd.premium_detail_id
,d.Account_build_id
,Bill_group_id
,account_name
,d.group_id
,group_sub_id
,addr.state_cd
,plan_code
,subscriber_first_name
,subscriber_last_name
,resident_state
,CONVERT(VARCHAR(10),d.effective_date,101)
,hic_number
,social_security_number
,net_pdp_premium
,net_enhanced_premium
,net_self_funded
,total_billed_premium
,pdp_cms_capitation_with_lis
,enhanced_cms_capitation_rate
,lis_offset
,total_pdp_premium
,total_enhanced_premium
,CASE WHEN COALESCE(adjustment_apply_month, '1/1/1900') < '1/1/2000'
THEN ''
ELSE CONVERT(VARCHAR(10),adjustment_apply_month ,101)
END
,adjustment_code
,CONVERT(VARCHAR(10),invoice_month,101)
,lives_pdp
,lives_enhanced
,commission_plan_id
,pdp_fees_and_commissions
,enhanced_fees_and_commissions
,aggregate_coverage_fee
,ISNULL(CONVERT(VARCHAR(20),late_enrollment_penalty),'')
,d.Subscriber_id
,CONVERT(VARCHAR(10),Report_month,101)
,appe.plan_id
,appe.plan_election_name
ORDER BY d.Account_build_id, d.subscriber_id
--14864 results being returned
September 21, 2015 at 4:43 pm
cthorn112 (9/21/2015)
--WHEN (pd.premium_amount) IS NULL THEN 0--ELSE (pd.premium_amount)
COALESCE(pd.premium_amount, 0)
or
ISNULL(pd.premium_amount, 0)
COALESCE allowed unlimited parameters and will change datatype to that of the highest precedence - so if you have INT and BIGINT parameters the result will be BIGINT. Don't use it with a SELECT as a parameter (the SELECT will be executed twice)
ISNULL uses the datatype from the first parameter and is limited to exactly two parameters
(Just for completeness : if used in a SELECT INTO statement then the two have different Nullability)
September 21, 2015 at 9:21 pm
Adding the WHERE pd.premium_amount IS NOT NULL will filter out rows where the column is NULL, but not replace the values.
I would probably use the ISNULL approach as Kristen suggested if you want a zero back instead of the NULL
September 22, 2015 at 12:12 am
Hi cthorn112
I think the CASE Statement needs ELSE part, something like
....
,CASE
WHEN appe.plan_id IN (2238,2239,2240,2241,2242,2244,2246,2247,2248,2249,2250,2251,2565,2566)
THEN SUM(pd.premium_amount)
ELSE 0
END AS [Fidelity Premium] --newly added column
...
AND pd.premium_amount IS NOT NULL
...
D.Mincic
😀
MCTS Sql Server 2008, Database Development
September 22, 2015 at 4:24 am
I'd use Kristen's approach for showing data without NULL values. Just don't use the same thing in the WHERE, HAVING or ON clauses because you'll cause table scans since that's a function on a column.
"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
September 22, 2015 at 10:13 am
Kristen,
Thank you for replying to my post. I tried using both approaches COALESCE and ISNULL with no changes in the number of rows that were returned. I did need to add ,pd.premium_amount to the GROUP BY since I was using an aggregate function, line 93. I did notice that the values for column pd.premium_column were no longer NULL which was a little confusing because here I have the same number of rows as before but there are no NULL values for the pd.premium_column. It is worth mentioning that pd.premium_amount is an INT and I replaced the CASE statement with COALESCE and also tried the same with ISNULL. Below is my code. Can you please let me know what I am doing wrong. I would have bet that COALESCE or ISNULL was the answer to my problem. I guess I'm a little naïve to think that adding two columns would be a piece of cake :ermm:
-Chris
USE [AmLink_DW]
DECLARE @Bil_Grps TABLE (bil_grp_id int, bil_grp_sname varchar(50))
INSERT INTO @Bil_Grps (bil_grp_id, bil_grp_sname)
SELECT *
FROM (
VALUES
(1,'NPRIT'),
(2,'BCBS'),
(4,'LGA'),
(5,'HSM'),
(11,'HSM TPA'),
(13,'IHP'),
(15,'LM'),
(17,'SGA'),
(18,'CCSA')) AS vtable
([bill_group_id],[bill_group_short_name])
SELECT DISTINCTpd.premium_detail_id
,d.Account_build_id
,Bill_group_id
,account_name
,d.group_id
,group_sub_id
,addr.state_cd AS [Group State] --newly added column
,plan_code
,subscriber_first_name
,subscriber_last_name
,resident_state
,CONVERT(VARCHAR(10),d.effective_date,101) AS effective_date
,hic_number
,social_security_number
,COALESCE(pd.premium_amount,0) AS [Fidelity Premium]
--,ISNULL(pd.premium_amount,0) AS [Fidelity Premium]
--,CASE WHEN appe.plan_id IN (2238,2239,2240,2241,2242,2244,2246,2247,2248,2249,2250,2251,2565,2566)
--THEN SUM(pd.premium_amount)
--END AS [Fidelity Premium] --newly added column
,net_pdp_premium
,net_enhanced_premium
,net_self_funded
,total_billed_premium
,pdp_cms_capitation_with_lis
,enhanced_cms_capitation_rate
,lis_offset
,total_pdp_premium
,total_enhanced_premium
,CASE WHEN COALESCE(adjustment_apply_month, '1/1/1900') < '1/1/2000'
THEN ''
ELSE CONVERT(VARCHAR(10),adjustment_apply_month ,101)
END AS adjustment_apply_month
,adjustment_code
,CONVERT(VARCHAR(10),invoice_month,101) AS invoice_month
,lives_pdp
,lives_enhanced
,commission_plan_id
,pdp_fees_and_commissions
,enhanced_fees_and_commissions
,aggregate_coverage_fee
,ISNULL(CONVERT(VARCHAR(20),late_enrollment_penalty),'') as late_enrollment_penalty
,d.Subscriber_id
,CONVERT(VARCHAR(10),Report_month,101) AS Report_month
,appe.plan_id
,appe.plan_election_name
FROM AMLINK_GB.AmLink_GB.dbo.algb_premium_detail pd
INNER JOIN AMLINK_GB.AmLink_GB.dbo.algb_subscriber_plan_election spe ON spe.subscriber_plan_election_id = pd.subscriber_plan_election_id
INNER JOIN AMLINK_GB.AmLink_GB.dbo.algb_account_group_plan_election agpe ON agpe.group_plan_election_id = spe.group_plan_election_id
INNER JOIN AMLINK_GB.AmLink_GB.dbo.algb_account_product_plan_election appe ON appe.account_product_plan_election_id = agpe.account_product_plan_election_id
INNER JOIN AMLINK_GB.AmLink_GB.dbo.algb_plan p ON p.plan_id = appe.plan_id
INNER JOIN DW_GB_STERLING_PART_D d ON d.commission_plan_id = appe.carrier_plan_id AND pd.Subscriber_id = d.Subscriber_id
INNER JOIN AMLINK_GB.AmLink_GB.dbo.algb_subscriber s ON s.subscriber_id = d.Subscriber_id AND s.[enabled] = 1
INNER JOIN AMLINK_GB.AmLink_GB.dbo.algb_account_group ag ON ag.account_group_id = s.account_group_id AND ag.enabled = 1
INNER JOIN AMLINK_GB.AmLink_GB.dbo.algb_group_address ga ON ga.group_id = ag.group_id AND ga.enabled = 1
INNER JOIN AMLINK_GB.AmLink_GB.dbo.algb_address addr ON addr.address_id = ga.address_id AND addr.enabled = 1
WHERE 1 = 1
AND pd.premium_detail_date = '08/20/2015'
AND d.Report_month = '08/20/2015'
AND d.social_security_number <> '258-30-0237'
AND pd.premium_amount IS NOT NULL --tried this but didn't change the number of results
GROUP BYpd.premium_detail_id
,d.Account_build_id
,Bill_group_id
,account_name
,d.group_id
,group_sub_id
,addr.state_cd
,plan_code
,subscriber_first_name
,subscriber_last_name
,resident_state
,CONVERT(VARCHAR(10),d.effective_date,101)
,hic_number
,social_security_number
,pd.premium_amount --added this
,net_pdp_premium
,net_enhanced_premium
,net_self_funded
,total_billed_premium
,pdp_cms_capitation_with_lis
,enhanced_cms_capitation_rate
,lis_offset
,total_pdp_premium
,total_enhanced_premium
,CASE WHEN COALESCE(adjustment_apply_month, '1/1/1900') < '1/1/2000'
THEN ''
ELSE CONVERT(VARCHAR(10),adjustment_apply_month ,101)
END
,adjustment_code
,CONVERT(VARCHAR(10),invoice_month,101)
,lives_pdp
,lives_enhanced
,commission_plan_id
,pdp_fees_and_commissions
,enhanced_fees_and_commissions
,aggregate_coverage_fee
,ISNULL(CONVERT(VARCHAR(20),late_enrollment_penalty),'')
,d.Subscriber_id
,CONVERT(VARCHAR(10),Report_month,101)
,appe.plan_id
,appe.plan_election_name
ORDER BY d.Account_build_id, d.subscriber_id
--14864 results being returned
September 22, 2015 at 12:10 pm
Not sure what you are needing to achieve, but did you perhaps need:
SUM(COALESCE(pd.premium_amount, 0))
in which case you won't need pd.premium_amount in the GROUP BY.
I imagine that you don't want the pd.premium_amount condition in the WHERE clause either.
September 22, 2015 at 7:20 pm
Hi,
I thought that the query was generated by a report tool or something.
..DISTINCT and Grouping,,... predicate 1 =1 and so on.
The logic of the query is not clear. Do you want to SUM pd.premium_amount(s) or do you want to group the results by pd.premium_amount
and/or you want to include/exclude NULL pd.premium_amount(s) BEFORE grouping.
The number of rows in the result-set(for the parameter values you're using) may be the same for different query logic.
--
The aggregate function SUM ignores NULL values and returns NULL if all aggregated values are all NULLs e.g. sum(NULL,NULL) is NULL or SUM(1,2,NULL,1,NULL) is 4.
If you want to replace possible NULL result with 0 from the SUM function use ISNULL(SUM(pd.premium_amount),0)
🙂
D.Mincic
😀
MCTS Sql Server 2008, Database Development
September 24, 2015 at 9:30 am
The query was written by someone who no longer works for the company. I did not get a chance to meet them.
I'm looking to SUM pd.premium_amounts. Originally I wanted to exclude the results that had a NULL value for pd.premium_amount but after talking to the person who requested this I wanted those results returned.
I talked to one of the senior developers here and they helped me with the rest of it. Below is the final query which has been approved by the requester. Thank you Dean, Kristen and everyone for their help with this. I'm happy to put this ticket to rest as it was a huge pain in the butt. Have a great rest of the day!
-Chris
USE [AmLink_DW]
--CREATE TABLE #Bil_Grps (bil_grp_id int, bil_grp_sname varchar(50))
--INSERT INTO #Bil_Grps (bil_grp_id, bil_grp_sname)
DECLARE @Bil_Grps TABLE
(
bil_grp_id INT,
bil_grp_sname VARCHAR(50)
)
INSERT INTO @Bil_Grps
(
bil_grp_id,
bil_grp_sname
)
SELECT *
FROM ( VALUES ( 1, 'NPRIT'), ( 2, 'BCBS'), ( 4, 'LGA'), ( 5, 'HSM'), ( 11, 'HSM TPA'), ( 13, 'IHP'), ( 15, 'LM'),
( 17, 'SGA'), ( 18, 'CCSA') ) AS vtable ( [bill_group_id], [bill_group_short_name] )
SELECT bg.bil_grp_sname AS [Bill Group Name],
ptd.Bill_group_id AS [Bill Group ID],
ptd.account_name AS [Account Name],
ptd.group_id AS [GROUP ID],
ptd.group_sub_id AS [Group Sub ID],
addr.state_cd AS [GROUP STATE], --newly added column
ptd.plan_code AS [Plan Code],
ptd.subscriber_first_name AS [Subscriber First Name],
ptd.subscriber_last_name AS [Subscriber Last Name],
ptd.resident_state AS [Resident State],
CONVERT(VARCHAR(10), ptd.effective_date, 101) AS [Effective Date],
ptd.hic_number AS [HIC Number],
ptd.social_security_number AS [Social Security Number],
ISNULL(fidelity.fidelity_premium,0) AS [Fidelity Premium], --newly added column
ptd.net_pdp_premium AS [Net PDP Premium],
ptd.net_enhanced_premium AS [Net Enchanced Premium],
ptd.net_self_funded AS [Net Self Funded],
ptd.total_billed_premium AS [Total Billed Premium],
ptd.pdp_cms_capitation_with_lis AS [PDP CMS Capitation with LIS],
ptd.enhanced_cms_capitation_rate AS [Enhanced CMS Capitation Rate],
ptd.lis_offset AS [LIS Offset],
ptd.total_pdp_premium AS [Total PDP Premium],
ptd.total_enhanced_premium AS [Total Enhanced Premium],
CASE WHEN COALESCE(ptd.adjustment_apply_month, '1/1/1900') < '1/1/2000' THEN ''
ELSE CONVERT(VARCHAR(10), ptd.adjustment_apply_month, 101)
END AS [Adjustment Apply Month],
ptd.adjustment_code AS [Adjustment Code],
CONVERT(VARCHAR(10), ptd.invoice_month, 101) AS [Invoice Month],
ptd.lives_pdp AS [Lives - PDP],
ptd.lives_enhanced AS [Lives - Enhanced],
ptd.commission_plan_id AS [Commission Plan ID],
ptd.pdp_fees_and_commissions AS [PDP Fees and Commissions],
ptd.enhanced_fees_and_commissions AS [Enhanced Fees and Commissions],
ptd.aggregate_coverage_fee AS [Aggregate Coverage Fee],
ISNULL(CONVERT(VARCHAR(20), ptd.late_enrollment_penalty), '') AS [LEP - (Late Enrollment Penalty)],
ptd.Subscriber_id AS [Subscriber ID]
--CONVERT(VARCHAR(10), ptd.Report_month, 101) AS Report_month
FROM dbo.DW_GB_STERLING_PART_D ptd
INNER JOIN @Bil_Grps bg ON ptd.Bill_group_id = bg.bil_grp_id
JOIN AMLINK_GB.AmLink_GB.dbo.algb_subscriber s ON s.subscriber_id = ptd.Subscriber_id AND s.[enabled] = 1
JOIN AMLINK_GB.AmLink_GB.dbo.algb_account_group ag ON ag.account_group_id = s.account_group_id AND ag.enabled = 1
JOIN AMLINK_GB.AmLink_GB.dbo.algb_group_address ga ON ga.group_id = ag.group_id AND ga.enabled = 1
JOIN AMLINK_GB.AmLink_GB.dbo.algb_address addr ON addr.address_id = ga.address_id AND addr.enabled = 1 AND addr.address_type_id IN (1)
--LEFT JOIN AMLINK_GB.AmLink_GB.dbo.algb_group_address agaddr ON ptd.group_id = agaddr.group_id AND agaddr.enabled = 1
--LEFT JOIN AMLINK_GB.AmLink_GB.dbo.algb_address addr ON agaddr.address_id = addr.address_id AND addr.address_type_id IN (1) /* 1- physical 2-mailing */
OUTER APPLY
(
SELECT SUM(pd.premium_amount) AS fidelity_premium
FROM amlink_gb.AmLink_GB.dbo.algb_subscriber_plan_election spe
INNER JOIN amlink_gb.AmLink_GB.dbo.algb_account_group_plan_election agpe ON agpe.group_plan_election_id = spe.group_plan_election_id
INNER JOIN amlink_gb.AmLink_GB.dbo.algb_account_product_plan_election appe ON appe.account_product_plan_election_id = agpe.account_product_plan_election_id
INNER JOIN amlink_gb.AmLink_GB.dbo.algb_plan pln ON pln.plan_id = appe.plan_id
INNER JOIN amlink_gb.AmLink_GB.dbo.algb_premium_detail pd ON spe.subscriber_plan_election_id = pd.subscriber_plan_election_id AND pd.premium_detail_date = ptd.Report_month AND pd.enabled = 1
WHERE spe.subscriber_id = ptd.Subscriber_id
AND COALESCE(appe.carrier_plan_id, pln.carrier_plan_id) = ptd.commission_plan_id
AND pln.carrier_org_branch_relationship_id = 15451
) fidelity
WHERE ptd.Report_month = '09/20/2015'
--and ptd.social_security_number = '222-22-2222'
--and ptd.account_name = 'REBCO Health Benefit Trust'
--and ptd.social_security_number = '222-22-2222'
AND ptd.social_security_number <> '222-22-2222'
September 24, 2015 at 11:18 am
Dean Mincic (9/22/2015)
Hi cthorn112I think the CASE Statement needs ELSE part, something like
....
,CASE
WHEN appe.plan_id IN (2238,2239,2240,2241,2242,2244,2246,2247,2248,2249,2250,2251,2565,2566)
THEN SUM(pd.premium_amount)
ELSE 0
END AS [Fidelity Premium] --newly added column
...
AND pd.premium_amount IS NOT NULL
...
+1
If doesn't matter whether premium amount is null, the original CASE is instructing not to return anything for the SUM if the plan isn't in the specific list of plans.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply