Increment column for streaks

up vote
12
down vote

favorite

How do I get the following result highlighted in yellow?
enter image description here

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+

edited Aug 21 at 18:06

Lukasz Szozda

70.3k84993

asked Aug 21 at 17:54

pathDongle

779523

6

I know the upvotes show it, but it's so nice to see a well formulated question, with sample data, expected results, an attempt, and then to top it off both a CREATE and INSERT statement. Thank you. :)
â€“Â Larnu
Aug 21 at 18:16

1

@Larnu And not the usual trivial conversion failed from varchar to datetime for a chagne :-)
â€“Â Zohar Peled
Aug 21 at 18:18

It's the "how to I split this delimited list" questions that really bug me @ZoharPeled . So many of them.... Lol
â€“Â Larnu
Aug 21 at 18:20

add a commentÂ |Â

up vote
12
down vote

favorite

How do I get the following result highlighted in yellow?
enter image description here

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+

edited Aug 21 at 18:06

Lukasz Szozda

70.3k84993

asked Aug 21 at 17:54

pathDongle

779523

6

I know the upvotes show it, but it's so nice to see a well formulated question, with sample data, expected results, an attempt, and then to top it off both a CREATE and INSERT statement. Thank you. :)
â€“Â Larnu
Aug 21 at 18:16

1

@Larnu And not the usual trivial conversion failed from varchar to datetime for a chagne :-)
â€“Â Zohar Peled
Aug 21 at 18:18

It's the "how to I split this delimited list" questions that really bug me @ZoharPeled . So many of them.... Lol
â€“Â Larnu
Aug 21 at 18:20

add a commentÂ |Â

up vote
12
down vote

favorite

How do I get the following result highlighted in yellow?
enter image description here

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+

edited Aug 21 at 18:06

Lukasz Szozda

70.3k84993

asked Aug 21 at 17:54

pathDongle

779523

How do I get the following result highlighted in yellow?
enter image description here

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+

edited Aug 21 at 18:06

Lukasz Szozda

70.3k84993

asked Aug 21 at 17:54

pathDongle

779523

edited Aug 21 at 18:06

Lukasz Szozda

70.3k84993

edited Aug 21 at 18:06

Lukasz Szozda

70.3k84993

edited Aug 21 at 18:06

Lukasz Szozda

70.3k84993

asked Aug 21 at 17:54

pathDongle

779523

asked Aug 21 at 17:54

pathDongle

779523

asked Aug 21 at 17:54

pathDongle

779523

6

I know the upvotes show it, but it's so nice to see a well formulated question, with sample data, expected results, an attempt, and then to top it off both a CREATE and INSERT statement. Thank you. :)
â€“Â Larnu
Aug 21 at 18:16

1

@Larnu And not the usual trivial conversion failed from varchar to datetime for a chagne :-)
â€“Â Zohar Peled
Aug 21 at 18:18

It's the "how to I split this delimited list" questions that really bug me @ZoharPeled . So many of them.... Lol
â€“Â Larnu
Aug 21 at 18:20

add a commentÂ |Â

6

I know the upvotes show it, but it's so nice to see a well formulated question, with sample data, expected results, an attempt, and then to top it off both a CREATE and INSERT statement. Thank you. :)
â€“Â Larnu
Aug 21 at 18:16

1

@Larnu And not the usual trivial conversion failed from varchar to datetime for a chagne :-)
â€“Â Zohar Peled
Aug 21 at 18:18

It's the "how to I split this delimited list" questions that really bug me @ZoharPeled . So many of them.... Lol
â€“Â Larnu
Aug 21 at 18:20

I know the upvotes show it, but it's so nice to see a well formulated question, with sample data, expected results, an attempt, and then to top it off both a CREATE and INSERT statement. Thank you. :)
â€“Â Larnu
Aug 21 at 18:16

@Larnu And not the usual trivial conversion failed from varchar to datetime for a chagne :-)
â€“Â Zohar Peled
Aug 21 at 18:18

It's the "how to I split this delimited list" questions that really bug me @ZoharPeled . So many of them.... Lol
â€“Â Larnu
Aug 21 at 18:20

add a commentÂ |Â

3 Answers
3

active

oldest

votes

up vote
6
down vote

accepted

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

answered Aug 21 at 18:03

Lukasz Szozda

70.3k84993

1

I like this approach, slightly easier to understand than the other traditional approaches to gaps and islands.
â€“Â sgeddes
Aug 21 at 18:07

add a commentÂ |Â

up vote
3
down vote

One method is to use a CTE to define your groupings, and then do a further ROW_NUMBER() on those, resulting in:

