Solved

Arithmetic Error Converting Int to Numeric when Datatype is Already Int

Posted on 2011-02-14
20
275 Views
Last Modified: 2012-05-11
I have the following update statement that is failing with the notorious Arithmetic overflow error converting int to data type numeric.  Problem is that the data type for the field is already numeric.  My question is how do I modify the update to return a result?  And how would I modify the code to return one of 5 results if the result of the calculation falls within ranges?  In other words, if the calculation results falls within the range 100-95 then update the field with the word "Superior" if between 95-85 then "Good" etc.

I am omitting all the middle calculation as it is a book, but here is the basic code:

update FACILITY
set MAINTANCE_RATING=(

select
--Add additional fields here such as PSC #, School Name, LEA Name
--Code below calculates the Overall Rating for a Facility based on Methodology developed in FY 2007
(
convert (decimal(6,2),
(sum
(
(CASE roadways WHEN 'Superior' THEN 95 WHEN 'Good' THEN 85 WHEN 'Adequate' THEN 75 WHEN 'Not Adequate' THEN 65 WHEN 'Poor' THEN '55' else 0 END)+
(CASE site_appearance WHEN 'Superior' THEN 95 WHEN 'Good' THEN 85 WHEN 'Adequate' THEN 75 WHEN 'Not Adequate' THEN 65 WHEN 'Poor' THEN 55 else 0 END)+
(CASE exterior WHEN 'Superior' THEN 95 WHEN 'Good' THEN 85 WHEN 'Adequate' THEN 75 WHEN 'Not Adequate' THEN 65 WHEN 'Poor' THEN 55 else 0 END)+
....
)
)
)
/
(70-
sum
(
(case roadways when 'Not Applicable' then 1 else 0 end) + (case site_appearance when 'Not Applicable' then 1 else 0 end)
+(case exterior when 'Not Applicable' then 1 else 0 end) + (case playground when 'Not Applicable' then 1 else 0 end)
)
)
+5
)
as [Overall Rating]

from maintenance_survey_results m

)
from Maintenance_Survey_Results,FACILITY
where Maintenance_Survey_Results.PSC_Number=FACILITY.PSC_NUMBER
0
Comment
Question by:mounty95
  • 9
  • 6
  • 2
  • +2
20 Comments
 
LVL 11

Expert Comment

by:rajvja
ID: 34889279
Hi

Instead of decimal(6,2), try using just decimal
0
 
LVL 11

Expert Comment

by:rajvja
ID: 34889322
Hi,

   May be the query resulting second part(after /) returning 0.
0
 
LVL 29

Expert Comment

by:Olaf Doschke
ID: 34889413
So simplifying the calculation Sum(...) ends up as some int and you also divide by 70- some other summation, another int, overall the calculation is convert(decimal(6,2),someint/someint).

Well, you can't expect this to have 2 decimal places, therefor the error. I'd rather use CAST() instead of CONVERT(), as that will round the computed value.

CAST(sum(...)/(70-sum()) As decimal(6,2)) as [overall_rating]

Open in new window


Also, what's the exact data type of the field you talk of? Is it really decimal(6,2)?

In regard to converting the overall rating into text ratings, you can again make use of CASE

Select [overall_rating],
Case
           WHEN   100>=[overall_rating] and [overall_rating]>95 Then 'Superior'
           WHEN   95>=[overall_rating] and [overall_rating]>85 Then 'Good'
        ...
Endcase AS [overall_textrating]
From ...

Open in new window


Bye, Olaf.

0
 

Author Comment

by:mounty95
ID: 34889415
Changing from decimal (6,2) to just decimal returned a result, but the same result for all those records effected.  The result in the column says -680.

The result after the "/" can not result in 0 based on the logic.

Is the same result because of the way that I am trying to link the two tables?  If so, how do I resolve?

Also, how would I change the update to enter a word of Superior/Good/Adequate/Not Adequate/Poor depending on the result of the math?
0
 
LVL 39

Expert Comment

by:lcohan
ID: 34889538
What is your TARGET column datatype?

For the word you can build a case like below:
.......
CASE WHEN table.column = 10 THEN  Superior
            WHEN table.column  < 10 AND table.column >= 8 THEN  Good
            WHEN table.column  < 8 AND table.column >=6 THEN  Adequate
            WHEN table.column  <  6 AND table.column >=4 THEN  Not Adequate
            WHEN table.column  < 4 THEN  Poor  ELSE NotRated END as RatingColumn,
.......

0
 
LVL 39

Expert Comment

by:lcohan
ID: 34889569
Sorry the quotes around the strings got stripped out misteriously...


CASE WHEN [table].[column] = 10 THEN  'Superior'
            WHEN [table].[column]  < 10 AND [table].[column] >= 8 THEN  'Good'
            WHEN [table].[column]  < 8 AND [table].[column] >=6 THEN  'Adequate'
            WHEN [table].[column]  <  6 AND [table].[column] >=4 THEN  'Not Adequate'
            WHEN [table].[column]  < 4 THEN  Poor  ELSE 'NotRated' END as RatingColumn
0
 

Author Comment

by:mounty95
ID: 34889644
The target column datatype is int.

Here is the entire script if that helps.  I now get the same result of -680 in  every column even though there is not a calculation for every record.  I feel like I am going backwards here:

