How to calculate the total Hours

Using SQL Ser开发者_高级运维ver 2005

Table1

ID Time 

001  060000 (HHMMSS)
001  080000
002  100000 
002  090000 
002  120000
.....

Intime, Outtime datatype is varchar.

I want to calculate the total time group by id

How to calculate the total Hours.

Need Query Help

based on the data give, throw away the 0000 at the end, change to int, SUM

SELECT
    SUM(CAST(REVERSE(SUBSTRING(REVERSE(TimeCol),5,8000) as int), ID
FROM
    MyTable
GROUP BY
    ID

If you have minutes but no seconds, you can modify this to throw away 00 at the end, split into hours + minutes and sum that...

The problem is:

• breaking HHMMSS string into separate HH, MM and SS
• then finding a common unit (whole seconds for example, but Ilooked at the data so chose whole hours)
• SUMming to give total common unit
• changing to desired output unit (hours in this case)

You'd have make your life a lot easier if you'd have stored this data as minutes (or seconds if you really need that level of detail).

As such, I suspect you'll need to normalise all of the data into minutes, etc. prior to adding them, as I don't believe you can meaningfully SUM a time field in this manner.

There are a variety of workarounds discussed over on http://www.dbforums.com/microsoft-sql-server/1113948-sum-time-sql-server.html - all of which convert the time field before attempting to sum it.