Converting sequential time records into IN and OUT times

• drew.allen

  SSC Guru

  Points: 76988

  More actions

  August 8, 2023 at 2:37 pm

  #4255981

  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 output.  Fix your sample data and/or update your expected results.  We're not mind readers.

  Drew

  J. Drew Allen
  Business Intelligence Analyst
  Philadelphia, PA

• homebrew01

  SSC Guru

  Points: 55548

  More actions

  August 8, 2023 at 3:31 pm

  #4256005

  Replying to get past dreaded 16 reply, page 2 forum bug.

• Mathanagopal Parameswaran

  SSC Enthusiast

  Points: 190

  More actions

  August 9, 2023 at 5:23 am

  #4256454

  Ken McKelvey wrote:

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

  WITH DateList
  AS
  (
      SELECT T.EmployeeId, X.PunchTime
          ,(ROW_NUMBER() OVER (PARTITION BY T.EmployeeId ORDER BY X.PunchTime) -1 )/2 AS Grp
      FROM #t1 T
      CROSS APPLY (VALUES (T.InTime),(T.OutTime)) X (PunchTime)
      GROUP BY T.EmployeeId, X.PunchTime
  )
  SELECT EmployeeId
      ,MIN(PunchTime) AS InTime
      ,MAX(PunchTime) AS OutTime
  FROM DateList
  GROUP BY EmployeeId, Grp;

  Whatever your data is like, you should now have enough information to solve the problem yourself.

  Hi Ken,

  Thanks for your script. I had a bit of confusion now I'm clear. I was provided with the wrong test data yesterday. I'm sorry for that. I'll provide the correct test data. the test data contains missed punch,  the default value(1900-01-01 00:00:00) as absent, day shift and night shift also.

  I want to see all test data records with AttendanceDate, missed punch, and the default value(1900-01-01 00:00:00) as absent, day shift, and night shift. but the night shift needs to correct it. which you have done earlier.

  /* *** Test Data */
  SET ANSI_NULLS,  QUOTED_IDENTIFIER, ANSI_PADDING ON;
  GO
  CREATE TABLE #t1
  (
      EmployeeId int NOT NULL
  ,AttendanceDate datetime NOT NULL
      ,InTime datetime NOT NULL
  ,OutTime datetime NOT NULL
      ,PRIMARY KEY (EmployeeId,AttendanceDate)
  );
  INSERT INTO #t1
  VALUES 
  (4119,'2023-05-01 00:00:00','1900-01-01 00:00:00','1900-01-01 00:00:00'),-- Absent
  (4119,'2023-05-02 00:00:00','1900-01-01 00:00:00','1900-01-01 00:00:00'),-- Absent
  (4119,'2023-05-03 00:00:00','1900-01-01 00:00:00','1900-01-01 00:00:00'),-- Absent
  (4119,'2023-05-04 00:00:00','1900-01-01 00:00:00','1900-01-01 00:00:00'),-- Absent
  (4119,'2023-05-05 00:00:00','1900-01-01 00:00:00','1900-01-01 00:00:00'),-- Absent
  (4119,'2023-05-06 00:00:00','1900-01-01 00:00:00','1900-01-01 00:00:00'),-- Absent
  (4119,'2023-05-07 00:00:00','1900-01-01 00:00:00','1900-01-01 00:00:00'),-- Absent
  (4119,'2023-05-08 00:00:00','1900-01-01 00:00:00','1900-01-01 00:00:00'),-- Absent
  (4119,'2023-05-09 00:00:00','2023-05-09 18:25:56','2023-05-09 18:25:56'),-- Missed Punch Out
  (4119,'2023-05-10 00:00:00','2023-05-10 20:53:14','2023-05-10 20:53:14'),-- Missed Punch Out
  (4119,'2023-05-11 00:00:00','2023-05-11 20:52:49','2023-05-11 20:52:49'),-- Missed Punch Out
  (4119,'2023-05-12 00:00:00','2023-05-12 20:50:58','2023-05-12 20:50:58'),-- Missed Punch Out
  (4119,'2023-05-13 00:00:00','2023-05-13 20:50:34','2023-05-13 20:50:34'),-- Missed Punch Out
  (4119,'2023-05-14 00:00:00','2023-05-14 10:08:14','2023-05-14 23:24:45'),-- Morning Shift
  (4119,'2023-05-15 00:00:00','2023-05-15 09:58:18','2023-05-15 22:27:44'),-- Morning Shift
  (4119,'2023-05-16 00:00:00','2023-05-16 08:51:18','2023-05-16 21:07:42'),-- Morning Shift
  (4119,'2023-05-17 00:00:00','2023-05-17 08:55:37','2023-05-17 21:06:09'),-- Morning Shift
  (4119,'2023-05-18 00:00:00','2023-05-18 08:55:02','2023-05-18 21:04:18'),-- Morning Shift
  (4119,'2023-05-19 00:00:00','2023-05-19 08:51:05','2023-05-19 21:07:33'),-- Morning Shift
  (4119,'2023-05-20 00:00:00','2023-05-20 08:56:23','2023-05-20 21:03:49'),-- Morning Shift
  (4119,'2023-05-21 00:00:00','2023-05-21 08:56:56','2023-05-21 21:03:01'),-- Morning Shift
  (4119,'2023-05-22 00:00:00','2023-05-22 08:53:14','2023-05-22 21:01:30'),-- Morning Shift
  (4119,'2023-05-23 00:00:00','2023-05-23 08:53:50','2023-05-23 21:05:19'),-- Morning Shift
  (4119,'2023-05-24 00:00:00','2023-05-24 08:55:22','2023-05-24 21:04:01'),-- Morning Shift
  (4119,'2023-05-25 00:00:00','2023-05-25 08:50:01','2023-05-25 21:05:59'),-- Morning Shift
  (4119,'2023-05-26 00:00:00','2023-05-26 20:56:33','2023-05-26 20:56:33'),-- Night Shift
  (4119,'2023-05-27 00:00:00','2023-05-27 05:04:51','2023-05-27 20:56:49'),-- Night Shift
  (4119,'2023-05-28 00:00:00','2023-05-28 05:54:43','2023-05-28 09:08:19'),
  (4119,'2023-05-29 00:00:00','2023-05-29 20:56:46','2023-05-29 20:56:46'),-- Night Shift
  (4119,'2023-05-30 00:00:00','2023-05-30 04:15:15','2023-05-30 20:52:35'),-- Night Shift
  (4119,'2023-05-31 00:00:00','2023-05-31 05:30:38','2023-05-31 20:53:58'),-- Night Shift
  (4119,'2023-06-01 00:00:00','2023-06-01 04:38:38','2023-06-01 20:43:50'),-- Night Shift
  (4119,'2023-06-02 00:00:00','2023-06-02 05:38:42','2023-06-02 20:38:38'),-- Night Shift
  (4119,'2023-06-03 00:00:00','2023-06-03 05:30:39','2023-06-03 20:35:58'),-- Night Shift
  (4119,'2023-06-04 00:00:00','2023-06-04 04:30:34','2023-06-04 14:37:55'),-- Afternoon Shift
  (4119,'2023-06-05 00:00:00','2023-06-05 11:30:24','2023-06-05 22:30:14'),-- Afternoon Shift
  (4119,'2023-06-06 00:00:00','2023-06-06 14:33:19','2023-06-05 23:38:44') -- Afternoon Shift
  GO
  /* *** End Test Data *** */

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

