Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2001-12-05 : 08:49:02
|
Mike writes "Dear Sirs:I am attempting to write a query that will allow me to calculate the total time a person spends on the telephone. We have a simple phone system in our office that will spit out SMDR records via a serial port. I can then capture this data and import it to MSSQL via the DTS wizard. When I run the select on a particular Station # I get the following results:Date Time Originator ID Duration Nov 29 2001 3:36PM DN2590 023 00:00:11Nov 29 2001 4:46PM DN2590 031 00:03:17Nov 29 2001 5:03PM DN2590 034 00:01:56Nov 29 2001 5:35PM DN2590 040 00:01:10 I would like to be able to calculate the total Duration for a given period. I can figure everything out, except how to perform a SUM on the Duration field. Probably because the data type for that field is nvarchar. Should I convert the data type? I have been through the book, and on-line quite a bit, but can't seem to find sample code for this, can you help? Any hint to the correct direction would be greatly appreciated. Thank you.Sincerely,Mike Edwardsmedwardsi@feco.net" |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2001-12-05 : 09:28:17
|
| SELECT Sum(DateDiff(ss, '00:00:00', convert(datetime, Duration, 108) )) FROM myTableThis will convert the nvarchar time into a datetime value, and sum up the number of seconds in each duration. You can divide the sum by 3600 to get hours, etc.Check Books Online for the CONVERT() and DATEDIFF() functions (and other date functions) for more details. |
 |
|
|
|
|
|
Calculating Total Time for Multiple Records