sql server counting recursions in a table

Question

what i have right now is a table that is structured like this

. Current table (desired) Start END STATUS SELECTION | Cycle 2018-11-03 11:45:00.000 2018-11-03 11:45:00.000 0 0 | 0 2018-11-04 06:45:00.000 2018-11-04 07:26:20.000 2 0 | 1 2018-11-04 07:26:20.000 2018-11-04 08:16:35.000 5 0 | 1 2018-11-04 08:16:35.000 2018-11-04 08:16:55.000 6 0 | 1 2018-11-04 08:16:55.000 2018-11-04 08:19:00.000 2 0 | 1 2018-11-04 08:19:00.000 2018-11-04 08:20:10.000 3 0 | 1 2018-11-04 08:20:10.000 2018-11-04 08:25:30.000 0 0 | 1 2018-11-04 08:25:30.000 2018-11-04 08:25:55.000 1 0 | 2 2018-11-04 08:25:55.000 2018-11-04 08:54:25.000 2 0 | 2 2018-11-04 08:54:25.000 2018-11-04 09:02:25.000 5 0 | 2 2018-11-04 09:02:25.000 2018-11-04 09:03:00.000 3 0 | 2 2018-11-04 09:03:00.000 2018-11-04 09:39:44.000 0 0 | 2 2018-11-04 09:39:44.000 2018-11-04 23:17:14.000 0 0 | 2 2018-11-04 23:17:14.000 2018-11-05 00:55:35.000 0 0 | 2 2018-11-05 00:55:35.000 2018-11-05 00:56:00.000 1 0 | 3 2018-11-05 00:56:00.000 2018-11-05 03:20:36.000 2 0 | 3 2018-11-05 03:20:36.000 2018-11-05 03:52:05.000 2 0 | 3

i need a new colum that i would call Cycle that is able to "count" the ammount of times the status went from 0 to anything different from 0 (depicts the starting of the machine process this describes) and kept different than 0 (when it returns to 0 it defines the process has finished one cycle). i attempted using a counter tied to a variable to assign a value that changes depending on the status value row by row to assign a value to a cycle column.

Visual studio does not allow what i attempted to do saying a fixed value has been assigned to the column and as such it cannot assign the value.

Someone suggested using a ranking function but i don't understand clearly how it works from the MS documentation and thus how to apply to the issue

Answer 1

I think the below is what you're after:

• Generate a sequential ID (GeneratedId) based on the order of the items; this way we can tell what the next record in the table should be by adding 1 to the current value; so we can easily move through the records step by step in our query.
• Working through the records sequentially, each time we find status=0 for a previous record and status=1 for the current record, add one to the cycle counter; for all other values, take the previous record's counter value.

Fiddle Example: https://dbfiddle.uk/?rdbms=sqlserver_2017&fiddle=c89c13ede9fe5b752368ded49e7a1c3d

create table TestData
(
    DateOrder datetime not null
    , StatusId int not null
)
insert TestData(DateOrder, StatusId)
values ('2018-11-06 01:00', 0)
, ('2018-11-06 02:00', 1)
, ('2018-11-06 03:00', 2)
, ('2018-11-06 04:00', 0)
, ('2018-11-06 05:00', 0)
, ('2018-11-06 06:00', 0)
, ('2018-11-06 07:00', 2)
, ('2018-11-06 08:00', 1)
, ('2018-11-06 09:00', 3)
, ('2018-11-06 10:00', 0)
, ('2018-11-06 11:00', 1)

;with cte (DateOrder, StatusId, GeneratedId) as 
(
    select DateOrder, StatusId, row_number() over (order by DateOrder) GeneratedId
    from TestData
)
, cte2 (DateOrder, StatusId, GeneratedId, Cycle) as
(
    select DateOrder, StatusId, GeneratedId, 0 
    from cte
    where GeneratedId = 1

    union all

    select cte.DateOrder, cte.StatusId, cte.GeneratedId
    , case 
        when cte2.StatusId = 0 and cte.StatusId != 0 then cte2.Cycle + 1  
        else cte2.Cycle 
    end Cycle
    from cte2
    inner join cte 
    on cte.GeneratedId = cte2.GeneratedId + 1
)
select * from cte2 order by DateOrder

Answer 2

There must be a prettier way to do this, but at the moment I'm stumped. I've created a table variable with your sample data and this additional column that you've said contains a date and can be used for ordering. Since I'm lacking in imagination, I've used an int instead, but the logic should work.

declare @t table (Status int not null, Cycle int not null, ReallyDate int not null)
insert into @t(Status,Cycle,ReallyDate) values
(0,0,1 ),    (2,1,2 ),    (5,1,3 ),    (6,1,4 ),    (2,1,5 ),    (3,1,6 ),
(0,1,7 ),    (1,2,8 ),    (2,2,9 ),    (5,2,10),    (3,2,11),    (0,2,12),
(0,2,13),    (0,2,14),    (1,3,15),    (2,3,16),    (2,3,17),    (5,3,18),
(6,3,19),    (2,3,20),    (5,3,21),    (3,3,22),    (0,3,23);

With Numbered as (
    select
        *,
        ROW_NUMBER() OVER (ORDER BY ReallyDate) as rn
    from @t
), Roots as (
    select
        t1.*,
        ROW_NUMBER() OVER (ORDER BY t1.ReallyDate) as CyclePrime
    from
        Numbered t1
            left join
        Numbered t2
            on
                t1.rn = t2.rn - 1 and
                t2.Status = 0
    where
        t1.Status = 0 and
        t2.rn is null
)
select
    n.*,
    COALESCE(r.CyclePrime,0) as Cycle
from
    Numbered n
        left join
    Roots r
        left join
    Roots r_anti
        on
            r_anti.CyclePrime = r.CyclePrime + 1
        on
            n.rn > r.rn and
            n.rn <= r_anti.rn

Of course, my data already had a column I could have used instead of having the Numbered CTE, but you have a datetime column and need to generate something like rn.

Then Roots is the tricky one. It identifies 0 rows that aren't followed immediately by another 0 row. These we use to assign cycle numbers.

The in the last select, we attempt to join whatever row we're working with to the previous Root row, and take the cycle # from that row.

Results (including bonus columns):

Status      Cycle       ReallyDate  rn                   Cycle
----------- ----------- ----------- -------------------- --------------------
0           0           1           1                    0
2           1           2           2                    1
5           1           3           3                    1
6           1           4           4                    1
2           1           5           5                    1
3           1           6           6                    1
0           1           7           7                    1
1           2           8           8                    2
2           2           9           9                    2
5           2           10          10                   2
3           2           11          11                   2
0           2           12          12                   2
0           2           13          13                   2
0           2           14          14                   2
1           3           15          15                   3
2           3           16          16                   3
2           3           17          17                   3
5           3           18          18                   3
6           3           19          19                   3
2           3           20          20                   3
5           3           21          21                   3
3           3           22          22                   3
0           3           23          23                   3

Answer 3

SELECT SUM(Cycle) FROM table-name;

Answer 4

SELECT COUNT (DISTINCT Cycle) where Cycle > 0 FROM table-name