Display aggregate of total time as HH:MM

[guardmetrics]

I have a table which includes a timestamp and a type field (among others) I've created triggers and a view that combines this table into itself and displays a clock-in time, clock-out time, and a total time difference between them in HH:MM format.  The formula in the calculated field is below:

CONVERT(varchar(5),  DATEADD(minute, DATEDIFF(minute, [@field:Patrol_Activity_Database_Date], [@field:Patrol_Activity_Database_1_Date]), 0), 114)

My issue is that I need to have a total/aggregate for this field, but it gives an invalid error if I just do sum, and I've been unable to figure out how to get it to add the time and display it in the HH:MM format.

Any help would be great!

[Hastur]

@guardmetrics

The calculated field should look like this in:

CAST(((
  DATEDIFF(minute, [@field:Patrol_Activity_Database_Date], [@field:Patrol_Activity_Database_1_Date]) 
	- 
  (DATEDIFF(minute, [@field:Patrol_Activity_Database_Date], [@field:Patrol_Activity_Database_1_Date]) % 60)
)
/
60) AS nvarchar)
+
':'
+
CAST((DATEDIFF(minute, [@field:Patrol_Activity_Database_Date], [@field:Patrol_Activity_Database_1_Date]) % 60) AS nvarchar)

Also you may find other examples here - 

 

[guardmetrics]

@Hastur This is working great.  The only thing is that it is showing time like this " 8:0" instead of "8:00". Any idea on how to get it to show 2 digits to the right of the colon?

[Hastur]

@guardmetrics

You may use this formula:

CAST(((
  DATEDIFF(minute, [@field:Patrol_Activity_Database_Date], [@field:Patrol_Activity_Database_1_Date]) 
	- 
  (DATEDIFF(minute, [@field:Patrol_Activity_Database_Date], [@field:Patrol_Activity_Database_1_Date]) % 60)
)
/
60) AS nvarchar)
+
':'
+
CASE WHEN LEN(CAST((DATEDIFF(minute, [@field:Patrol_Activity_Database_Date], [@field:Patrol_Activity_Database_1_Date]) % 60) AS nvarchar)) = 1
  THEN '0' + CAST((DATEDIFF(minute, [@field:Patrol_Activity_Database_Date], [@field:Patrol_Activity_Database_1_Date]) % 60) AS nvarchar)
  ELSE CAST((DATEDIFF(minute, [@field:Patrol_Activity_Database_Date], [@field:Patrol_Activity_Database_1_Date]) % 60) AS nvarchar)
END

 

[guardmetrics]

@HasturThanks so much!  How do you make this work in the aggregate?