Going more than 24hrs and adding days

Question

I have an expression in a table that is calculating the total of the column that has different times in it. But after the total goes over 24 hours it resets. I want to add to have days also in it if it goes more than 24hrs. I have so far added days in the format but this means that even with 0 days it gives 01 days since it is using the days section of the date. This is wrong and I either want to take one away from this ot have some sort of a counter to count the days. This is the expression if have so far:

=format(DateAdd("s", SUM(Fields!TotalDowntime.Value), "00:00:00"), "dd 'days' HH 'hrs' mm 'mins' ss 'secs'")

I have tried to format and using dateadd function to see if this can be done in a different way

Answer 1

The following isn't a full answer, but I'm hoping it will help you towards one. I think I've understood from your question that the field which records your TotalDowntime figure records this value in seconds.

SELECT
   TotalDowntime/(24*60*60) as [days],
   TotalDowntime/(60*60) % 24 as [hours],
   TotalDowntime/(60) % 60 as [minutes],
   TotalDowntime % 60 as [seconds]
FROM
   MyTable

You'll see that all the parts simply cut off any decimals that result from the division. The % is SQL Servers "Mod" function that returns the remainder on division.

You should be able to put code similar to the above "server side" and concatenate these columns in reportbuilder (or server side if you wish).