Click here to Skip to main content
12,630,420 members (30,582 online)
Rate this:
 
Please Sign up or sign in to vote.
See more: SQL
I have problem with calculating the total working time in h:m:s format

pls help.

this is my sample data

empid time        date         status
1     9:00:04     08-01-2013   login
1     9:30:45     08-01-2013   logOut
1     9:45:20     08-01-2013   login
1     12:30:59    08-01-2013   logOut
Posted 7-Jan-13 16:11pm
Updated 7-Jan-13 16:29pm
v2
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 3

Hi Manoj

As per my understanding you want this .....

I create a table like you mention(add a primary key ) and fill the same data .....

Create table T1 
(ID int identity (1,1),
 EmpID int , 
 Etime time, 
 EDate date, 
 EStatus Char(1) )
Write a sql query
SELECT Tab1.EMPID , Tab1.EDate
,SUM([SECONDS]) AS [TOTAL TIME in SECONDS]
,CONVERT(varchar (20), SUM(Tab1.[SECONDS])/(60 * 60))+ ':' +
CONVERT(varchar(20), (SUM(Tab1.[SECONDS])%(60 * 60))/ 60) + ':' +
CONVERT(varchar(20), (SUM(Tab1.[SECONDS])%(60 * 60*60))%60) AS [WORKING TIME]
FROM
(SELECT A.EmpID , A.Etime AS [IN TIME]
, B.Etime AS [OUT TIME]
, A.EDate
, Datediff (SECOND,A.Etime,B.Etime  ) as [SECONDS]
FROM
T1 as A (NOLOCK)
INNER JOIN t1 as B  ON a.ID = (b.ID -1)  AND A.EStatus = 'I'
  WHERE  B.EStatus = 'O' ) AS Tab1
  GROUP BY Tab1.EMPID , Tab1.EDate

Output
EMPID       EDate      TOTAL TIME in SECONDS WORKING TIME
----------- ---------- --------------------- --------------------------------------
1           2013-08-01  11780                 3:16:20
 

----------------------------------------------------------------------------------
Regards,
Vijay
  Permalink  
Comments
manoj12.shrivastava 8-Jan-13 10:54am
   
Thank you very much sir
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 1

What have you tried ? DateDiff will give you the difference between two dates.
  Permalink  
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 2

you can refer version 2, in below link

Get duration between two dates in years,months,days and hours using sql server[^]

Happy Coding!
:)
  Permalink  
Comments
URVISHSUTHAR 8-Jan-13 3:27am
   
yep, it can fulfill your recruitment

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
Top Experts
Last 24hrsThis month


Advertise | Privacy | Mobile
Web01 | 2.8.161205.3 | Last Updated 8 Jan 2013
Copyright © CodeProject, 1999-2016
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100