update FACILITY
set MAINTANCE_RATING=
(
select

(
convert (decimal,
(sum
(
(CASE roadways WHEN 'Superior' THEN 95 WHEN 'Good' THEN 85 WHEN 'Adequate' THEN 75 WHEN 'Not Adequate' THEN 65 WHEN 'Poor' THEN '55' else 0 END)+
(CASE site_appearance WHEN 'Superior' THEN 95 WHEN 'Good' THEN 85 WHEN 'Adequate' THEN 75 WHEN 'Not Adequate' THEN 65 WHEN 'Poor' THEN 55 else 0 END)+
(CASE exterior WHEN 'Superior' THEN 95 WHEN 'Good' THEN 85 WHEN 'Adequate' THEN 75 WHEN 'Not Adequate' THEN 65 WHEN 'Poor' THEN 55 else 0 END)+
(CASE playground WHEN 'Superior' THEN 95 WHEN 'Good' THEN 85 WHEN 'Adequate' THEN 75 WHEN 'Not Adequate' THEN 65 WHEN 'Poor' THEN 55 else 0 END)+
(CASE sidewalks WHEN 'Superior' THEN 95 WHEN 'Good' THEN 85 WHEN 'Adequate' THEN 75 WHEN 'Not Adequate' THEN 65 WHEN 'Poor' THEN 55 else 0 END)+
(CASE walls WHEN 'Superior' THEN 95 WHEN 'Good' THEN 85 WHEN 'Adequate' THEN 75 WHEN 'Not Adequate' THEN 65 WHEN 'Poor' THEN 55 else 0 END)+
(CASE ceilings WHEN 'Superior' THEN 95 WHEN 'Good' THEN 85 WHEN 'Adequate' THEN 75 WHEN 'Not Adequate' THEN 65 WHEN 'Poor' THEN 55 else 0 END)+
(CASE ac WHEN 'Superior' THEN 95 WHEN 'Good' THEN 85 WHEN 'Adequate' THEN 75 WHEN 'Not Adequate' THEN 65 WHEN 'Poor' THEN 55 else 0 END)+
(CASE chilled_water_distribution WHEN 'Superior' THEN 95 WHEN 'Good' THEN 85 WHEN 'Adequate' THEN 75 WHEN 'Not Adequate' THEN 65 WHEN 'Poor' THEN 55 else 0 END)+
(CASE verticalconveyance WHEN 'Superior' THEN 95 WHEN 'Good' THEN 85 WHEN 'Adequate' THEN 75 WHEN 'Not Adequate' THEN 65 WHEN 'Poor' THEN 55 else 0 END)+
(CASE site_utilities WHEN 'Superior' THEN 190 WHEN 'Good' THEN 170 WHEN 'Adequate' THEN 150 WHEN 'Not Adequate' THEN 130 WHEN 'Poor' THEN 110 else 0 end)+
(CASE gutters WHEN 'Superior' THEN 190 WHEN 'Good' THEN 170 WHEN 'Adequate' THEN 150 WHEN 'Not Adequate' THEN 130 WHEN 'Poor' THEN 110 else 0 end)+
(CASE windows WHEN 'Superior' THEN 190 WHEN 'Good' THEN 170 WHEN 'Adequate' THEN 150 WHEN 'Not Adequate' THEN 130 WHEN 'Poor' THEN 110 else 0 end)+
(CASE flashing WHEN 'Superior' THEN 190 WHEN 'Good' THEN 170 WHEN 'Adequate' THEN 150 WHEN 'Not Adequate' THEN 130 WHEN 'Poor' THEN 110 else 0 end)+
(CASE roof_drains WHEN 'Superior' THEN 190 WHEN 'Good' THEN 170 WHEN 'Adequate' THEN 150 WHEN 'Not Adequate' THEN 130 WHEN 'Poor' THEN 110 else 0 end)+
(CASE rooftop_equip WHEN 'Superior' THEN 190 WHEN 'Good' THEN 170 WHEN 'Adequate' THEN 150 WHEN 'Not Adequate' THEN 130 WHEN 'Poor' THEN 110 else 0 end)+
(CASE skylights WHEN 'Superior' THEN 190 WHEN 'Good' THEN 170 WHEN 'Adequate' THEN 150 WHEN 'Not Adequate' THEN 130 WHEN 'Poor' THEN 110 else 0 end)+
(CASE interior_appearance WHEN 'Superior' THEN 190 WHEN 'Good' THEN 170 WHEN 'Adequate' THEN 150 WHEN 'Not Adequate' THEN 130 WHEN 'Poor' THEN 110 else 0 end)+
(CASE floors WHEN 'Superior' THEN 190 WHEN 'Good' THEN 170 WHEN 'Adequate' THEN 150 WHEN 'Not Adequate' THEN 130 WHEN 'Poor' THEN 110 else 0 end)+
(CASE interior_doors WHEN 'Superior' THEN 190 WHEN 'Good' THEN 170 WHEN 'Adequate' THEN 150 WHEN 'Not Adequate' THEN 130 WHEN 'Poor' THEN 110 else 0 end)+
(CASE lighting WHEN 'Superior' THEN 190 WHEN 'Good' THEN 170 WHEN 'Adequate' THEN 150 WHEN 'Not Adequate' THEN 130 WHEN 'Poor' THEN 110 else 0 end)+
(CASE equipment_rooms WHEN 'Superior' THEN 190 WHEN 'Good' THEN 170 WHEN 'Adequate' THEN 150 WHEN 'Not Adequate' THEN 130 WHEN 'Poor' THEN 110 else 0 end)+
(CASE fcu_radiators WHEN 'Superior' THEN 190 WHEN 'Good' THEN 170 WHEN 'Adequate' THEN 150 WHEN 'Not Adequate' THEN 130 WHEN 'Poor' THEN 110 else 0 end)+
(CASE steam_distribution WHEN 'Superior' THEN 190 WHEN 'Good' THEN 170 WHEN 'Adequate' THEN 150 WHEN 'Not Adequate' THEN 130 WHEN 'Poor' THEN 110 else 0 end)+
(CASE hot_water_distribution WHEN 'Superior' THEN 190 WHEN 'Good' THEN 170 WHEN 'Adequate' THEN 150 WHEN 'Not Adequate' THEN 130 WHEN 'Poor' THEN 110 else 0 end)+
(CASE exterior_structural WHEN 'Superior' THEN 285 WHEN 'Good' THEN 255 WHEN 'Adequate' THEN 225 WHEN 'Not Adequate' THEN 195 WHEN 'Poor' THEN 165 else 0 end)+
(CASE entryways WHEN 'Superior' THEN 285 WHEN 'Good' THEN 255 WHEN 'Adequate' THEN 225 WHEN 'Not Adequate' THEN 195 WHEN 'Poor' THEN 165 else 0 end)+
(CASE roof WHEN 'Superior' THEN 285 WHEN 'Good' THEN 255 WHEN 'Adequate' THEN 225 WHEN 'Not Adequate' THEN 195 WHEN 'Poor' THEN 165 else 0 end)+
(CASE electrical_distribution WHEN 'Superior' THEN 285 WHEN 'Good' THEN 255 WHEN 'Adequate' THEN 225 WHEN 'Not Adequate' THEN 195 WHEN 'Poor' THEN 165 else 0 end)+
(CASE electrical_service WHEN 'Superior' THEN 285 WHEN 'Good' THEN 255 WHEN 'Adequate' THEN 225 WHEN 'Not Adequate' THEN 195 WHEN 'Poor' THEN 165 else 0 end)+
(CASE fire_safety WHEN 'Superior' THEN 285 WHEN 'Good' THEN 255 WHEN 'Adequate' THEN 225 WHEN 'Not Adequate' THEN 195 WHEN 'Poor' THEN 165 else 0 end)+
(CASE boilers WHEN 'Superior' THEN 285 WHEN 'Good' THEN 255 WHEN 'Adequate' THEN 225 WHEN 'Not Adequate' THEN 195 WHEN 'Poor' THEN 165 else 0 end)+
(CASE ventilation WHEN 'Superior' THEN 285 WHEN 'Good' THEN 255 WHEN 'Adequate' THEN 225 WHEN 'Not Adequate' THEN 195 WHEN 'Poor' THEN 165 else 0 end)+
(CASE plumbing WHEN 'Superior' THEN 285 WHEN 'Good' THEN 255 WHEN 'Adequate' THEN 225 WHEN 'Not Adequate' THEN 195 WHEN 'Poor' THEN 165 else 0 end)+
(CASE int_sub_struct WHEN 'Superior' THEN 285 WHEN 'Good' THEN 255 WHEN 'Adequate' THEN 225 WHEN 'Not Adequate' THEN 195 WHEN 'Poor' THEN 165 else 0 end)
)
)
)
/
(70-
sum
(
(case roadways when 'Not Applicable' then 1 else 0 end) + (case site_appearance when 'Not Applicable' then 1 else 0 end)
+(case exterior when 'Not Applicable' then 1 else 0 end) + (case playground when 'Not Applicable' then 1 else 0 end)
+(case sidewalks when 'Not Applicable' then 1 else 0 end)+(case walls when 'Not Applicable' then 1 else 0 end)
+(case ceilings when 'Not Applicable' then 1 else 0 end)+(case ac when 'Not Applicable' then 1 else 0 end)
+(case chilled_water_distribution when 'Not Applicable' then 1 else 0 end)+(case verticalconveyance when 'Not Applicable' then 1 else 0 end)
+(case site_utilities when 'Not Applicable' then 2 else 0 end)+(case gutters when 'Not Applicable' then 2 else 0 end)
+(case windows when 'Not Applicable' then 2 else 0 end)+(case flashing when 'Not Applicable' then 2 else 0 end)
+(case roof_drains when 'Not Applicable' then 2 else 0 end)+(case rooftop_equip when 'Not Applicable' then 2 else 0 end)
+(case skylights when 'Not Applicable' then 2 else 0 end)+(case interior_appearance when 'Not Applicable' then 2 else 0 end)
+(case floors when 'Not Applicable' then 2 else 0 end)+(case interior_doors when 'Not Applicable' then 2 else 0 end)
+(case lighting when 'Not Applicable' then 2 else 0 end)+(case equipment_rooms when 'Not Applicable' then 2 else 0 end)
+(case fcu_radiators when 'Not Applicable' then 2 else 0 end)+(case steam_distribution when 'Not Applicable' then 2 else 0 end)
+(case hot_water_distribution when 'Not Applicable' then 2 else 0 end)+(case exterior_structural when 'Not Applicable' then 3 else 0 end)
+(case entryways when 'Not Applicable' then 3 else 0 end)+(case roof when 'Not Applicable' then 3 else 0 end)
+(case electrical_distribution when 'Not Applicable' then 3 else 0 end)+(case electrical_service when 'Not Applicable' then 3 else 0 end)
+(case fire_safety when 'Not Applicable' then 3 else 0 end)+(case boilers when 'Not Applicable' then 3 else 0 end)
+(case ventilation when 'Not Applicable' then 3 else 0 end)+(case plumbing when 'Not Applicable' then 3 else 0 end)
+(case int_sub_struct when 'Not Applicable' then 3 else 0 end)
)
)
+5
)
as [Overall Rating]
from maintenance_survey_results m,FACILITY f
where m.PSC_Number=f.PSC_NUMBER)
0
 

Author Comment

by:mounty95
ID: 34889656
Where would I insert the case to get the wording?
0
 
LVL 39

Expert Comment

by:lcohan
ID: 34889882
So, here is the SELECT statement but if you need to perform an update then obviously you will need the TARGET column for your RATING and should be easy for you to write the update statement.

....
select

