How do I convert the sum of minutes into Hours:Minutes format How do I add the value of two money fields that contain a number of minutes and return a string formatted as hours:Minutes?

The query I have so far:

SELECT (
  SELECT ISNULL(SUM(action.service_time), 0) AS Expr1
  FROM dbo.act_reg AS action
  INNER JOIN dbo.act_type ON action.act_type_id = dbo.act_type.act_type_id
  WHERE (dbo.act_type.act_type_n IN ('Travel Time'))
  AND (action.incident_id = inc.incident_id)
) AS [Travel Time], (
  SELECT ISNULL(SUM(action.service_time), 0) AS Expr1
  FROM dbo.act_reg AS action
  INNER JOIN dbo.act_type AS act_type_6 ON action.act_type_id = act_type_6.act_type_id
  WHERE (act_type_6.act_type_n NOT IN ('Travel Time', 'Site Departure'))
  AND (action.incident_id = inc.incident_id)
) AS [Total Productive Time]
FROM dbo.incident AS inc
INNER JOIN dbo.assyst_usr ON inc.ass_usr_id = dbo.assyst_usr.assyst_usr_id
INNER JOIN dbo.serv_dept ON dbo.assyst_usr.serv_dept_id = dbo.serv_dept.serv_dept_id


Expected Result:

Travel Time     Total Productive Time              Total Service Time  
60.00           17.00                              1:17
2520.00         10240.00                           212:40


Description:

'Travel Time' and 'Total Productive Time' are retrieving perfectly, please help me to get the 'Total Service Time' in (HH:Min) format.
You'll simply have to build that format manually.

SELECT  t.TravelTime, t.TotalProductiveTime,
        TotalServiceTime = CONVERT( NVARCHAR( 16 ), 
            FLOOR( ( t.TravelTime + t.TotalProductiveTime ) / 60.0 ) ) 
            + N':' + CONVERT( NVARCHAR( 5 ), CONVERT( INTEGER, 
            FLOOR( ( t.TravelTime + t.TotalProductiveTime ) % 60.0 ) ) )
FROM (  SELECT  TravelTime = CONVERT( MONEY, 2520.00 ), 
                TotalProductiveTime = CONVERT( MONEY, 10240.00 ) ) t


The above calculation divides the total number of minutes by 60 to get the number of hours, then uses the modulo operator (%) to return the remaining number of seconds.  The results are appended together to form the hours:minutes string desired.

To put this into the context of your query, you could put your T-SQL into a CTE or subquery, then perform the calculation above on the two components:

;WITH src AS (
    SELECT (
      SELECT ISNULL(SUM(action.service_time), 0) AS Expr1
      FROM dbo.act_reg AS action
      INNER JOIN dbo.act_type 
          ON action.act_type_id = dbo.act_type.act_type_id
      WHERE (dbo.act_type.act_type_n IN ('Travel Time'))
      AND (action.incident_id = inc.incident_id)
    ) AS [Travel Time], (
      SELECT ISNULL(SUM(action.service_time), 0) AS Expr1
      FROM dbo.act_reg AS action
      INNER JOIN dbo.act_type AS act_type_6 
          ON action.act_type_id = act_type_6.act_type_id
      WHERE (act_type_6.act_type_n NOT IN ('Travel Time', 'Site Departure'))
      AND (action.incident_id = inc.incident_id)
    ) AS [Total Productive Time]
    FROM dbo.incident AS inc
    INNER JOIN dbo.assyst_usr 
        ON inc.ass_usr_id = dbo.assyst_usr.assyst_usr_id
    INNER JOIN dbo.serv_dept 
        ON dbo.assyst_usr.serv_dept_id = dbo.serv_dept.serv_dept_id
)
SELECT src.[Travel Time]
    , src.[Total Productive Time]
    , [Total Service Time] = CONVERT(VARCHAR(20)
        , FLOOR((src.[Travel Time] + src.[Total Productive Time]) / 60)) 
        + ':' 
        + CONVERT(VARCHAR(20), 
             (src.[Travel Time] + src.[Total Productive Time]) % 60
          )
FROM src ;