Example of output needed...
31/OfficeVisit
my sp
ALTER procedure EncounterCodes_NET
(
@.ClinicID int
)
as
select
CombinedField=(EnCodeID + EnCodeDesc)
from
Clinic_Encodes
Where
ClinicID=@.ClinicID
Order by Sortorderreplace the EncodeID with
Cast(EnCodeID as varchar)
I have a select query that returns three integer fields from a table the
values range from 0 to 5. On each row I would like to calculate the average
value in the three fields however, the difficulty is that only the rows
where the value is greater than 0 should be included in the calculation.
To make this clearer please consider the following example:
Col1 Col2 Col2 Average Calculation
1 2 3 2 (Col1+Col2+Col3)/3
2 2 0 2 (Col1+Col2+Col3)/2
The first row should be divided by 3 because each of the three columns has a
value greater than 0, however the second row should be divided by 2 because
only two of the value are greater than 0.
Is this possible?
Also is is possible to pass the results of one calculation into another
calculation.
Thanks in advance,
SteveSELECT col1, col2, col3, (col1 + col2 + col3)/(case when col1 = 0 then
0 else 1 end + case when col2 = 0 then 0 else 1 end + case when col3 =
0 then 0 else 1 end)
FROM My_Table
-Tom.|||(col1+col2+col3) /
(SIGN(col1)+ SIGN(col2)+ SIGN(col3))
If they can be negative, use ABS(SIGN (X))|||Thanks Tom,
This worked fine.
"Thomas R. Hummel" <tom_hummel@.hotmail.com> wrote in message
news:1109973262.691311.81820@.o13g2000cwo.googlegro ups.com...
> SELECT col1, col2, col3, (col1 + col2 + col3)/(case when col1 = 0 then
> 0 else 1 end + case when col2 = 0 then 0 else 1 end + case when col3 =
> 0 then 0 else 1 end)
> FROM My_Table
> -Tom.
