Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Case Statement

Author  Topic 

jcb267
Constraint Violating Yak Guru

291 Posts
Posted - 2014-05-01 : 13:10:02
Hi -

Can anyone please help me with this case statement:
====================
, case
when count(distinct RateCode) = 2 and RateCode like 'Indivdual' then 'EE_E' --2 Tier
when count(distinct RateCode) = 2 and RateCode like 'Individual' then 'EE_E' --2 Tier
when count(distinct RateCode) = 2 and RateCode like 'Individuual' then 'EE_E' --2 Tier
when count(distinct RateCode) = 2 and RateCode like 'Individual and Spouse' then 'Family' --2 Tier
when count(distinct RateCode) = 2 and RateCode like 'Individual and 1 Dependent' then 'Family' --2 Tier
when count(distinct RateCode) = 2 and RateCode like 'Individual and Dependents' then 'Family' --2 Tier
when count(distinct RateCode) = 2 and RateCode like 'Individual and Family' then 'Family' --2 Tier

when count(distinct RateCode) = 3 and RateCode like 'Indivdual' then 'EE_E' --3 Tier
when count(distinct RateCode) = 3 and RateCode like 'Individual' then 'EE_E' --3 Tier
when count(distinct RateCode) = 3 and RateCode like 'Individuual' then 'EE_E' --3 Tier
when count(distinct RateCode) = 3 and RateCode like 'Individual and Spouse' then 'EE_1' --3 Tier
when count(distinct RateCode) = 3 and RateCode like 'Individual and 1 Dependent' then 'EE_1' --3 Tier
when count(distinct RateCode) = 3 and RateCode like 'Individual and Dependents' then 'Family' --3 Tier
when count(distinct RateCode) = 3 and RateCode like 'Individual and Family' then 'Family' --3 Tier

when count(distinct RateCode) = 4 and RateCode like 'Indivdual' then 'EE_E' --4 Tier
when count(distinct RateCode) = 4 and RateCode like 'Individual' then 'EE_E' --4 Tier
when count(distinct RateCode) = 4 and RateCode like 'Individuual' then 'EE_E' --4 Tier
when count(distinct RateCode) = 4 and RateCode like 'Individual and Spouse' then 'EE_1' --4 Tier
when count(distinct RateCode) = 4 and RateCode like 'Individual and 1 Dependent' then 'Emp+Child(ren)' --4 Tier
when count(distinct RateCode) = 4 and RateCode like 'Individual and Dependents' then 'Emp+Child(ren)' --4 Tier
when count(distinct RateCode) = 4 and RateCode like 'Individual and Family' then 'Family' --4 Tier

else 'Tier Not Defined'
end
as Tier
====================

I am trying to assign a "Tier" based on a distinct count of entries to the RateCode field then if the RateCode entry is Individual the assigned tier will be 'EE_E'.

The code runs fine but does not give me the correct answers, it is assiging everything to the default 'Tier Not Defined' tier.

Thanks!

John

gbritton
Master Smack Fu Yak Hacker

2780 Posts
Posted - 2014-05-01 : 13:33:13
You'd be better off putting the RateCode, Count and Tier in a lookup table then joining on that. A long case statement like this would be nasty to maintain.

However, to further debug your problem, we'll need to see some sample data
Go to Top of Page

jcb267
Constraint Violating Yak Guru

291 Posts
Posted - 2014-05-01 : 13:36:41
I thought of the lookup table idea, that gets messy too and I think I would run into the same problem......
Go to Top of Page

TG
Master Smack Fu Yak Hacker

6065 Posts
Posted - 2014-05-01 : 13:38:59
yeah- that can't work. If "count(distinct ratecode)" = 4 then there must be some values that don't equal 'Individual and Family'

and your: "..rateCode like 'Individual and Family'
like in this case is the same as equal because you are not using any wildcards.

Don't know what you're trying to do so I can't offer an alternativie but as you have it now you can't get anything other than 'Tier Not Defined'

Be One with the Optimizer
TG
Go to Top of Page

jcb267
Constraint Violating Yak Guru

291 Posts
Posted - 2014-05-01 : 13:39:37
Hi gbritton -

Here is all of the code:
====-- Input Parameters

declare @dataasof datetime,
@ageasof datetime

set @dataasof = '03/01/2014'
set @ageasof = '07/01/2013'

-- Member Data

select SponsorID
, Gender
, case
when count(distinct RateCode) = 2 and RateCode like 'Indivdual' then 'EE_E' --2 Tier
when count(distinct RateCode) = 2 and RateCode like 'Individual' then 'EE_E' --2 Tier
when count(distinct RateCode) = 2 and RateCode like 'Individuual' then 'EE_E' --2 Tier
when count(distinct RateCode) = 2 and RateCode like 'Individual and Spouse' then 'Family' --2 Tier
when count(distinct RateCode) = 2 and RateCode like 'Individual and 1 Dependent' then 'Family' --2 Tier
when count(distinct RateCode) = 2 and RateCode like 'Individual and Dependents' then 'Family' --2 Tier
when count(distinct RateCode) = 2 and RateCode like 'Individual and Family' then 'Family' --2 Tier

