Between Statement not working in Case Select

[faithfulsinger]

I'm done staring at this. I'm sure it's something blatantly obvious, but I can't see why the between statement the 2nd and 3rd line of this HouseholdType case statement is not working. I've pasted partial results below the code. The first statement works as expected, but you will notice MID 25 and 34 are displaying Unknown when they should display Majority Elderly. MID 36 should be displaying Majority Family. I appreciate any help on resolving this.

select  distinct pr_project.projectnumber As MasterIDProjectNumber,
		Case when count(distinct BuildingCounty.BldCnty) = 1 then Max(BuildingCounty.BldCnty) 
		else 'SCATTERED' end As County,
	Case when count(distinct pr_building.city) = 1 then Max(pr_building.city)
		else 'SCATTERED' end As City,
	Case 
		when (Targeting.NumberUnits / TotalUnits.Units)*100 = 100 then 'All ' + HouseholdDesc
		when ((Targeting.NumberUnits / TotalUnits.Units)*100) Between 50 and 99 then 'Majority ' + HouseholdDesc
		when (Targeting.NumberUnits / TotalUnits.Units)*100 Between 10 and 49 then 'Mixed'		
		else 'Unknown' 
		end As HouseholdType,
	UPPER(AssistedLivingType.AssistedLivingdescription)As AssistedLivingType,
	Targeting.NumberUnits,
	HouseholdDesc,
	TotalUnits.Units as TotalUnits,
	'RPM' as Source
	

from ESQLTEST01SQL2005.RPMSystem.dbo.pr_project

inner join (select pr_targeting.projectid as ProjectID, MaxUnits.NumberUnits as NumberUnits, pr_targeting.targetingtypeid as TargetingTypeID
	from ESQLTEST01SQL2005.RPMSystem.dbo.pr_targeting
	inner join (select pr_targeting.projectid, max(pr_targeting.numberUnits) as NumberUnits
		from ESQLTEST01SQL2005.RPMSystem.dbo.pr_targeting
		group by pr_targeting.projectid) as MaxUnits on pr_targeting.projectid = MaxUnits.projectid and pr_targeting.NumberUnits = MaxUnits.NumberUnits) as Targeting on
		pr_project.projectid = Targeting.ProjectID

inner join (select targetingtypeid,
		Case 
		when pr_targetingtype.targetingtypeid in ('3','5','6','7') then 'Supportive Housing' 
		when pr_targetingtype.targetingtypeid = '4' then 'Elderly' else
		pr_targetingtype.description end As HouseholdDesc
		from ESQLTEST01SQL2005.RPMSystem.dbo.pr_targetingtype) Household
	on targeting.targetingtypeid = Household.targetingtypeid

left outer join (select pr_project.projectnumber as ProjectNumber,
		Case
		when pr_targetingtype.targetingtypeid = '4' then 'RCAC'
		end As AssistedLivingDescription
		from ESQLTEST01SQL2005.RPMSystem.dbo.pr_targeting
		inner join ESQLTEST01SQL2005.RPMSystem.dbo.pr_targetingtype on
		pr_targeting.targetingtypeid = pr_targetingtype.targetingtypeid
		inner join ESQLTEST01SQL2005.RPMSystem.dbo.pr_project on
		pr_project.projectid = pr_targeting.projectid
		where pr_targetingtype.targetingtypeid = '4'

		union

		select	m*rtgage.projectid as ProjectNumber,
			portxref.portfoli as AssistedLivingDescription
			from dbo.m*rtgage
			inner join dbo.portxref on portxref.loannumber = m*rtgage.loannumber
				and portxref.cid = m*rtgage.cid
				and portxref.div = m*rtgage.div
			where portfoli in ('CBRF','RCAC')
		) AssistedLivingType
	on PR_Project.ProjectNumber = AssistedLivingType.ProjectNumber

inner join ESQLTEST01SQL2005.RPMSystem.dbo.county
on county.county_id = pr_project.county

inner join ESQLTEST01SQL2005.RPMSystem.dbo.pr_building
on pr_project.projectid = pr_building.projectid

inner join (select pr_project.projectid, buildingid,
		case when pr_building.county = ' ' then county.name
		else pr_building.county end As BldCnty
		from ESQLTEST01SQL2005.RPMSystem.dbo.pr_building,
		ESQLTEST01SQL2005.RPMSystem.dbo.county,
		ESQLTEST01SQL2005.RPMSystem.dbo.pr_project
		where pr_building.projectid = pr_Project.projectid and
		pr_project.county = county.county_id) BuildingCounty
	on BuildingCounty.BuildingId = Pr_Building.BuildingID