(
convert (decimal,
(sum
(
(CASE roadways WHEN 'Superior' THEN 95 WHEN 'Good' THEN 85 WHEN 'Adequate' THEN 75 WHEN 'Not Adequate' THEN 65 WHEN 'Poor' THEN '55' else 0 END)+
(CASE site_appearance WHEN 'Superior' THEN 95 WHEN 'Good' THEN 85 WHEN 'Adequate' THEN 75 WHEN 'Not Adequate' THEN 65 WHEN 'Poor' THEN 55 else 0 END)+
(CASE exterior WHEN 'Superior' THEN 95 WHEN 'Good' THEN 85 WHEN 'Adequate' THEN 75 WHEN 'Not Adequate' THEN 65 WHEN 'Poor' THEN 55 else 0 END)+
(CASE playground WHEN 'Superior' THEN 95 WHEN 'Good' THEN 85 WHEN 'Adequate' THEN 75 WHEN 'Not Adequate' THEN 65 WHEN 'Poor' THEN 55 else 0 END)+
(CASE sidewalks WHEN 'Superior' THEN 95 WHEN 'Good' THEN 85 WHEN 'Adequate' THEN 75 WHEN 'Not Adequate' THEN 65 WHEN 'Poor' THEN 55 else 0 END)+
(CASE walls WHEN 'Superior' THEN 95 WHEN 'Good' THEN 85 WHEN 'Adequate' THEN 75 WHEN 'Not Adequate' THEN 65 WHEN 'Poor' THEN 55 else 0 END)+
(CASE ceilings WHEN 'Superior' THEN 95 WHEN 'Good' THEN 85 WHEN 'Adequate' THEN 75 WHEN 'Not Adequate' THEN 65 WHEN 'Poor' THEN 55 else 0 END)+
(CASE ac WHEN 'Superior' THEN 95 WHEN 'Good' THEN 85 WHEN 'Adequate' THEN 75 WHEN 'Not Adequate' THEN 65 WHEN 'Poor' THEN 55 else 0 END)+
(CASE chilled_water_distribution WHEN 'Superior' THEN 95 WHEN 'Good' THEN 85 WHEN 'Adequate' THEN 75 WHEN 'Not Adequate' THEN 65 WHEN 'Poor' THEN 55 else 0 END)+
(CASE verticalconveyance WHEN 'Superior' THEN 95 WHEN 'Good' THEN 85 WHEN 'Adequate' THEN 75 WHEN 'Not Adequate' THEN 65 WHEN 'Poor' THEN 55 else 0 END)+
(CASE site_utilities WHEN 'Superior' THEN 190 WHEN 'Good' THEN 170 WHEN 'Adequate' THEN 150 WHEN 'Not Adequate' THEN 130 WHEN 'Poor' THEN 110 else 0 end)+
(CASE gutters WHEN 'Superior' THEN 190 WHEN 'Good' THEN 170 WHEN 'Adequate' THEN 150 WHEN 'Not Adequate' THEN 130 WHEN 'Poor' THEN 110 else 0 end)+
(CASE windows WHEN 'Superior' THEN 190 WHEN 'Good' THEN 170 WHEN 'Adequate' THEN 150 WHEN 'Not Adequate' THEN 130 WHEN 'Poor' THEN 110 else 0 end)+
(CASE flashing WHEN 'Superior' THEN 190 WHEN 'Good' THEN 170 WHEN 'Adequate' THEN 150 WHEN 'Not Adequate' THEN 130 WHEN 'Poor' THEN 110 else 0 end)+
(CASE roof_drains WHEN 'Superior' THEN 190 WHEN 'Good' THEN 170 WHEN 'Adequate' THEN 150 WHEN 'Not Adequate' THEN 130 WHEN 'Poor' THEN 110 else 0 end)+
(CASE rooftop_equip WHEN 'Superior' THEN 190 WHEN 'Good' THEN 170 WHEN 'Adequate' THEN 150 WHEN 'Not Adequate' THEN 130 WHEN 'Poor' THEN 110 else 0 end)+
(CASE skylights WHEN 'Superior' THEN 190 WHEN 'Good' THEN 170 WHEN 'Adequate' THEN 150 WHEN 'Not Adequate' THEN 130 WHEN 'Poor' THEN 110 else 0 end)+
(CASE interior_appearance WHEN 'Superior' THEN 190 WHEN 'Good' THEN 170 WHEN 'Adequate' THEN 150 WHEN 'Not Adequate' THEN 130 WHEN 'Poor' THEN 110 else 0 end)+
(CASE floors WHEN 'Superior' THEN 190 WHEN 'Good' THEN 170 WHEN 'Adequate' THEN 150 WHEN 'Not Adequate' THEN 130 WHEN 'Poor' THEN 110 else 0 end)+
(CASE interior_doors WHEN 'Superior' THEN 190 WHEN 'Good' THEN 170 WHEN 'Adequate' THEN 150 WHEN 'Not Adequate' THEN 130 WHEN 'Poor' THEN 110 else 0 end)+
(CASE lighting WHEN 'Superior' THEN 190 WHEN 'Good' THEN 170 WHEN 'Adequate' THEN 150 WHEN 'Not Adequate' THEN 130 WHEN 'Poor' THEN 110 else 0 end)+
(CASE equipment_rooms WHEN 'Superior' THEN 190 WHEN 'Good' THEN 170 WHEN 'Adequate' THEN 150 WHEN 'Not Adequate' THEN 130 WHEN 'Poor' THEN 110 else 0 end)+
(CASE fcu_radiators WHEN 'Superior' THEN 190 WHEN 'Good' THEN 170 WHEN 'Adequate' THEN 150 WHEN 'Not Adequate' THEN 130 WHEN 'Poor' THEN 110 else 0 end)+
(CASE steam_distribution WHEN 'Superior' THEN 190 WHEN 'Good' THEN 170 WHEN 'Adequate' THEN 150 WHEN 'Not Adequate' THEN 130 WHEN 'Poor' THEN 110 else 0 end)+
(CASE hot_water_distribution WHEN 'Superior' THEN 190 WHEN 'Good' THEN 170 WHEN 'Adequate' THEN 150 WHEN 'Not Adequate' THEN 130 WHEN 'Poor' THEN 110 else 0 end)+
(CASE exterior_structural WHEN 'Superior' THEN 285 WHEN 'Good' THEN 255 WHEN 'Adequate' THEN 225 WHEN 'Not Adequate' THEN 195 WHEN 'Poor' THEN 165 else 0 end)+
(CASE entryways WHEN 'Superior' THEN 285 WHEN 'Good' THEN 255 WHEN 'Adequate' THEN 225 WHEN 'Not Adequate' THEN 195 WHEN 'Poor' THEN 165 else 0 end)+
(CASE roof WHEN 'Superior' THEN 285 WHEN 'Good' THEN 255 WHEN 'Adequate' THEN 225 WHEN 'Not Adequate' THEN 195 WHEN 'Poor' THEN 165 else 0 end)+
(CASE electrical_distribution WHEN 'Superior' THEN 285 WHEN 'Good' THEN 255 WHEN 'Adequate' THEN 225 WHEN 'Not Adequate' THEN 195 WHEN 'Poor' THEN 165 else 0 end)+
(CASE electrical_service WHEN 'Superior' THEN 285 WHEN 'Good' THEN 255 WHEN 'Adequate' THEN 225 WHEN 'Not Adequate' THEN 195 WHEN 'Poor' THEN 165 else 0 end)+
(CASE fire_safety WHEN 'Superior' THEN 285 WHEN 'Good' THEN 255 WHEN 'Adequate' THEN 225 WHEN 'Not Adequate' THEN 195 WHEN 'Poor' THEN 165 else 0 end)+
(CASE boilers WHEN 'Superior' THEN 285 WHEN 'Good' THEN 255 WHEN 'Adequate' THEN 225 WHEN 'Not Adequate' THEN 195 WHEN 'Poor' THEN 165 else 0 end)+
(CASE ventilation WHEN 'Superior' THEN 285 WHEN 'Good' THEN 255 WHEN 'Adequate' THEN 225 WHEN 'Not Adequate' THEN 195 WHEN 'Poor' THEN 165 else 0 end)+
(CASE plumbing WHEN 'Superior' THEN 285 WHEN 'Good' THEN 255 WHEN 'Adequate' THEN 225 WHEN 'Not Adequate' THEN 195 WHEN 'Poor' THEN 165 else 0 end)+
(CASE int_sub_struct WHEN 'Superior' THEN 285 WHEN 'Good' THEN 255 WHEN 'Adequate' THEN 225 WHEN 'Not Adequate' THEN 195 WHEN 'Poor' THEN 165 else 0 end)
)
)
)
/
(70-
sum
(
(case roadways when 'Not Applicable' then 1 else 0 end) + (case site_appearance when 'Not Applicable' then 1 else 0 end)
+(case exterior when 'Not Applicable' then 1 else 0 end) + (case playground when 'Not Applicable' then 1 else 0 end)
+(case sidewalks when 'Not Applicable' then 1 else 0 end)+(case walls when 'Not Applicable' then 1 else 0 end)
+(case ceilings when 'Not Applicable' then 1 else 0 end)+(case ac when 'Not Applicable' then 1 else 0 end)
+(case chilled_water_distribution when 'Not Applicable' then 1 else 0 end)+(case verticalconveyance when 'Not Applicable' then 1 else 0 end)
+(case site_utilities when 'Not Applicable' then 2 else 0 end)+(case gutters when 'Not Applicable' then 2 else 0 end)
+(case windows when 'Not Applicable' then 2 else 0 end)+(case flashing when 'Not Applicable' then 2 else 0 end)
+(case roof_drains when 'Not Applicable' then 2 else 0 end)+(case rooftop_equip when 'Not Applicable' then 2 else 0 end)
+(case skylights when 'Not Applicable' then 2 else 0 end)+(case interior_appearance when 'Not Applicable' then 2 else 0 end)
+(case floors when 'Not Applicable' then 2 else 0 end)+(case interior_doors when 'Not Applicable' then 2 else 0 end)
+(case lighting when 'Not Applicable' then 2 else 0 end)+(case equipment_rooms when 'Not Applicable' then 2 else 0 end)
+(case fcu_radiators when 'Not Applicable' then 2 else 0 end)+(case steam_distribution when 'Not Applicable' then 2 else 0 end)
+(case hot_water_distribution when 'Not Applicable' then 2 else 0 end)+(case exterior_structural when 'Not Applicable' then 3 else 0 end)
+(case entryways when 'Not Applicable' then 3 else 0 end)+(case roof when 'Not Applicable' then 3 else 0 end)
+(case electrical_distribution when 'Not Applicable' then 3 else 0 end)+(case electrical_service when 'Not Applicable' then 3 else 0 end)
+(case fire_safety when 'Not Applicable' then 3 else 0 end)+(case boilers when 'Not Applicable' then 3 else 0 end)
+(case ventilation when 'Not Applicable' then 3 else 0 end)+(case plumbing when 'Not Applicable' then 3 else 0 end)
+(case int_sub_struct when 'Not Applicable' then 3 else 0 end)
)
)
+5
)
as [Overall Rating],

