;WITH cte AS ( 
	SELECT 	N, EmpID, Dates, ROW_NUMBER() OVER (PARTITION BY EmpID ORDER BY Dates) AS Row
	FROM tblAttendance 
), cte2 AS (
	SELECT *, CASE WHEN (Row / 2) * 2 = Row THEN 1 ELSE 0 END AS Even
	FROM cte
)

SELECT a.EmpID, a.Dates AS [In], b.Dates AS [Out] 
FROM cte2 a
INNER JOIN cte2 b
	ON a.EmpID = b.empID
	AND a.Row = b.Row - 1
	AND a.Even = 0

quote:

---

Originally posted by visakh16

something like:-

SELECT t1.*,
CASE WHEN t2.[ time_stamp] IS NOT NULL   THEN 1 ELSE 0 END,
t2.[ time_stamp]
FROM TableA t1
OUTER APPLY(SELECT  [ time_stamp]
            FROM TableB
            WHERE [user]=t1.[user]
            AND Ad = t1.Ad
            AND [ time_stamp]> t1.[ time_stamp]
            AND [ time_stamp] < DATEADD(mi,DATEDIFF(mi,0,t1.[ time_stamp])+1,0)
           )t2

i cant understand on what basis you get rows for D though. they do not fall within 1 minute slot as in case of others

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

---