title: Daily Attendance Summed Weekly
body: <p>Greetings all,<br>
  
      I’m wondering if I could get some insights on the best way to get Attendance data.<br>
  I need get every viable in(AttendanceTypeID = 1) and out(AttendanceTypeID = 2) pairs in a day. Sum up the time values up per employee by week starting Sunday and ending Saturday.  The closet in to the an out is the business rule for what is needed, and there may be many in/out pairs in a single day.  I am also anchored to SQL 2008 logic since some of our customers are still using SQL 2008.  I have seen a view examples using CTE’s, which I prefer, but they had used lead,lag, LastVal, NestVal functions which start in 2012.<br>
  Output.</p>
  
  <p><a href="https://forums.asp.net/t/1946217.aspx?Time+Attendance+working+hours" rel="nofollow noreferrer">https://forums.asp.net/t/1946217.aspx?Time+Attendance+working+hours</a><br>
  <a href="https://www.red-gate.com/simple-talk/sql/t-sql-programming/solving-complex-t-sql-problems,-step-by-step/" rel="nofollow noreferrer">https://www.red-gate.com/simple-talk/sql/t-sql-programming/solving-complex-t-sql-problems,-step-by-step/</a><br></p>
  
  <pre><code>CREATE TABLE tblAttendance
  (
  [AttendanceID] int, 
  [DepartmentID] int, 
  [EmployeeID] int, 
  [AttendanceDate] datetime, 
  [AttendanceTime] varchar(8), 
  [AttendanceTypeID] int, 
  [AttendanceCodeID] int, 
  [Submitted] int
  );
  
  
  
  INSERT INTO tblAttendance
      ([AttendanceID], [DepartmentID], [EmployeeID], [AttendanceDate], 
  [AttendanceTime], [AttendanceTypeID], [AttendanceCodeID], [Submitted])
      VALUES
          (838, 33, 260, '2018-02-26 00:00:00', '8:00:00', 1, 1, 0),
          (839, 33, 260, '2018-02-26 00:00:00', '22:00:00', 2, 1, 0),
          (836, 41, 344, '2018-02-26 00:00:00', '9:00:00', 1, 1, 0),
          (837, 41, 344, '2018-02-26 00:00:00', '22:00:00', 2, 1, 0),
          (812, 33, 348, '2018-02-26 00:00:00', '8:00:00', 1, 1, 0),
          (813, 33, 348, '2018-02-26 00:00:00', '12:00:00', 2, 1, 0),
          (814, 33, 350, '2018-02-26 00:00:00', '8:00:00', 1, 1, 0),
          (815, 33, 350, '2018-02-26 00:00:00', '12:00:00', 2, 1, 0),
          (930, 7, 361, '2018-02-26 00:00:00', '7:00:00', 1, 1, 0),
          (931, 7, 361, '2018-02-26 00:00:00', '9:00:00', 2, 1, 0),
          (940, 19, 361, '2018-02-26 00:00:00', '8:55:00', 1, 1, 0),
          (941, 19, 361, '2018-02-26 00:00:00', '10:00:00', 2, 1, 0),
          (824, 1, 114, '2018-02-27 00:00:00', '23:59:57', 2, 1, 0),
          (816, 33, 206, '2018-02-27 00:00:00', '11:12:00', 1, 1, 0),
          (819, 33, 206, '2018-02-27 00:00:00', '11:16:00', 2, 1, 0),
          (822, 1, 350, '2018-02-27 00:00:00', '0:00:00', 1, 1, 0),
          (829, 33, 350, '2018-02-27 00:00:00', '16:15:30', 1, 1, 0),
          (830, 33, 359, '2018-02-27 00:00:00', '16:15:30', 1, 1, 0),
          (932, 7, 361, '2018-02-27 00:00:00', '7:00:00', 1, 1, 0),
          (933, 7, 361, '2018-02-27 00:00:00', '9:00:00', 2, 1, 0),
          (942, 19, 361, '2018-02-27 00:00:00', '8:30:00', 1, 1, 0),
          (943, 19, 361, '2018-02-27 00:00:00', '10:30:00', 2, 1, 0),
          (835, 33, 206, '2018-02-28 00:00:00', '9:23:00', 1, 1, 0),
          (934, 7, 361, '2018-02-28 00:00:00', '7:00:00', 1, 1, 0),
          (935, 7, 361, '2018-02-28 00:00:00', '9:00:00', 2, 1, 0),
          (944, 19, 361, '2018-02-28 00:00:00', '7:00:00', 1, 1, 0),
          (945, 19, 361, '2018-02-28 00:00:00', '9:00:00', 2, 1, 0),
          (936, 7, 361, '2018-03-01 00:00:00', '7:00:00', 1, 1, 0),
          (937, 7, 361, '2018-03-01 00:00:00', '9:00:00', 2, 1, 0),
          (946, 19, 361, '2018-03-01 00:00:00', '9:00:00', 1, 1, 0),
          (947, 19, 361, '2018-03-01 00:00:00', '9:30:00', 2, 1, 0),
          (840, 33, 350, '2018-03-02 00:00:00', '8:21:00', 1, 1, 0),
          (841, 33, 350, '2018-03-02 00:00:00', '8:22:00', 2, 1, 0),
          (938, 7, 361, '2018-03-02 00:00:00', '7:00:00', 1, 1, 0),
          (939, 7, 361, '2018-03-02 00:00:00', '9:00:00', 2, 1, 0),
          (948, 19, 361, '2018-03-02 00:00:00', '9:01:00', 1, 1, 0),
          (949, 19, 361, '2018-03-02 00:00:00', '10:00:00', 2, 1, 0),
          (894, 33, 260, '2018-03-05 00:00:00', '6:00:00', 1, 1, 0),
          (895, 33, 260, '2018-03-05 00:00:00', '7:00:00', 2, 1, 0),
          (859, 33, 348, '2018-03-05 00:00:00', '9:30:00', 1, 1, 0),
          (860, 33, 348, '2018-03-05 00:00:00', '9:30:00', 1, 1, 0),
          (842, 33, 206, '2018-03-06 00:00:00', '13:21:00', 1, 1, 0),
          (856, 33, 206, '2018-03-06 00:00:00', '2:51:27', 2, 1, 0),
          (848, 33, 206, '2018-03-06 00:00:00', '13:57:00', 2, 1, 0),
          (843, 33, 260, '2018-03-06 00:00:00', '14:00:00', 1, 1, 0),
          (861, 33, 348, '2018-03-06 00:00:00', '9:30:00', 1, 1, 0),
          (862, 33, 348, '2018-03-06 00:00:00', '9:31:00', 1, 1, 0),
          (853, 33, 350, '2018-03-06 00:00:00', '2:32:36', 1, 1, 0),
          (854, 33, 350, '2018-03-06 00:00:00', '2:48:54', 1, 1, 0),
          (855, 33, 350, '2018-03-06 00:00:00', '2:49:11', 1, 1, 0),
          (857, 33, 350, '2018-03-06 00:00:00', '2:52:19', 1, 1, 0),
          (844, 33, 350, '2018-03-06 00:00:00', '13:32:00', 1, 1, 0),
          (846, 33, 350, '2018-03-06 00:00:00', '13:53:00', 1, 1, 0),
          (845, 33, 350, '2018-03-06 00:00:00', '13:35:00', 2, 1, 0),
          (850, 33, 350, '2018-03-06 00:00:00', '14:06:00', 2, 1, 0),
          (847, 33, 359, '2018-03-06 00:00:00', '13:56:00', 1, 1, 0),
          (858, 33, 359, '2018-03-06 00:00:00', '14:52:00', 1, 1, 0),
          (852, 33, 359, '2018-03-06 00:00:00', '2:29:06', 2, 1, 0),
          (851, 33, 359, '2018-03-06 00:00:00', '14:09:00', 2, 1, 0),
          (896, 33, 260, '2018-03-07 00:00:00', '6:00:00', 1, 1, 0),
          (897, 33, 260, '2018-03-07 00:00:00', '7:00:00', 2, 1, 0),
          (907, 33, 348, '2018-03-07 00:00:00', '7:00:00', 1, 1, 0),
          (908, 33, 348, '2018-03-07 00:00:00', '8:00:00', 2, 1, 0),
          (893, 33, 350, '2018-03-07 00:00:00', '14:36:40', 1, 1, 0),
          (915, 33, 350, '2018-03-07 00:00:00', '17:00:00', 2, 1, 0),
          (913, 33, 359, '2018-03-07 00:00:00', '8:00:00', 1, 1, 0),
          (914, 33, 359, '2018-03-07 00:00:00', '17:00:00', 2, 1, 0),
          (923, 33, 348, '2018-03-08 00:00:00', '7:00:00', 1, 1, 0),
          (925, 33, 348, '2018-03-08 00:00:00', '11:00:00', 1, 1, 0),
          (928, 33, 348, '2018-03-08 00:00:00', '11:45:00', 1, 1, 0),
          (927, 33, 348, '2018-03-08 00:00:00', '11:35:00', 2, 1, 0),
          (924, 33, 348, '2018-03-08 00:00:00', '12:00:00', 2, 1, 0),
          (926, 33, 348, '2018-03-08 00:00:00', '13:00:00', 2, 1, 0),
          (898, 33, 350, '2018-03-08 00:00:00', '11:42:57', 1, 1, 0),
          (906, 33, 206, '2018-03-09 00:00:00', '8:14:43', 1, 1, 0),
          (905, 33, 260, '2018-03-09 00:00:00', '8:12:53', 1, 1, 0),
          (972, 33, 348, '2018-03-09 00:00:00', '8:00:00', 1, 1, 0),
          (911, 33, 350, '2018-03-09 00:00:00', '10:43:59', 1, 1, 0),
          (912, 33, 350, '2018-03-09 00:00:00', '10:44:22', 1, 1, 0),
          (917, 33, 350, '2018-03-09 00:00:00', '12:44:48', 1, 1, 0),
          (929, 33, 350, '2018-03-09 00:00:00', '15:18:17', 1, 1, 0),
          (921, 33, 350, '2018-03-09 00:00:00', '12:50:14', 2, 1, 0),
          (918, 33, 359, '2018-03-09 00:00:00', '12:44:48', 1, 1, 0),
          (922, 33, 359, '2018-03-09 00:00:00', '12:50:14', 2, 1, 0),
          (970, 33, 350, '2018-03-11 00:00:00', '11:25:00', 1, 1, 0),
          (968, 33, 260, '2018-03-12 00:00:00', '7:00:00', 1, 1, 0),
          (962, 33, 348, '2018-03-12 00:00:00', '7:00:00', 1, 1, 0),
          (969, 33, 348, '2018-03-12 00:00:00', '9:00:00', 2, 1, 0),
          (951, 33, 350, '2018-03-12 00:00:00', '8:54:07', 1, 1, 0),
          (953, 33, 350, '2018-03-12 00:00:00', '10:31:29', 1, 1, 0),
          (954, 33, 350, '2018-03-12 00:00:00', '10:34:18', 1, 1, 0),
          (950, 33, 350, '2018-03-12 00:00:00', '8:21:11', 2, 1, 0),
          (952, 33, 350, '2018-03-12 00:00:00', '9:02:26', 2, 1, 0),
          (955, 33, 359, '2018-03-12 00:00:00', '10:37:26', 1, 1, 0),
          (959, 33, 206, '2018-03-13 00:00:00', '1:01:38', 1, 1, 0),
          (961, 33, 206, '2018-03-13 00:00:00', '14:00:51', 1, 1, 0),
          (975, 33, 295, '2018-03-13 00:00:00', '7:00:00', 1, 1, 0),
          (976, 33, 295, '2018-03-13 00:00:00', '8:00:00', 2, 1, 0),
          (957, 33, 350, '2018-03-13 00:00:00', '11:26:52', 1, 1, 0),
          (958, 33, 359, '2018-03-13 00:00:00', '1:00:12', 1, 1, 0),
          (965, 33, 359, '2018-03-13 00:00:00', '21:00:00', 1, 1, 0),
          (960, 33, 359, '2018-03-13 00:00:00', '13:14:14', 2, 1, 0),
          (967, 33, 359, '2018-03-13 00:00:00', '22:00:00', 2, 1, 0),
          (964, 33, 350, '2018-03-14 00:00:00', '14:34:36', 2, 1, 0),
          (963, 33, 359, '2018-03-14 00:00:00', '13:45:25', 1, 1, 0)
      ;
  </code></pre>
  
  <p>Here is the query I used to return the data above:</p>
  
  <pre><code>SELECT  AttendanceID   , DepartmentID   , EmployeeID   , AttendanceDate   , AttendanceTime   , AttendanceTypeID   , AttendanceCodeID   , Submitted
  FROM tblAttendance
  WHERE AttendanceDate BETWEEN @StartDate AND @EndDate
  AND AttendanceTypeID IN (1,2)  -- 1 = in, and 2 = out
  AND AttendanceCodeID = 1
  ORDER BY
  AttendanceDate
  ,AttendanceTime
  </code></pre>
  
  <p><br>
  The tables added below show the steps that are needed to get the end results.
  First a data example:</p>
  
  <pre>
  +------------+----------------+----------------+------------------+
  | EmployeeID | AttendanceDate | AttendanceTime | AttendanceTypeID |
  +------------+----------------+----------------+------------------+
  | 350        | 3/6/18         | 2:32:36        |        1         |
  | 350        | 3/6/18         | 2:48:54        |        1         |
  | 350        | 3/6/18         | 2:49:11        |        1         |
  | 350        | 3/6/18         | 2:52:19        |        1         |
  | 350        | 3/6/18         | 13:32:00       |        1         |
  | 350        | 3/6/18         | 13:53:00       |        1         |
  | 350        | 3/6/18         | 13:35:00       |        2         |
  | 350        | 3/6/18         | 14:06:00       |        2         |
  | 350        | 3/7/18         | 14:36:40       |        1         |
  | 350        | 3/7/18         | 17:00:00       |        2         |
  | 350        | 3/8/18         | 11:42:57       |        1         |
  | 350        | 3/9/18         | 10:43:59       |        1         |
  | 350        | 3/9/18         | 10:44:22       |        1         |
  | 350        | 3/9/18         | 12:44:48       |        1         |
  | 350        | 3/9/18         | 15:18:17       |        1         |
  | 350        | 3/9/18         | 12:50:14       |        2         |
  | 350        | 3/9/18         | 10:43:59       |        1         |
  | 350        | 3/9/18         | 10:44:22       |        1         |
  | 350        | 3/9/18         | 12:44:48       |        1         |
  | 350        | 3/9/18         | 15:18:17       |        1         |
  | 350        | 3/9/18         | 12:50:14       |        2         |
  +------------+----------------+----------------+------------------+
  </pre>
  
  <p>Second Pair up the matching data in out pairs, and get there time difference.</p>
  
  <pre>
  +------------+----------------+------------------+------------------+
  | EmployeeID | AttendanceDate | AttendanceTime   | Pair Total       |
  +------------+----------------+------------------+------------------+
  | 350        | 3/6/18         |13:35:00 -13:32:00|     0:03:00      |
  | 350        | 3/6/18         |14:06:00 -13:53:00|     0:13:00      |
  | 350        | 3/7/18         |17:00:00 -14:36:40|     2:23:20      |
  | 350        | 3/9/18         |12:44:48 -12:44:48|     0:05:26      |
  +------------+----------------+------------------+------------------+
  </pre>
  
  <p>Third the compiled data for the employee during that week.
  <pre>
  +------------+-------------------+----------------------+
  | EmployeeID | AttendanceWeek    | AttendanceTime Total | 
  +------------+-------------------+----------------------+
  | 350        | 3/4/18 to 3/10/18 |     2:44:46          |
  +------------+-------------------+----------------------+
  </pre><br>
  Hope that this helps narrow the focus.</p>
  
  <p>Thank you.</p>