SQL Help

[mikeadn]

I am running the following code, however I know that numerator = 7 and denominator = 23. Why is HCOObservation always coming out as a whole number of 1.00?

SELECT
(COUNT(CASE WHEN [AMI-1] = 'B' THEN [A].[CID] ELSE NULL END)) AS NUMERATOR,
(COUNT(CASE WHEN [AMI-1] IN ('A', 'B') THEN [A].[CID] ELSE NULL END)) AS DENOMINATOR,
(((CAST(COUNT(CASE WHEN [AMI-1] = 'B' THEN [A].[CID] ELSE NULL END)AS FLOAT) * (1)) /
(COUNT(CASE WHEN [AMI-1] = 'B' THEN [A].[CID] ELSE NULL END)))) AS HCOOBSERVATION
FROM EOC AS A INNER JOIN AMI AS B
ON A.CID = B.CID
WHERE DISCHGDATE BETWEEN '01-01-2005' AND '03-31-2005'

Mike Cormier
May God bless everything you do...

 

[r937]

why?

because you are not dividing numerator by denominator, you are dividing numerator by numerator!

that's always gonna give you 1.00

cut & paste error, prolly


rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts May 8 2005)

 

[mikeadn]

THanks for the extra set of eyes. Been a long weekend already.

Mike Cormier
May God bless everything you do...