In / out and attendance punch employee shifts base in biometric machine

See more:

SQL-Server

biometrics

table shift
FromTime ToTime
08:00:00 18:00:00
this is employee shift.. More can be included deference shifts employee wise .



table in/out

EnrollNumber DateInOut Biometric machine
15045 2016-04-11 08:00:00 IN
15045 2016-04-11 23:00:00 OUT
15045 2016-04-12 08:00:00 IN
15045 2016-04-13 00:15:00 NEXT DAY OUT
15045 2016-04-13 06:00:00




I want to display

EnrollNumber DateIn DateOut
15045 2016-04-11 08:00:00 2016-04-11 23:00:00 CURRENT DAY OUT
15045 2016-04-12 08:00:00 2016-04-13 00:15:00 NEXT DAY OUT


I need to display the correct in/out by employee number and microsoft sql server views using how to create this table.

What I have tried:

FromTime ToTime
22:00:00 6:00:00
09:00:00 18:00:00

EnrollNumber DateInOut
15045 2016-04-11 22:00:00
15045 2016-04-11 23:00:00
15045 2016-04-12 06:00:00
15045 2016-04-12 22:00:00
15045 2016-04-13 06:00:00
15045 2016-04-14 06:00:00
15045 2016-04-14 22:00:00
15045 2016-04-15 06:00:00
15045 2016-04-18 09:10:00
15045 2016-04-18 18:00:00
15045 2016-04-18 20:00:00

I try your solution
;WITH clean AS
(
SELECT EnrollNumber, DateInOut AS DateIn,
ROW_NUMBER() OVER (PARTITION BY EnrollNumber ORDER BY EnrollNumber, DateInOut) rn
FROM tblCheckInOut

), clean2 AS
(
SELECT DISTINCT clean.EnrollNumber, clean.DateIn, prev.DateIn as DatePrev, clean.rn
FROM clean
LEFT OUTER JOIN clean prev ON prev.rn = clean.rn - 1

), clean3 AS(
SELECT DISTINCT EnrollNumber, CASE WHEN DATEDIFF(hh, DatePrev, DateIn) <= (SELECT DATEDIFF(hh, CAST([To Time] AS DateTime), CAST([From Time] AS DateTime))
FROM dbo.tblEmployee emp INNER JOIN
dbo.tblShiftMatrix m ON emp.EmpNo = m.EmpId INNER JOIN
dbo.tblShift s ON m.ShiftId = s.Id
WHERE emp.EmpNo = EnrollNumber AND CAST(ShiftDate as Date) = cast(T1.DateIn as date))

THEN DatePrev ELSE DateIn END AS DateInOut
FROM clean2 T1)
, clean4 AS (
SELECT EnrollNumber, DateInOut AS DateIn
,ROW_NUMBER()
OVER(PARTITION BY EnrollNumber ORDER BY EnrollNumber, DateInOut) AS rn
from clean3
),clean5 AS (
SELECT DISTINCT clean4.EnrollNumber, clean4.DateIn, nxt.DateIn as DateOut, clean4.rn
FROM clean4
LEFT OUTER JOIN clean4 nxt ON nxt.rn = clean4.rn + 1
)
SELECT EnrollNumber,Cast(DateIn as Date) Date, Cast(DateIn as datetime) as [Time In], Cast(DateOut as datetime) as [Time Out]
FROM clean5
WHERE DateOut IS NOT NULL AND rn%2<>0
ORDER BY EnrollNumber, DateIn

and the result is
EnrollNumber Date Time In Time Out
15045 2016-04-08 2016-04-08 21:00:00.000 2016-04-09 06:00:00.000
15045 2016-04-11 2016-04-11 22:00:00.000 2016-04-12 06:00:00.000
15045 2016-04-12 2016-04-12 22:00:00.000 2016-04-13 06:00:00.000
15045 2016-04-13 2016-04-13 22:30:00.000 2016-04-14 06:00:00.000
15045 2016-04-14 2016-04-14 22:00:00.000 2016-04-18 09:10:00.000
15045 2016-04-18 2016-04-18 18:00:00.000 2016-04-18 20:00:00.000

This code cannot create ms sql server views table use.. and How to views create sql server and date range filter?

@Member 12322427