Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
mcujardo
Starting Member
5 Posts |
 Posted - 2012-01-09 : 03:04:50
|
| Hi,I have this query below, basically my main concern is this line on the query that I have posted:CAST((SUM(duration) / DATEDIFF(minute, '2011-12-01', '2011-12-08')) AS DECIMAL(2, 2)) AS 'DowntimePercentage'My goal is to calculate the Downtime Percentage using the statement but, when I execute the query below, the column always has 0.00 value.Can you help me or guide me on what I've done wrong and how to correct it?ThanksSELECT SUM(duration) AS DowntimeDuration , COUNT(util_reas.reas_desc) AS Frequency , CAST((SUM(duration) / DATEDIFF(minute, '2011-12-01', '2011-12-08')) AS DECIMAL(2, 2)) AS 'DowntimePercentage' , util_reas.reas_desc AS DownDescriptionFROM (SELECT cust_util_log.event_time_local , cust_util_log.state_cd , cust_util_log.reas_cd , cust_util_log.duration , cust_util_log.raw_reas_cd , cust_util_log.ent_id FROM cust_util_log UNION SELECT util_log.event_time_local , util_log.state_cd , util_log.reas_cd , util_log.duration , util_log.raw_reas_cd , util_log.ent_id FROM util_log) cust_util JOIN [site] ON cust_util.ent_id = [site].ent_id JOIN util_reas ON util_reas.reas_cd = cust_util.reas_cd WHERE cust_util.state_cd = 5 AND (event_time_local BETWEEN '2011-12-01' AND '2011-12-08') AND [site].site_name ='Lanco 3'GROUP BY util_reas.reas_descORDER BY [Frequency] DESC |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-01-09 : 03:58:53
|
try like below..CAST((SUM(duration)* 1.0 / DATEDIFF(minute, '2011-12-01', '2011-12-08')) AS DECIMAL(2, 2)) AS 'DowntimePercentage'... ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
 |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
|
|
|
|
|
|
|
Computed Column Always has 0.00 Value