Time in Seconds but Display as Hours, Minutes and Seconds but be able to SUM

[sflindsey]

I have a number of columns in a table where the duration time is stored in the number of seconds (the source tables are in SQL Server 2005).  I want to be able to SUM the data for totals.  When I display the data I want to convert the number of seconds to display as HH:MM:SS.

I have been able to convert the seconds to display properly but the SUM will not work because it no longer recognizes the number of seconds and tries to summarize what is displayed. 

For example:
Agent1      2:00:00
Agent2      3:00:00
Total         5:00:00

Source data for the two rows is stored in seconds as 7200 and 10800.

Does anyone have any ideas how this could be accomplished???

Thanks in advance!!

[kgl_m]

Hi, when I had a similar issue I made two data items. One with the original value and one with the formatted/calculated value. Then I use the original value for other calculations and the formatted value for display.

Hope this helps.

Also, would you please share how you got the time to display in hh:mm:ss format by coverting the seconds? I'm having the same issue and the solution of [Data Item]/86400 doesn't work for me.

[sflindsey]

Thanks for the reply!  I finally figured out how to do it.  I created a new calculation in Framework Manager on the Query Subject Definition called Sign-on Time Display.  Here is what I did to display the number of seconds:
CASE
WHEN CAST(TOTAL([Business View].[Daily Agent Activity].[Sign-on Time]) / 3600 AS INTEGER) < 10
THEN '0' + TRIM(CAST(CAST(TOTAL([Business View].[Daily Agent Activity].[Sign-on Time]) / 3600 AS INTEGER) AS CHAR(1)))
ELSE TRIM(CAST(CAST(TOTAL([Business View].[Daily Agent Activity].[Sign-on Time]) / 3600 AS INTEGER) AS CHAR(3)))
END
+ ':' +
CASE
WHEN CAST(MOD(TOTAL([Business View].[Daily Agent Activity].[Sign-on Time]), 3600) / 60 AS INTEGER) < 10
THEN '0' + TRIM(CAST(CAST(MOD(TOTAL([Business View].[Daily Agent Activity].[Sign-on Time]), 3600) / 60 AS INTEGER) AS CHAR(1)))
ELSE TRIM(CAST(CAST(MOD(TOTAL([Business View].[Daily Agent Activity].[Sign-on Time]), 3600) / 60 AS INTEGER) AS CHAR(2)))
END
+ ':' +
CASE
WHEN MOD(TOTAL([Business View].[Daily Agent Activity].[Sign-on Time]), 60) < 10
THEN '0' + TRIM(CAST(CAST(MOD(TOTAL([Business View].[Daily Agent Activity].[Sign-on Time]), 60) AS INTEGER) AS CHAR(1)))
ELSE TRIM(CAST(CAST(MOD(TOTAL([Business View].[Daily Agent Activity].[Sign-on Time]), 60) AS INTEGER) AS CHAR(2)))
END

HTH!

[kgl_m]

Applause!  :D

Your solution works great. thanks!

[fgarav]

09:30@sflindsey  ... great work , but just a question...
I used your calculation to convert number of seconds in format hh:mm... all seems to be ok  on new calculation in query subject definition, i run test and all ok, the result is
09:30,10.35 and so on , so i have saved my query subject and run the test on all query subject but result are
*.36 *.36 and so on.... can u give me some idea what's wrong ?
my formula is
CASE
WHEN CAST(TOTAL([Foundation Object View].[TBL_PCO_DETTAGLIO].[REDDITO_SECONDI]) / 3600 AS INTEGER) < 10
THEN '0' + TRIM(CAST(CAST(TOTAL([Foundation Object View].[TBL_PCO_DETTAGLIO].[REDDITO_SECONDI]) / 3600 AS INTEGER) AS CHAR(1)))
ELSE TRIM(CAST(CAST(TOTAL([Foundation Object View].[TBL_PCO_DETTAGLIO].[REDDITO_SECONDI]) / 3600 AS INTEGER) AS CHAR(3)))
END
+ ':' +
CASE
WHEN CAST(MOD(TOTAL([Foundation Object View].[TBL_PCO_DETTAGLIO].[REDDITO_SECONDI]); 3600) / 60 AS INTEGER) < 10
THEN '0' + TRIM(CAST(CAST(MOD(TOTAL([Foundation Object View].[TBL_PCO_DETTAGLIO].[REDDITO_SECONDI]);3600) / 60 AS INTEGER) AS CHAR(1)))
ELSE TRIM(CAST(CAST(MOD(TOTAL([Foundation Object View].[TBL_PCO_DETTAGLIO].[REDDITO_SECONDI]);3600) / 60 AS INTEGER) AS CHAR(2)))
END