CASE WHEN [Overall Rating] = 10 THEN  'Superior'
            WHEN [Overall Rating]  < 10 AND [Overall Rating] >= 8 THEN  'Good'
            WHEN [Overall Rating]  < 8 AND [Overall Rating] >=6 THEN  'Adequate'
            WHEN [Overall Rating]  <  6 AND [Overall Rating] >=4 THEN  'Not Adequate'
            WHEN [Overall Rating]  < 4 THEN  'Poor'  ELSE 'NotRated' END as RatingColumn

from maintenance_survey_results m,FACILITY f
where m.PSC_Number=f.PSC_NUMBER
0
 

Author Comment

by:mounty95
ID: 34890140
I get an Invalid column name 'Overall Rating' for each line at the bottom and then
Only one expression can be specified in the select list when the subquery is not introduced with Exists.

Not sure what to do with these.
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 40

Expert Comment

by:Sharath
ID: 34890323
Can you just run this and post the result.
SELECT (CONVERT(DECIMAL,(SUM((CASE roadways 
                                WHEN 'Superior' THEN 95 
                                WHEN 'Good' THEN 85 
                                WHEN 'Adequate' THEN 75 
                                WHEN 'Not Adequate' THEN 65 
                                WHEN 'Poor' THEN '55' 
                                ELSE 0 
                              END) + (CASE site_appearance 
                                        WHEN 'Superior' THEN 95 
                                        WHEN 'Good' THEN 85 
                                        WHEN 'Adequate' THEN 75 
                                        WHEN 'Not Adequate' THEN 65 
                                        WHEN 'Poor' THEN 55 
                                        ELSE 0 
                                      END) + (CASE exterior 
                                                WHEN 'Superior' THEN 95 
                                                WHEN 'Good' THEN 85 
                                                WHEN 'Adequate' THEN 75 
                                                WHEN 'Not Adequate' THEN 65 
                                                WHEN 'Poor' THEN 55 
                                                ELSE 0 
                                              END) + (CASE playground 
                                                        WHEN 'Superior' THEN 95 
                                                        WHEN 'Good' THEN 85 
                                                        WHEN 'Adequate' THEN 75 
                                                        WHEN 'Not Adequate' THEN 65 
                                                        WHEN 'Poor' THEN 55 
                                                        ELSE 0 
                                                      END) + (CASE sidewalks 
                                                                WHEN 'Superior' THEN 95 
                                                                WHEN 'Good' THEN 85 
                                                                WHEN 'Adequate' THEN 75 
                                                                WHEN 'Not Adequate' THEN 65 
                                                                WHEN 'Poor' THEN 55 
                                                                ELSE 0 
                                                              END) + (CASE walls 
                                                                        WHEN 'Superior' THEN 95
                                                                        WHEN 'Good' THEN 85 
                                                                        WHEN 'Adequate' THEN 75
                                                                        WHEN 'Not Adequate' THEN 65
                                                                        WHEN 'Poor' THEN 55 
                                                                        ELSE 0 
                                                                      END) + (CASE ceilings 
                                                                                WHEN 'Superior' THEN 95
                                                                                WHEN 'Good' THEN 85
                                                                                WHEN 'Adequate' THEN 75
                                                                                WHEN 'Not Adequate' THEN 65
                                                                                WHEN 'Poor' THEN 55
                                                                                ELSE 0 
                                                                              END) + (CASE ac
                                                                                        WHEN 'Superior' THEN 95
                                                                                        WHEN 'Good' THEN 85
                                                                                        WHEN 'Adequate' THEN 75
                                                                                        WHEN 'Not Adequate' THEN 65
                                                                                        WHEN 'Poor' THEN 55
                                                                                        ELSE 0
                                                                                      END) + (CASE chilled_water_distribution
                                                                                                WHEN 'Superior' THEN 95
                                                                                                WHEN 'Good' THEN 85
                                                                                                WHEN 'Adequate' THEN 75
                                                                                                WHEN 'Not Adequate' THEN 65
                                                                                                WHEN 'Poor' THEN 55
                                                                                                ELSE 0
                                                                                              END) + (CASE verticalconveyance
                                                                                                        WHEN 'Superior' THEN 95
                                                                                                        WHEN 'Good' THEN 85
                                                                                                        WHEN 'Adequate' THEN 75
                                                                                                        WHEN 'Not Adequate' THEN 65
                                                                                                        WHEN 'Poor' THEN 55
                                                                                                        ELSE 0
                                                                                                      END) + (CASE site_utilities
                                                                                                                WHEN 'Superior' THEN 190
                                                                                                                WHEN 'Good' THEN 170
                                                                                                                WHEN 'Adequate' THEN 150
                                                                                                                WHEN 'Not Adequate' THEN 130
                                                                                                                WHEN 'Poor' THEN 110
                                                                                                                ELSE 0
                                                                                                              END) + (CASE gutters
                                                                                                                        WHEN 'Superior' THEN 190
                                                                                                                        WHEN 'Good' THEN 170
                                                                                                                        WHEN 'Adequate' THEN 150
                                                                                                                        WHEN 'Not Adequate' THEN 130
                                                                                                                        WHEN 'Poor' THEN 110
                                                                                                                        ELSE 0
                                                                                                                      END) + (CASE windows
                                                                                                                                WHEN 'Superior' THEN 190
                                                                                                                                WHEN 'Good' THEN 170
                                                                                                                                WHEN 'Adequate' THEN 150
                                                                                                                                WHEN 'Not Adequate' THEN 130
                                                                                                                                WHEN 'Poor' THEN 110
                                                                                                                                ELSE 0
                                                                                                                              END) + (CASE flashing
                                                                                                                                        WHEN 'Superior' THEN 190
                                                                                                                                        WHEN 'Good' THEN 170
                                                                                                                                        WHEN 'Adequate' THEN 150
                                                                                                                                        WHEN 'Not Adequate' THEN 130
                                                                                                                                        WHEN 'Poor' THEN 110
                                                                                                                                        ELSE 0
                                                                                                                                      END) + (CASE roof_drains
                                                                                                                                                WHEN 'Superior' THEN 190
                                                                                                                                                WHEN 'Good' THEN 170
                                                                                                                                                WHEN 'Adequate' THEN 150
                                                                                                                                                WHEN 'Not Adequate' THEN 130
                                                                                                                                                WHEN 'Poor' THEN 110
                                                                                                                                                ELSE 0
                                                                                                                                              END) + (CASE rooftop_equip
                                                                                                                                                        WHEN 'Superior' THEN 190
                                                                                                                                                        WHEN 'Good' THEN 170
                                                                                                                                                        WHEN 'Adequate' THEN 150
                                                                                                                                                        WHEN 'Not Adequate' THEN 130
                                                                                                                                                        WHEN 'Poor' THEN 110
                                                                                                                                                        ELSE 0
                                                                                                                                                      END) + (CASE skylights
                                                                                                                                                                WHEN 'Superior' THEN 190
                                                                                                                                                                WHEN 'Good' THEN 170
                                                                                                                                                                WHEN 'Adequate' THEN 150
                                                                                                                                                                WHEN 'Not Adequate' THEN 130
                                                                                                                                                                WHEN 'Poor' THEN 110
                                                                                                                                                                ELSE 0
                                                                                                                                                              END) + (CASE interior_appearance
                                                                                                                                                                        WHEN 'Superior' THEN 190
                                                                                                                                                                        WHEN 'Good' THEN 170
                                                                                                                                                                        WHEN 'Adequate' THEN 150
                                                                                                                                                                        WHEN 'Not Adequate' THEN 130
                                                                                                                                                                        WHEN 'Poor' THEN 110
                                                                                                                                                                        ELSE 0
                                                                                                                                                                      END) + (CASE floors
                                                                                                                                                                                WHEN 'Superior' THEN 190
                                                                                                                                                                                WHEN 'Good' THEN 170
                                                                                                                                                                                WHEN 'Adequate' THEN 150
                                                                                                                                                                                WHEN 'Not Adequate' THEN 130
                                                                                                                                                                                WHEN 'Poor' THEN 110
                                                                                                                                                                                ELSE 0
                                                                                                                                                                              END) + (CASE interior_doors
                                                                                                                                                                                        WHEN 'Superior' THEN 190
                                                                                                                                                                                        WHEN 'Good' THEN 170
                                                                                                                                                                                        WHEN 'Adequate' THEN 150
                                                                                                                                                                                        WHEN 'Not Adequate' THEN 130
                                                                                                                                                                                        WHEN 'Poor' THEN 110
                                                                                                                                                                                        ELSE 0
                                                                                                                                                                                      END) + (CASE lighting
                                                                                                                                                                                                WHEN 'Superior' THEN 190
                                                                                                                                                                                                WHEN 'Good' THEN 170
                                                                                                                                                                                                WHEN 'Adequate' THEN 150
                                                                                                                                                                                                WHEN 'Not Adequate' THEN 130
                                                                                                                                                                                                WHEN 'Poor' THEN 110
                                                                                                                                                                                                ELSE 0
                                                                                                                                                                                              END) + (CASE equipment_rooms
                                                                                                                                                                                                        WHEN 'Superior' THEN 190
                                                                                                                                                                                                        WHEN 'Good' THEN 170
                                                                                                                                                                                                        WHEN 'Adequate' THEN 150
                                                                                                                                                                                                        WHEN 'Not Adequate' THEN 130
                                                                                                                                                                                                        WHEN 'Poor' THEN 110
                                                                                                                                                                                                        ELSE 0
                                                                                                                                                                                                      END) + (CASE fcu_radiators
                                                                                                                                                                                                                WHEN 'Superior' THEN 190
                                                                                                                                                                                                                WHEN 'Good' THEN 170
                                                                                                                                                                                                                WHEN 'Adequate' THEN 150
                                                                                                                                                                                                                WHEN 'Not Adequate' THEN 130
                                                                                                                                                                                                                WHEN 'Poor' THEN 110
                                                                                                                                                                                                                ELSE 0
                                                                                                                                                                                                              END) + (CASE steam_distribution
                                                                                                                                                                                                                        WHEN 'Superior' THEN 190
                                                                                                                                                                                                                        WHEN 'Good' THEN 170
                                                                                                                                                                                                                        WHEN 'Adequate' THEN 150
                                                                                                                                                                                                                        WHEN 'Not Adequate' THEN 130
                                                                                                                                                                                                                        WHEN 'Poor' THEN 110
                                                                                                                                                                                                                        ELSE 0
                                                                                                                                                                                                                      END) + (CASE hot_water_distribution
                                                                                                                                                                                                                                WHEN 'Superior' THEN 190
                                                                                                                                                                                                                                WHEN 'Good' THEN 170
                                                                                                                                                                                                                                WHEN 'Adequate' THEN 150
                                                                                                                                                                                                                                WHEN 'Not Adequate' THEN 130
                                                                                                                                                                                                                                WHEN 'Poor' THEN 110
                                                                                                                                                                                                                                ELSE 0
                                                                                                                                                                                                                              END) + (CASE exterior_structural
                                                                                                                                                                                                                                        WHEN 'Superior' THEN 285
                                                                                                                                                                                                                                        WHEN 'Good' THEN 255
                                                                                                                                                                                                                                        WHEN 'Adequate' THEN 225
                                                                                                                                                                                                                                        WHEN 'Not Adequate' THEN 195
                                                                                                                                                                                                                                        WHEN 'Poor' THEN 165
                                                                                                                                                                                                                                        ELSE 0
                                                                                                                                                                                                                                      END) + (CASE entryways
                                                                                                                                                                                                                                                WHEN 'Superior' THEN 285
                                                                                                                                                                                                                                                WHEN 'Good' THEN 255
                                                                                                                                                                                                                                                WHEN 'Adequate' THEN 225
                                                                                                                                                                                                                                                WHEN 'Not Adequate' THEN 195
                                                                                                                                                                                                                                                WHEN 'Poor' THEN 165
                                                                                                                                                                                                                                                ELSE 0
                                                                                                                                                                                                                                              END) + (CASE roof
                                                                                                                                                                                                                                                        WHEN 'Superior' THEN 285
                                                                                                                                                                                                                                                        WHEN 'Good' THEN 255
                                                                                                                                                                                                                                                        WHEN 'Adequate' THEN 225
                                                                                                                                                                                                                                                        WHEN 'Not Adequate' THEN 195
                                                                                                                                                                                                                                                        WHEN 'Poor' THEN 165
                                                                                                                                                                                                                                                        ELSE 0
                                                                                                                                                                                                                                                      END) + (CASE electrical_distribution
                                                                                                                                                                                                                                                                WHEN 'Superior' THEN 285
                                                                                                                                                                                                                                                                WHEN 'Good' THEN 255
                                                                                                                                                                                                                                                                WHEN 'Adequate' THEN 225
                                                                                                                                                                                                                                                                WHEN 'Not Adequate' THEN 195
                                                                                                                                                                                                                                                                WHEN 'Poor' THEN 165
                                                                                                                                                                                                                                                                ELSE 0
                                                                                                                                                                                                                                                              END) + (CASE electrical_service
                                                                                                                                                                                                                                                                        WHEN 'Superior' THEN 285
                                                                                                                                                                                                                                                                        WHEN 'Good' THEN 255
                                                                                                                                                                                                                                                                        WHEN 'Adequate' THEN 225
                                                                                                                                                                                                                                                                        WHEN 'Not Adequate' THEN 195
                                                                                                                                                                                                                                                                        WHEN 'Poor' THEN 165
                                                                                                                                                                                                                                                                        ELSE 0
                                                                                                                                                                                                                                                                      END) + (CASE fire_safety
                                                                                                                                                                                                                                                                                WHEN 'Superior' THEN 285
                                                                                                                                                                                                                                                                                WHEN 'Good' THEN 255
                                                                                                                                                                                                                                                                                WHEN 'Adequate' THEN 225
                                                                                                                                                                                                                                                                                WHEN 'Not Adequate' THEN 195
                                                                                                                                                                                                                                                                                WHEN 'Poor' THEN 165
                                                                                                                                                                                                                                                                                ELSE 0
                                                                                                                                                                                                                                                                              END) + (CASE boilers
                                                                                                                                                                                                                                                                                        WHEN 'Superior' THEN 285
                                                                                                                                                                                                                                                                                        WHEN 'Good' THEN 255
                                                                                                                                                                                                                                                                                        WHEN 'Adequate' THEN 225
                                                                                                                                                                                                                                                                                        WHEN 'Not Adequate' THEN 195
                                                                                                                                                                                                                                                                                        WHEN 'Poor' THEN 165
                                                                                                                                                                                                                                                                                        ELSE 0
                                                                                                                                                                                                                                                                                      END) + (CASE ventilation
                                                                                                                                                                                                                                                                                                WHEN 'Superior' THEN 285
                                                                                                                                                                                                                                                                                                WHEN 'Good' THEN 255
                                                                                                                                                                                                                                                                                                WHEN 'Adequate' THEN 225
                                                                                                                                                                                                                                                                                                WHEN 'Not Adequate' THEN 195
                                                                                                                                                                                                                                                                                                WHEN 'Poor' THEN 165
                                                                                                                                                                                                                                                                                                ELSE 0
                                                                                                                                                                                                                                                                                              END) + (CASE plumbing
                                                                                                                                                                                                                                                                                                        WHEN 'Superior' THEN 285
                                                                                                                                                                                                                                                                                                        WHEN 'Good' THEN 255
                                                                                                                                                                                                                                                                                                        WHEN 'Adequate' THEN 225
                                                                                                                                                                                                                                                                                                        WHEN 'Not Adequate' THEN 195
                                                                                                                                                                                                                                                                                                        WHEN 'Poor' THEN 165
                                                                                                                                                                                                                                                                                                        ELSE 0
                                                                                                                                                                                                                                                                                                      END) + (CASE int_sub_struct
                                                                                                                                                                                                                                                                                                                WHEN 'Superior' THEN 285
                                                                                                                                                                                                                                                                                                                WHEN 'Good' THEN 255
                                                                                                                                                                                                                                                                                                                WHEN 'Adequate' THEN 225
                                                                                                                                                                                                                                                                                                                WHEN 'Not Adequate' THEN 195
                                                                                                                                                                                                                                                                                                                WHEN 'Poor' THEN 165
                                                                                                                                                                                                                                                                                                                ELSE 0
                                                                                                                                                                                                                                                                                                              END)))) / (70 - SUM((CASE roadways
                                                                                                                                                                                                                                                                                                                                     WHEN 'Not Applicable' THEN 1
                                                                                                                                                                                                                                                                                                                                     ELSE 0
                                                                                                                                                                                                                                                                                                                                   END) + (CASE site_appearance
                                                                                                                                                                                                                                                                                                                                             WHEN 'Not Applicable' THEN 1
                                                                                                                                                                                                                                                                                                                                             ELSE 0
                                                                                                                                                                                                                                                                                                                                           END) + (CASE exterior
                                                                                                                                                                                                                                                                                                                                                     WHEN 'Not Applicable' THEN 1
                                                                                                                                                                                                                                                                                                                                                     ELSE 0
                                                                                                                                                                                                                                                                                                                                                   END) + (CASE playground
                                                                                                                                                                                                                                                                                                                                                             WHEN 'Not Applicable' THEN 1
                                                                                                                                                                                                                                                                                                                                                             ELSE 0
                                                                                                                                                                                                                                                                                                                                                           END) + (CASE sidewalks
                                                                                                                                                                                                                                                                                                                                                                     WHEN 'Not Applicable' THEN 1
                                                                                                                                                                                                                                                                                                                                                                     ELSE 0
                                                                                                                                                                                                                                                                                                                                                                   END) + (CASE walls
                                                                                                                                                                                                                                                                                                                                                                             WHEN 'Not Applicable' THEN 1
                                                                                                                                                                                                                                                                                                                                                                             ELSE 0
                                                                                                                                                                                                                                                                                                                                                                           END) + (CASE ceilings
                                                                                                                                                                                                                                                                                                                                                                                     WHEN 'Not Applicable' THEN 1
                                                                                                                                                                                                                                                                                                                                                                                     ELSE 0
                                                                                                                                                                                                                                                                                                                                                                                   END) + (CASE ac
                                                                                                                                                                                                                                                                                                                                                                                             WHEN 'Not Applicable' THEN 1
                                                                                                                                                                                                                                                                                                                                                                                             ELSE 0
                                                                                                                                                                                                                                                                                                                                                                                           END) + (CASE chilled_water_distribution
                                                                                                                                                                                                                                                                                                                                                                                                     WHEN 'Not Applicable' THEN 1
                                                                                                                                                                                                                                                                                                                                                                                                     ELSE 0
                                                                                                                                                                                                                                                                                                                                                                                                   END) + (CASE verticalconveyance
                                                                                                                                                                                                                                                                                                                                                                                                             WHEN 'Not Applicable' THEN 1
                                                                                                                                                                                                                                                                                                                                                                                                             ELSE 0
                                                                                                                                                                                                                                                                                                                                                                                                           END) + (CASE site_utilities
                                                                                                                                                                                                                                                                                                                                                                                                                     WHEN 'Not Applicable' THEN 2
                                                                                                                                                                                                                                                                                                                                                                                                                     ELSE 0
                                                                                                                                                                                                                                                                                                                                                                                                                   END) + (CASE gutters
                                                                                                                                                                                                                                                                                                                                                                                                                             WHEN 'Not Applicable' THEN 2
                                                                                                                                                                                                                                                                                                                                                                                                                             ELSE 0
                                                                                                                                                                                                                                                                                                                                                                                                                           END) + (CASE windows
                                                                                                                                                                                                                                                                                                                                                                                                                                     WHEN 'Not Applicable' THEN 2
                                                                                                                                                                                                                                                                                                                                                                                                                                     ELSE 0
                                                                                                                                                                                                                                                                                                                                                                                                                                   END) + (CASE flashing
                                                                                                                                                                                                                                                                                                                                                                                                                                             WHEN 'Not Applicable' THEN 2
                                                                                                                                                                                                                                                                                                                                                                                                                                             ELSE 0
                                                                                                                                                                                                                                                                                                                                                                                                                                           END) + (CASE roof_drains
                                                                                                                                                                                                                                                                                                                                                                                                                                                     WHEN 'Not Applicable' THEN 2
                                                                                                                                                                                                                                                                                                                                                                                                                                                     ELSE 0
                                                                                                                                                                                                                                                                                                                                                                                                                                                   END) + (CASE rooftop_equip
                                                                                                                                                                                                                                                                                                                                                                                                                                                             WHEN 'Not Applicable' THEN 2
                                                                                                                                                                                                                                                                                                                                                                                                                                                             ELSE 0
                                                                                                                                                                                                                                                                                                                                                                                                                                                           END) + (CASE skylights
                                                                                                                                                                                                                                                                                                                                                                                                                                                                     WHEN 'Not Applicable' THEN 2
                                                                                                                                                                                                                                                                                                                                                                                                                                                                     ELSE 0
                                                                                                                                                                                                                                                                                                                                                                                                                                                                   END) + (CASE interior_appearance
                                                                                                                                                                                                                                                                                                                                                                                                                                                                             WHEN 'Not Applicable' THEN 2
                                                                                                                                                                                                                                                                                                                                                                                                                                                                             ELSE 0
                                                                                                                                                                                                                                                                                                                                                                                                                                                                           END) + (CASE floors
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     WHEN 'Not Applicable' THEN 2
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     ELSE 0
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   END) + (CASE interior_doors
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                             WHEN 'Not Applicable' THEN 2
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                             ELSE 0
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           END) + (CASE lighting
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     WHEN 'Not Applicable' THEN 2
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     ELSE 0
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   END) + (CASE equipment_rooms
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                             WHEN 'Not Applicable' THEN 2
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                             ELSE 0
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           END) + (CASE fcu_radiators
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     WHEN 'Not Applicable' THEN 2
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     ELSE 0
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   END) + (CASE steam_distribution
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                             WHEN 'Not Applicable' THEN 2
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                             ELSE 0
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           END) + (CASE hot_water_distribution
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     WHEN 'Not Applicable' THEN 2
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     ELSE 0
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   END) + (CASE exterior_structural
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                             WHEN 'Not Applicable' THEN 3
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                             ELSE 0
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           END) + (CASE entryways
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     WHEN 'Not Applicable' THEN 3
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     ELSE 0
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   END) + (CASE roof
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                             WHEN 'Not Applicable' THEN 3
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                             ELSE 0
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           END) + (CASE electrical_distribution
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     WHEN 'Not Applicable' THEN 3
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     ELSE 0
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   END) + (CASE electrical_service
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                             WHEN 'Not Applicable' THEN 3
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                             ELSE 0
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           END) + (CASE fire_safety
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     WHEN 'Not Applicable' THEN 3
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     ELSE 0
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   END) + (CASE boilers
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                             WHEN 'Not Applicable' THEN 3
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                             ELSE 0
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           END) + (CASE ventilation
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     WHEN 'Not Applicable' THEN 3
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     ELSE 0
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   END) + (CASE plumbing
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                             WHEN 'Not Applicable' THEN 3
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                             ELSE 0
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           END) + (CASE int_sub_struct
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     WHEN 'Not Applicable' THEN 3
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     ELSE 0
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   END))) + 5) AS [Overall Rating]
  FROM maintenance_survey_results m 
       JOIN FACILITY f 
         ON m.PSC_Number = f.PSC_NUMBER

