Summation of Hour Numbers in T-SQL

This blog article is nothing but to record a standard script how to calculate the summation of hour numbers in report, for purpose not to think again in future.

Hour number need to be accumulated in minutes before present the final result. Else, it might be not accurate enough.

In inner query:

, SUM(DATEDIFF(MI, Departure, Arrival)) AS Mins

Note: DATEDIFF to work out every leg/trip's minutes. SUM() is to work out summation in this category togethere with GROUP BY.

In presentation outer query:

, SUM(CASE WHEN ... Mins ... END)/60 AS ...

Note: SUM() together with CASE statment is to format the presentation of the report.

In case wanting to keep decimal places, use:

, SUM(CASE WHEN ... Mins ... END)/60.0 AS ...
or
, SUM(CASE WHEN ... Mins ... END)/60.00 AS ...

For more about decimal place, please refer to http://koncordpartners.blogspot.com/2010/09/decimal-place-in-t-sql.html.