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.