Your sample data does not include the value 1900-01-01, yet you expect it in your output.  Those rows are missing, because your sample data does not match your expected...

As your latest data seems to have no missing times, try something like:

WITH DateList
AS
(
    SELECT T.EmployeeId, X.PunchTime
      ...

• This reply was modified 1 year, 5 months ago by  Mathanagopal Parameswaran.
• This reply was modified 1 year, 5 months ago by  Mathanagopal Parameswaran.

This is a very basic attempt to solve your problem.  It does not match your expected data, but, as others have pointed out, your expected data may be missing...

Hi Ken,

Thanks for your script it's working as expected. you're correct the above test data is not correct. Apologies for that. we have lots of columns that's why I didn't...

Hi Ken,

Kindly find the below test data.

/* *** Test Data*/

SET ANSI_NULLS,  QUOTED_IDENTIFIER, ANSI_PADDING ON;
GO
CREATE TABLE #t1
(
    EmployeeId int NOT NULL
,AttendanceDate datetime NOT NULL
    ,InTime datetime...

• This reply was modified 1 year, 5 months ago by  Mathanagopal Parameswaran.

Hi Phil,

a) Whether a row is good or not - NOT

b) If a row is considered bad, how should its revised InTime or OutTime be derived? - I...

Hi Ken,

Thanks for your script. I'm getting the expected result But we have InTime and OutTime in the same row and single table.  How can this be done?

Your expected results ignore rows with InTime from 28-31 May, presumably because these rows are considered 'good'? - No, missed punch out for the 28th. I just described two sample...