Hi,I have a report that will display maintenence jobs that have been completed against shopping stores. Below is an example of the calculation that I can add to my SQL query to give me Days/Hours/minutes. This is the time from when the job started to when it was completed.--Convert minutes to Day, Hour, Minutedeclare @diff intset @diff = Datediff(mi,'2011-01-01 09:00:00.000','2011-01-02 12:30:00.000')SELECT CONVERT(varchar(6), @diff/1440 )+ ':' + RIGHT('0' + CONVERT(varchar(2), (@diff % 1440) / 60), 2)+ ':' + RIGHT('0' + CONVERT(varchar(2), @diff % 60), 2) AS ClosedownI will be adding this to the query and the field on the report will be called CloseDown. This will show the time taken for each job.However, I need to calcualte the AVERAGE time of how long a job took to Closedown for each store; Each store is a grouping of jobsTo find the average closedown time for the grouping, the only way I can think of is by adding this logic to a text box within the grouping and using the average function. To do this I've used DateDiff in the query to give me the minutes for each job, I've called this field ClosedownMins which is just the minutes from the job start to job end for each job. I've got the first part of the logic to get me the day: =Format(round(AVG(CInt(Fields!ClosedownMins.Value)) / 1440).ToString) + "d "
Round will strip the decimals, AVG will give me the average minutes and Cint converts the minutes to INT. The 1440 are how many minutes in the day and the "d " represents day.I'd like to know how to do this for the HOURS AND MINUTES but don't know how to do this as I'm unsure about the percentage symbol and how to use the same logic in SSRS.Can anyone help... I think I've loosing my hair fast lol
Add SQL logic to SSRS text box
Posted - 2011-04-14 : 05:09:33
