Summing and Formating Time Field 2

[DWheater]

Hi

I'm trying to generate a report for our call logger, it has a field called Duration that holds the length of time a call took.

I'd like to be able to create a report that shows a Summary of calls to international destinations and the total number of calls and total length of those calls. I have the first part, the total number of calls, but I can't get the total length to work, this is what I have so far: (It's on SQL 2008 Express)

SELECT TOP 100
Calls.DigitsDialled,
DiallingCodes.Details as "Location",
DiallingCodes.Code,
count(Calls.DigitsDialled) as "No of Calls",
[COLOR=#F57900]convert(varchar(20), dateadd(second, Sum(DATEDIFF(S,'00:00:00',Duration)), 0), 108) as "Total Length"[/color]
FROM Calls
LEFT JOIN DiallingCodes ON Calls.DigitsDialled LIKE DiallingCodes.Code + '%'
WHERE Calls.DigitsDialled Like '00%'
AND (DATEPART(Month,Calls.LogDate) = 12 AND DATEPART(YEAR,Calls.LogDate) = 2013)
Group BY Calls.DigitsDialled,DiallingCodes.Details,DiallingCodes.Code
ORDER BY "No of Calls" DESC

The problem is the field is showing HH:MM:SS yet some of the fields are longer than 24 hours so it's not showing the full information. Is there anyway to format the field as hhh:mm:ss?
or is there a better way of doing this?

 

[imex]

Hi,

Try:

convert(varchar(20), DATEDIFF(DAY, 0, dateadd(second, Sum(DATEDIFF(S,'00:00:00',Duration)), 0)) * 24 +
                     DATEPART(HOUR, dateadd(second, Sum(DATEDIFF(S,'00:00:00',Duration)), 0)) ) +
right(convert(varchar(20), dateadd(second, Sum(DATEDIFF(S,'00:00:00',Duration)), 0), 108), 6) as "Total Length"

Hope this helps.

[URL unfurl="true"]http://www.imoveisemexposicao.com.br/imobiliarias-em-guarulhos[/url]

 

[DWheater]

Perfect. Thank you so much.

 

[gmmastros]

Personally, I would just show the fractional number of hours instead of converting to hours, minutes and seconds. To do this, simply divide the duration by 3600.0.

Recognizing that you don't always have freedom to dictate the the output format...

I would approach this in 2 parts. The first part is to calculate the whole number or hours.

DateDiff returns an integer, so DateDiff / 3600 will return the whole number of hours.

The next part is to format the minutes and seconds. To do this, I would use the modulus operator ( % ) which returns the remainder.

Ex:

Declare @Duration Int

Set @Duration = 200000

Select Convert(VarChar(20), @Duration / 3600) 
       + ':' 
       + SubString(convert(varchar(20), dateadd(second, @Duration % 3600, 0), 108), 4, 20)

You can copy/paste the code shown above in to a query window and run it. Change the duration and re-run it until you are comfortable with the output. Then incorporate this in to your query.



-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[jbenson001]

Very slick George