Double of Total Problem

Posted by Gopal on Stack Overflow See other posts from Stack Overflow or by Gopal
Published on 2010-03-08T04:53:06Z Indexed on 2010/03/08 5:06 UTC
Read the original article Hit count: 395

Filed under:
|
|

Table1

ID  |  WorkTime
-----------------
001 |  10:50:00
001 |  00:00:00
002 |  ....

WorkTime Datatype is *varchar(.

SELECT ID, 
       CONVERT(varchar(10), TotalSeconds1 / 3600) + ':' + RIGHT('00' + CONVERT(varchar(2), (TotalSeconds1 - TotalSeconds1 / 3600 * 3600) / 60), 2) + ':' + RIGHT('00' + CONVERT(varchar(2), TotalSeconds1 - (TotalSeconds1 / 3600 * 3600 + (TotalSeconds1 - TotalSeconds1 / 3600 * 3600) / 60 * 60)), 2) AS TotalWork 
From  ( SELECT ID, 
               SUM(DATEDIFF(second, CONVERT(datetime, '1/1/1900'), 
               CONVERT(datetime, '1/1/1900 ' + WorkTime))) AS TotalSeconds1 
          FROM table1 
      group by ID) AS tab1 
where id = '001'

The above Query is showing "double the total of time"

For Example

From table1 i want to calculate the total WorkTime, when i run the above query it is showing

ID WorkTime

001 21:40:00
002...,

But it should show like this

ID Worktime

001 10:50:00
...,

How to avoid the double total of worktime. How to modify my query.

Need Query Help

© Stack Overflow or respective owner

Related posts about sql-server

Related posts about sql