Open in new window

Do you want to update MAINTANCE_RATING column n FACILITY with this value for all th records?
0
 

Author Comment

by:mounty95
ID: 34890626
The script returns -680 which is what was updated into the Facility table for every record.

I have two tables:  Maintenance_survey_results and Facility.  The two tables are joined by the PSC_number field.  I need to update the MAINTANCE_RATING field in the Facility table for each record in the Maintenace_survey_results table and the join is not equal.  There are not equal (there are more records in the Facility table than in the Maintenace_survey_results table.
0
 
LVL 40

Expert Comment

by:Sharath
ID: 34891014
>> The script returns -680 which is what was updated into the Facility table for every record.

Is that what you are looking for?
0
 

Author Comment

by:mounty95
ID: 34891255
No each record in the Maintenance_survey_results should return a value between 0-100.  The records in the Maintenance_survey_results represent an inspection of a facility.  I want to update the maintnance_results field in the Facility table with the calculated result from the Maintenance_survey_results table.  There join is not equal as there are more facilities in the Facility table than there are in the Maintenance_survey_results table.
0
 

Author Comment

by:mounty95
ID: 34891357
Overall Rating is an alias for the calculation that is performed.  The errors that I receive on the Case must be because Overall Rating can not be evaluated, so where must I put the case syntax to make evaluate the whole calculation?
0
 
LVL 39

Expert Comment

by:lcohan
ID: 34891494
That's true - my bad.....hard to work on pseudo code sometimes. Thi happens because of the alias column so a statement like below fails


select 7 as [Overall Rating],
            CASE WHEN [Overall Rating] = 10 THEN  'Superior'
            WHEN [Overall Rating]  < 10 AND [Overall Rating] >= 8 THEN  'Good'
            WHEN [Overall Rating]  < 8 AND [Overall Rating] >=6 THEN  'Adequate'
            WHEN [Overall Rating]  <  6 AND [Overall Rating] >=4 THEN  'Not Adequate'
            WHEN [Overall Rating]  < 4 THEN  'Poor'  ELSE 'NotRated' END as RatingColumn


but think that you can put the big SELECT in a view then use that view to build the case like in example below it will workj 100%:


           
create view test_view as
select 7 as [Overall Rating]

Select [Overall Rating],
            CASE WHEN [Overall Rating] = 10 THEN  'Superior'
            WHEN [Overall Rating]  < 10 AND [Overall Rating] >= 8 THEN  'Good'
            WHEN [Overall Rating]  < 8 AND [Overall Rating] >=6 THEN  'Adequate'
            WHEN [Overall Rating]  <  6 AND [Overall Rating] >=4 THEN  'Not Adequate'
            WHEN [Overall Rating]  < 4 THEN  'Poor'  ELSE 'NotRated' END as RatingColumn
FROM test_view
0
 
LVL 39

Expert Comment

by:lcohan
ID: 34891512
More speciffic for your case:

CREATE VIEW test_view AS
select
(
convert (decimal,
(sum
(
(CASE roadways WHEN 'Superior' THEN 95 WHEN 'Good' THEN 85 WHEN 'Adequate' THEN 75 WHEN 'Not Adequate' THEN 65 WHEN 'Poor' THEN '55' else 0 END)+
(CASE site_appearance WHEN 'Superior' THEN 95 WHEN 'Good' THEN 85 WHEN 'Adequate' THEN 75 WHEN 'Not Adequate' THEN 65 WHEN 'Poor' THEN 55 else 0 END)+
(CASE exterior WHEN 'Superior' THEN 95 WHEN 'Good' THEN 85 WHEN 'Adequate' THEN 75 WHEN 'Not Adequate' THEN 65 WHEN 'Poor' THEN 55 else 0 END)+
(CASE playground WHEN 'Superior' THEN 95 WHEN 'Good' THEN 85 WHEN 'Adequate' THEN 75 WHEN 'Not Adequate' THEN 65 WHEN 'Poor' THEN 55 else 0 END)+
(CASE sidewalks WHEN 'Superior' THEN 95 WHEN 'Good' THEN 85 WHEN 'Adequate' THEN 75 WHEN 'Not Adequate' THEN 65 WHEN 'Poor' THEN 55 else 0 END)+
(CASE walls WHEN 'Superior' THEN 95 WHEN 'Good' THEN 85 WHEN 'Adequate' THEN 75 WHEN 'Not Adequate' THEN 65 WHEN 'Poor' THEN 55 else 0 END)+
(CASE ceilings WHEN 'Superior' THEN 95 WHEN 'Good' THEN 85 WHEN 'Adequate' THEN 75 WHEN 'Not Adequate' THEN 65 WHEN 'Poor' THEN 55 else 0 END)+
(CASE ac WHEN 'Superior' THEN 95 WHEN 'Good' THEN 85 WHEN 'Adequate' THEN 75 WHEN 'Not Adequate' THEN 65 WHEN 'Poor' THEN 55 else 0 END)+
(CASE chilled_water_distribution WHEN 'Superior' THEN 95 WHEN 'Good' THEN 85 WHEN 'Adequate' THEN 75 WHEN 'Not Adequate' THEN 65 WHEN 'Poor' THEN 55 else 0 END)+
(CASE verticalconveyance WHEN 'Superior' THEN 95 WHEN 'Good' THEN 85 WHEN 'Adequate' THEN 75 WHEN 'Not Adequate' THEN 65 WHEN 'Poor' THEN 55 else 0 END)+
(CASE site_utilities WHEN 'Superior' THEN 190 WHEN 'Good' THEN 170 WHEN 'Adequate' THEN 150 WHEN 'Not Adequate' THEN 130 WHEN 'Poor' THEN 110 else 0 end)+
(CASE gutters WHEN 'Superior' THEN 190 WHEN 'Good' THEN 170 WHEN 'Adequate' THEN 150 WHEN 'Not Adequate' THEN 130 WHEN 'Poor' THEN 110 else 0 end)+
(CASE windows WHEN 'Superior' THEN 190 WHEN 'Good' THEN 170 WHEN 'Adequate' THEN 150 WHEN 'Not Adequate' THEN 130 WHEN 'Poor' THEN 110 else 0 end)+
(CASE flashing WHEN 'Superior' THEN 190 WHEN 'Good' THEN 170 WHEN 'Adequate' THEN 150 WHEN 'Not Adequate' THEN 130 WHEN 'Poor' THEN 110 else 0 end)+
(CASE roof_drains WHEN 'Superior' THEN 190 WHEN 'Good' THEN 170 WHEN 'Adequate' THEN 150 WHEN 'Not Adequate' THEN 130 WHEN 'Poor' THEN 110 else 0 end)+
(CASE rooftop_equip WHEN 'Superior' THEN 190 WHEN 'Good' THEN 170 WHEN 'Adequate' THEN 150 WHEN 'Not Adequate' THEN 130 WHEN 'Poor' THEN 110 else 0 end)+
(CASE skylights WHEN 'Superior' THEN 190 WHEN 'Good' THEN 170 WHEN 'Adequate' THEN 150 WHEN 'Not Adequate' THEN 130 WHEN 'Poor' THEN 110 else 0 end)+
(CASE interior_appearance WHEN 'Superior' THEN 190 WHEN 'Good' THEN 170 WHEN 'Adequate' THEN 150 WHEN 'Not Adequate' THEN 130 WHEN 'Poor' THEN 110 else 0 end)+
(CASE floors WHEN 'Superior' THEN 190 WHEN 'Good' THEN 170 WHEN 'Adequate' THEN 150 WHEN 'Not Adequate' THEN 130 WHEN 'Poor' THEN 110 else 0 end)+
(CASE interior_doors WHEN 'Superior' THEN 190 WHEN 'Good' THEN 170 WHEN 'Adequate' THEN 150 WHEN 'Not Adequate' THEN 130 WHEN 'Poor' THEN 110 else 0 end)+
(CASE lighting WHEN 'Superior' THEN 190 WHEN 'Good' THEN 170 WHEN 'Adequate' THEN 150 WHEN 'Not Adequate' THEN 130 WHEN 'Poor' THEN 110 else 0 end)+
(CASE equipment_rooms WHEN 'Superior' THEN 190 WHEN 'Good' THEN 170 WHEN 'Adequate' THEN 150 WHEN 'Not Adequate' THEN 130 WHEN 'Poor' THEN 110 else 0 end)+
(CASE fcu_radiators WHEN 'Superior' THEN 190 WHEN 'Good' THEN 170 WHEN 'Adequate' THEN 150 WHEN 'Not Adequate' THEN 130 WHEN 'Poor' THEN 110 else 0 end)+
(CASE steam_distribution WHEN 'Superior' THEN 190 WHEN 'Good' THEN 170 WHEN 'Adequate' THEN 150 WHEN 'Not Adequate' THEN 130 WHEN 'Poor' THEN 110 else 0 end)+
(CASE hot_water_distribution WHEN 'Superior' THEN 190 WHEN 'Good' THEN 170 WHEN 'Adequate' THEN 150 WHEN 'Not Adequate' THEN 130 WHEN 'Poor' THEN 110 else 0 end)+
(CASE exterior_structural WHEN 'Superior' THEN 285 WHEN 'Good' THEN 255 WHEN 'Adequate' THEN 225 WHEN 'Not Adequate' THEN 195 WHEN 'Poor' THEN 165 else 0 end)+
(CASE entryways WHEN 'Superior' THEN 285 WHEN 'Good' THEN 255 WHEN 'Adequate' THEN 225 WHEN 'Not Adequate' THEN 195 WHEN 'Poor' THEN 165 else 0 end)+
(CASE roof WHEN 'Superior' THEN 285 WHEN 'Good' THEN 255 WHEN 'Adequate' THEN 225 WHEN 'Not Adequate' THEN 195 WHEN 'Poor' THEN 165 else 0 end)+
(CASE electrical_distribution WHEN 'Superior' THEN 285 WHEN 'Good' THEN 255 WHEN 'Adequate' THEN 225 WHEN 'Not Adequate' THEN 195 WHEN 'Poor' THEN 165 else 0 end)+
(CASE electrical_service WHEN 'Superior' THEN 285 WHEN 'Good' THEN 255 WHEN 'Adequate' THEN 225 WHEN 'Not Adequate' THEN 195 WHEN 'Poor' THEN 165 else 0 end)+
(CASE fire_safety WHEN 'Superior' THEN 285 WHEN 'Good' THEN 255 WHEN 'Adequate' THEN 225 WHEN 'Not Adequate' THEN 195 WHEN 'Poor' THEN 165 else 0 end)+
(CASE boilers WHEN 'Superior' THEN 285 WHEN 'Good' THEN 255 WHEN 'Adequate' THEN 225 WHEN 'Not Adequate' THEN 195 WHEN 'Poor' THEN 165 else 0 end)+
(CASE ventilation WHEN 'Superior' THEN 285 WHEN 'Good' THEN 255 WHEN 'Adequate' THEN 225 WHEN 'Not Adequate' THEN 195 WHEN 'Poor' THEN 165 else 0 end)+
(CASE plumbing WHEN 'Superior' THEN 285 WHEN 'Good' THEN 255 WHEN 'Adequate' THEN 225 WHEN 'Not Adequate' THEN 195 WHEN 'Poor' THEN 165 else 0 end)+
(CASE int_sub_struct WHEN 'Superior' THEN 285 WHEN 'Good' THEN 255 WHEN 'Adequate' THEN 225 WHEN 'Not Adequate' THEN 195 WHEN 'Poor' THEN 165 else 0 end)
)
)
)
/
(70-
sum
(
(case roadways when 'Not Applicable' then 1 else 0 end) + (case site_appearance when 'Not Applicable' then 1 else 0 end)
+(case exterior when 'Not Applicable' then 1 else 0 end) + (case playground when 'Not Applicable' then 1 else 0 end)
+(case sidewalks when 'Not Applicable' then 1 else 0 end)+(case walls when 'Not Applicable' then 1 else 0 end)
+(case ceilings when 'Not Applicable' then 1 else 0 end)+(case ac when 'Not Applicable' then 1 else 0 end)
+(case chilled_water_distribution when 'Not Applicable' then 1 else 0 end)+(case verticalconveyance when 'Not Applicable' then 1 else 0 end)
+(case site_utilities when 'Not Applicable' then 2 else 0 end)+(case gutters when 'Not Applicable' then 2 else 0 end)
+(case windows when 'Not Applicable' then 2 else 0 end)+(case flashing when 'Not Applicable' then 2 else 0 end)
+(case roof_drains when 'Not Applicable' then 2 else 0 end)+(case rooftop_equip when 'Not Applicable' then 2 else 0 end)
+(case skylights when 'Not Applicable' then 2 else 0 end)+(case interior_appearance when 'Not Applicable' then 2 else 0 end)
+(case floors when 'Not Applicable' then 2 else 0 end)+(case interior_doors when 'Not Applicable' then 2 else 0 end)
+(case lighting when 'Not Applicable' then 2 else 0 end)+(case equipment_rooms when 'Not Applicable' then 2 else 0 end)
+(case fcu_radiators when 'Not Applicable' then 2 else 0 end)+(case steam_distribution when 'Not Applicable' then 2 else 0 end)
+(case hot_water_distribution when 'Not Applicable' then 2 else 0 end)+(case exterior_structural when 'Not Applicable' then 3 else 0 end)
+(case entryways when 'Not Applicable' then 3 else 0 end)+(case roof when 'Not Applicable' then 3 else 0 end)
+(case electrical_distribution when 'Not Applicable' then 3 else 0 end)+(case electrical_service when 'Not Applicable' then 3 else 0 end)
+(case fire_safety when 'Not Applicable' then 3 else 0 end)+(case boilers when 'Not Applicable' then 3 else 0 end)
+(case ventilation when 'Not Applicable' then 3 else 0 end)+(case plumbing when 'Not Applicable' then 3 else 0 end)
+(case int_sub_struct when 'Not Applicable' then 3 else 0 end)
)
)
+5
)
as [Overall Rating]
from maintenance_survey_results m,FACILITY f
where m.PSC_Number=f.PSC_NUMBER
GO


SELECT [Overall Rating],
            CASE WHEN [Overall Rating] = 10 THEN  'Superior'
            WHEN [Overall Rating]  < 10 AND [Overall Rating] >= 8 THEN  'Good'
            WHEN [Overall Rating]  < 8 AND [Overall Rating] >=6 THEN  'Adequate'
            WHEN [Overall Rating]  <  6 AND [Overall Rating] >=4 THEN  'Not Adequate'
            WHEN [Overall Rating]  < 4 THEN  'Poor'  ELSE 'NotRated' END as RatingColumn
FROM test_view
GO
 
0
 

Author Comment

by:mounty95
ID: 34891686
My problem is that I am trying to do an update like:

update facility
set maintnance_result=

Do calculation

If calculation is between 100 and 95 then update with Superior
If calculation is between 95 and 85 then update with Good

where recordid from facility table = recordid from maintenance_survey_results
0
 
LVL 39

Accepted Solution

by:
lcohan earned 500 total points
ID: 34891772
Well you could have all the calculations done by the SQL VIEW and please add following column to your view:

CREATE VIEW test_view AS
select m.PSC_Number,
(
convert (decimal,.......................


Then build your update statement based on the view - something like:

UPDATE FACILITY SET MAINTANCE_RATING = test_view.[Overall Rating],
                              Rating = CASE WHEN [Overall Rating] = 10 THEN  'Superior'
                                                WHEN [Overall Rating]  < 10 AND [Overall Rating] >= 8 THEN  'Good'
                                                      WHEN [Overall Rating]  < 8 AND [Overall Rating] >=6 THEN  'Adequate'
                                                      WHEN [Overall Rating]  <  6 AND [Overall Rating] >=4 THEN  'Not Adequate'
                                                WHEN [Overall Rating]  < 4 THEN  'Poor'  ELSE 'NotRated' END
FROM test_view WHERE FACILITY.PSC_Number=test_view.PSC_NUMBER

0
 

Author Closing Comment

by:mounty95
ID: 35010784
Not quite what I wanted, but thank you for offering a solution.
0

Featured Post

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

Suggested Solutions

Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now