Query to findout total in time and out time [Resolved]

Posted by Amritha444 under Sql Server on 2/11/2015 | Points: 10 | Views : 445 | Status : [Member] | Replies : 1
Hi All

Daily punching of an employee
Type Date Punch time
INPUNCH 2015-01-02 00:00:00.000 1900-01-01 10:55:40.000
OUTPUNCH 2015-01-02 00:00:00.000 1900-01-01 14:35:36.000
INPUNCH 2015-01-02 00:00:00.000 1900-01-01 15:15:10.000
OUTPUNCH 2015-01-02 00:00:00.000 1900-01-01 21:42:30.000
.
.
.
How to findout intime ie((OUTPUNCH1-INPUNCH1)+(OUTPUNCH2-INPUNCH2)..+(OUPUNCHN-INPUNCHN)
Outtime ie((INPUNCH2-OUTPUNCH1)+(INPUNCH3-OUTPUNCH2)..+(INPUNCHN-OUTPUNCHN-1)

How to findout total intime and out time ??




Responses

Posted by: Bandi on: 2/12/2015 [Member] [MVP] Platinum | Points: 50

Up
0
Down

Resolved
DECLARE @EmpSwiping TABLE( SwipeType varchar(20), SwipeDate datetime, Punchtime DATETIME)
INSERT @EmpSwiping
SELECT 'INPUNCH', '2015-01-02 00:00:00.000', '1900-01-01 10:55:40.000' union all
SELECT 'OUTPUNCH', '2015-01-02 00:00:00.000', '1900-01-01 14:35:36.000' union all
SELECT 'INPUNCH', '2015-01-02 00:00:00.000', '1900-01-01 15:15:10.000' union all
SELECT 'OUTPUNCH', '2015-01-02 00:00:00.000', '1900-01-01 21:42:30.000'

SELECT SwipeDate, CAst(DATEADD(MI, SUM(datediff(Mi, '1900-01-01',TotalInDiff)),'1900-01-01') As TIME) TotalInTime
,CAST( Max(OutTime) - MIN(InTime) - DATEADD(MI, SUM(datediff(Mi, '1900-01-01',TotalInDiff)),'1900-01-01') as TIME) TotalOutTime
FROM (
SELECT SwipeDate, MAX(SwipeDate+Punchtime) AS InTime, OutTime
,OutTime-MAX(SwipeDate+Punchtime) TotalInDiff
FROM @EmpSwiping t
OUTER APPLY (SELECT TOP 1 SwipeDate+Punchtime AS OutTime
FROM @EmpSwiping
WHERE (SwipeDate+Punchtime) > (t.SwipeDate+t.Punchtime)
AND SwipeType = 'OUTPUNCH'
ORDER BY OutTime
)t1
WHERE SwipeType = 'INPUNCH'
GROUP BY SwipeDate,OutTime ) TempTiming
GROUP BY SwipeDate


Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Amritha444, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response