基于另一列增加值

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.

因此，序列字段将其操作数增加1，其中“True_False”为0，然后在“True_False”为1的位置停止，然后重复增量以创建所需输出中显示的序列。

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')

4 个解决方案

#1

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

#2

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

这可以使用标识列和while循环，这里我使用临时表编写。

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

#3

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

希望这有助于此解决方案提供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

#4

-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

#1