Please provide the table DDL. It is nearly impossible to figure this out with what you have provided. Looking at the data you have provided it looks like there are consecutive rows with a timein and no timeout. Is this correct? I'd probably do something with ROW_NUMBER() to correct the issue. Something like:

DECLARE @time TABLE

(

empno CHAR(3),

datelog DATETIME,

timein DATETIME,

[timeout] DATETIME

);

INSERT INTO @time

(empno, datelog, timein, [timeout])

VALUES

('001', '07/01/2012', '2:00pm', NULL),

('001', '07/02/2012', '1:00am', NULL),

('001', '07/02/2012', '2:00pm', '11:00pm'),

('001', '07/03/2012', '2:00pm', '11:00pm');

WITH timetest

AS (

SELECT

ROW_NUMBER() OVER (PARTITION BY EmpNo ORDER BY DateLog ASC) AS row_asc,

empno,

datelog,

timein,

[timeout]

FROM

@time

),

inandout

AS (

SELECT

TIN.empno,

TIN.datelog,

TIN.timein,

TOUT.datelog AS timeout_date,

CASE WHEN TIN.timeout IS NULL AND

TOUT.timeout IS NULL THEN TOUT.timein

ELSE TIN.timeout

END AS [timeout]

FROM

timetest AS TIN

LEFT JOIN timetest AS TOUT

ON TIN.empno = TOUT.empno AND

TIN.row_asc = CASE WHEN TIN.timeout IS NULL AND

TOUT.timeout IS NULL

THEN TOUT.row_asc - 1

ELSE TOUT.row_asc

END

)

SELECT

*

FROM

inandout

WHERE

timein IS NOT NULL AND

[TIMEOUT] IS NOT NULL