• Mathanagopal Parameswaran

  SSC Enthusiast

  Points: 190

  More actions

  August 9, 2023 at 6:01 am

  #4256472

  drew.allen wrote:

  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 output.  Fix your sample data and/or update your expected results.  We're not mind readers.

  Drew

  Hi Allen,

  Thanks for your clarification. you're right. I'm new to this forum. that's why I don't how to provide the test data. I'll provide the test data and expected data.

  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 NOT NULL
  ,OutTime datetime NOT NULL
      ,PRIMARY KEY (EmployeeId,AttendanceDate)
  );
  INSERT INTO #t1
  VALUES 
  (4119,'2023-05-01 00:00:00','1900-01-01 00:00:00','1900-01-01 00:00:00'),-- Absent
  (4119,'2023-05-02 00:00:00','1900-01-01 00:00:00','1900-01-01 00:00:00'),-- Absent
  (4119,'2023-05-03 00:00:00','1900-01-01 00:00:00','1900-01-01 00:00:00'),-- Absent
  (4119,'2023-05-04 00:00:00','1900-01-01 00:00:00','1900-01-01 00:00:00'),-- Absent
  (4119,'2023-05-05 00:00:00','1900-01-01 00:00:00','1900-01-01 00:00:00'),-- Absent
  (4119,'2023-05-06 00:00:00','1900-01-01 00:00:00','1900-01-01 00:00:00'),-- Absent
  (4119,'2023-05-07 00:00:00','1900-01-01 00:00:00','1900-01-01 00:00:00'),-- Absent
  (4119,'2023-05-08 00:00:00','1900-01-01 00:00:00','1900-01-01 00:00:00'),-- Absent
  (4119,'2023-05-09 00:00:00','2023-05-09 18:25:56','2023-05-09 18:25:56'),-- Missed Punch Out
  (4119,'2023-05-10 00:00:00','2023-05-10 20:53:14','2023-05-10 20:53:14'),-- Missed Punch Out
  (4119,'2023-05-11 00:00:00','2023-05-11 20:52:49','2023-05-11 20:52:49'),-- Missed Punch Out
  (4119,'2023-05-12 00:00:00','2023-05-12 20:50:58','2023-05-12 20:50:58'),-- Missed Punch Out
  (4119,'2023-05-13 00:00:00','2023-05-13 20:50:34','2023-05-13 20:50:34'),-- Missed Punch Out
  (4119,'2023-05-14 00:00:00','2023-05-14 10:08:14','2023-05-14 23:24:45'),-- Morning Shift
  (4119,'2023-05-15 00:00:00','2023-05-15 09:58:18','2023-05-15 22:27:44'),-- Morning Shift
  (4119,'2023-05-16 00:00:00','2023-05-16 08:51:18','2023-05-16 21:07:42'),-- Morning Shift
  (4119,'2023-05-17 00:00:00','2023-05-17 08:55:37','2023-05-17 21:06:09'),-- Morning Shift
  (4119,'2023-05-18 00:00:00','2023-05-18 08:55:02','2023-05-18 21:04:18'),-- Morning Shift
  (4119,'2023-05-19 00:00:00','2023-05-19 08:51:05','2023-05-19 21:07:33'),-- Morning Shift
  (4119,'2023-05-20 00:00:00','2023-05-20 08:56:23','2023-05-20 21:03:49'),-- Morning Shift
  (4119,'2023-05-21 00:00:00','2023-05-21 08:56:56','2023-05-21 21:03:01'),-- Morning Shift
  (4119,'2023-05-22 00:00:00','2023-05-22 08:53:14','2023-05-22 21:01:30'),-- Morning Shift
  (4119,'2023-05-23 00:00:00','2023-05-23 08:53:50','2023-05-23 21:05:19'),-- Morning Shift
  (4119,'2023-05-24 00:00:00','2023-05-24 08:55:22','2023-05-24 21:04:01'),-- Morning Shift
  (4119,'2023-05-25 00:00:00','2023-05-25 08:50:01','2023-05-25 21:05:59'),-- Morning Shift
  (4119,'2023-05-26 00:00:00','2023-05-26 20:56:33','2023-05-26 20:56:33'),-- Night Shift
  (4119,'2023-05-27 00:00:00','2023-05-27 05:04:51','2023-05-27 20:56:49'),-- Night Shift
  (4119,'2023-05-28 00:00:00','2023-05-28 05:54:43','2023-05-28 09:08:19'),
  (4119,'2023-05-29 00:00:00','2023-05-29 20:56:46','2023-05-29 20:56:46'),-- Night Shift
  (4119,'2023-05-30 00:00:00','2023-05-30 04:15:15','2023-05-30 20:52:35'),-- Night Shift
  (4119,'2023-05-31 00:00:00','2023-05-31 05:30:38','2023-05-31 20:53:58'),-- Night Shift
  (4119,'2023-06-01 00:00:00','2023-06-01 04:38:38','2023-06-01 20:43:50'),-- Night Shift
  (4119,'2023-06-02 00:00:00','2023-06-02 05:38:42','2023-06-02 20:38:38'),-- Night Shift
  (4119,'2023-06-03 00:00:00','2023-06-03 05:30:39','2023-06-03 20:35:58'),-- Night Shift
  (4119,'2023-06-04 00:00:00','2023-06-04 04:30:34','2023-06-04 14:37:55'),-- Afternoon Shift
  (4119,'2023-06-05 00:00:00','2023-06-05 11:30:24','2023-06-05 22:30:14'),-- Afternoon Shift
  (4119,'2023-06-06 00:00:00','2023-06-06 14:33:19','2023-06-05 23:38:44') -- Afternoon Shift
  GO
  /* *** End Test Data *** */

  Expecting Data:

  /* *** Expecting Data */
  SET ANSI_NULLS,  QUOTED_IDENTIFIER, ANSI_PADDING ON;
  GO
  CREATE TABLE #t2
  (
      EmployeeId int NOT NULL
  ,AttendanceDate datetime NOT NULL
      ,InTime datetime NOT NULL
  ,OutTime datetime NOT NULL
      ,PRIMARY KEY (EmployeeId,AttendanceDate)
  );
  INSERT INTO #t2
  VALUES 
  (4119,'2023-05-01 00:00:00','1900-01-01 00:00:00','1900-01-01 00:00:00'),-- Absent
  (4119,'2023-05-02 00:00:00','1900-01-01 00:00:00','1900-01-01 00:00:00'),-- Absent
  (4119,'2023-05-03 00:00:00','1900-01-01 00:00:00','1900-01-01 00:00:00'),-- Absent
  (4119,'2023-05-04 00:00:00','1900-01-01 00:00:00','1900-01-01 00:00:00'),-- Absent
  (4119,'2023-05-05 00:00:00','1900-01-01 00:00:00','1900-01-01 00:00:00'),-- Absent
  (4119,'2023-05-06 00:00:00','1900-01-01 00:00:00','1900-01-01 00:00:00'),-- Absent
  (4119,'2023-05-07 00:00:00','1900-01-01 00:00:00','1900-01-01 00:00:00'),-- Absent
  (4119,'2023-05-08 00:00:00','1900-01-01 00:00:00','1900-01-01 00:00:00'),-- Absent
  (4119,'2023-05-09 00:00:00','2023-05-09 18:25:56','2023-05-09 18:25:56'),-- Missed Punch Out
  (4119,'2023-05-10 00:00:00','2023-05-10 20:53:14','2023-05-10 20:53:14'),-- Missed Punch Out
  (4119,'2023-05-11 00:00:00','2023-05-11 20:52:49','2023-05-11 20:52:49'),-- Missed Punch Out
  (4119,'2023-05-12 00:00:00','2023-05-12 20:50:58','2023-05-12 20:50:58'),-- Missed Punch Out
  (4119,'2023-05-13 00:00:00','2023-05-13 20:50:34','2023-05-13 20:50:34'),-- Missed Punch Out
  (4119,'2023-05-14 00:00:00','2023-05-14 10:08:14','2023-05-14 23:24:45'),-- Morning Shift
  (4119,'2023-05-15 00:00:00','2023-05-15 09:58:18','2023-05-15 22:27:44'),-- Morning Shift
  (4119,'2023-05-16 00:00:00','2023-05-16 08:51:18','2023-05-16 21:07:42'),-- Morning Shift
  (4119,'2023-05-17 00:00:00','2023-05-17 08:55:37','2023-05-17 21:06:09'),-- Morning Shift
  (4119,'2023-05-18 00:00:00','2023-05-18 08:55:02','2023-05-18 21:04:18'),-- Morning Shift
  (4119,'2023-05-19 00:00:00','2023-05-19 08:51:05','2023-05-19 21:07:33'),-- Morning Shift
  (4119,'2023-05-20 00:00:00','2023-05-20 08:56:23','2023-05-20 21:03:49'),-- Morning Shift
  (4119,'2023-05-21 00:00:00','2023-05-21 08:56:56','2023-05-21 21:03:01'),-- Morning Shift
  (4119,'2023-05-22 00:00:00','2023-05-22 08:53:14','2023-05-22 21:01:30'),-- Morning Shift
  (4119,'2023-05-23 00:00:00','2023-05-23 08:53:50','2023-05-23 21:05:19'),-- Morning Shift
  (4119,'2023-05-24 00:00:00','2023-05-24 08:55:22','2023-05-24 21:04:01'),-- Morning Shift
  (4119,'2023-05-25 00:00:00','2023-05-25 08:50:01','2023-05-25 21:05:59'),-- Morning Shift
  (4119,'2023-05-26 00:00:00','2023-05-26 20:56:33','2023-05-27 05:04:51'),-- Night Shift
  (4119,'2023-05-27 00:00:00','2023-05-27 20:56:49','2023-05-28 05:54:43'),-- Night Shift
  (4119,'2023-05-28 00:00:00','2023-05-28 09:08:19','2023-05-28 09:08:19'),-- Missed Punch Out
  (4119,'2023-05-29 00:00:00','2023-05-29 20:56:46','2023-05-30 04:15:15'),-- Night Shift
  (4119,'2023-05-30 00:00:00','2023-05-30 20:52:35','2023-05-31 05:30:38'),-- Night Shift
  (4119,'2023-05-31 00:00:00','2023-05-31 20:53:58','2023-06-01 04:38:38'),-- Night Shift
  (4119,'2023-06-01 00:00:00','2023-06-01 20:43:50','2023-06-02 05:38:42'),-- Night Shift
  (4119,'2023-06-02 00:00:00','2023-06-02 20:38:38','2023-06-03 05:30:39'),-- Night Shift
  (4119,'2023-06-03 00:00:00','2023-06-03 20:35:58','2023-06-04 04:30:34'),-- Night Shift
  (4119,'2023-06-04 00:00:00','2023-06-04 14:37:55','2023-06-04 14:37:55'),-- Missed Punch Out
  (4119,'2023-06-05 00:00:00','2023-06-05 11:30:24','2023-06-05 22:30:14'),-- Afternoon Shift
  (4119,'2023-06-06 00:00:00','2023-06-06 14:33:19','2023-06-05 23:38:44') -- Afternoon Shift
  GO
  /* *** End Expecting Data *** */

