Partition rows based on order and data range

Question

What kind of query would support taking into account both the order of my rows and a data range within them when ranking the row? The data should be processed in identity order and I want the range boundaries defines as [Time] > 2 or [Time] < -2.

Edit: In other words, I want to treat the rows as a list and break them into a list of lists where the ParitionId is the index of the list of rows.

Edit 2: I forgot to make it clear that the partition id should increment by one every time the [Time] value falls outside of the desired range. This requirement means we can't just bucket the rows using GROUP BY with a case statement returning true or false if [Time] falls inside the range or outside.

MS SQL Server 2014 Schema Setup:

CREATE TABLE foobartable
    ([ID] int Identity(1,1) NOT NULL, [Time] float, [X] float, [Y] float)
;

INSERT INTO foobartable
    ([Time], [X], [Y])
VALUES
    (0.0, 1, 1),
    (1.0, 1, 1),
    (2.0, 1, 1),
    (3.0, 1, 1),
    (2.0, 1, 1),
    (1.0, 1, 1),
    (-1.0, 1, 1),
    (-2.0, 1, 1),
    (-3.0, 1, 1),
    (-2.0, 1, 1),
    (-1.0, 1, 1),
    (0.0, 1, 1),
    (1.0, 1, 1),
    (2.0, 1, 1),
    (3.0, 1, 1),
    (2.0, 1, 1)
;

Desired Results

| Id | PartitionId |
|----|-------------|
|  1 |           0 |
|  2 |           0 |
|  3 |           0 |
|  4 |           1 |
|  5 |           2 |
|  6 |           2 |
|  7 |           2 |
|  8 |           2 |
|  9 |           3 |
| 10 |           4 |
| 11 |           4 |
| 12 |           4 |
| 13 |           4 |
| 14 |           4 |
| 15 |           5 |
| 16 |           6 |

Answer 1

The solution I came up with:

SQL Fiddle

MS SQL Server 2014 Schema Setup:

CREATE TABLE foobartable
    ([ID] int Identity(1,1) NOT NULL, [Time] float, [X] float, [Y] float)
;

INSERT INTO foobartable
    ([Time], [X], [Y])
VALUES
    (0.0, 1, 1),
    (1.0, 1, 1),
    (2.0, 1, 1),
    (3.0, 1, 1),
    (2.0, 1, 1),
    (1.0, 1, 1),
    (-1.0, 1, 1),
    (-2.0, 1, 1),
    (-3.0, 1, 1),
    (-2.0, 1, 1),
    (-1.0, 1, 1),
    (0.0, 1, 1),
    (1.0, 1, 1),
    (2.0, 1, 1),
    (3.0, 1, 1),
    (2.0, 1, 1)
;

Query 1:

with pairs as (
  select
    t1.[ID] as [ID1],
    t2.[ID] as [ID2]
  from foobartable as t1
  left outer join foobartable as t2 on (t1.[ID] + 1) = t2.[ID]
),

loop ([ID], [Level])
as (
  select top 1
    t.[ID],
    0
  from foobartable as t
  union all
  select
    p.[ID2],
    (case when (case when t1.[Time] > 2 or t1.[Time] < -2 then 0 else 1 end) <> (case when t2.[Time] > 2 or t2.[Time] < -2 then 0 else 1 end) then [Level] + 1 else [Level] end) as [Level]
  from loop
  inner join pairs as p on p.[ID1] = loop.[ID]
  inner join foobartable as t1 on p.[ID1] = t1.[ID]
  inner join foobartable as t2 on p.[ID2] = t2.[ID]
 )

 select [Id], [Level] as [PartitionId] from loop

Results:

| Id | PartitionId |
|----|-------------|
|  1 |           0 |
|  2 |           0 |
|  3 |           0 |
|  4 |           1 |
|  5 |           2 |
|  6 |           2 |
|  7 |           2 |
|  8 |           2 |
|  9 |           3 |
| 10 |           4 |
| 11 |           4 |
| 12 |           4 |
| 13 |           4 |
| 14 |           4 |
| 15 |           5 |
| 16 |           6 |

Answer 2

I think you can use Recursive CTE. Something like this:

WITH cte AS
(
    SELECT *, row_number() over(ORDER BY ID) rn
        FROM #foobartable 
),
cte2 as
(
    SELECT cast(0 AS int) AS rn, NULL AS id,
        cast(NULL AS float) AS time,
        cast(NULL AS float) AS x,
        cast(NULL AS float) AS y,
        0 AS p
    UNION all
    SELECT cast(cte.rn AS int), cte.id,
        cte.[Time], cte.x, cte.y,
         iif(
                cte2.time <= 2.0 AND cte.time > 2.0 OR 
                cte2.time > 2.0 AND cte.time <= 2.0 OR 
                cte2.time >= -2.0 AND cte.time < -2.0 OR
                cte2.time < -2.0 AND cte.time >= -2.0
                ,p + 1
                ,p
            ) AS p
        FROM cte2
        JOIN cte ON cte2.rn + 1 = cte.rn
)
SELECT id, p AS PartitionId
    FROM cte2 
    WHERE id IS NOT NULL
    ORDER BY id;

Output:

id          PartitionId
----------- -----------
1           0
2           0
3           0
4           1
5           2
6           2
7           2
8           2
9           3
10          4
11          4
12          4
13          4
14          4
15          5
16          6