Increment value based on another column

Question

I want to increment a column and then stop and start again based on a value in another column.

For e.g:

I have a table:

CustomerID  Reportdate  True_False
9001        2013-01-01  0
9001        2013-02-01  0
9001        2013-03-01  0
9001        2013-04-01  1
9001        2013-05-01  0
9001        2013-06-01  1
9001        2013-07-01  1
9001        2013-08-01  0
9001        2013-09-01  1
9001        2013-10-01  0
9001        2013-11-01  0
9001        2013-12-01  0
9001        2014-01-01  1
9001        2014-02-01  1
9001        2014-03-01  0
9001        2014-04-01  0
9001        2014-05-01  0
9001        2014-06-01  0
9001        2014-07-01  0
9001        2014-08-01  0
9001        2014-09-01  1
9001        2014-10-01  1
9001        2014-11-01  1
9001        2014-12-01  1
9002        2013-01-01  0
9002        2013-02-01  0
9002        2013-03-01  0
9002        2013-04-01  1
9002        2013-05-01  1
9002        2013-06-01  1
9002        2013-07-01  0
9002        2013-08-01  1
9002        2013-09-01  0
9002        2013-10-01  1
9002        2013-11-01  1
9002        2013-12-01  1
9002        2014-01-01  1
9002        2014-02-01  0
9002        2014-03-01  0
9002        2014-04-01  0
9002        2014-05-01  0
9002        2014-06-01  0
9002        2014-07-01  1
9002        2014-08-01  1
9002        2014-09-01  1
9002        2014-10-01  0
9002        2014-11-01  1
9002        2014-12-01  0

The desired output:

CustomerID  Reportdate  True_False  Sequence
9001        2013-01-01  0           1 
9001        2013-02-01  0           2
9001        2013-03-01  0           3
9001        2013-04-01  1           0
9001        2013-05-01  0           1
9001        2013-06-01  1           0
9001        2013-07-01  1           0
9001        2013-08-01  0           1
9001        2013-09-01  1           0
9001        2013-10-01  0           1
9001        2013-11-01  0           2
9001        2013-12-01  0           3 
9001        2014-01-01  1           0
9001        2014-02-01  1           0
9001        2014-03-01  0           1
9001        2014-04-01  0           2
9001        2014-05-01  0           3 
9001        2014-06-01  0           4
9001        2014-07-01  0           5 
9001        2014-08-01  0           6
9001        2014-09-01  1           0
9001        2014-10-01  1           0
9001        2014-11-01  1           0
9001        2014-12-01  1           0
9002        2013-01-01  0           1
9002        2013-02-01  0           2
9002        2013-03-01  0           3
9002        2013-04-01  1           0
9002        2013-05-01  1           0
9002        2013-06-01  1           0
9002        2013-07-01  0           1
9002        2013-08-01  1           0
9002        2013-09-01  0           1
9002        2013-10-01  1           0
9002        2013-11-01  1           0
9002        2013-12-01  1           0
9002        2014-01-01  1           0
9002        2014-02-01  0           1
9002        2014-03-01  0           2
9002        2014-04-01  0           3
9002        2014-05-01  0           4  
9002        2014-06-01  0           5
9002        2014-07-01  1           0
9002        2014-08-01  1           0
9002        2014-09-01  1           0
9002        2014-10-01  0           1
9002        2014-11-01  1           0
9002        2014-12-01  0           1

So the sequence-field increment its operand by 1 where "True_False" is 0 then stops at where "True_False" is 1 and subsequently repeats the incrementation to create the sequence shown in the desired output.

All help is welcome,

 CREATE TABLE test (
    CustomerID bigint,
    DateKey date,
    True_false bit,

);

