title: Iterate a SQL Query through multiple parameters one at a time and union the results
body: <p>I have a query I have written for SQL Server 2012 to take a dynamic list of personnel and determine how long they spend each day in certain areas. When I run the query a person at a time, I get good results. However, if I pass all the parameters within an IN clause then I not only get less results, but some of the values are different and just plain wrong.</p>
  
  <p>This will eventually be run within a report in SSRS, but even running the data straight in SSMS it is showing the same behavior.</p>
  
  <p>The <code>@People</code> is a separate query that gathers a list of the people which were actually in the area that day. <code>@Yesterday</code> and <code>@Today</code> are just variables for dates.</p>
  
  <pre><code>SELECT *
  FROM
      (SELECT 
           AdmitType,
           Time,
           Person,
           Door,
           Direction,
           CASE 
              WHEN Direction LIKE 'In' AND LEAD(Direction, 1) OVER(ORDER BY Time ASC) LIKE 'Out'
                 THEN DATEDIFF (second, Time, LEAD(Time, 1) OVER(ORDER BY Time ASC))
                 ELSE NULL
           END AS 'Duration'
       FROM 
           ActivityLog
       WHERE 
           Door IN ('Room1', 'Room2', 'Room3')
           AND Time BETWEEN @Yesterday AND @Today
           AND Person IN @People) s1
  ORDER BY 
      Person, Door, Time ASC
  </code></pre>
  
  <p>If I run the above query and pass the <code>@People</code> parameter with a value of <code>('Tom', 'Dick', 'Harry')</code> then I will get something like this back for Tom's entries (eliminating some columns and multiple people and doors for clarity):  </p>
  
  <pre><code>Time    Direction    Duration  
  -----------------------------
  7:12:04 IN           922  
  7:27:26 OUT          NULL  
  8:40:37 IN           NULL  
  9:07:04 OUT          NULL  
  </code></pre>
  
  <p>But If I run the query with just Tom listed in the IN clause, I get the following which is correct:  </p>
  
  <pre><code>Time    Direction    Duration  
  -----------------------------
  7:12:04 IN           922  
  7:27:26 OUT          NULL  
  8:40:37 IN           1587  
  9:07:04 OUT          NULL  
  </code></pre>
  
  <p>How can I either fix my query so that it returns the correct information with everyone selected (approximately 25 people usually) or take the parameter and run each person one at a time and union the results?</p>
  
  <p>I have seen a query which passes a list one at a time and unions all the results, but I do not know how to write that in such a way as to accept the parameter input that would be passed from SSRS.</p>
  
  <p>Additional Data Added from Alan Schofield suggestion:
  When I added the PARTITION BY Clause I am now getting data for those previous NULLs, but it is not correct data.</p>
  
  <pre><code>SELECT *
  FROM
      (SELECT 
           AdmitType,
           Time,
           Person,
           Door,
           Direction,
           CASE 
              WHEN Direction LIKE 'In' AND LEAD(Direction, 1) OVER(PARTITION BY Person, Door ORDER BY Time ASC) LIKE 'Out'
                 THEN DATEDIFF (second, Time, LEAD(Time, 1) OVER(ORDER BY Time ASC))
                 ELSE NULL
           END AS 'Duration'
       FROM 
           ActivityLog
       WHERE 
           Door IN ('Room1', 'Room2', 'Room3')
           AND Time BETWEEN @Yesterday AND @Today
           AND Person IN @People) s1
  ORDER BY 
      Person, Door, Time ASC
  </code></pre>
  
  <p>HERE is a larger example data set with the new PARTITION BY clause results:  </p>
  
  <pre><code>Time      Person     Door    Direction    Duration  
  18:07:22  John       Room1   IN           1308  (Correct)
  18:29:10  John       Room1   Out          NULL  
  17:42:18  John       Room3   IN           1406  (Correct)
  18:05:44  John       Room3   Out          NULL
  7:12:04   Tom        Room1   IN           922   (Correct)
  7:27:26   Tom        Room1   Out          NULL
  8:40:37   Tom        Room1   IN           2    (Previously NULL, Now 2, should be 1587 and is when queried alone)
  9:07:04   Tom        Room1   Out          NULL
  9:09:07   Tom        Room1   IN           31   (Previously NULL, Now 31, should be 1256 and is when queried alone)
  9:30:03   Tom        Room1   Out          NULL
  9:38:48   Tom        Room1   IN           48   (Correct)
  9:39:36   Tom        Room1   Out          NULL
  9:39:45   Tom        Room1   Out          NULL
  9:40:36   Tom        Room1   IN           90   (Correct)
  9:42:06   Tom        Room1   Out          NULL
  10:25:0   Tom        Room1   IN           47   (Previously 47, Now 47, Should be 8939 and is when queried alone)
  12:54:08  Tom        Room1   Out          NULL
  14:09:42  Tom        Room1   IN           39   (Correct)
  14:10:21  Tom        Room1   Out          NULL
  14:39:03  Tom        Room1   IN           1316 (Previously NULL, Now 1316, should be 4178 and is when queried alone)
  15:48:41  Tom        Room1   Out          NULL
  15:49:20  Tom        Room1   IN           52   (Previously NULL, Now 52, should be 1542 and is when queried alone)
  16:15:02  Tom        Room1   Out          NULL
  16:22:26  Tom        Room1   IN           292  (Previously 292, Now 292, should be 1504 and is when queried alone)
  16:47:30  Tom        Room1   Out          NULL
  </code></pre>
  
  <p>The data continues a lot more but hopefully this gives a better idea.</p>