when count(distinct RateCode) = 3 and RateCode like 'Indivdual' then 'EE_E' --3 Tier
when count(distinct RateCode) = 3 and RateCode like 'Individual' then 'EE_E' --3 Tier
when count(distinct RateCode) = 3 and RateCode like 'Individuual' then 'EE_E' --3 Tier
when count(distinct RateCode) = 3 and RateCode like 'Individual and Spouse' then 'EE_1' --3 Tier
when count(distinct RateCode) = 3 and RateCode like 'Individual and 1 Dependent' then 'EE_1' --3 Tier
when count(distinct RateCode) = 3 and RateCode like 'Individual and Dependents' then 'Family' --3 Tier
when count(distinct RateCode) = 3 and RateCode like 'Individual and Family' then 'Family' --3 Tier

when count(distinct RateCode) = 4 and RateCode like 'Indivdual' then 'EE_E' --4 Tier
when count(distinct RateCode) = 4 and RateCode like 'Individual' then 'EE_E' --4 Tier
when count(distinct RateCode) = 4 and RateCode like 'Individuual' then 'EE_E' --4 Tier
when count(distinct RateCode) = 4 and RateCode like 'Individual and Spouse' then 'EE_1' --4 Tier
when count(distinct RateCode) = 4 and RateCode like 'Individual and 1 Dependent' then 'Emp+Child(ren)' --4 Tier
when count(distinct RateCode) = 4 and RateCode like 'Individual and Dependents' then 'Emp+Child(ren)' --4 Tier
when count(distinct RateCode) = 4 and RateCode like 'Individual and Family' then 'Family' --4 Tier

else 'Tier Not Defined'
end
as Tier

, COUNT(distinct memberid) as Members
, (DateDiff(day,birthdate,@ageasof)/365.242) as Age

into ##tempagemem

from MemberMonths

where SponsorId = 100807
and ReportingMonth = @dataasof

group by Gender
, SponsorID
, RateCode
, (DateDiff(day,birthdate,@ageasof)/365.242)
====
Go to Top of Page

jcb267
Constraint Violating Yak Guru

291 Posts
Posted - 2014-05-01 : 13:43:42
Yes, it does get only 'Tier Not Defined' I substituted like for equals because = would not work on a varchar......

UGGGH! I'm really stuck in the mud.

.
quote:
Originally posted by TG

yeah- that can't work. If "count(distinct ratecode)" = 4 then there must be some values that don't equal 'Individual and Family'

and your: "..rateCode like 'Individual and Family'
like in this case is the same as equal because you are not using any wildcards.

Don't know what you're trying to do so I can't offer an alternativie but as you have it now you can't get anything other than 'Tier Not Defined'

Be One with the Optimizer
TG
Go to Top of Page

TG
Master Smack Fu Yak Hacker

6065 Posts
Posted - 2014-05-01 : 13:45:07
you're GROUP BY includes [RateCode] so count(distinct rateCode) will always return 1.

Be One with the Optimizer
TG
Go to Top of Page

jcb267
Constraint Violating Yak Guru

291 Posts
Posted - 2014-05-01 : 13:47:42
OOOPS, I did not know that. I thought I had to include it??
quote:
Originally posted by TG

you're GROUP BY includes [RateCode] so count(distinct rateCode) will always return 1.

Be One with the Optimizer
TG
Go to Top of Page

TG
Master Smack Fu Yak Hacker

6065 Posts
Posted - 2014-05-01 : 13:52:31
>>UGGGH! I'm really stuck in the mud.

at least you'll need to describe what your trying to do and include the business logic that determines the [Tier].

But ideally you should provide DDL (CREATE TABLE statements), DML (INSERT VALUES statements for sample data), and a desired result set based on your sample data.

Be One with the Optimizer
TG
Go to Top of Page

jcb267
Constraint Violating Yak Guru

291 Posts
Posted - 2014-05-01 : 14:09:49
OK, Here is a stab at it......

The code I posted is to be used to count members of health insurance plans by tier (called RateCode in the DB). I have included a customers SponsorID, which uniquely identifies each customer. For the most part there is only 1 sponsorid per report but on occasion there could be multiple sponsorid's that form a "group".

Does that help?
Go to Top of Page

TG
Master Smack Fu Yak Hacker

6065 Posts
Posted - 2014-05-01 : 15:57:43
That explanation just raises more questions for me. If you want help you'll need to do the DDL/DML/Expected Results route.


Be One with the Optimizer
TG
Go to Top of Page

Lamprey
Master Smack Fu Yak Hacker

4614 Posts
Posted - 2014-05-01 : 16:15:51
Any chance you can fix/clean your data? This seems more like a place where you should take a step back and say we need to clean the data instead of trying to use it as is.
Go to Top of Page

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts
Posted - 2014-05-02 : 07:01:25
Seems that the entire DB needs redesigned. I bet there are columns that hold comma separated data too.








How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site