inner join (SELECT PR_Project.ProjectNumber, PR_Project.ParentProjectID, isnull(SUM(IsNull(PR_RequiredUnitMix.NumberUnits,0)),0) as Units
                  FROM  ESQLTEST01SQL2005.RPMSystem.dbo.PR_Project PR_Project 
                  INNER JOIN ESQLTEST01SQL2005.RPMSystem.dbo.PR_Snapshot PR_Snapshot ON  PR_Snapshot.SnapshotName = 'Current' 
                              and PR_Project.ProjectID=PR_Snapshot.ProjectID 
                  INNER JOIN ESQLTEST01SQL2005.RPMSystem.dbo.PR_RequiredUnitMix PR_RequiredUnitMix ON PR_Snapshot.SnapshotID=PR_RequiredUnitMix.SnapshotID 
		  where IsMonitoredProject = 1
                  group by PR_Project.ProjectNumber, PR_Project.ParentProjectID         
                  having PR_Project.ParentProjectID =  max(PR_Project.ParentProjectID)) TotalUnits
	on TotalUnits.ProjectNumber = pr_project.projectnumber

where pr_Project.IsMonitoredProject = 1 and pr_project.projectNumber <> '3000'--this is a test project 


group by pr_project.projectnumber,
	Household.HouseholdDesc,
	Targeting.NumberUnits,
	TotalUnits.Units,
	AssistedLivingType.AssistedLivingDescription

order by pr_project.projectnumber

Partial Results
MID County City HouseholdType AssistedLivingType NumberUnits HouseholdDesc TotalUnits Source
1 SAUK BARABOO All Family NULL 40 Family 40 RPM
2 GREEN LAKEBERLIN All Family NULL 32 Family 32 RPM
3 WALWORTH WHITEWATEAll Family NULL 40 Family 40 RPM
4 DODGE WAUPUN All Family NULL 40 Family 40 RPM
5 CHIPPEWA CADOTT All Family NULL 24 Family 24 RPM
6 BARRON BARRON All Family NULL 24 Family 24 RPM
7 PEPIN DURAND All Family NULL 24 Family 24 RPM
8 CHIPPEWA CHIPPEWA All Family NULL 72 Family 72 RPM
9 LA CROSSELACROSSE All Family NULL 72 Family 72 RPM
10 DUNN MENOMONIEAll Family NULL 24 Family 24 RPM
11 CHIPPEWA STANLEY All Family NULL 24 Family 24 RPM
13 JACKSON BLACK RIVAll Elderly NULL 21 Elderly 21 RPM
15 JUNEAU MAUSTON All Family NULL 12 Family 12 RPM
17 MARQUETTEWESTFIELDAll Elderly NULL 8 Elderly 8 RPM
25 GRANT BOSCOBEL Unknown NULL 18 Elderly 20 RPM
34 WASHINGTONJACKSON Unknown NULL 23 Elderly 25 RPM
36 ASHLAND ASHLAND Unknown NULL 12 Family 22 RPM
39 MILWAUKEEMILWAUKEEAll Family NULL 18 Family 18 RPM

 

[gmmastros]

I think your problem is a result of integer math. Let me explain.

SQL Server takes your data types in to account when performing math. When you divide an integer by another integer, the result is an integer. For example....

Select 200 / 500 * 100

When you run the code above in a query window, you get 0 instead of the 40 that you would expect. To accommodate this, you could do this...

Select 100.0 * 200 / 500

Notice that I am using 100.0 instead of 100. When you run this code, you will get 40.000000 which is the correct value. To fix your code, you could do something like this...

	Case 
		when 100.0 * Targeting.NumberUnits / TotalUnits.Units = 100 then 'All ' + HouseholdDesc
		when 100.0 * Targeting.NumberUnits / TotalUnits.Units Between 50 and 99 then 'Majority ' + HouseholdDesc
		when 100.0 * Targeting.NumberUnits / TotalUnits.Units Between 10 and 49 then 'Mixed'		
		else 'Unknown' 
		end As HouseholdType,

You'll need to repeat this logic in other parts of your query.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[gmmastros]

I need to edit my advice a little. I do think my diagnosis of the problem is accurate, but not the cure.

By using 100.0 you cause floating point math. Then, when SQL Server does the between comparison, it will convert the integers to floating point numbers to match the data type of the expression on the left.

Suppose the result of the expression on the left is 99.1, this value would fall through to the 'unknown' result because it's not equal to 100 and it's not between 50./0 and 99.0.

To accomodate this, you could change your code to:

Case 
  when [!]Round([/!]100.0 * Targeting.NumberUnits / TotalUnits.Units[!], 0)[/!] = 100 then 'All ' + HouseholdDesc
  when [!]Round([/!]100.0 * Targeting.NumberUnits / TotalUnits.Units[!], 0)[/!] Between 50 and 99 then 'Majority ' + HouseholdDesc
  when [!]Round([/!]100.0 * Targeting.NumberUnits / TotalUnits.Units[!], 0)[/!] Between 10 and 49 then 'Mixed'		
  else 'Unknown' 
  end As HouseholdType,

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[faithfulsinger]

You second solution seems to be working perfectly. I never gave the decimals a thought. My calculator is set to 2. Thank you so much.

 

[gmmastros]

You're not the first person that has gotten tripped up by integer math, and you won't be the last. I'm glad I was able to help.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom