title: Duration of items differences grouped and possible null in sql server
body: <p>I've been on this for the last day, cannot get my head around the query, I can use a while loop and then create a new table but would really help if I can get this done in a queries and sub queries.</p>
  <p>I got table data as per below:</p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th style="text-align: center;">Primkey</th>
  <th style="text-align: center;">DateTimeCreated</th>
  <th style="text-align: center;">DateTimeProduced</th>
  <th style="text-align: center;">AreaID</th>
  <th style="text-align: center;">StationDescription</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td style="text-align: center;">1</td>
  <td style="text-align: center;">2022-07-26 11:39:33.470</td>
  <td style="text-align: center;">2022-07-26 11:39:33.470</td>
  <td style="text-align: center;">1</td>
  <td style="text-align: center;">Station #10</td>
  </tr>
  <tr>
  <td style="text-align: center;">2</td>
  <td style="text-align: center;">2022-07-26 11:40:01.817</td>
  <td style="text-align: center;">2022-07-26 11:40:01.817</td>
  <td style="text-align: center;">1</td>
  <td style="text-align: center;">Station #10</td>
  </tr>
  <tr>
  <td style="text-align: center;">4</td>
  <td style="text-align: center;">2022-07-26 12:14:26.653</td>
  <td style="text-align: center;">2022-07-26 12:14:26.653</td>
  <td style="text-align: center;">2</td>
  <td style="text-align: center;">Station #12</td>
  </tr>
  <tr>
  <td style="text-align: center;">5</td>
  <td style="text-align: center;">2022-07-26 12:18:47.417</td>
  <td style="text-align: center;">2022-07-26 12:18:47.417</td>
  <td style="text-align: center;">2</td>
  <td style="text-align: center;">Station #12</td>
  </tr>
  <tr>
  <td style="text-align: center;">6</td>
  <td style="text-align: center;">2022-07-26 12:19:16.213</td>
  <td style="text-align: center;">2022-07-26 12:19:16.213</td>
  <td style="text-align: center;">2</td>
  <td style="text-align: center;">Station #12</td>
  </tr>
  </tbody>
  </table>
  </div>
  <p>The result I'm looking for must be grouped by AreaID and StationDescription with duration from start and end from applicable DateTimeProduced. Need to check duration for each station.</p>
  <p>Desired result:</p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th style="text-align: center;">StationID</th>
  <th style="text-align: center;">AreaID</th>
  <th style="text-align: center;">StartDateTime</th>
  <th style="text-align: center;">EndDateTime</th>
  <th style="text-align: center;">DurationSekonds</th>
  <th style="text-align: center;">Status</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td style="text-align: center;">Station #10</td>
  <td style="text-align: center;">1</td>
  <td style="text-align: center;">2022-07-26 11:39:33.470</td>
  <td style="text-align: center;">2022-07-26 11:40:01.817</td>
  <td style="text-align: center;">00:00:28</td>
  <td style="text-align: center;">Completed</td>
  </tr>
  <tr>
  <td style="text-align: center;">Station #12</td>
  <td style="text-align: center;">2</td>
  <td style="text-align: center;">2022-07-26 12:14:26.653</td>
  <td style="text-align: center;">2022-07-26 12:18:47.417</td>
  <td style="text-align: center;">00:04:21</td>
  <td style="text-align: center;">Completed</td>
  </tr>
  <tr>
  <td style="text-align: center;">Station #12</td>
  <td style="text-align: center;">2</td>
  <td style="text-align: center;">2022-07-26 12:19:16.213</td>
  <td style="text-align: center;">null</td>
  <td style="text-align: center;">null</td>
  <td style="text-align: center;">Busy</td>
  </tr>
  </tbody>
  </table>
  </div>
  <p>I've tried numerous grouping and joining, but cannot get my head around this.</p>
  <pre><code>    select t1.StationDescription,t1.AreaID,t1.StartpartinianedRow,t1.DateTimeProduced,t2.DateTimeProduced,LEAD(t2.StartpartinianedRow,1) OVER (Order by t2.AreaID, t2.StationDescription asc)
      from
      (
          select *, ROW_NUMBER() OVER (PARTITION BY AreaID order by DateTimeProduced) as StartpartinianedRow
          FROM tb_ActualProduction) t1
          JOIN (select *, ROW_NUMBER() OVER (PARTITION BY AreaID order by DateTimeProduced) as StartpartinianedRow from tb_ActualProduction) t2 ON
          t1.AreaID = t2.AreaID and
          t1.StartpartinianedRow = t2.StartpartinianedRow - 1         
          where t1.DateTimeProduced &lt; t2.DateTimeProduced
  </code></pre>
  <p>Produces which is not correct:</p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th style="text-align: center;">StationID</th>
  <th style="text-align: center;">AreaID</th>
  <th style="text-align: center;">StartDateTime</th>
  <th style="text-align: center;">EndDateTime</th>
  <th style="text-align: center;">DurationSekonds</th>
  <th style="text-align: center;">Status</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td style="text-align: center;">Station #10</td>
  <td style="text-align: center;">1</td>
  <td style="text-align: center;">2022-07-26 11:39:33.470</td>
  <td style="text-align: center;">2022-07-26 11:40:01.817</td>
  <td style="text-align: center;">28</td>
  <td style="text-align: center;">2</td>
  </tr>
  <tr>
  <td style="text-align: center;">Station #12</td>
  <td style="text-align: center;">2</td>
  <td style="text-align: center;">2022-07-26 12:14:26.653</td>
  <td style="text-align: center;">2022-07-26 12:18:47.417</td>
  <td style="text-align: center;">261</td>
  <td style="text-align: center;">3</td>
  </tr>
  <tr>
  <td style="text-align: center;">Station #12</td>
  <td style="text-align: center;">2</td>
  <td style="text-align: center;">2022-07-26 12:18:47.417</td>
  <td style="text-align: center;">2022-07-26 12:19:16.213</td>
  <td style="text-align: center;">29</td>
  <td style="text-align: center;">NULL</td>
  </tr>
  </tbody>
  </table>
  </div>