March 8, 2011 at 2:12 pm
Hi all,
What I really want to do in the below statement is have the three values display on one row and am running into difficulty in the syntax for the division statement listed below as c. A and B display fine on their own but as soon as I try to utilize the aliases involved in the third step I get the "multi-part identifier cannot be bound" error. I know I'm close, but I need some help. Any help is greatly appreciated!
select (select count(distinct encounter) as numerator from temp_discharge inner join temp_cdmcharges on encounter=encounternumber where dateadd(day,3,servicedate) < admission_date) as a,
(select count(encounter) as denominator from temp_discharge) as b,
((a.numerator/b.denominator) * 100) as c
March 8, 2011 at 2:44 pm
Because they appear in the SELECT clause instead of the FROM clause the aliases a, b, and c are column aliases, but you are trying to treat a and b as if they were table aliases in the expression for column c. If you want a and b to be table aliases, you need to move them to the FROM clause (and also provide a join).
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
March 8, 2011 at 3:03 pm
Try this:
WITH cte(numerator, denominator)
AS (SELECT (SELECT COUNT(DISTINCT encounter) AS numerator
FROM temp_discharge
INNER JOIN temp_cdmcharges ON encounter = encounternumber
WHERE DATEADD(day, 3, servicedate) < admission_date
),
(SELECT COUNT(encounter) AS denominator
FROM temp_discharge
)
)
SELECT numerator AS a,
denominator AS b,
((numerator / denominator) * 100) AS c
FROM cte ;
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
March 8, 2011 at 3:16 pm
That worked, thanks!
The one final question I have is that c is now coming up very small, even with the multiplication by 100. How may I go about extending the decimal point to reflect the value rather than simply showing up currently as "0" since it's rounded as such?
Ex. If it's 0.495% I still want it to show that value rather than just 0%
March 8, 2011 at 3:27 pm
You'll want to convert the numerator and denominator to float or some form of a decimal before doing your division...sorry I did not consider it.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
March 8, 2011 at 3:49 pm
I feel like I'm still doing something wrong as the decimal has yet to move. Am I placing the command in the proper area?
WITH diagnostic(numerator, denominator)
AS (SELECT (SELECT COUNT(DISTINCT encounter) AS numerator
FROM temp_discharge
INNER JOIN temp_cdmcharges ON encounter = encounternumber
WHERE DATEADD(day, 3, servicedate) < admission_date
),
(SELECT COUNT(encounter) AS denominator
FROM temp_discharge
)
)
SELECT convert(decimal(6),numerator) AS a,
convert(decimal(6),denominator) AS b,
((numerator / denominator) * 100) AS c
FROM diagnostic ;
March 8, 2011 at 4:00 pm
This may clear things up:
SELECT CONVERT(DECIMAL(6), 10.95) AS a,
CONVERT(DECIMAL(6, 2), 10.95) AS b
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
March 8, 2011 at 8:08 pm
Since the numerator and denominator are likely of the INT datatype, you can simply change this...
((a.numerator/b.denominator) * 100)
... to this...
a.numerator*100.0/b.denominator
... and you'll get your decimal places. 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
March 9, 2011 at 7:54 am
Unfortunately it's not enough believe it or not. In this case, a = 52 and b = 10496, so c's percentage will be 0.495%. I'm still only receiving "0" with the above recommendations. In each case, I'm hoping that the percentage will be very minute like this as well, so it will be common place.
I'm wondering if there is an easy way to write it as a case statement instead where I could assume that I only want to go as far as three digits past the decimal point maximum and that it should never exceed one character before the decimal. So if I say that it's three digits I should have '0.' + c + '%' or where it's four I create a substring to put the first character + '.' + final three characters + '%'. Is that too much work for what I'm trying to achieve?
March 9, 2011 at 7:58 am
Untested...
WITH diagnostic(numerator, denominator)
AS (SELECT CONVERT(numeric(9,3), (SELECT COUNT(DISTINCT encounter) AS numerator
FROM temp_discharge
INNER JOIN temp_cdmcharges ON encounter = encounternumber
WHERE DATEADD(day, 3, servicedate) < admission_date
)),
CONVERT(numeric(9,3),(SELECT COUNT(encounter) AS denominator
FROM temp_discharge
))
)
SELECT numerator AS a,
denominator AS b,
((numerator/ denominator) * 100) AS c
FROM diagnostic ;
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 9, 2011 at 8:54 am
That did it! Thank you all very much!
March 9, 2011 at 1:30 pm
Jayded (3/9/2011)
Unfortunately it's not enough believe it or not. In this case, a = 52 and b = 10496, so c's percentage will be 0.495%. I'm still only receiving "0" with the above recommendations. In each case, I'm hoping that the percentage will be very minute like this as well, so it will be common place.I'm wondering if there is an easy way to write it as a case statement instead where I could assume that I only want to go as far as three digits past the decimal point maximum and that it should never exceed one character before the decimal. So if I say that it's three digits I should have '0.' + c + '%' or where it's four I create a substring to put the first character + '.' + final three characters + '%'. Is that too much work for what I'm trying to achieve?
Apparently, you didn't try the simple method I posted. 😉 Here it is again... round it to how ever many decimal places you actually need.
DECLARE @Numerator INT,
@Denominator INT
SELECT @Numerator = 52,
@Denominator = 10496
SELECT @Numerator * 100.0 / @Denominator
Here's the output...
0.495426829268
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply