SQL Iterate over row without a cursor

Question

I have some data that reads the status of a sensor. The sensor reports it status every 15 mins. This goes something like...

Sensor ID, Status, TimeStamp, **WhatIWantSessionID**
1, Off, 0345, **0**
1, Charge, 0400, **1**
1, Charge, 0415, **1**
1, Off, 0430, **0**
1, Charge, 0445, **2**
1, Off, 0500, **0** 

"WhatIWantSessionID" ... I want to set a incremented sessionID each time a Charge session starts. Keeping that sessionID until the status switches state. The following link is brilliant, and "nearly" does what I need, but not quite (as I don´t have a flag for the start/stop over a session). Help very much appreciated. Iterate over rows using SQL

Answer 1

There has got to be a much better way to do this, but the following example works (ugly, and I'm sure someone will post something clever and brilliant, but I haven't had enough coffee yet today to do clever, and it works).

create or replace temporary table stacko_64351844 (
    id number,
    status varchar(20),
    ts     varchar(20),
    desired number);

INSERT INTO stacko_64351844 
VALUES 
(1, 'Off', '0345', 0),
(1, 'Charge', '0400', 1),
(1, 'Charge', '0415', 1),
(1, 'Off', '0430', 0),
(1, 'Charge', '0445', 2),
(1, 'Off', '0500', 0),
(1, 'Charge', '0515', 3),
(1, 'Charge', '0530', 3),
(1, 'Charge', '0545', 3),
(1, 'Charge', '0600', 3),
(1, 'Off', '0615', 0);

SELECT id,
       status, ts,
       desired,
       NVL(calc_desired, max(calc_desired) over (partition by id  order by id, ts rows between unbounded preceding and current row)) as calc_desired
FROM (
SELECT id, 
       status, 
       ts, 
       desired,
       tmp_desired,
       CASE
         WHEN status = 'Off' THEN 0
         WHEN (status != 'Charge' AND status = lag(status, 1, null)
              over (partition by id order by id, ts, status DESC) )
               THEN null
         ELSE
           sum(tmp_desired) over (partition by id, tmp_desired order by id, ts rows between unbounded preceding and current row)
       end as calc_desired
FROM (
  SELECT id, 
         status, 
         ts, 
         desired, 
         CASE
         WHEN status = 'Off' THEN 0
         WHEN status = lag(status, 1, null) 
              over (partition by id order by id, ts, status DESC) 
               THEN null
         ELSE
           1
       end  as tmp_desired
  FROM   stacko_64351844)
ORDER BY id, ts, status DESC);

RESULTS
ID  STATUS  TS  DESIRED CALC_DESIRED
1   Off     0345    0   0
1   Charge  0400    1   1
1   Charge  0415    1   1
1   Off     0430    0   0
1   Charge  0445    2   2
1   Off     0500    0   0
1   Charge  0515    3   3
1   Charge  0530    3   3
1   Charge  0545    3   3
1   Charge  0600    3   3
1   Off     0615    0   0