Increment column for streaks

  • A+
Category:Languages

How do I get the following result highlighted in yellow? Increment column for streaks

Essentially I want a calculated field which increments by 1 when VeganOption = 1 and is zero when VeganOption = 0

I have tried using the following query but using partition continues to increment after a zero. I'm a bit stuck on this one.

SELECT [UniqueId]       ,[Meal]       ,[VDate]       ,[VeganOption]       , row_number() over (partition by [VeganOption] order by [UniqueId])   FROM [Control]   order by [UniqueId] 

Table Data:

CREATE TABLE Control     ([UniqueId] int, [Meal] varchar(10), [VDate] datetime, [VeganOption] int);  INSERT INTO Control ([UniqueId], [Meal], [VDate], [VeganOption]) VALUES ('1', 'Breakfast',' 2018-08-01 00:00:00', 1), ('2', 'Lunch',' 2018-08-01 00:00:00', 1), ('3', 'Dinner',' 2018-08-01 00:00:00', 1), ('4', 'Breakfast',' 2018-08-02 00:00:00', 1), ('5', 'Lunch',' 2018-08-02 00:00:00', 0), ('6', 'Dinner',' 2018-08-02 00:00:00', 0), ('7', 'Breakfast',' 2018-08-03 00:00:00', 1), ('8', 'Lunch',' 2018-08-03 00:00:00', 1), ('9', 'Dinner',' 2018-08-03 00:00:00', 1), ('10', 'Breakfast',' 2018-08-04 00:00:00', 0), ('11', 'Lunch',' 2018-08-04 00:00:00', 1), ('12', 'Dinner',' 2018-08-04 00:00:00', 1) ; 

This is for SQL Server 2016+

 


You could create subgroups using SUM and then ROW_NUMBER:

WITH cte AS (   SELECT [UniqueId]       ,[Meal]       ,[VDate]       ,[VeganOption]       ,sum(CASE WHEN VeganOption = 1 THEN 0 ELSE 1 END)           over (order by [UniqueId]) AS grp  --switching 0 <-> 1   FROM [Control] ) SELECT *,CASE WHEN VeganOption =0 THEN 0         ELSE ROW_NUMBER() OVER(PARTITION BY veganOption, grp ORDER BY [UniqueId])         END AS VeganStreak                  -- main group and calculated subgroup FROM cte order by [UniqueId]; 

Rextester Demo

Comment

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen: