Inkrementwert basierend auf einer anderen Spalte

Ich möchte eine Spalte inkrementieren und dann aufhören und wieder anfangen, basierend auf einem Wert in einer anderen Spalte.

Zum Beispiel:

Ich habe einen Tisch:

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 

Die gewünschte Ausgabe:

 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 erhöht das Sequenzfeld seinen Operanden um 1, wobei "True_False" 0 ist und dann anhält, wo "True_False" 1 ist und anschließend die Inkrementierung wiederholt, um die in der gewünschten Ausgabe gezeigte Sequenz zu erzeugen.

Alle Hilfe ist willkommen,

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

Das sollte der Trick machen:

 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 

Dies ist möglich mit Identitätsspalte und while-loop, hier schreibe ich mit temp-Tabelle.

 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 

Hope this Hilft, Diese Lösung liefert das genaue Ergebnis als das, was von OP angefordert wird

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

Ausgabe:

 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 

Sie können die windowsfaktorsumme für die Berechnung der laufenden Summe wie unten verwenden

 ;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