title: Microsoft SQL SERVER : GROUP rows by 30 minutes according to first different row (rows can have different days and time)
body: <p>I have a transaction table with different columns for the sake of the example I only keep the related ones to question.</p>
  
  <p>My table: transaction</p>
  
  <pre><code>id  TransactionTime             idClient 
  ---------------------------------------------
  1   2019-05-29 09:37:30.000     1531201523215
  1   2019-05-29 09:37:30.000     1531201523215
  2   2019-05-29 10:20:06.000     1531201523215
  3   2019-05-29 10:45:19.000     1531201523215
  4   2019-05-29 10:50:19.000     1531201523215
  5   2019-05-29 10:51:43.000     1531201523215
  6   2019-05-29 10:57:36.000     1531201523215
  6   2019-05-29 10:57:36.000     1531201523215
  7   2019-05-30 10:07:14.000     1531201523215
  8   2019-05-30 10:37:12.000     1531201523215
  9   2019-05-30 10:38:11.000     1531201523215
  10  2019-05-30 10:46:17.000     1531201523215
  </code></pre>
  
  <p>What I want to achieve is Group rows within 30 minutes of each other, the desired output would be</p>
  
  <pre><code>id  TransactionTime             idClient            Count
  ----------------------------------------------------------
  1   2019-05-29 09:37:30.000     1531201523215       2  
  2   2019-05-29 10:20:06.000     1531201523215       2
  4   2019-05-29 10:50:19.000     1531201523215       4
  7   2019-05-30 10:07:14.000     1531201523215       2      
  9   2019-05-30 10:38:11.000     1531201523215       2
  </code></pre>
  
  <p>I tried using : </p>
  
  <pre><code>SELECT ROW_NUMBER() OVER (
          PARTITION BY tl.idClient
          ,(dateadd(minute, (datediff(second, 0, tl.TransactionTime) / 60 / 30) * 30, 0)) ORDER BY tl.idClient
              ,tl.TransactionTime
          ) AS filter_row
  </code></pre>
  
  <p>But I couldn't achieve what I wanted ... Been stuck for 4  days now... Please Help.</p>