title: SQL query to match rows for a sample attendance management report
body: <p>I've an access log table for attendance management system like this:   </p>
  
  <pre><code>    ID      LogDate              EmployeeId Direction
      242     2014-05-02 07:59:20.000 2565    in
      1907    2014-05-02 19:00:01.000 2565    out
      3648    2014-05-03 08:08:03.000 2565    in
      5611    2014-05-03 19:32:37.000 2565    out
      5612    2014-05-03 19:33:44.000 2565    out
      7315    2014-05-05 08:01:01.000 2565    in
      9301    2014-05-05 19:37:48.000 2565    out
      10857   2014-05-06 08:29:44.000 2565    in
      12327   2014-05-06 18:51:56.000 2565    out
      14331   2014-05-07 08:44:03.000 2565    in
      16084   2014-05-07 19:09:07.000 2565    out
      17608   2014-05-08 07:58:13.000 2565    in
      21155   2014-05-09 08:12:26.000 2565    in
      22673   2014-05-09 18:42:55.000 2565    out
      24550   2014-05-10 08:13:09.000 2565    in
      26455   2014-05-10 19:02:28.000 2565    out
      30013   2014-05-12 19:07:53.000 2565    out
      31083   2014-05-13 08:00:27.000 2565    in
      31087   2014-05-13 08:02:39.000 2565    in
      33084   2014-05-13 18:58:09.000 2565    out
      35201   2014-05-14 08:31:13.000 2565    in
      37238   2014-05-14 19:10:03.000 2565    out
      39225   2014-05-15 08:47:44.000 2565    in
      40636   2014-05-15 18:59:47.000 2565    out
      42969   2014-05-16 09:58:17.000 2565    in
      42970   2014-05-16 09:58:24.000 2565    in
      42974   2014-05-16 09:58:51.000 2565    in
      44114   2014-05-16 18:49:39.000 2565    out
      44116   2014-05-16 18:50:43.000 2565    out
      46284   2014-05-17 09:07:03.000 2565    in
      47323   2014-05-17 17:56:57.000 2565    out
      48719   2014-05-19 08:05:28.000 2565    in
      49919   2014-05-19 18:52:35.000 2565    out
      51188   2014-05-20 09:02:07.000 2565    in
      52348   2014-05-20 19:01:05.000 2565    out
      54231   2014-05-21 09:35:49.000 2565    in
      55896   2014-05-21 19:57:48.000 2565    out
  </code></pre>
  
  <p>I have the following conditions to get the duration for a sample 12 hours shift log details:  </p>
  
  <ol>
  <li>Ideal case: Wherein each In and corresponding Out (directions) to be matched and the duration to be calculated by deducting Out-time from In-time</li>
  <li>In, No-Out, In: Wherein, there is no Out record for the In and it is followed by another In. Display NULL and hence no duration can be calculated, since an Out is missing, let us put 12:00:00 (shift duration).</li>
  <li>In, Out, Out: The duration should be equal to earliest Out minus latest In.</li>
  <li>In, Out, In, Out: Duration = First out - First In + second out - second in.</li>
  </ol>
  
  <p>Note: This is for 12 hour shift and note that when there is an in on 20-july-2014 19:00:00 hours, the out might happen on 21-july-2014 07:05:00 and the duration is 12:05:00 hours.</p>
  
  <p>I've written Sqls to get the result, but I'm unable to handle certain conditions:
  Here is the <a href="http://sqlfiddle.com/#!3/2f0f0/1" rel="nofollow">test data</a>:</p>
  
  <pre><code>IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[tmpAccessLogTbl]') AND type in (N'U'))
  DROP TABLE [dbo].[tmpAccessLogTbl]
  GO
  
  CREATE TABLE  tmpAccessLogTbl   (
      [ID] [int] NOT NULL PRIMARY KEY,
      [LogDate] [datetime] NOT NULL,
      [EmployeeId] [int] NOT NULL,
      [Direction] [nvarchar](3) NULL
  ) 
  ;
  
  
  
  INSERT INTO tmpAccessLogTbl ([ID], [LogDate], [EmployeeId], [Direction])
  SELECT 242, '20140502 07:59:20.000', 2565, N'in' UNION ALL
  SELECT 1907, '20140502 19:00:01.000', 2565, N'out' UNION ALL
  SELECT 3648, '20140503 08:08:03.000', 2565, N'in' UNION ALL
  SELECT 5611, '20140503 19:32:37.000', 2565, N'out' UNION ALL
  SELECT 5612, '20140503 19:33:44.000', 2565, N'out' UNION ALL
  SELECT 7315, '20140505 08:01:01.000', 2565, N'in' UNION ALL
  SELECT 9301, '20140505 19:37:48.000', 2565, N'out' UNION ALL
  SELECT 10857, '20140506 08:29:44.000', 2565, N'in' UNION ALL
  SELECT 12327, '20140506 18:51:56.000', 2565, N'out' UNION ALL
  SELECT 14331, '20140507 08:44:03.000', 2565, N'in' UNION ALL
  SELECT 16084, '20140507 19:09:07.000', 2565, N'out' UNION ALL
  SELECT 17608, '20140508 07:58:13.000', 2565, N'in' UNION ALL
  SELECT 21155, '20140509 08:12:26.000', 2565, N'in' UNION ALL
  SELECT 22673, '20140509 18:42:55.000', 2565, N'out' UNION ALL
  SELECT 24550, '20140510 08:13:09.000', 2565, N'in' UNION ALL
  SELECT 26455, '20140510 19:02:28.000', 2565, N'out' UNION ALL
  SELECT 30013, '20140512 19:07:53.000', 2565, N'out' UNION ALL
  SELECT 31083, '20140513 08:00:27.000', 2565, N'in' UNION ALL
  SELECT 31087, '20140513 08:02:39.000', 2565, N'in' UNION ALL
  SELECT 33084, '20140513 18:58:09.000', 2565, N'out' UNION ALL
  SELECT 35201, '20140514 08:31:13.000', 2565, N'in' UNION ALL
  SELECT 37238, '20140514 19:10:03.000', 2565, N'out' UNION ALL
  SELECT 39225, '20140515 08:47:44.000', 2565, N'in' UNION ALL
  SELECT 40636, '20140515 18:59:47.000', 2565, N'out' UNION ALL
  SELECT 42969, '20140516 09:58:17.000', 2565, N'in' UNION ALL
  SELECT 42970, '20140516 09:58:24.000', 2565, N'in' UNION ALL
  SELECT 42974, '20140516 09:58:51.000', 2565, N'in' UNION ALL
  SELECT 44114, '20140516 18:49:39.000', 2565, N'out' UNION ALL
  SELECT 44116, '20140516 18:50:43.000', 2565, N'out' UNION ALL
  SELECT 46284, '20140517 09:07:03.000', 2565, N'in' UNION ALL
  SELECT 47323, '20140517 17:56:57.000', 2565, N'out'
  ;
  
  
  SELECT t.EmployeeId,
         t.AttendanceDate,
         t.InTime,
         CASE 
              WHEN DATEDIFF(HOUR, t.InTime, t.OutTime) &gt; 23 THEN NULL
              ELSE t.OutTime
         END  AS OutTime,
         CAST (DATEDIFF(HOUR, t.InTime, t.OutTime) AS VARCHAR) + ':' +  CAST(DATEDIFF(minute, t.InTime, t.OutTime) %60  AS VARCHAR) AS Duration
  FROM   (
             SELECT i.EmployeeId,
                    CONVERT(VARCHAR, i.LogDate, 106) AS AttendanceDate,
                    i.LogDate  AS InTime,
                    (
                        SELECT MIN(o.LogDate) AS OutTime
                        FROM   tmpAccessLogTbl o
                        WHERE  o.Direction = 'Out'
                               AND o.EmployeeId = i.EmployeeId
                               AND o.LogDate &gt; i.LogDate
                    )          AS OutTime
             FROM   tmpAccessLogTbl       i
             WHERE  i.Direction = 'In'
         )    AS t
  ORDER BY
         t.EmployeeId,
         t.AttendanceDate    
  </code></pre>
  
  <p>My results are as follows from this Sql:</p>
  
  <pre><code>EmployeeId  AttendanceDate     InTime       OutTime                 Duration
  2565    02 May 2014 2014-05-02 07:59:20.000 2014-05-02 19:00:01.000 12:1
  2565    03 May 2014 2014-05-03 08:08:03.000 2014-05-03 19:32:37.000 11:24
  2565    05 May 2014 2014-05-05 08:01:01.000 2014-05-05 19:37:48.000 11:36
  2565    06 May 2014 2014-05-06 08:29:44.000 2014-05-06 18:51:56.000 10:22
  2565    07 May 2014 2014-05-07 08:44:03.000 2014-05-07 19:09:07.000 11:25
  2565    08 May 2014 2014-05-08 07:58:13.000 NULL                    35:44
  2565    09 May 2014 2014-05-09 08:12:26.000 2014-05-09 18:42:55.000 10:30
  2565    10 May 2014 2014-05-10 08:13:09.000 2014-05-10 19:02:28.000 11:49
  2565    13 May 2014 2014-05-13 08:00:27.000 2014-05-13 18:58:09.000 10:58
  2565    13 May 2014 2014-05-13 08:02:39.000 2014-05-13 18:58:09.000 10:56
  2565    14 May 2014 2014-05-14 08:31:13.000 2014-05-14 19:10:03.000 11:39
  2565    15 May 2014 2014-05-15 08:47:44.000 2014-05-15 18:59:47.000 10:12
  2565    16 May 2014 2014-05-16 09:58:17.000 2014-05-16 18:49:39.000 9:51
  2565    16 May 2014 2014-05-16 09:58:24.000 2014-05-16 18:49:39.000 9:51
  2565    16 May 2014 2014-05-16 09:58:51.000 2014-05-16 18:49:39.000 9:51
  2565    17 May 2014 2014-05-17 09:07:03.000 2014-05-17 17:56:57.000 8:49
  </code></pre>
  
  <p>To summarize, I will put the the results of my above Sql and expected results here:</p>
  
  <pre><code>                         -- My Current Query  --     --  Expected Results --
  Sl  EmpID     Day       InTime  OutTime Duration    InTime  OutTime Duration
  1   2565    02-May-2014 7:59    19:00   11:00:41    7:59    19:00   11:00:41
  2   2565    03-May-2014 8:08    19:32   11:24:34    8:08    19:32   11:24:34
  3   2565    05 May 2014 8:01    19:37   11:36:47    8:01    19:37   11:36:47
  4   2565    06 May 2014 8:29    18:51   10:22:12    8:29    18:51   10:22:12
  5   2565    07 May 2014 8:44    19:09   10:25:04    8:44    19:09   10:25:04
  6   2565    08 May 2014 7:58    NULL    NULL        7:58            12:00:00
  7   2565    09 May 2014 8:12    18:42   10:30:29    8:12    18:42   10:30:29
  8   2565    10 May 2014 8:13    19:02   10:49:19    8:13    19:02   10:49:19
  9   2565    13 May 2014 8:00    18:58   10:57:42            
  10  2565    13 May 2014 8:02    18:58   10:55:30    8:02    18:58   10:55:30
  11  2565    14 May 2014 8:31    19:10   10:38:50    8:31    19:10   10:38:50
  12  2565    15 May 2014 8:47    18:59   10:12:03    8:47    18:59   10:12:03
  13  2565    16 May 2014 9:58    18:49   8:51:22         
  14  2565    16 May 2014 9:58    18:49   8:51:15         
  15  2565    16 May 2014 9:58    18:49   8:50:48     9:58    18:49   8:50:48
  16  2565    17 May 2014 9:07    17:56   8:49:54     9:07    17:56   8:49:54
  </code></pre>
  
  <p>In the expected result, whenever there is no out time to corresponding in, the duration should be 12:00:00.</p>
  
  <p>Please help me to achieve this.
  Thanks in advance.</p>