SQL Server find each occurrence in table when it passes certain value

Question

I have some data in table.

<table>
<tr>
<td>id</td>
<td>date&nbsp;</td>
<td>value</td>
</tr>
<tr>
<td>1</td>
<td>1/1/2018 12:15&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</td>
<td>90</td>
</tr>
<tr>
<td>2</td>
<td>1/1/2018 12:16</td>
<td>89</td>
</tr>
<tr>
<td>3</td>
<td>1/1/2018 12:17</td>
<td>88</td>
</tr>
<tr>
<td>4</td>
<td>1/1/2018 12:18</td>
<td>91</td>
</tr>
<tr>
<td>5</td>
<td>1/1/2018 12:19</td>
<td>92</td>
</tr>
<tr>
<td>6</td>
<td>1/1/2018 12:20</td>
<td>91</td>
</tr>
<tr>
<td>7</td>
<td>1/1/2018 12:21</td>
<td>89</td>
</tr>
<tr>
<td>8</td>
<td>1/1/2018 12:22</td>
<td>90</td>
</tr>
<tr>
<td>9</td>
<td>1/1/2018 12:23</td>
<td>91</td>
</tr>
<tr>
<td>10</td>
<td>1/1/2018 12:24</td>
<td>85</td>
</tr>
</table>

what I need is to find every time a value goes below 90 and then assign them groups accordingly. Once it goes below 90, maintain the same group till it reaches 90 or goes above 90. Then change the groupId and so on. So in the above instance

<table>
    <tr>
    <td>id</td>
    <td>date&nbsp;</td>
    <td>value</td>
    <td>goingBelow90</td>
    <td>group</td>
    </tr>
    <tr>
    <td>1</td>
    <td>1/1/2018 12:15&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</td>
    <td>90</td>
    <td>N</td>
    <td>1</td>
    </tr>
    <tr>
    <td>2</td>
    <td>1/1/2018 12:16</td>
    <td>89</td>
     <td>Y</td>
      <td>2</td>
    </tr>
    <tr>
    <td>3</td>
    <td>1/1/2018 12:17</td>
    <td>88</td>
     <td>Y</td>
      <td>2</td>
    </tr>
    <tr>
    <td>4</td>
    <td>1/1/2018 12:18</td>
    <td>91</td>
     <td>N</td>
      <td>3</td>
    </tr>
    <tr>
    <td>5</td>
    <td>1/1/2018 12:19</td>
    <td>92</td>
     <td>N</td>
      <td>3</td>
    </tr>
    <tr>
    <td>6</td>
    <td>1/1/2018 12:20</td>
    <td>91</td>
     <td>N</td>
      <td>3</td>
    </tr>
    <tr>
    <td>7</td>
    <td>1/1/2018 12:21</td>
    <td>89</td>
     <td>Y</td>
      <td>4</td>
    </tr>
    <tr>
    <td>8</td>
    <td>1/1/2018 12:22</td>
    <td>90</td>
     <td>N</td>
      <td>5</td>
    </tr>
    <tr>
    <td>9</td>
    <td>1/1/2018 12:23</td>
    <td>91</td>
     <td>N</td>
      <td>5</td>
    </tr>
    <tr>
    <td>10</td>
    <td>1/1/2018 12:24</td>
    <td>85</td>
     <td>Y</td>
      <td>6</td>
    </tr>
    </table>

After jumping through hoops and hoops I am able to achieve that, but it's 0% efficient. Query while fetching 10k records and assigning groups takes about 7 minutes which is not acceptable. Please let me know

Answer 1

The group value would seem to be the sum of the number of times the value exceeds 90, cumulatively. That is easy to express in SQL:

select t.*,
       sum(case when value >= 90 then 1 else 0 end) over (order by date) as grp
from t;