Double of Total Problem
- by Gopal
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