WITH Grps AS(
 SELECT *,
 ROW_NUMBER() OVER (ORDER BY UniqueID ASC) - 
 ROW_NUMBER() OVER (PARTITION BY VeganOption ORDER BY UniqueID ASC) AS Grp
 FROM Control)
SELECT *,
 CASE VeganOption WHEN 0 THEN 0 ELSE ROW_NUMBER() OVER (PARTITION BY Grp ORDER BY UniqueID ASC) END
FROM Grps
ORDER BY UniqueId;

edited Aug 21 at 18:11

answered Aug 21 at 18:01

Larnu

10.9k21328

1

You should zero VeganOption = 0 rextester.com/COTWN74238. Rows 5,6 should be (0,0) instead (1,2). Same for row 10.
â€“Â Lukasz Szozda
Aug 21 at 18:09

1

@LukaszSzozda you're right, I'd missed that. :)
â€“Â Larnu
Aug 21 at 18:10

add a commentÂ |Â

up vote
3
down vote

This is a variant on gaps-and-islands.

I like to define streaks using the difference of row numbers. This looks like

select c.*,
 (case when veganoption = 1
 then row_number() over (partition by veganoption, seqnum - seqnum_v order by uniqueid)
 else 0
 end) as veganstreak
from (select c.*,
 row_number() over (partition by veganoption order by uniqueid) as seqnum_v,
 row_number() over (order by uniqueid) as seqnum
 from c
 ) c;

Why this works is a bit hard to explain. But, if you look at the results of the subquery, you'll see how the difference of row numbers defines the streaks you want to identify. The rest is just applying row_number() to enumerate the meals.

Here is a Rextester.

edited Aug 22 at 1:42

answered Aug 21 at 17:59

Gordon Linoff

717k30269374

It produces incorrect result: rextester.com/YDVD78565. Last two rows (4,5) instead of (1,2)
â€“Â Lukasz Szozda
Aug 21 at 18:07

1

@LukaszSzozda . . . Thank you for putting that together. I didn't notice that the date is not unique. The uniqueid is the right column to use for the logic.
â€“Â Gordon Linoff
Aug 22 at 1:42

add a commentÂ |Â

Your Answer

StackExchange.ifUsing("editor", function ()
StackExchange.using("externalEditor", function ()
StackExchange.using("snippets", function ()
StackExchange.snippets.init();
);
);
, "code-snippets");

StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "1"
;
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function()
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled)
StackExchange.using("snippets", function()
createEditor();
);

else
createEditor();

);

function createEditor()
StackExchange.prepareEditor(
heartbeatType: 'answer',
convertImagesToLinks: true,
noModals: false,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
);

);

draft saved

draft discarded

StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f51954221%2fincrement-column-for-streaks%23new-answer', 'question_page');

);

Post as a guest

Name

3 Answers
3

active

oldest

votes

3 Answers
3

active

oldest

votes

up vote
6
down vote

accepted

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

answered Aug 21 at 18:03

Lukasz Szozda

70.3k84993

1

I like this approach, slightly easier to understand than the other traditional approaches to gaps and islands.
â€“Â sgeddes
Aug 21 at 18:07

add a commentÂ |Â

up vote
6
down vote

accepted

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

answered Aug 21 at 18:03

Lukasz Szozda

70.3k84993

1

I like this approach, slightly easier to understand than the other traditional approaches to gaps and islands.
â€“Â sgeddes
Aug 21 at 18:07

add a commentÂ |Â

up vote
6
down vote

accepted

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

answered Aug 21 at 18:03

Lukasz Szozda

70.3k84993

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

answered Aug 21 at 18:03

Lukasz Szozda

70.3k84993

answered Aug 21 at 18:03

Lukasz Szozda

70.3k84993

answered Aug 21 at 18:03

Lukasz Szozda

70.3k84993

answered Aug 21 at 18:03

Lukasz Szozda

70.3k84993

1

I like this approach, slightly easier to understand than the other traditional approaches to gaps and islands.
â€“Â sgeddes
Aug 21 at 18:07

add a commentÂ |Â

1

I like this approach, slightly easier to understand than the other traditional approaches to gaps and islands.
â€“Â sgeddes
Aug 21 at 18:07

I like this approach, slightly easier to understand than the other traditional approaches to gaps and islands.
â€“Â sgeddes
Aug 21 at 18:07

add a commentÂ |Â

up vote
3
down vote

One method is to use a CTE to define your groupings, and then do a further ROW_NUMBER() on those, resulting in:

WITH Grps AS(
 SELECT *,
 ROW_NUMBER() OVER (ORDER BY UniqueID ASC) - 
 ROW_NUMBER() OVER (PARTITION BY VeganOption ORDER BY UniqueID ASC) AS Grp
 FROM Control)
