July 29, 2005 at 8:59 am
Hi
I have a computed column ColA :- formula = (ColB+ColC+ColD)/3.
Sometimes ColC is left empty, with the result that ColA is null.
What I want to do is make ColC a computed column (= (ColB + ColD)/2) if its value is null, so that ColA will never be left with a null value.
Is it possible to write a formula to do this?
Thanks for any help
Steve
July 29, 2005 at 9:37 am
Begin tran T1
If (Select count(*) from yourtable where ColC is null) = 0
Begin
update yourtable
set ColA = (ColB + ColC + ColD)/3
End
If (Select count(*) from yourtable where ColC is null) <> 0
Begin
update yourtable
set ColC = (ColB + ColD)/2
update yourtable
set ColA = (ColB + ColC + ColD)/3
End
Commit tran T1
July 29, 2005 at 9:51 am
Stephen - can you not resolve this by setting defaults on b, c & d to zero and changing the formula to:
CASE (ColB + ColC + ColD)
WHEN 0 THEN 0
ELSE (ColB + ColC + ColD)/3
END
better yet - could you explain in detail what colA should be based on certain values in columns b, c & d ?! I think you should be able to do this by just changing the formula in your computed column!
**ASCII stupid question, get a stupid ANSI !!!**
July 29, 2005 at 10:51 am
>>I have a computed column ColA :- formula = (ColB+ColC+ColD)/3.<<
change the formula to:
= (ColB+Isnull(ColC,0)+ColD)/(3 - (case when Col3 is null then 1 else 0 end))
* Noel
July 29, 2005 at 11:39 am
Hey noel - here's someone who's in need of your vast expansive knowledge...
**ASCII stupid question, get a stupid ANSI !!!**
August 1, 2005 at 3:10 am
Thanks for all replies.
>> better yet - could you explain in detail what colA should be based on certain values in columns b, c & d ?! I think you should be able to do this by just changing the formula in your computed column! <<
Most users enter data into all four columns. They get a mean value from a computed column (i.e the sum of those four columns/4).
Some users complained that because they sometimes cannot provide a value for the third of these columns, they are unable to get a mean value (null is returned). Consequence was that some users started to enter their own estimated value into the third column so that a mean value was returned. Since this was the case, it was decided that a standard formula should be adopted to place a value into the third column if that column was null. The formula is (Col2+Col4)/2 (i.e. If Col3 is null, then take an average from Col2 and Col4 as Col3's value).
So. to recap:
Computed col 'MeanValue' has a formula (Col1+Col2+Col3+Col4)/4. This is fine for 90% of users.
Some users cannot always provide a value for Col3, so miss out on the MeanValue.
So, if Col3 is null give it a value of (Col2+Col4)/2
Because I don't know enough SQL syntax, at the moment (as a temp measure) I get the application that fronts the db to check for Col3=null and change its value to avg(Col2+Col4) when the data is entered. Naughty, I know, but I have gone as far as setting a flag to indicate that this is a calculated value. All a bit of a kludge, which is why I'd like to get an appropriate formula working.
Thanks a lot for your help.
Steve
August 1, 2005 at 6:24 am
Further to last message. I was in error re Col3 computation.
There are FIVE columns; MeanValue = Avg(Col1+Col2+Col3+Col4). Col5 doesn't figure in this, but......
If Col4 is null, derive Col4 value from Avg(Col3 + COL5)
Good news is that thanks to your help (in particular Noel's reminding me of the IsNull function) I now have a working formula for MeanValue
convert(decimal(7,3),(([Col1] + [Col2] + [Col3] + IsNull([Col4],convert(decimal(7,3),([Col3] + [Col5])/2))) / 4))
Well ... sort of!
Can anybody now spot why MeanValue returns no more than two decimal places when it should return three? Col1 - Col5 are all smallints.
Thanks again
Steve
August 1, 2005 at 7:14 am
Duh!
Ignore the stupid question in my last post. If the sum is divided by 4 the decimal part could only be .25, .5, or .75, couldn't it?
So, in fact the formula works perfectly - thanks to you all.
Steve
August 1, 2005 at 7:39 am
convert(decimal(7,3),(([Col1] + [Col2] + [Col3] + IsNull([Col4],convert(decimal(7,3),([Col3] + [Col5])/2))) / 4))
Can anybody now spot why MeanValue returns no more than two decimal places when it should return three? Col1 - Col5 are all smallints.
Steve, sorry I don't have time right now to test but I am going to guess the decimal place issue may come from one of two sources:
1) an implicit conversion when you add and divide data of various types. This suggestion will lower performance a bit but you might want to convert all the smallint values to decimal before you add them and divide the results.
2)The data. Is it possible the result happens to have only two significant digits to the right of the decimal because of the values in the columns. In Query Analyzer, you should still see trailing zeros but you may not in an application front end.
As another bit of potentially useless information, have you considered the fact that the AVG() function disregards NULL values? I notice you are forcing a value into a null field which may be required by your buisness rules.
HTH
August 1, 2005 at 8:27 am
Hi CJohnson
>> Is it possible the result happens to have only two significant digits to the right of the decimal because of the values in the columns. In Query Analyzer, you should still see trailing zeros but you may not in an application front end. <<
You are thinking along the same lines that I (evemntually) thought along, namely that any integer divided by 4 can only return either another integer or a real containing two decimal digits (.25, .5, .75) - hence my referral to it as a "stupid question". However, my thinking was not correct, since a derived Col4 value might itself be a real rather than an integer.
I think maybe the point you make in
>> you might want to convert all the smallint values to decimal before you add them and divide the results <<
is a good one. I'll play a bit.
>> have you considered the fact that the AVG() function disregards NULL values? <<
This would be fine if it were ok to calc the mean value from only three fields, but it has to be calculated from the four I mentioned. So, in fact, although I'm not a statistician, I guess that a mean value calculated from four fields where one of those fields is not actual data but a derived average of two of the other fields is not a mean value at all and is quite pointless. I think more than anything the idea is to pander to the few who cannot enter all values.
Thanks for your help
RegardsSteve
August 1, 2005 at 8:58 am
Steve,
Your last post raises a question and potential problem. Are you storing the result of (Col3 + Col5) / 2 in column 4? As you point out, this may well be a real number and will be truncated when stored which will affect the later MeanValue calculation. The MeanValue calculation would need to be performed before you save the data. Is this MeanValue stored in the table?
Even though the normalization purists will tell you not to store computed values, it sometimes is the best approach. Having said that, you may be better off letting the users enter the data they have and create a view that performs all the computations. Users and applications would then have access to the view rather than the base table for all but data entry functions.
Looking back at your earlier post, you mention that you are currently handling this condition in the front end and marking the record as calculated. If this distinction is important, the flag seems necessary. Is there a reason this process won't work for you? One school of thought is that business rules should be implemented in a middle tier rather than in the database. As DBAs, of course, we know the database rules supreme; however, in this case unless another app may need to populate the table you may be better off putting the logic in the front end app. You do still face the issue of storing a real number in a smallint field.
August 1, 2005 at 11:14 am
CJohnson
First I should say that you were correct when you suggested that ints in the function should first be converted to reals - I discovered (in fact I knew this but had obviously forgotten) that int type takes precedence over real in a calculation, and so a function containing an int will return an int, truncating the real part of the number. To get a real I would need to explicitly convert all the smallint values to decimal in the formula I posted above.
Now to answer your latest post. No the result of (Col3 + Col5) / 2 is not stored; the value in this column remains null if the user does not explicitly enter a value (90% of users do actually enter a value here), and the calculation is used in the formula for getting MeanValue (a calculated field). Initially, I used the front end app to do the (Col3 + Col5) / 2 calculation and store it (flagging in a separate boolean field the fact that this value was derived), until my boss (one of the purists you mentioned) refused to allow it on the grounds that (Col3 + Col5) / 2 is derived, not actual, data and should therefore not be written to the database. There began my initial quest for an expansion of the MeanValue formula to incorporate any (Col3 + Col5) / 2 calculations.
convert(decimal(7,3),(([Col1] + [Col2] + [Col3] + IsNull([Col4],convert(decimal(7,3),([Col3] + [Col5])/2))) / 4))
will do the trick if I add the convert funtion to all of the smallint values, and, of course, it means that I can do away with the extra boolean flag fields because the Col4 value clearly shows null.
I hope all of this makes some sense. Grateful thanks for your input
Regards
Steve
August 1, 2005 at 11:23 am
Glad to see the problem resolved.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply