title: How to calculate time worked for time values in the same column
body: <p>I have the following data where all the clock ins and clock outs are in the same column. Following are the areas that I have noticed the data going weird:</p>
  
  <ol>
  <li>A person who clocks in, forgets to clock out and vice-a-versa </li>
  <li>The data is spread over a large period of time (2-3 years). </li>
  <li>There are duplicates of the same clock-in/clock-out.</li>
  <li>A person can work with clockin-Clockout-Break-Clockin-Clockout values. </li>
  <li>A person could start work at 10:00 P.M and end work at 8:00 A.M The next
  morning.</li>
  </ol>
  
  <p>I have the following data for your consideration.  Please also look at the SQL Fiddle for this problem. <a href="http://sqlfiddle.com/#!3/726b4/1/0" rel="nofollow">SQL Fiddle</a>
  *<em>EDIT: Added new data to SQLFiddle.</em>*It only allows 8k characters. Sad.
      IF OBJECT_ID('tempdb..#TempTable') IS NOT NULL DROP TABLE #TempTable
      CREATE TABLE #TempTable (
          EmployeeName varchar(40),
          EmployeeID INT,
          PostEditPunchValue DATETIME,
          PunchDate DATETIME,
          FormatTime varchar(20),
      )</p>
  
  <pre><code>INSERT INTO #TempTable (EmployeeName,EmployeeID, PostEditPunchValue,PunchDate,FormatTime) VALUES 
  ('FNU, LNU M', '12345', '2007-01-01 00:00:00.000', '2007-01-01 00:00:00.000', '12:00 AM' ),
  ('FNU, LNU M', '12345', '2008-01-01 00:00:00.000', '2008-01-01 00:00:00.000', '12:00 AM' ),
  ('FNU, LNU M', '12345', '2007-01-02 15:30:00.000', '2007-01-02 00:00:00.000', ' 3:30 PM' ),
  ('FNU, LNU M', '12345', '2007-01-02 08:17:00.000', '2007-01-02 00:00:00.000', ' 8:17 AM' ),
  ('FNU, LNU M', '12345', '2007-01-02 08:17:00.000', '2007-01-02 00:00:00.000', ' 8:17 AM' ),
  ('FNU, LNU M', '12345', '2009-01-02 08:00:00.000', '2009-01-02 00:00:00.000', ' 8:00 AM' ),
  ('FNU, LNU M', '12345', '2009-01-02 16:25:00.000', '2009-01-02 00:00:00.000', ' 4:25 PM' ),
  ('FNU, LNU M', '12345', '2009-01-02 16:25:00.000', '2009-01-02 00:00:00.000', ' 4:25 PM' ),
  ('FNU, LNU M', '12345', '2007-01-03 00:00:00.000', '2007-01-03 00:00:00.000', '12:00 AM' ),
  ('FNU, LNU M', '12345', '2009-01-03 07:50:00.000', '2009-01-03 00:00:00.000', ' 7:50 AM' ),
  ('FNU, LNU M', '12345', '2009-01-03 07:50:00.000', '2009-01-03 00:00:00.000', ' 7:50 AM' ),
  ('FNU, LNU M', '12345', '2009-01-03 16:01:00.000', '2009-01-03 00:00:00.000', ' 4:01 PM' )
  </code></pre>
  
  <p>And below is the code that I came up with. It works ok as long as the hours are not > 8 but filters out some legit records too where a person might have worked 9-10 hours on a given shift. Can someone suggest whats wrong and what I need to do to fix it?</p>
  
  <pre><code>;WITH 
  CTE2T AS
  (
      SELECT DISTINCT EmployeeName, PostEditPunchValue from #TempTable
      WHERE PostEditPunchValue BETWEEN PostEditPunchValue AND DATEADD(HH,23,PostEditPunchValue) AND PostEditPunchValue &lt;&gt; PunchDate
      and EmployeeName = 'FNU, LNU M' 
  ),
  CTE1 AS
  (
      SELECT  
           T1.EmployeeName
          ,t1.PostEditPunchValue StartDate
          ,min(t2.PostEditPunchValue) EndDate
      FROM 
          CTE2T AS t1
          LEFT JOIN CTE2T AS t2 ON (t1.EmployeeName = t2.EmployeeName
                                    and  t1.PostEditPunchValue &lt; t2.PostEditPunchValue)
      WHERE 
          t1.EmployeeName = 'FNU, LNU M' 
      group by
          t1.PostEditPunchValue
      ,   t1.EmployeeName
  ),
  CTE2 AS
  ( 
      SELECT 
          EmployeeName, StartDate, EndDate,
          DATEDIFF(HH,StartDate,EndDate) 'Hours' 
      from 
          cte1 
      group by 
          EmployeeName, StartDate, EndDate
  )
  SELECT * FROM cte2 where Hours &gt; 8 or Hours = 0
  </code></pre>
  
  <p>The output that I get (from the code) is as below:</p>
  
  <pre><code>EmployeeName                   StartDate             EndDate        Hours   Flag
  
  
  ANDERSON, LETICIA G|    2007-01-02 08:17:00.000|    2007-01-02 15:30:00.000| 7| 
  ANDERSON, LETICIA G|    2009-01-02 08:00:00.000|    2009-01-02 16:25:00.000| 8| 
  ANDERSON, LETICIA G|    2009-01-03 07:50:00.000|    2009-01-03 16:01:00.000| 9| Y
  </code></pre>