Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations IamaSherpa on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Average of columns 1

Status
Not open for further replies.
primagic

primagic

IS-IT--Management
Jul 24, 2008
476
GB
I have 17 columns in a table that are all decimal field type.

I have a computed column that calculates the average of these columns using:

Code: 
(((((((((((((((((isnull([Criteria1],(0))+isnull([Criteria2],(0)))+isnull([Criteria3],(0)))+isnull([Criteria4],(0)))+isnull([Criteria5],(0)))+isnull([Criteria6],(0)))+isnull([Criteria7],(0)))+isnull([Criteria8],(0)))+isnull([Criteria9],(0)))+isnull([Criteria10],(0)))+isnull([Criteria11],(0)))+isnull([Criteria12],(0)))+isnull([Criteria13],(0)))+isnull([Criteria14],(0)))+isnull([Criteria15],(0)))+isnull([Criteria16],(0)))+isnull([Criteria17],(0)))/(17))

This works fine but I need to ensure that the calculated figure is not greater than the lower of the last two criterias (16 & 17)

So if Criteria 16 was 2.6 And Criteria 17 was 2.3 then if the calculated amount is 2.4 then this is incorrect as its higher then the lower of the two criterias.

How do I ensure this is checked. I want it to work on insert of new records and update of the record.

Thanks
 
Sort by date Sort by votes
CASE WHEN x > y THEN X ELSE Y END

Lodlaiden

You've got questions and source code. We want both!
Oh? That? That's not an important password. - IT Security Admin (pw on whiteboard)
 
Upvote 0 Downvote
Thank you for the reply. Where would I use this code?
 
Upvote 0 Downvote
Honestly I'd add another calcuated column to assist with this, incase you need to "show your work" on a report.
The code I have here is pseudo code, but should get you close.

Add a new column with this as the calculation:
I'm going to call this BaseAverage, for the second code block

Code: 
ALTER dbo.X
ADD BaseAverage AS (
(isnull([Criteria1],0)+ isnull([Criteria2],0)+ isnull([Criteria3],0)+ isnull([Criteria4],0)+
isnull([Criteria5],0)+ isnull([Criteria6],0)+ isnull([Criteria7],0)+ isnull([Criteria8],0)+
isnull([Criteria9],0)+ isnull([Criteria10],0)+ isnull([Criteria11],0)+ isnull([Criteria12],0)+
isnull([Criteria13],0)+ isnull([Criteria14],0)+ isnull([Criteria15],0)+ isnull([Criteria16],0)+
isnull([Criteria17],0)) / 17)

I'm writing this next part assuming you need the lowest of the 3 values (BaseAverage, Crit16, Crit17)

Code: 
ALTER dbo.X
ALTER COLUMN [red]YourOriginalColumn[/red] AS (
CASE WHEN BaseAverage < isnull([Criteria16],0) && BaseAverage < isnull([Criteria17],0) 
THEN BaseAverage 
ELSE CASE WHEN isnull([Criteria16],0) > isnull([Criteria17],0) THEN isnull([Criteria16],0) ELSE isnull([Criteria17],0) END)

Lodlaiden

You've got questions and source code. We want both!
Oh? That? That's not an important password. - IT Security Admin (pw on whiteboard)
 
Upvote 0 Downvote
If I try any of the ALTER statements I get the following error:

Code: 
Unknown object type 'dbo' used in a CREATE, DROP, or ALTER statement.
 
Upvote 0 Downvote
Modified my code to read:

Code: 
USE smart
ALTER TABLE TeachingDetails DROP COLUMN SuggestedGrade 
 GO
 
 ALTER TABLE TeachingDetails ADD SuggestedGrade AS ( CASE WHEN BaseAverage < isnull([Criteria16],0) && BaseAverage < isnull([Criteria17],0) THEN BaseAverage ELSE CASE WHEN isnull([Criteria16],0) > isnull([Criteria17],0) THEN isnull([Criteria16],0) ELSE isnull([Criteria17],0) END)

Now my error is Incorrect syntax near '&'.

 
Upvote 0 Downvote
oh, sorry. mixing languages.

&& should be AND

Lod

