1

How can i caluculate average HH:MM in SSRS Report.I am getting data with below query

 select O.ORDERNUM, Cast(DateAdd(s, AVG(CAST(DateDiff( s, '00:00', cast(DROPTIME as time(0))) AS INT)), '00:00' ) as Time(0)) AS ATIME
FROM ORDE_ O
WHERE
O.CLIENTNUM='HLEX1'

Data is coming like below 

OrderNum        ATIME
123            16:20:30
124            17:30:00
125            17:56:43
126            17:55:00
               Here i want to display Average Hour and Minutes

How can i do this thanks for your help.

A.Goutam
  • 3,422
  • 9
  • 42
  • 90

1 Answers1

1

I would change the approach slightly. Instead of getting each order's average time and returning a time datatype, just return an integer in seconds. You can then get an average of those integer values and convert back to a time form (if required) in the report itself.

Alan Schofield
  • 19,839
  • 3
  • 22
  • 35
  • thanks for your comment can you explain how can i change int to hh:mm on Report and second how can i average there. For convert in to Integer i did this `AVG(CAST(DateDiff( ms, '00:00', cast(DROPTIME as time(0))) AS INT)) ` – A.Goutam Nov 19 '19 at 12:00
  • for Average i am using expression `SUM(Fields!ATIME.Value)/CountDistinct(Fields!ATIME.Value)` currently – A.Goutam Nov 19 '19 at 12:02