calculating Total In-Time from InTime & OutTime columns

Bandi
Posted by Bandi under Sql Server category on | Points: 40 | Views : 468

--Sample Data
DECLARE @WorkngHrs TABLE(date DATETIME, timein DATETIME, timeout DATETIME, spendtime TIME)
INSERT INTO @WorkngHrs
SELECT '2013-01-08 00:00:00.000', '2013-01-08 09:17:00.000', '2013-01-08 12:37:00.000', '03:20:00' union all
SELECT '2013-01-08 00:00:00.000', '2013-01-08 17:21:00.000', '2013-01-08 18:40:00.000', '01:19:00'
/*
Expected output
--------------date--------------------timein---------------------------timeout--------------------spendtime
2013-01-08 00:00:00.000------2013-01-08 09:17:00.000----2013-01-08 12:37:00.000----04:39:00
2013-01-08 00:00:00.000------2013-01-08 17:21:00.000----2013-01-08 18:40:00.000---- */
--Query
SELECT [date],
CONVERT(varchar(8),DATEADD(ss,SUM(DATEDIFF(ss,TimeIn,TimeOut)),0),108) AS SpendTime
FROM @WorkngHrs
GROUP BY [date]

Comments or Responses

Posted by: Amatya on: 8/6/2015 Level:Silver | Status: [Member] | Points: 10
Whats This.. ?

Login to post response