How to find first positive value and third consecutive positive values in SQL?
<p>I have tried using ROW_NUMBER but haven't quite got it. Any ideas on the best way to achieve this.</p>

<p>I am looking to find:</p>

<pre><code>- What month did they first cash flow.

- What month did they average 3 months cash flow.
</code></pre>

<p><strong>Sample Data</strong>:
Office ,Balance &amp; Year month  </p>

<pre><code>  ------------------------------
  |  Office | Balance |  YrMo  |
  |   12    |  111    | 201510 |
  |   12    |  222    | 201511 |
  |   12    | -444    | 201512 |
  |   12    | -777    | 201601 |
  |   12    |  555    | 201602 |
  |   12    |  666    | 201603 |
  |   12    | -888    | 201604 |
  |   12    |  777    | 201605 |
  |   40    | -555    | 201510 |
  |   40    | -200    | 201511 |
  |   40    |  0      | 201512 |
  |   40    |  100    | 201601 |
  |   40    | -555    | 201602 |
  |   40    |  666    | 201603 |
  |   40    |  777    | 201604 |
  |   40    |  888    | 201605 |
  |   40    |  999    | 201606 |
</code></pre>

<p>The first Positive Balances would be:</p>

<pre><code>-office 12 , Balance 111 , YrMo 201510
-office 40 , Balance 100 , YrMo 201601
</code></pre>

<p>The first month the office averaged 3 positive balance:</p>

<pre><code>-office 40 , Balance 999 , YrMo 201606
</code></pre>

<p>Here is the #test table script:</p>

<pre><code>IF OBJECT_ID('tempdb..#test') IS NOT NULL
DROP TABLE #test
GO

CREATE TABLE #test (office INT , Balance INT, YrMo INT ) ;

INSERT  INTO #test VALUES  (12 ,  111 , 201510) ;
INSERT  INTO #test VALUES  (12 ,  222 , 201511) ;
INSERT  INTO #test VALUES  (12 , -444 , 201512) ;
INSERT  INTO #test VALUES  (12 , -777 , 201601) ;
INSERT  INTO #test VALUES  (12 ,  555 , 201602) ;
INSERT  INTO #test VALUES  (12 ,  666 , 201603) ;
INSERT  INTO #test VALUES  (12 , -888 , 201604) ;
INSERT  INTO #test VALUES  (12 ,  777 , 201605) ;
INSERT  INTO #test VALUES  (40 , -555 , 201510) ;
INSERT  INTO #test VALUES  (40 , -200 , 201511) ;
INSERT  INTO #test VALUES  (40 ,  0   , 201512) ;
INSERT  INTO #test VALUES  (40 , 100  , 201601) ;
INSERT  INTO #test VALUES  (40 , -555 , 201602) ;
INSERT  INTO #test VALUES  (40 , 666  , 201603) ;
INSERT  INTO #test VALUES  (40,  777  , 201604) ;
INSERT  INTO #test VALUES  (40 , 888  , 201605) ;
INSERT  INTO #test VALUES  (40 , 999  , 201606) ;
</code></pre>

<p>Thanks in advance</p>