CODEWITHSUNDEEP
sql-server
genadi
genadi

Reputation: 65

Renumbering consecutive values

I have table where I have game dates and results (0 means lose and 1 win)

+------+--------------+--------+ 
| id   | game_date    | result | 
+------+--------------+--------+
| 1    | '2016-09-01' | 1      |
| 2    | '2016-09-02' | 1      |
| 3    | '2016-09-03' | 0      |
| 4    | '2016-09-04' | 1      |
| 5    | '2016-09-04' | 1      |
| 6    | '2016-09-04' | 1      |
| 7    | '2016-09-05' | 1      |
| 8    | '2016-09-06' | 0      |
| 9    | '2016-09-07' | 1      |

I need get all wins, where wins are consecutive (sorted by date) and renumber from 1 to last win.

Result should be this:

+------+--------------+--------+------------
| id   | game_date    | result | 
+------+--------------+--------+-------------
| 1    | '2016-09-01' | 1      | 1
| 2    | '2016-09-02' | 1      | 2
| 3    | '2016-09-03' | 0      |
| 4    | '2016-09-04' | 1      | 1
| 5    | '2016-09-04' | 1      | 2
| 6    | '2016-09-04' | 1      | 3
| 7    | '2016-09-05' | 1      | 4
| 8    | '2016-09-06' | 0      |
| 9    | '2016-09-07' | 1      | 1

Upvotes: 3

Views: 56

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269953

You can do this by identifying the groups of adjacent values. A simple way is a difference of row numbers. Another method is to assign the maximum date for each "0" value:

select id, game_date, result,
       (case when result = 1
             then row_number() over (partition by result, max_gamedate order by id)
        end) as newcol
from (select t.*,
             max(case when result = 0 then game_date end) over
                 (order by id) as max_gamedate
      from t
     ) t

Upvotes: 3

Oto Shavadze
Oto Shavadze

Reputation: 42773

DECLARE @games TABLE (
    id INT,
    game_date DATE,
    result INT
);
INSERT INTO @games
VALUES
(1, '2016-09-01', 1),
(2, '2016-09-02', 1),
(3, '2016-09-03', 0),
(4, '2016-09-04', 1),
(5, '2016-09-04', 1),
(6, '2016-09-04', 1),
(7, '2016-09-05', 1),
(8, '2016-09-06', 0),
(9, '2016-09-07', 1);

WITH CTE AS(
    select t3.*,   COUNT(collect) OVER(PARTITION BY collect) as collect_count FROM (
        select t.*, t2.rn, sum(result) over(order by game_date, t.id) as collect from @games t 
        left join (
            SELECT id, row_number() over(order by game_date, id) as rn
            FROM @games t where result  = 1
        )  t2
        on t.id = t2.id
    )t3
)
select CTE.id, CTE.game_date, CTE.result, rn - COALESCE( (SELECT MAX(collect) FROM CTE innert WHERE collect < CTE.rn and collect_count > 1) , 0) sequence_number 
FROM CTE
order by game_date, id

Upvotes: 0

Related Questions