title: Flexible running window to count entrys within timerange
body: <p>I have some devices in the field, sending data by GSM by losing connection from time to time. As I have limited disk space, I tend to loose some data in the periods without connection so I like to evaluate the amount of pending data to get some insight of the situation.</p>
  
  <p>If I reduces the table from my server to the columns I need, it looks like this:</p>
  
  <pre><code>Declare @table  as table( 
  timestamp datetime, 
  lastchanged datetime )
  INSERT INTO @table
  VALUES 
  ('2019-04-01 12:27:23', '2019-04-01 12:26:17'),
  ('2019-04-01 12:27:23', '2019-04-01 12:25:47'),
  ('2019-04-01 12:27:23', '2019-04-01 12:26:17'),
  ('2019-04-01 12:27:23', '2019-04-01 12:26:03'),
  ('2019-04-01 12:27:23', '2019-04-01 12:26:20'),
  ('2019-04-01 12:28:23', '2019-04-01 12:25:52'),
  ('2019-04-01 12:28:23', '2019-04-01 12:26:22'),
  ('2019-04-01 12:28:23', '2019-04-01 12:26:18'),
  ('2019-04-01 12:28:23', '2019-04-01 12:25:54'),
  ('2019-04-01 12:29:23', '2019-04-01 12:25:47'),
  ('2019-04-01 12:29:23', '2019-04-01 12:26:17'),
  ('2019-04-01 12:29:23', '2019-04-01 12:25:47'),
  ('2019-04-01 12:29:23', '2019-04-01 12:25:45'),
  ('2019-04-01 12:30:23', '2019-04-01 12:26:17'),
  ('2019-04-01 12:30:23', '2019-04-01 12:25:47'),
  ('2019-04-01 12:30:23', '2019-04-01 12:26:17'),
  ('2019-04-01 12:31:23', '2019-04-01 12:26:03'),
  ('2019-04-01 12:31:23', '2019-04-01 12:26:20'),
  ('2019-04-01 12:31:23', '2019-04-01 12:25:52'),
  ('2019-04-01 12:31:23', '2019-04-01 12:26:22'),
  ('2019-04-01 12:31:23', '2019-04-01 12:26:18'),
  ('2019-04-01 12:31:23', '2019-04-01 12:25:54'),
  ('2019-04-01 12:32:23', '2019-04-01 12:25:47'),
  ('2019-04-01 12:32:23', '2019-04-01 12:26:17'),
  ('2019-04-01 12:32:23', '2019-04-01 12:25:47'),
  ('2019-04-01 12:32:23', '2019-04-01 12:25:45');
  </code></pre>
  
  <p>as the value of timestamp (first column) is createt at the server and lastchanged (second column) is a timestamp from the device, the differenc obviously is the delay by the sending process.</p>
  
  <p>From every sensor from my device I get a separate entry, so I just need to count rows to get an idea of how many datapoints were stored on the device in a specific range of time (this varies, depending on the behavior of the device).</p>
  
  <p>Now I like to group by timestamp to evaluate for each of this timestamps, how many datasets have been already created, but did not reach the server so fare.</p>
  
  <p>So, for every timestamp I would like to know the amount of rows in the dataset, which have a timestamp in the future but a lastchanged in the past.</p>
  
  <p>As my dataset is very big, I would like to avoid any fetching.</p>
  
  <p>And yes, this should not be a in-time analysis, I just need it to understand the behavior of the device and adjust some parameters. </p>
  
  <p>I already tryed this:</p>
  
  <pre><code>SELECT
      A.timestamp,
      COUNT(case when (A.timestamp &lt; B.timestamp AND A.timestamp &gt; B.lastchanged  ) then 1 else null end) AS CountPending
  FROM @table A, @table B
  GROUP BY A.timestamp
  ORDER BY A.timestamp
  </code></pre>
  
  <p>and think that CTEs and subquerys will not help as I need to do the filtering and counting in one step. And as I understand, classical running windows in SQL just need a fixed amount of entry for the window-range, but in my dataset, this number is not constant at all.</p>
  
  <p>Applied on the example from above, the result is just wrong:  </p>
  
  <pre><code>2019-04-01 12:27:23.000 105
  2019-04-01 12:28:23.000 68
  2019-04-01 12:29:23.000 52
  2019-04-01 12:30:23.000 30
  2019-04-01 12:31:23.000 24
  2019-04-01 12:32:23.000 0
  </code></pre>