brain dust

The Absolute.

Tuesday, November 23, 2004

If you want to get a float or decimal number back when doing an aggregation on a field and its conditional, make sure that if you have a default response, that the value of the response matches the value type expected back.

This sample kept returning 1 back. I knew that the data should return 1.05045564. I tried round and several other instances. It turns out that the ELSE 0 was causing the format to be int.

SELECT @AvgCallTime =CASE WHEN @DayCount > 0 THEN
SUM(CASE WHEN DATEPART(dd, CallTime) = @ReportDay THEN Duration ELSE 0 END)/60/@DayCount
ELSE 0 END,

FROM table_Data
WHERE CallTime <= @AsOfDate

But when I changed it to this it works:

SELECT @AvgCallTime =CASE WHEN @DayCount > 0 THEN
SUM(CASE WHEN DATEPART(dd, CallTime) = @ReportDay THEN Duration ELSE 0.00 END)/60/@DayCount
ELSE 0 END,
FROM table_Data
WHERE CallTime <= @AsOfDate



SOOO, the else of the conditional case statement drives the formatting of the returned value.

HTH.

0 Comments:

Post a Comment

<< Home