You've got questions and source code. We want both!
Oh? That? That's not an important password. - IT Security Admin (pw on whiteboard)
 
Upvote 0 Downvote
Thanks for the reply.

Now I have:

Code: 
USE smart
GO
ALTER TABLE TeachingDetails ADD SuggestedGrade AS ( CASE WHEN BaseAverage < isnull([Criteria16],0) AND BaseAverage < isnull([Criteria17],0) THEN BaseAverage ELSE CASE WHEN isnull([Criteria16],0) > isnull([Criteria17],0) THEN isnull([Criteria16],0) ELSE isnull([Criteria17],0) END)

I get error Incorrect syntax near ')'. Line 1
 
Upvote 0 Downvote
ELSE isnull([Criteria17],0) END)
...
ELSE isnull([Criteria17],0) END [red]END[/red])

Lod

You've got questions and source code. We want both!
Oh? That? That's not an important password. - IT Security Admin (pw on whiteboard)
 
Upvote 0 Downvote
Getting there.

I now get

'Computed column 'BaseAverage' in table 'TeachingDetails' is not allowed to be used in another computed-column definition.'

Would it be better to handle this at client side (asp.net)
 
Upvote 0 Downvote
This is just a whole lot of ugly.
Ideally something like this is tucked in a report, and never looked at again, but you'll probably wind up needing it later.
There are ways to have it be a calculated column, but have it actually reside on disk, so it's not constantly recomputing it.

Code: 
TeachingDetails ADD SuggestedGrade AS ( CASE WHEN (
(isnull([Criteria1],0)+ isnull([Criteria2],0)+ isnull([Criteria3],0)+ isnull([Criteria4],0)+
isnull([Criteria5],0)+ isnull([Criteria6],0)+ isnull([Criteria7],0)+ isnull([Criteria8],0)+
isnull([Criteria9],0)+ isnull([Criteria10],0)+ isnull([Criteria11],0)+ isnull([Criteria12],0)+
isnull([Criteria13],0)+ isnull([Criteria14],0)+ isnull([Criteria15],0)+ isnull([Criteria16],0)+
isnull([Criteria17],0)) / 17) 
 < isnull([Criteria16],0) AND (
(isnull([Criteria1],0)+ isnull([Criteria2],0)+ isnull([Criteria3],0)+ isnull([Criteria4],0)+
isnull([Criteria5],0)+ isnull([Criteria6],0)+ isnull([Criteria7],0)+ isnull([Criteria8],0)+
isnull([Criteria9],0)+ isnull([Criteria10],0)+ isnull([Criteria11],0)+ isnull([Criteria12],0)+
isnull([Criteria13],0)+ isnull([Criteria14],0)+ isnull([Criteria15],0)+ isnull([Criteria16],0)+
isnull([Criteria17],0)) / 17) 
< isnull([Criteria17],0) THEN (
(isnull([Criteria1],0)+ isnull([Criteria2],0)+ isnull([Criteria3],0)+ isnull([Criteria4],0)+
isnull([Criteria5],0)+ isnull([Criteria6],0)+ isnull([Criteria7],0)+ isnull([Criteria8],0)+
isnull([Criteria9],0)+ isnull([Criteria10],0)+ isnull([Criteria11],0)+ isnull([Criteria12],0)+
isnull([Criteria13],0)+ isnull([Criteria14],0)+ isnull([Criteria15],0)+ isnull([Criteria16],0)+
isnull([Criteria17],0)) / 17) 
ELSE CASE WHEN isnull([Criteria16],0) > isnull([Criteria17],0) THEN isnull([Criteria16],0) ELSE isnull([Criteria17],0) END)

You've got questions and source code. We want both!
Oh? That? That's not an important password. - IT Security Admin (pw on whiteboard)
 
Upvote 0 Downvote
Wow. Thanks for taking the time to write that.

That was successfully added. One small tweak though

In my table I have:

Criteria 16 Criteria17 BaseAverage SuggestedRating
2.6 2.3 2.447058 2.600000
2.6 4.0 2.276470 2.276470

Line 2 is correct because the base average is lower then the lowest of criteria16 and 17 which is 2.6,

