Omit rows that have NULL values

Transact-SQL
1

Greetings All,

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 DISTINCT	pd.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 BY	pd.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
2

Try:

CASE WHEN appe.plan_id IN (2238,2239,2240,2241,2242,2244,2246,2247,2248,2249,2250,2251,2565,2566) 
THEN SUM(CASE WHEN (pd.premium_amount) IS NULL THEN 0 ELSE (pd.premium_amount) END )
AS [Fidelity Premium] --newly added column
3

That WHERE condition will drop rows with a null value in that column. But you're using DISTINCT with a (very) large GROUP BY, so you could easily have the same number of rows in the result set even if fewer rows were read to produce that output set. You really should restructure the joins so that DISTINCT and/or GROUP BY is not needed, since you're not even doing any SUMs, MAXes or other group functions anyway. I couldn't begin to guess at the appropriate logic to do that since not all of the columns have table aliases.

Btw, you should edit out the SSN in the query, even if you do specify <>.

4

@djj55 thanks for the reply. I tried the code you suggested and that returned an error message, "Msg 156, Level 15, State 1, Line 87 Incorrect syntax near the keyword 'AS'." I tried putting the parenthesis before END but that didn't work. It looks like a simple syntax error. I'm scratching my head. Below is what I have

,CASE WHEN appe.plan_id IN (2238,2239,2240,2241,2242,2244,2246,2247,2248,2249,2250,2251,2565,2566) 
					THEN SUM(CASE WHEN (pd.premium_amount) IS NULL 
					THEN 0 ELSE (pd.premium_amount)
					END ) AS [Fidelity Premium] --newly added column
5

I missed an END as you need one for each CASE. Also make sure there are matching parenthesis.

Something like

,CASE WHEN appe.plan_id IN (2238,2239,2240,2241,2242,2244,2246,2247,2248,2249,2250,2251,2565,2566) 
					THEN SUM(CASE WHEN (pd.premium_amount) IS NULL 
					THEN 0 ELSE (pd.premium_amount)
					END ) END AS [Fidelity Premium] --newly added column
6

@ScottPletcher I hear what you are saying. I inherited this code and working with what I have. I'm relatively new to this company and not looking to rewrite code especially if it's not "absolutely necessary". Believe it or now the query only takes 2 seconds to run. If it were taking significantly longer then I would rewrite the JOIN statements. About the SSN number. Your are right I should have, it didn't even cross my mind. If someone is looking to take it then there life must be pretty lame.

-Chris

7

@djj55 Ah that's right! I had tunnel vision and didn't think about the other END. I updated the code and unfortunately that did not have any change on the results. I don't think this will solve my issue. Won't this logic SET any pd.premium_amount that IS NULL = 0? I'm looking to get rid of the results that have a NULL value for pd.premium_amount. Please clarify if I'm misunderstanding this. Regardless I appreciate your input.

-Chris

8

@cthorn112, You have a point that if all values are null then the result would be zero. I would suggest a where criteria to avoid that :grinning:

9

Sorry, I wasn't clear. I meant you should edit-out the SSN on your posting on this site. Of course you never want to use any valid SSN in a public posting.