SELECT *,
 CASE VeganOption WHEN 0 THEN 0 ELSE ROW_NUMBER() OVER (PARTITION BY Grp ORDER BY UniqueID ASC) END
FROM Grps
ORDER BY UniqueId;

edited Aug 21 at 18:11

answered Aug 21 at 18:01

Larnu

10.9k21328

1

You should zero VeganOption = 0 rextester.com/COTWN74238. Rows 5,6 should be (0,0) instead (1,2). Same for row 10.
â€“Â Lukasz Szozda
Aug 21 at 18:09

1

@LukaszSzozda you're right, I'd missed that. :)
â€“Â Larnu
Aug 21 at 18:10

add a commentÂ |Â

up vote
3
down vote

One method is to use a CTE to define your groupings, and then do a further ROW_NUMBER() on those, resulting in:

WITH Grps AS(
 SELECT *,
 ROW_NUMBER() OVER (ORDER BY UniqueID ASC) - 
 ROW_NUMBER() OVER (PARTITION BY VeganOption ORDER BY UniqueID ASC) AS Grp
 FROM Control)
SELECT *,
 CASE VeganOption WHEN 0 THEN 0 ELSE ROW_NUMBER() OVER (PARTITION BY Grp ORDER BY UniqueID ASC) END
FROM Grps
ORDER BY UniqueId;

edited Aug 21 at 18:11

answered Aug 21 at 18:01

Larnu

10.9k21328

1

You should zero VeganOption = 0 rextester.com/COTWN74238. Rows 5,6 should be (0,0) instead (1,2). Same for row 10.
â€“Â Lukasz Szozda
Aug 21 at 18:09

1

@LukaszSzozda you're right, I'd missed that. :)
â€“Â Larnu
Aug 21 at 18:10

add a commentÂ |Â

up vote
3
down vote

One method is to use a CTE to define your groupings, and then do a further ROW_NUMBER() on those, resulting in:

WITH Grps AS(
 SELECT *,
 ROW_NUMBER() OVER (ORDER BY UniqueID ASC) - 
 ROW_NUMBER() OVER (PARTITION BY VeganOption ORDER BY UniqueID ASC) AS Grp
 FROM Control)
SELECT *,
 CASE VeganOption WHEN 0 THEN 0 ELSE ROW_NUMBER() OVER (PARTITION BY Grp ORDER BY UniqueID ASC) END
FROM Grps
ORDER BY UniqueId;

edited Aug 21 at 18:11

answered Aug 21 at 18:01

Larnu

10.9k21328

One method is to use a CTE to define your groupings, and then do a further ROW_NUMBER() on those, resulting in:

WITH Grps AS(
 SELECT *,
 ROW_NUMBER() OVER (ORDER BY UniqueID ASC) - 
 ROW_NUMBER() OVER (PARTITION BY VeganOption ORDER BY UniqueID ASC) AS Grp
 FROM Control)
SELECT *,
 CASE VeganOption WHEN 0 THEN 0 ELSE ROW_NUMBER() OVER (PARTITION BY Grp ORDER BY UniqueID ASC) END
FROM Grps
ORDER BY UniqueId;

edited Aug 21 at 18:11

answered Aug 21 at 18:01

Larnu

10.9k21328

edited Aug 21 at 18:11

answered Aug 21 at 18:01

Larnu

10.9k21328

answered Aug 21 at 18:01

Larnu

10.9k21328

answered Aug 21 at 18:01

Larnu

10.9k21328

1

You should zero VeganOption = 0 rextester.com/COTWN74238. Rows 5,6 should be (0,0) instead (1,2). Same for row 10.
â€“Â Lukasz Szozda
Aug 21 at 18:09

1

@LukaszSzozda you're right, I'd missed that. :)
â€“Â Larnu
Aug 21 at 18:10

add a commentÂ |Â

1

You should zero VeganOption = 0 rextester.com/COTWN74238. Rows 5,6 should be (0,0) instead (1,2). Same for row 10.
â€“Â Lukasz Szozda
Aug 21 at 18:09

1

@LukaszSzozda you're right, I'd missed that. :)
â€“Â Larnu
Aug 21 at 18:10

You should zero VeganOption = 0 rextester.com/COTWN74238. Rows 5,6 should be (0,0) instead (1,2). Same for row 10.
â€“Â Lukasz Szozda
Aug 21 at 18:09

@LukaszSzozda you're right, I'd missed that. :)
â€“Â Larnu
Aug 21 at 18:10

add a commentÂ |Â

up vote
3
down vote

This is a variant on gaps-and-islands.

I like to define streaks using the difference of row numbers. This looks like