insert into test 
values 
     (9001,'2013-01-01','1'),
     (9001,'2013-02-01','0'),
     (9001,'2013-03-01','0'),
     (9001,'2013-04-01','0'),
     (9001,'2013-05-01','1'),
     (9001,'2013-06-01','1'),
     (9001,'2013-07-01','0'),
     (9001,'2013-08-01','0'),
     (9001,'2013-09-01','0'),
     (9001,'2013-10-01','0'),
     (9001,'2013-11-01','0'),
     (9001,'2013-12-01','1'),
     (9001,'2014-01-01','1'),
     (9001,'2014-02-01','0'),
     (9001,'2014-03-01','1'),
     (9001,'2014-04-01','0'),
     (9001,'2014-05-01','0'),
     (9001,'2014-06-01','1'),
     (9001,'2014-07-01','1'),
     (9001,'2014-08-01','0'),
     (9001,'2014-09-01','1'),
     (9001,'2014-10-01','0'),
     (9002,'2014-11-01','0'),
     (9002,'2014-12-01','0'),
     (9002,'2013-01-01','0'),
     (9002,'2013-02-01','0'),
     (9002,'2013-03-01','0'),
     (9002,'2013-04-01','1'),
     (9002,'2013-05-01','1'),
     (9002,'2013-06-01','0'),
     (9002,'2013-07-01','1'),
     (9002,'2013-08-01','1'),
     (9002,'2013-09-01','1'),
     (9002,'2013-10-01','1'),
     (9002,'2013-11-01','0'),
     (9002,'2013-12-01','1'),
     (9002,'2014-01-01','1'),
     (9002,'2014-02-01','0'),
     (9002,'2014-03-01','1'),
     (9002,'2014-04-01','1'),
     (9002,'2014-05-01','1'),
     (9002,'2014-06-01','0'),
     (9002,'2014-07-01','1'),
     (9002,'2014-08-01','1'),
     (9002,'2014-09-01','0'),
     (9002,'2014-10-01','0'),
     (9002,'2014-11-01','0'),
     (9002,'2014-12-01','0')

@Veljko89 I don't think it's that simple. What would the partition be? What about resetting the sequence? — Tim Biegeleisen
– Tim Biegeleisen, Commented Mar 23, 2017 at 9:22
@TimBiegeleisen you were right ... sorry for misleading there — Veljko89
– Veljko89, Commented Mar 23, 2017 at 9:47
This looks ever so slightly like this question. But this one has more data. — HABO
– HABO, Commented Mar 23, 2017 at 13:19

dean · Accepted Answer · 2017-03-24 08:47:03Z

This should do the trick:

declare @t table (CustomerID int, Reportdate date, True_False int)
insert @t values 
(9001,        '2013-01-01',  0),
(9001,        '2013-02-01',  0),
(9001,        '2013-03-01',  0),
(9001,        '2013-04-01',  1),
(9001,        '2013-05-01',  0),
(9001,        '2013-06-01',  1),
(9001,        '2013-07-01',  1),
(9001,        '2013-08-01',  0),
(9001,        '2013-09-01',  1),
(9001,        '2013-10-01',  0),
(9001,        '2013-11-01',  0),
(9001,        '2013-12-01',  0),
(9001,        '2014-01-01',  1),
(9001,        '2014-02-01',  1),
(9001,        '2014-03-01',  0),
(9001,        '2014-04-01',  0),
(9001,        '2014-05-01',  0),
(9001,        '2014-06-01',  0),
(9001,        '2014-07-01',  0),
(9001,        '2014-08-01',  0),
(9001,        '2014-09-01',  1),
(9001,        '2014-10-01',  1),
(9001,        '2014-11-01',  1),
(9001,        '2014-12-01',  1),
(9002,        '2013-01-01',  0),
(9002,        '2013-02-01' , 0),
(9002,        '2013-03-01' , 0),
(9002,        '2013-04-01',  1),
(9002,        '2013-05-01' , 1),
(9002,        '2013-06-01'  ,1),
(9002,        '2013-07-01',  0),
(9002,        '2013-08-01',  1),
(9002,        '2013-09-01',  0),
(9002,        '2013-10-01',  1),
(9002,        '2013-11-01',  1),
(9002,        '2013-12-01',  1),
(9002,        '2014-01-01',  1),
(9002,        '2014-02-01',  0),
(9002,        '2014-03-01',  0),
(9002,        '2014-04-01',  0),
(9002,        '2014-05-01',  0),
(9002,        '2014-06-01',  0),
(9002,        '2014-07-01',  1),
(9002,        '2014-08-01',  1),
(9002,        '2014-09-01',  1),
(9002,        '2014-10-01',  0),
(9002,        '2014-11-01',  1),
(9002,        '2014-12-01',  0)