however Line 1 is wrong. It should read 2.3 (Criteria17) because the base average is higher then the lower of the two being 2.3.

Do I just swap around criteria16 & 17 in the code?

 
Upvote 0 Downvote
Just to reiterate, you need the lowest of the 3 values...
This is why I wanted the initial averaging as it's own calculated column.

Give this a go, I had missed a value state
Code: 
TeachingDetails ADD SuggestedGrade AS ( 

CASE WHEN (
(isnull([Criteria1],0)+ isnull([Criteria2],0)+ isnull([Criteria3],0)+ isnull([Criteria4],0)+
isnull([Criteria5],0)+ isnull([Criteria6],0)+ isnull([Criteria7],0)+ isnull([Criteria8],0)+
isnull([Criteria9],0)+ isnull([Criteria10],0)+ isnull([Criteria11],0)+ isnull([Criteria12],0)+
isnull([Criteria13],0)+ isnull([Criteria14],0)+ isnull([Criteria15],0)+ isnull([Criteria16],0)+
isnull([Criteria17],0)) / 17) < isnull([Criteria16],0) AND (
(isnull([Criteria1],0)+ isnull([Criteria2],0)+ isnull([Criteria3],0)+ isnull([Criteria4],0)+
isnull([Criteria5],0)+ isnull([Criteria6],0)+ isnull([Criteria7],0)+ isnull([Criteria8],0)+
isnull([Criteria9],0)+ isnull([Criteria10],0)+ isnull([Criteria11],0)+ isnull([Criteria12],0)+
isnull([Criteria13],0)+ isnull([Criteria14],0)+ isnull([Criteria15],0)+ isnull([Criteria16],0)+
isnull([Criteria17],0)) / 17) < isnull([Criteria17],0) 
THEN (
	(isnull([Criteria1],0)+ isnull([Criteria2],0)+ isnull([Criteria3],0)+ isnull([Criteria4],0)+
	isnull([Criteria5],0)+ isnull([Criteria6],0)+ isnull([Criteria7],0)+ isnull([Criteria8],0)+
	isnull([Criteria9],0)+ isnull([Criteria10],0)+ isnull([Criteria11],0)+ isnull([Criteria12],0)+
	isnull([Criteria13],0)+ isnull([Criteria14],0)+ isnull([Criteria15],0)+ isnull([Criteria16],0)+
	isnull([Criteria17],0)) / 17) 
WHEN isnull([Criteria16],0) < isnull([Criteria17],0)  AND (
isnull([Criteria16],0) < 
(isnull([Criteria1],0)+ isnull([Criteria2],0)+ isnull([Criteria3],0)+ isnull([Criteria4],0)+
isnull([Criteria5],0)+ isnull([Criteria6],0)+ isnull([Criteria7],0)+ isnull([Criteria8],0)+
isnull([Criteria9],0)+ isnull([Criteria10],0)+ isnull([Criteria11],0)+ isnull([Criteria12],0)+
isnull([Criteria13],0)+ isnull([Criteria14],0)+ isnull([Criteria15],0)+ isnull([Criteria16],0)+
isnull([Criteria17],0)) / 17)
THEN isnull([Criteria16],0)
ELSE isnull([Criteria17],0) END)

You've got questions and source code. We want both!
Oh? That? That's not an important password. - IT Security Admin (pw on whiteboard)
 
Upvote 0 Downvote
Perfect. Thank you so much. I really appreciate your time.

 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

Bhashi
  • Locked
  • Question
Related to Average 1
Replies
1
Views
118
Andrzejek
Andrzejek
peac3
  • Locked
  • Question
fill the gap of hierarchy data
Replies
11
Views
560
Chris Miller
Chris Miller
grnzbra
  • Locked
  • Question
SELECT TOP 1 of Each of a Group
Replies
5
Views
258
grnzbra
grnzbra
leifoet
  • Locked
  • Question
How can I update the input of a form (rows and columns) to the database in one query ?
Replies
1
Views
206
Chris Miller
Chris Miller
grnzbra
  • Locked
  • Question
How Do I Add Column to Middle of Table
Replies
4
Views
518
BobSuruncle
BobSuruncle

Part and Inventory Search

Sponsor

Back
Top