John_P (3/5/2013)
Looking at Thomas' select statement made me think of one of my favorite functions, that (IMHO) doesn't get enough use: SIGNNow, this only works here because there are no negative numbers for each of the countries. SIGN returns one of four values:
SIGN(X) = -1 when X < 0
SIGN(X) = 0 when X = 0
SIGN(X) = 1 when X = 1
SIGN(NULL) = NULL
The bottom part of the average calculation:
(CASE WHEN ISNULL(Denmark,0)>0 THEN 1 ELSE 0 END +
CASE WHEN ISNULL(Finland,0)>0 THEN 1 ELSE 0 END +
CASE WHEN ISNULL(Lithuania,0)>0 THEN 1 ELSE 0 END +
CASE WHEN ISNULL(Norway,0)>0 THEN 1 ELSE 0 END +
CASE WHEN ISNULL(Sweden,0)>0 THEN 1 ELSE 0 END)
Can be replace with the following:
(SIGN(ISNULL(Denmark,0)) +SIGN(ISNULL(Finland,0)) + SIGN(ISNULL(Lithuania,0)) + SIGN(ISNULL(Norway,0)) + SIGN(ISNULL(Sweden,0))
Good Luck! John
Thank you John!
This was interesting.
By the way my solution above had to be changed a little bit to take care of division by zero problem (when all droprates in countries is null or equal to zero) but handled that by adding a -0.0000001 dummy value to the division operator.
As I only output a rounded 6 decimal value to the user it doesn´t affect anything.
(ugly workaround I know, but easy ;-(
Thank´s again! // Thomas