title: T-SQL Calculate time between changing status per hour
body: <p><strong>EDIT:</strong> <em>I changed my example and made it more simple. First Quote is how the source table looks like, second quote is how the result should look like.</em></p>
  
  <hr>
  
  <p>Hello everyone,</p>
  
  <p>I have multiple parking that only sends changing states.
  It sends a "1" when a car arrived at the parking, then it doesn't send anything until the car leaves again. At that moment the parking sends a "0". I need to do analysis over a long time, so it would be awesome to see the amount of time per hour or so to not get too many rows (compared by minute).</p>
  
  <p>The data looks like this (as requested I reduce it to parking-ID 10 and just the last record from 19.12. and the records from 20.12.):</p>
  
  <pre><code>+------------+------------------+--------+-------------+
  | Parking-ID | DateTime         | Status | Comment     |
  +------------+------------------+--------+-------------+
  | 10         | 20.12.2019 16:35 | 0      | Car left    |
  +------------+------------------+--------+-------------+
  | 10         | 20.12.2019 08:22 | 1      | Car arrived |
  +------------+------------------+--------+-------------+
  | 10         | 19.12.2019 22:47 | 0      | Car left    |
  +------------+------------------+--------+-------------+
  </code></pre>
  
  <p>Now to not make it too easy for me, next to the "free" and "taken" status there is also a warm status. 1 hour after a car left the parking should be marked as "warm" because some cars have to come and go fast in a few minutes and this time range should be shown as "warm".</p>
  
  <p>To not get too many rows (like for every minute), I would appreciate if it would be possible to get the summary per hour. For my analysis I should be able to see how many hours per day the parking was taken, how many hours it was warm and how many hours it was free.</p>
  
  <p>So the result should look something like this (for Parking-ID 10 and for 20.12.2019):</p>
  
  <pre><code>+------------+------------------+--------+----------+---------+
  | Parking-ID | DateTime         | Status | Duration | Comment |
  +------------+------------------+--------+----------+---------+
  | 10         | 20.12.2019 23:00 | 0      | 1.00     | Free    |
  +------------+------------------+--------+----------+---------+
  | 10         | 20.12.2019 22:00 | 0      | 1.00     | Free    |
  +------------+------------------+--------+----------+---------+
  | 10         | 20.12.2019 21:00 | 0      | 1.00     | Free    |
  +------------+------------------+--------+----------+---------+
  | 10         | 20.12.2019 20:00 | 0      | 1.00     | Free    |
  +------------+------------------+--------+----------+---------+
  | 10         | 20.12.2019 19:00 | 0      | 1.00     | Free    |
  +------------+------------------+--------+----------+---------+
  | 10         | 20.12.2019 18:00 | 0      | 1.00     | Free    |
  +------------+------------------+--------+----------+---------+
  | 10         | 20.12.2019 17:00 | 0      | 0.42     | Free    |
  +------------+------------------+--------+----------+---------+
  | 10         | 20.12.2019 17:00 | 2      | 0.58     | Warm    |
  +------------+------------------+--------+----------+---------+
  | 10         | 20.12.2019 16:00 | 2      | 0.42     | Warm    |
  +------------+------------------+--------+----------+---------+
  | 10         | 20.12.2019 16:00 | 1      | 0.58     | Taken   |
  +------------+------------------+--------+----------+---------+
  | 10         | 20.12.2019 15:00 | 1      | 1.00     | Taken   |
  +------------+------------------+--------+----------+---------+
  | 10         | 20.12.2019 14:00 | 1      | 1.00     | Taken   |
  +------------+------------------+--------+----------+---------+
  | 10         | 20.12.2019 13:00 | 1      | 1.00     | Taken   |
  +------------+------------------+--------+----------+---------+
  | 10         | 20.12.2019 12:00 | 1      | 1.00     | Taken   |
  +------------+------------------+--------+----------+---------+
  | 10         | 20.12.2019 11:00 | 1      | 1.00     | Taken   |
  +------------+------------------+--------+----------+---------+
  | 10         | 20.12.2019 10:00 | 1      | 1.00     | Taken   |
  +------------+------------------+--------+----------+---------+
  | 10         | 20.12.2019 09:00 | 1      | 1.00     | Taken   |
  +------------+------------------+--------+----------+---------+
  | 10         | 20.12.2019 08:00 | 1      | 0.63     | Taken   |
  +------------+------------------+--------+----------+---------+
  | 10         | 20.12.2019 08:00 | 0      | 0.37     | Free    |
  +------------+------------------+--------+----------+---------+
  | 10         | 20.12.2019 07:00 | 0      | 1.00     | Free    |
  +------------+------------------+--------+----------+---------+
  | 10         | 20.12.2019 06:00 | 0      | 1.00     | Free    |
  +------------+------------------+--------+----------+---------+
  | 10         | 20.12.2019 05:00 | 0      | 1.00     | Free    |
  +------------+------------------+--------+----------+---------+
  | 10         | 20.12.2019 04:00 | 0      | 1.00     | Free    |
  +------------+------------------+--------+----------+---------+
  | 10         | 20.12.2019 03:00 | 0      | 1.00     | Free    |
  +------------+------------------+--------+----------+---------+
  | 10         | 20.12.2019 02:00 | 0      | 1.00     | Free    |
  +------------+------------------+--------+----------+---------+
  | 10         | 20.12.2019 01:00 | 0      | 1.00     | Free    |
  +------------+------------------+--------+----------+---------+
  | 10         | 20.12.2019 00:00 | 0      | 1.00     | Free    |
  +------------+------------------+--------+----------+---------+
  </code></pre>
  
  <p>Does someone have a good approach? I already searched and tried but couldn't find a working approach.</p>
  
  <p>Thank you and best regards</p>