• Ken McKelvey

  SSCoach

  Points: 18843

  More actions

  August 12, 2023 at 12:35 pm

  #4259482

  You still have a typo in your test data in #t1! I am going to assume outtime on 2023-06-06 is 2023-06-06 23:38:44 and not 2023-06-05 23:38:44.

  The following works with your test data - you may have to adjust the boundary condition for NewGrp, in the Boundaries CTE, for your real data.

  WITH DateList
  AS
  (
      SELECT DISTINCT T.EmployeeId, X.PunchTime
      FROM #t1 T
          CROSS APPLY (VALUES (T.InTime),(T.OutTime)) X (PunchTime)
      WHERE T.InTime > '1900'
  )
  ,PrevTimes
  AS
  (
      SELECT EmployeeId, PunchTime
          ,LAG(PunchTime) OVER (PARTITION BY EmployeeId ORDER BY PunchTime) AS PrevPunchTime
      FROM DateList
  )
  ,Boundaries
  AS
  (
      SELECT EmployeeId, PunchTime
          ,CASE
              WHEN DATEDIFF(minute, PrevPunchTime, PunchTime) <= 
                  CASE
                      WHEN CAST(PrevPunchTime AS time) > '19:30'
                      THEN 10
                      WHEN CAST(PrevPunchTime AS time) BETWEEN '04:00' AND '06:00'
                      THEN 3
                      ELSE 15
                  END * 60
              THEN 0
              ELSE 1
          END AS NewGrp
      FROM PrevTimes
  )
  ,Grps
  AS
  (
      SELECT EmployeeId, PunchTime
          ,SUM(NewGrp) OVER (PARTITION BY EmployeeId ORDER BY PunchTime) AS Grp
      FROM Boundaries
  )
  SELECT EmployeeId, AttendanceDate, InTime, OutTime
  FROM #t1
  WHERE InTime = '1900'
  UNION ALL
  SELECT EmployeeId
      ,MIN(DATEADD(d, DATEDIFF(d, '1900', PunchTime), '1900')) AS AttendanceDate
      ,MIN(PunchTime) AS InTime
      ,MAX(PunchTime) AS OutTime
  FROM Grps
  GROUP BY EmployeeId, Grp
  ORDER BY EmployeeId, AttendanceDate;

• August 18, 2023 at 6:27 am

  This was removed by the editor as SPAM

• August 18, 2023 at 6:27 am

  This was removed by the editor as SPAM