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

Write New Post |

Search Forums | Answered

Resolved Posts |

Un Answered Posts |

Forums Home

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 ??

[Resolved]

Reply | Reply with Attachment

Alert Moderator

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 @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

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

Latest Posts