September 22, 2007 at 9:29 am
Hi, I have an issue with a calculation in SQL - The sql scripts takes a time period and divides it by 3600.00 to translate into decimal hours. So 1 min 42 secs becomes .0286111, I need to present this figure as 0.0286.
Would is use the CAST and As decimal command?
SUM(CASE
WHEN ACCESS_CODE NOT IN (0,4,5,6,7,8,9,1000,10,11,12,13,14,15,27,904,905,906) THEN ACCESS_DURATION / 3600.00
ELSE 0
END) AS 'access_hrs',
Nick
September 24, 2007 at 1:27 pm
Read about ROUND function in Books Online.
It takes three parameters
1. The value
2. Number of decimals
3. Round or truncate
N 56°04'39.16"
E 12°55'05.25"
September 24, 2007 at 4:20 pm
By the way - if you should simply flip the CASE syntax around so that you can express it as an IN as opposed to a NOT IN, it actually runs somewhat faster. Especially if you go to the trouble of sorting the elements in the IN ( )
e.g.
SUM(CASE
WHEN ACCESS_CODE NOT IN (0,4,5,6,7,8,9,1000,10,11,12,13,14,15,27,904,905,906,1000) THEN 0 else ACCESS_DURATION / 3600.00
ELSE 0
END)
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply