SQL - Finding sequence of events
1
I need some help identifying a sequence of events in SQL Server 08 R2.
This is the sample data:
ID | SampleTime | SampleValue | CycleNum
1 | 07:00:00 | 10 |
2 | 07:02:00 | 10 |
3 | 07:05:00 | 10 |
4 | 07:12:00 | 20 |
5 | 07:15:00 | 10 |
6 | 07:22:00 | 10 |
7 | 07:23:00 | 20 |
8 | 07:30:00 | 20 |
9 | 07:31:00 | 10 |
I have used the following as a guide, link
, but it doesn't give the required output
The rules are:
- A cycle starts at 10 and finishes at 20
- There can be multiple 10s before a 20, and multiple 20s before the next 10
- A cycle will always start at the first 10, and finish on the last 20 before the next 10.
Example Output
ID | SampleTime | SampleValue | CycleNum
1 | 07:00:00 | 10 | 1
2 | 07:02:00 | 10 | 1
3 | 07:05:00 | 10 | 1
4 | 07:12:00 | 20 | 1
5 | 07:15:00 | 10 | 2
6 | 07:22:00 | 10 | 2
7 | 07:23:00 | 20 | 2
8 | 07:30:00 | 20 | 2
9 | 07:31:00 | 10 | 3
Test Table
CREATE TABLE myTable (ID INT IDENTITY, SampleTime DATETIME, SampleValue INT, CycleNum INT)
INSERT INTO myTable (SampleTime, SampleValue)
VALUES ('07:00:00',10),
('07:02:00',10),
('07:05:00',10),
('07:12:00',20),
('07:15:00',10),
('07:22:00',10),
('07:23:00',20),
('07:30:00',20),
('07:31:00',10)
sql sql-server-2008
edited Nov 22 '18 at 2:31
Cœur
17.5k9104145
asked Mar 29 '17 at 1:15
wrofewrofe
85
add a comment |
1
I need some help identifying a sequence of events in SQL Server 08 R2.
This is the sample data:
ID | SampleTime | SampleValue | CycleNum
1 | 07:00:00 | 10 |
2 | 07:02:00 | 10 |
3 | 07:05:00 | 10 |
4 | 07:12:00 | 20 |
5 | 07:15:00 | 10 |
6 | 07:22:00 | 10 |
7 | 07:23:00 | 20 |
8 | 07:30:00 | 20 |
9 | 07:31:00 | 10 |
I have used the following as a guide, link
, but it doesn't give the required output
The rules are:
- A cycle starts at 10 and finishes at 20
- There can be multiple 10s before a 20, and multiple 20s before the next 10
- A cycle will always start at the first 10, and finish on the last 20 before the next 10.
Example Output
ID | SampleTime | SampleValue | CycleNum
1 | 07:00:00 | 10 | 1
2 | 07:02:00 | 10 | 1
3 | 07:05:00 | 10 | 1
4 | 07:12:00 | 20 | 1
5 | 07:15:00 | 10 | 2
6 | 07:22:00 | 10 | 2
7 | 07:23:00 | 20 | 2
8 | 07:30:00 | 20 | 2
9 | 07:31:00 | 10 | 3
Test Table
CREATE TABLE myTable (ID INT IDENTITY, SampleTime DATETIME, SampleValue INT, CycleNum INT)
INSERT INTO myTable (SampleTime, SampleValue)
VALUES ('07:00:00',10),
('07:02:00',10),
('07:05:00',10),
('07:12:00',20),
('07:15:00',10),
('07:22:00',10),
('07:23:00',20),
('07:30:00',20),
('07:31:00',10)
sql sql-server-2008
edited Nov 22 '18 at 2:31
Cœur
17.5k9104145
asked Mar 29 '17 at 1:15
wrofewrofe
85
Is theIDcolumn guaranteed to be contiguous?
– John Wu
Mar 29 '17 at 2:52
Yes - does using IDENTITY for the table design guarantee this?
– wrofe
Mar 29 '17 at 3:05
Using IDENTITY doesn't guarantee contiguous values. See CREATE TABLE (Transact-SQL) IDENTITY (Property)
– Mike Sherrill 'Cat Recall'
Mar 29 '17 at 13:26
add a comment |
1
1
1
I need some help identifying a sequence of events in SQL Server 08 R2.
This is the sample data:
ID | SampleTime | SampleValue | CycleNum
1 | 07:00:00 | 10 |
2 | 07:02:00 | 10 |
3 | 07:05:00 | 10 |
4 | 07:12:00 | 20 |
5 | 07:15:00 | 10 |
6 | 07:22:00 | 10 |
7 | 07:23:00 | 20 |
8 | 07:30:00 | 20 |
9 | 07:31:00 | 10 |
I have used the following as a guide, link
, but it doesn't give the required output
The rules are:
- A cycle starts at 10 and finishes at 20
- There can be multiple 10s before a 20, and multiple 20s before the next 10
- A cycle will always start at the first 10, and finish on the last 20 before the next 10.
Example Output
ID | SampleTime | SampleValue | CycleNum
1 | 07:00:00 | 10 | 1
2 | 07:02:00 | 10 | 1
3 | 07:05:00 | 10 | 1
4 | 07:12:00 | 20 | 1
5 | 07:15:00 | 10 | 2
6 | 07:22:00 | 10 | 2
7 | 07:23:00 | 20 | 2
8 | 07:30:00 | 20 | 2
9 | 07:31:00 | 10 | 3
Test Table
CREATE TABLE myTable (ID INT IDENTITY, SampleTime DATETIME, SampleValue INT, CycleNum INT)
INSERT INTO myTable (SampleTime, SampleValue)
VALUES ('07:00:00',10),
('07:02:00',10),
('07:05:00',10),
('07:12:00',20),
('07:15:00',10),
('07:22:00',10),
('07:23:00',20),
('07:30:00',20),
('07:31:00',10)
sql sql-server-2008
edited Nov 22 '18 at 2:31
Cœur
17.5k9104145
asked Mar 29 '17 at 1:15
wrofewrofe
85
I need some help identifying a sequence of events in SQL Server 08 R2.
This is the sample data:
ID | SampleTime | SampleValue | CycleNum
1 | 07:00:00 | 10 |
2 | 07:02:00 | 10 |
3 | 07:05:00 | 10 |
4 | 07:12:00 | 20 |
5 | 07:15:00 | 10 |
6 | 07:22:00 | 10 |
7 | 07:23:00 | 20 |
8 | 07:30:00 | 20 |
9 | 07:31:00 | 10 |
I have used the following as a guide, link
, but it doesn't give the required output
The rules are:
- A cycle starts at 10 and finishes at 20
- There can be multiple 10s before a 20, and multiple 20s before the next 10
- A cycle will always start at the first 10, and finish on the last 20 before the next 10.
Example Output
ID | SampleTime | SampleValue | CycleNum
1 | 07:00:00 | 10 | 1
2 | 07:02:00 | 10 | 1
3 | 07:05:00 | 10 | 1
4 | 07:12:00 | 20 | 1
5 | 07:15:00 | 10 | 2
6 | 07:22:00 | 10 | 2
7 | 07:23:00 | 20 | 2
8 | 07:30:00 | 20 | 2
9 | 07:31:00 | 10 | 3
Test Table
CREATE TABLE myTable (ID INT IDENTITY, SampleTime DATETIME, SampleValue INT, CycleNum INT)
INSERT INTO myTable (SampleTime, SampleValue)
VALUES ('07:00:00',10),
('07:02:00',10),
('07:05:00',10),
('07:12:00',20),
('07:15:00',10),
('07:22:00',10),
('07:23:00',20),
('07:30:00',20),
('07:31:00',10)
sql sql-server-2008
sql sql-server-2008
edited Nov 22 '18 at 2:31
Cœur
17.5k9104145
asked Mar 29 '17 at 1:15
wrofewrofe
85
edited Nov 22 '18 at 2:31
Cœur
17.5k9104145
asked Mar 29 '17 at 1:15
wrofewrofe
85
edited Nov 22 '18 at 2:31
Cœur
17.5k9104145
edited Nov 22 '18 at 2:31
Cœur
17.5k9104145
edited Nov 22 '18 at 2:31
Cœur
17.5k9104145
17.5k9104145
asked Mar 29 '17 at 1:15
wrofewrofe
85
asked Mar 29 '17 at 1:15
wrofewrofe
85
asked Mar 29 '17 at 1:15
wrofewrofe
85
85
Is theIDcolumn guaranteed to be contiguous?
– John Wu
Mar 29 '17 at 2:52
Yes - does using IDENTITY for the table design guarantee this?
– wrofe
Mar 29 '17 at 3:05
Using IDENTITY doesn't guarantee contiguous values. See CREATE TABLE (Transact-SQL) IDENTITY (Property)
– Mike Sherrill 'Cat Recall'
Mar 29 '17 at 13:26
add a comment |
Is theIDcolumn guaranteed to be contiguous?
– John Wu
Mar 29 '17 at 2:52
Yes - does using IDENTITY for the table design guarantee this?
– wrofe
Mar 29 '17 at 3:05
Using IDENTITY doesn't guarantee contiguous values. See CREATE TABLE (Transact-SQL) IDENTITY (Property)
– Mike Sherrill 'Cat Recall'
Mar 29 '17 at 13:26
Is the
ID column guaranteed to be contiguous?– John Wu
Mar 29 '17 at 2:52
Is the
ID column guaranteed to be contiguous?– John Wu
Mar 29 '17 at 2:52
Yes - does using IDENTITY for the table design guarantee this?
– wrofe
Mar 29 '17 at 3:05
Yes - does using IDENTITY for the table design guarantee this?
– wrofe
Mar 29 '17 at 3:05
Using IDENTITY doesn't guarantee contiguous values. See CREATE TABLE (Transact-SQL) IDENTITY (Property)
– Mike Sherrill 'Cat Recall'
Mar 29 '17 at 13:26
Using IDENTITY doesn't guarantee contiguous values. See CREATE TABLE (Transact-SQL) IDENTITY (Property)
– Mike Sherrill 'Cat Recall'
Mar 29 '17 at 13:26
add a comment |
1 Answer
1
active
oldest
votes
3
Try this... this will give the mapping of ID and CYCLENUM
WITH EVE_DATA AS (
SELECT ID
, SAMPLETIME
, SAMPLEVALUE
, CASE
WHEN (SAMPLEVALUE - lag(SAMPLEVALUE, 1, 0) over (order by SAMPLETIME ASC)) = -10
THEN 1
ELSE 0
END AS START_IND
FROM
MY_TABLE
)
SELECT T1.id
, SUM(T2.START_IND) + 1 AS CycleNum
FROM EVE_DATA T1
JOIN EVE_DATA T2
ON T1.ID >= T2.ID
GROUP BY T1.ID
ORDER BY T1.ID;
answered Mar 29 '17 at 2:03
PonsPons
933416
use of lag doesn't work on SQL Server 08 R2
– wrofe
Mar 29 '17 at 2:22
You can change the unsupportedlagexpression to(select samplevalue from mytable m where m.id = (mytable.id - 1)). The expressionmytable.id - 1is not robust, because there could be gaps in the sequence. But it's sufficient to show the way.
– Mike Sherrill 'Cat Recall'
Mar 29 '17 at 2:45
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',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f43082600%2fsql-finding-sequence-of-events%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
3
Try this... this will give the mapping of ID and CYCLENUM
WITH EVE_DATA AS (
SELECT ID
, SAMPLETIME
, SAMPLEVALUE
, CASE
WHEN (SAMPLEVALUE - lag(SAMPLEVALUE, 1, 0) over (order by SAMPLETIME ASC)) = -10
THEN 1
ELSE 0
END AS START_IND
FROM
MY_TABLE
)
SELECT T1.id
, SUM(T2.START_IND) + 1 AS CycleNum
FROM EVE_DATA T1
JOIN EVE_DATA T2
ON T1.ID >= T2.ID
GROUP BY T1.ID
ORDER BY T1.ID;
answered Mar 29 '17 at 2:03
PonsPons
933416
use of lag doesn't work on SQL Server 08 R2
– wrofe
Mar 29 '17 at 2:22
You can change the unsupportedlagexpression to(select samplevalue from mytable m where m.id = (mytable.id - 1)). The expressionmytable.id - 1is not robust, because there could be gaps in the sequence. But it's sufficient to show the way.
– Mike Sherrill 'Cat Recall'
Mar 29 '17 at 2:45
add a comment |
3
Try this... this will give the mapping of ID and CYCLENUM
WITH EVE_DATA AS (
SELECT ID
, SAMPLETIME
, SAMPLEVALUE
, CASE
WHEN (SAMPLEVALUE - lag(SAMPLEVALUE, 1, 0) over (order by SAMPLETIME ASC)) = -10
THEN 1
ELSE 0
END AS START_IND
FROM
MY_TABLE
)
SELECT T1.id
, SUM(T2.START_IND) + 1 AS CycleNum
FROM EVE_DATA T1
JOIN EVE_DATA T2
ON T1.ID >= T2.ID
GROUP BY T1.ID
ORDER BY T1.ID;
answered Mar 29 '17 at 2:03
PonsPons
933416
use of lag doesn't work on SQL Server 08 R2
– wrofe
Mar 29 '17 at 2:22
You can change the unsupportedlagexpression to(select samplevalue from mytable m where m.id = (mytable.id - 1)). The expressionmytable.id - 1is not robust, because there could be gaps in the sequence. But it's sufficient to show the way.
– Mike Sherrill 'Cat Recall'
Mar 29 '17 at 2:45
add a comment |
3
3
3
Try this... this will give the mapping of ID and CYCLENUM
WITH EVE_DATA AS (
SELECT ID
, SAMPLETIME
, SAMPLEVALUE
, CASE
WHEN (SAMPLEVALUE - lag(SAMPLEVALUE, 1, 0) over (order by SAMPLETIME ASC)) = -10
THEN 1
ELSE 0
END AS START_IND
FROM
MY_TABLE
)
SELECT T1.id
, SUM(T2.START_IND) + 1 AS CycleNum
FROM EVE_DATA T1
JOIN EVE_DATA T2
ON T1.ID >= T2.ID
GROUP BY T1.ID
ORDER BY T1.ID;
answered Mar 29 '17 at 2:03
PonsPons
933416
Try this... this will give the mapping of ID and CYCLENUM
WITH EVE_DATA AS (
SELECT ID
, SAMPLETIME
, SAMPLEVALUE
, CASE
WHEN (SAMPLEVALUE - lag(SAMPLEVALUE, 1, 0) over (order by SAMPLETIME ASC)) = -10
THEN 1
ELSE 0
END AS START_IND
FROM
MY_TABLE
)
SELECT T1.id
, SUM(T2.START_IND) + 1 AS CycleNum
FROM EVE_DATA T1
JOIN EVE_DATA T2
ON T1.ID >= T2.ID
GROUP BY T1.ID
ORDER BY T1.ID;
answered Mar 29 '17 at 2:03
PonsPons
933416
answered Mar 29 '17 at 2:03
PonsPons
933416
answered Mar 29 '17 at 2:03
PonsPons
933416
answered Mar 29 '17 at 2:03
PonsPons
933416
933416
use of lag doesn't work on SQL Server 08 R2
– wrofe
Mar 29 '17 at 2:22
You can change the unsupportedlagexpression to(select samplevalue from mytable m where m.id = (mytable.id - 1)). The expressionmytable.id - 1is not robust, because there could be gaps in the sequence. But it's sufficient to show the way.
– Mike Sherrill 'Cat Recall'
Mar 29 '17 at 2:45
add a comment |
use of lag doesn't work on SQL Server 08 R2
– wrofe
Mar 29 '17 at 2:22
You can change the unsupportedlagexpression to(select samplevalue from mytable m where m.id = (mytable.id - 1)). The expressionmytable.id - 1is not robust, because there could be gaps in the sequence. But it's sufficient to show the way.
– Mike Sherrill 'Cat Recall'
Mar 29 '17 at 2:45
use of lag doesn't work on SQL Server 08 R2
– wrofe
Mar 29 '17 at 2:22
use of lag doesn't work on SQL Server 08 R2
– wrofe
Mar 29 '17 at 2:22
You can change the unsupported
lag expression to (select samplevalue from mytable m where m.id = (mytable.id - 1)). The expression mytable.id - 1 is not robust, because there could be gaps in the sequence. But it's sufficient to show the way.– Mike Sherrill 'Cat Recall'
Mar 29 '17 at 2:45
You can change the unsupported
lag expression to (select samplevalue from mytable m where m.id = (mytable.id - 1)). The expression mytable.id - 1 is not robust, because there could be gaps in the sequence. But it's sufficient to show the way.– Mike Sherrill 'Cat Recall'
Mar 29 '17 at 2:45
add a comment |
draft saved
draft discarded
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f43082600%2fsql-finding-sequence-of-events%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Is the
IDcolumn guaranteed to be contiguous?– John Wu
Mar 29 '17 at 2:52
Yes - does using IDENTITY for the table design guarantee this?
– wrofe
Mar 29 '17 at 3:05
Using IDENTITY doesn't guarantee contiguous values. See CREATE TABLE (Transact-SQL) IDENTITY (Property)
– Mike Sherrill 'Cat Recall'
Mar 29 '17 at 13:26