;with x as (
select *, sum(true_false) over(partition by customerid order by reportdate) g
from @t
)
select customerid, reportdate, row_number() over(partition by customerid, g order by reportdate) - case when g = 0 then 0 else 1 end seq
from x

Your first result (2013-01-01) is coming back as 0. OP's sample shows should be 1
do you know a solution to adjust the first record starting with 1? @JohnHC
If I understand correctly, the first sequence for each customer should start with 1, and every other with 0? It's easy, I'll have the edit in a minute.

padmini · Accepted Answer · 2017-03-23 11:15:47Z

This is possible using identity column and while loop, here i'm writing using temp table.

select * into #temp from test

ALTER TABLE #temp
ADD ID INT IDENTITY(1,1)

ALTER TABLE #temp
ADD Sequence INT

DECLARE @Min INT,@Max INT,@COUNT INT
SET @Min=1
SET @COUNT=1  
SELECT @Max=COUNT(1) FROM test

WHILE(@Min<=@Max)
BEGIN
 IF((SELECT True_false FROM #temp WHERE ID=@Min)=0)
    BEGIN
        update  #temp set Sequence=@COUNT WHERE ID=@Min
        SET @COUNT=@COUNT+1
    END
 ELSE
    BEGIN
        update  #temp set Sequence=0 WHERE ID=@Min
        SET @COUNT=1
    END
SET @Min=@Min+1
END
SELECT * FROM #temp

Gouri Shankar Aechoor · Accepted Answer · 2017-03-23 14:02:31Z

Hope this Helps, This solution provides the exact result as what is requested by OP

;WITH cteResultset(CustomerID ,DateKey, True_false) AS
(
SELECT 9001,CAST('2013-01-01' AS DATE),CAST(0 AS BIT) UNION ALL
SELECT 9001,'2013-02-01',0 UNION ALL
SELECT 9001,'2013-03-01',0 UNION ALL
SELECT 9001,'2013-04-01',1 UNION ALL
SELECT 9001,'2013-05-01',0 UNION ALL
SELECT 9001,'2013-06-01',1 UNION ALL
SELECT 9001,'2013-07-01',1 UNION ALL
SELECT 9001,'2013-08-01',0 UNION ALL
SELECT 9001,'2013-09-01',1 UNION ALL
SELECT 9001,'2013-10-01',0 UNION ALL
SELECT 9001,'2013-11-01',0 UNION ALL
SELECT 9001,'2013-12-01',0 UNION ALL
SELECT 9001,'2014-01-01',1 UNION ALL
SELECT 9001,'2014-02-01',1 UNION ALL
SELECT 9001,'2014-03-01',0 UNION ALL
SELECT 9001,'2014-04-01',0 UNION ALL
SELECT 9001,'2014-05-01',0 UNION ALL
SELECT 9001,'2014-06-01',0 UNION ALL
SELECT 9001,'2014-07-01',0 UNION ALL
SELECT 9001,'2014-08-01',0 UNION ALL
SELECT 9001,'2014-09-01',1 UNION ALL
SELECT 9001,'2014-10-01',1 UNION ALL
SELECT 9001,'2014-11-01',1 UNION ALL
SELECT 9001,'2014-12-01',1 UNION ALL
SELECT 9002,'2013-01-01',0 UNION ALL
SELECT 9002,'2013-02-01',0 UNION ALL
SELECT 9002,'2013-03-01',0 UNION ALL
SELECT 9002,'2013-04-01',1 UNION ALL
SELECT 9002,'2013-05-01',1 UNION ALL
SELECT 9002,'2013-06-01',1 UNION ALL
SELECT 9002,'2013-07-01',0 UNION ALL
SELECT 9002,'2013-08-01',1 UNION ALL
SELECT 9002,'2013-09-01',0 UNION ALL
SELECT 9002,'2013-10-01',1 UNION ALL
SELECT 9002,'2013-11-01',1 UNION ALL
SELECT 9002,'2013-12-01',1 UNION ALL
SELECT 9002,'2014-01-01',1 UNION ALL
SELECT 9002,'2014-02-01',0 UNION ALL
SELECT 9002,'2014-03-01',0 UNION ALL
SELECT 9002,'2014-04-01',0 UNION ALL
SELECT 9002,'2014-05-01',0 UNION ALL
SELECT 9002,'2014-06-01',0 UNION ALL
SELECT 9002,'2014-07-01',1 UNION ALL
SELECT 9002,'2014-08-01',1 UNION ALL
SELECT 9002,'2014-09-01',1 UNION ALL
SELECT 9002,'2014-10-01',0 UNION ALL
SELECT 9002,'2014-11-01',1 UNION ALL
SELECT 9002,'2014-12-01',0
),cte_Sequence AS
(
SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS Id,*
FROM cteResultset ),
    cte AS (
        SELECT Id,
            CustomerID,
            DateKey,
            True_false,
            True_false AS Part
        FROM cte_Sequence
        WHERE id = 1

        UNION ALL

        SELECT a.Id,
            a.CustomerID,
            a.DateKey,
            a.True_false,
            cte.Part + a.True_false
        FROM cte
        INNER JOIN cte_Sequence a
            ON cte.id + 1 = a.id
        )

SELECT Id,CustomerID,
    DateKey,
    True_false,
    row_number() OVER (
        PARTITION BY CustomerID,
        Part ORDER BY DateKey
        ) AS Sequence
FROM cte
WHERE True_false = 0
UNION 
SELECT id,CustomerID,
    DateKey,
    True_false,
    0 AS Sequence
FROM cte
WHERE True_false = 1
ORDER BY Id
OPTION (MAXRECURSION 32767);

output:

Id  CustomerID  DateKey     True_false  Sequence
1   9001        2013-01-01  0           1
2   9001        2013-02-01  0           2
3   9001        2013-03-01  0           3
4   9001        2013-04-01  1           0
5   9001        2013-05-01  0           1
6   9001        2013-06-01  1           0
7   9001        2013-07-01  1           0
8   9001        2013-08-01  0           1
9   9001        2013-09-01  1           0
10  9001        2013-10-01  0           1
11  9001        2013-11-01  0           2
12  9001        2013-12-01  0           3
13  9001        2014-01-01  1           0
14  9001        2014-02-01  1           0
15  9001        2014-03-01  0           1
16  9001        2014-04-01  0           2
17  9001        2014-05-01  0           3
18  9001        2014-06-01  0           4
19  9001        2014-07-01  0           5
20  9001        2014-08-01  0           6
21  9001        2014-09-01  1           0
22  9001        2014-10-01  1           0
23  9001        2014-11-01  1           0
24  9001        2014-12-01  1           0
25  9002        2013-01-01  0           1
26  9002        2013-02-01  0           2
27  9002        2013-03-01  0           3
28  9002        2013-04-01  1           0
29  9002        2013-05-01  1           0
30  9002        2013-06-01  1           0
31  9002        2013-07-01  0           1
32  9002        2013-08-01  1           0
33  9002        2013-09-01  0           1
34  9002        2013-10-01  1           0
35  9002        2013-11-01  1           0
36  9002        2013-12-01  1           0
37  9002        2014-01-01  1           0
38  9002        2014-02-01  0           1
39  9002        2014-03-01  0           2
40  9002        2014-04-01  0           3
41  9002        2014-05-01  0           4
42  9002        2014-06-01  0           5
43  9002        2014-07-01  1           0
44  9002        2014-08-01  1           0
45  9002        2014-09-01  1           0
46  9002        2014-10-01  0           1
47  9002        2014-11-01  1           0
48  9002        2014-12-01  0           1

Kannan Kandasamy · Accepted Answer · 2017-03-23 15:05:56Z

-1

You can use windowing function sum for calculating running total as below

;with cte as (
select *, sum(True_false) over(partition by customerid order by ReportDate) TF from #customer
)
select *, [Sequence] = row_number() over (partition by Customerid, TF order by TF) - 1 from cte

answered Mar 23, 2017 at 15:05

Kannan Kandasamy

14k3 gold badges28 silver badges40 bronze badges

Collectives™ on Stack Overflow

Increment value based on another column

4 Answers 4

3 Comments

Comments

Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

3 Comments

Comments

Comments

Comments

Linked

Related