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

Posted by Amritha444 under Sql Server on 2/11/2015 | Points: 10 | Views : 620 | 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
 0 `DECLARE @EmpSwiping TABLE( SwipeType varchar(20), SwipeDate datetime, Punchtime DATETIME)INSERT @EmpSwipingSELECT 'INPUNCH', '2015-01-02 00:00:00.000', '1900-01-01 10:55:40.000' union allSELECT '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 allSELECT '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) TotalOutTimeFROM (SELECT SwipeDate, MAX(SwipeDate+Punchtime) AS InTime, OutTime,OutTime-MAX(SwipeDate+Punchtime) TotalInDiffFROM @EmpSwiping tOUTER APPLY (SELECT TOP 1 SwipeDate+Punchtime AS OutTime FROM @EmpSwiping WHERE (SwipeDate+Punchtime) > (t.SwipeDate+t.Punchtime) AND SwipeType = 'OUTPUNCH' ORDER BY OutTime )t1WHERE SwipeType = 'INPUNCH'GROUP BY SwipeDate,OutTime ) TempTimingGROUP BY SwipeDate` Mark This Response as Answer -- Chandu http://www.dotnetfunda.com/images/dnfmvp.gifAmritha444, if this helps please login to Mark As Answer. | Alert Moderator
Latest Posts