select c.*,
 (case when veganoption = 1
 then row_number() over (partition by veganoption, seqnum - seqnum_v order by uniqueid)
 else 0
 end) as veganstreak
from (select c.*,
 row_number() over (partition by veganoption order by uniqueid) as seqnum_v,
 row_number() over (order by uniqueid) as seqnum
 from c
 ) c;

Here is a Rextester.

edited Aug 22 at 1:42

answered Aug 21 at 17:59

Gordon Linoff

717k30269374

It produces incorrect result: rextester.com/YDVD78565. Last two rows (4,5) instead of (1,2)
â€“Â Lukasz Szozda
Aug 21 at 18:07

1

@LukaszSzozda . . . Thank you for putting that together. I didn't notice that the date is not unique. The uniqueid is the right column to use for the logic.
â€“Â Gordon Linoff
Aug 22 at 1:42

add a commentÂ |Â

up vote
3
down vote

This is a variant on gaps-and-islands.

I like to define streaks using the difference of row numbers. This looks like

select c.*,
 (case when veganoption = 1
 then row_number() over (partition by veganoption, seqnum - seqnum_v order by uniqueid)
 else 0
 end) as veganstreak
from (select c.*,
 row_number() over (partition by veganoption order by uniqueid) as seqnum_v,
 row_number() over (order by uniqueid) as seqnum
 from c
 ) c;

Here is a Rextester.

edited Aug 22 at 1:42

answered Aug 21 at 17:59

Gordon Linoff

717k30269374

It produces incorrect result: rextester.com/YDVD78565. Last two rows (4,5) instead of (1,2)
â€“Â Lukasz Szozda
Aug 21 at 18:07

1

@LukaszSzozda . . . Thank you for putting that together. I didn't notice that the date is not unique. The uniqueid is the right column to use for the logic.
â€“Â Gordon Linoff
Aug 22 at 1:42

add a commentÂ |Â

up vote
3
down vote

This is a variant on gaps-and-islands.

I like to define streaks using the difference of row numbers. This looks like

select c.*,
 (case when veganoption = 1
 then row_number() over (partition by veganoption, seqnum - seqnum_v order by uniqueid)
 else 0
 end) as veganstreak
from (select c.*,
 row_number() over (partition by veganoption order by uniqueid) as seqnum_v,
 row_number() over (order by uniqueid) as seqnum
 from c
 ) c;

Here is a Rextester.

edited Aug 22 at 1:42

answered Aug 21 at 17:59

Gordon Linoff

717k30269374

This is a variant on gaps-and-islands.

I like to define streaks using the difference of row numbers. This looks like

select c.*,
 (case when veganoption = 1
 then row_number() over (partition by veganoption, seqnum - seqnum_v order by uniqueid)
 else 0
 end) as veganstreak
from (select c.*,
 row_number() over (partition by veganoption order by uniqueid) as seqnum_v,
 row_number() over (order by uniqueid) as seqnum
 from c
 ) c;

Here is a Rextester.

edited Aug 22 at 1:42

answered Aug 21 at 17:59

Gordon Linoff

717k30269374

edited Aug 22 at 1:42

answered Aug 21 at 17:59

Gordon Linoff

717k30269374

answered Aug 21 at 17:59

Gordon Linoff

717k30269374

answered Aug 21 at 17:59

Gordon Linoff

717k30269374

It produces incorrect result: rextester.com/YDVD78565. Last two rows (4,5) instead of (1,2)
â€“Â Lukasz Szozda
Aug 21 at 18:07

1

@LukaszSzozda . . . Thank you for putting that together. I didn't notice that the date is not unique. The uniqueid is the right column to use for the logic.
â€“Â Gordon Linoff
Aug 22 at 1:42

add a commentÂ |Â

It produces incorrect result: rextester.com/YDVD78565. Last two rows (4,5) instead of (1,2)
â€“Â Lukasz Szozda
Aug 21 at 18:07

1

@LukaszSzozda . . . Thank you for putting that together. I didn't notice that the date is not unique. The uniqueid is the right column to use for the logic.
â€“Â Gordon Linoff
Aug 22 at 1:42

It produces incorrect result: rextester.com/YDVD78565. Last two rows (4,5) instead of (1,2)
â€“Â Lukasz Szozda
Aug 21 at 18:07

@LukaszSzozda . . . Thank you for putting that together. I didn't notice that the date is not unique. The uniqueid is the right column to use for the logic.
â€“Â Gordon Linoff
Aug 22 at 1:42

add a commentÂ |Â

draft saved

draft discarded

draft saved

draft discarded

Post as a guest

Name

Search This Blog

Iyfjky