Finding Count of Occurrences & Time Gap between a Timeline in MySQL Announcing the arrival...
Project Euler #1 in C++
Maximum summed subsequences with non-adjacent items
AppleTVs create a chatty alternate WiFi network
What's the meaning of "fortified infraction restraint"?
Crossing US/Canada Border for less than 24 hours
How to write this math term? with cases it isn't working
Denied boarding although I have proper visa and documentation. To whom should I make a complaint?
How to compare two different files line by line in unix?
Illegal assignment from sObject to Id
Why aren't air breathing engines used as small first stages?
What initially awakened the Balrog?
ArcGIS Pro Python arcpy.CreatePersonalGDB_management
How to install press fit bottom bracket into new frame
Effects on objects due to a brief relocation of massive amounts of mass
Sum letters are not two different
Why wasn't DOSKEY integrated with COMMAND.COM?
What is the topology associated with the algebras for the ultrafilter monad?
What would you call this weird metallic apparatus that allows you to lift people?
Generate an RGB colour grid
Do wooden building fires get hotter than 600°C?
Significance of Cersei's obsession with elephants?
How were pictures turned from film to a big picture in a picture frame before digital scanning?
Why is the AVR GCC compiler using a full `CALL` even though I have set the `-mshort-calls` flag?
Hangman Game with C++
Finding Count of Occurrences & Time Gap between a Timeline in MySQL
Announcing the arrival of Valued Associate #679: Cesar Manara
Planned maintenance scheduled April 23, 2019 at 00:00UTC (8:00pm US/Eastern)How do I calculate COUNT(*) for a range without needing to use multiple queries in mysqlError log - What does this mean and what can I do about it?Count/Statistic for number of SHOW INDEX queries in MySQL/MariaDB 5.5MySQL count multiple columns and sum the total occurrenceSubtract time difference between two columns in a tableMySQL binary log reporting execution time as UINT_MAXCombine individual sensor values into an overall value with correction factorsHow to join multiple results into one row with dynamic columns?Rock-Paper-Scissors-Spock-Lizard statistics DB design, is there a better way to achieve this?Basic question about grouping and counts
.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty{ margin-bottom:0;
}
I have a Table, like the below, which has a timeline and values (like YES/NO) that are coming from different sensors. How do I do the following:
How many times there has a been a flip-flops (for YES to NO or NO to YES), in the below example it has happened 2 times.
1 , A0001, NO, 2017-06-14 02:03:26.0
2 , A0001, NO, 2017-06-14 02:03:26.0
3 , A0001, YES, 2017-06-14 02:03:26.0
4 , A0001, NO, 2017-06-14 02:03:26.0
5 , A0001, YES, 2017-06-14 02:03:26.0
6 , A0001, NO , 2017-06-14 02:03:26.0
mysql datetime
edited Jun 14 '17 at 6:47
Marco
3,74031724
asked Jun 14 '17 at 2:09
Shan K SShan K S
1
bumped to the homepage by Community♦ 2 mins ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
add a comment |
I have a Table, like the below, which has a timeline and values (like YES/NO) that are coming from different sensors. How do I do the following:
How many times there has a been a flip-flops (for YES to NO or NO to YES), in the below example it has happened 2 times.
1 , A0001, NO, 2017-06-14 02:03:26.0
2 , A0001, NO, 2017-06-14 02:03:26.0
3 , A0001, YES, 2017-06-14 02:03:26.0
4 , A0001, NO, 2017-06-14 02:03:26.0
5 , A0001, YES, 2017-06-14 02:03:26.0
6 , A0001, NO , 2017-06-14 02:03:26.0
mysql datetime
edited Jun 14 '17 at 6:47
Marco
3,74031724
asked Jun 14 '17 at 2:09
Shan K SShan K S
1
bumped to the homepage by Community♦ 2 mins ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
To clarify: There have been two changes from 'NO' to 'YES', and two changes from 'YES' to 'NO'. That's a total of 4 changes. Are you only interested in changes in 1 direction, or perhaps only interested in round trips ('NO' => 'YES' => 'NO'). Also, are all sensors binary, or could some changes involve more than two possible values? Sample desired output, showing handling for more than two possible values if relevant, would help some.
– RDFozz
Jun 14 '17 at 16:48
add a comment |
I have a Table, like the below, which has a timeline and values (like YES/NO) that are coming from different sensors. How do I do the following:
How many times there has a been a flip-flops (for YES to NO or NO to YES), in the below example it has happened 2 times.
1 , A0001, NO, 2017-06-14 02:03:26.0
2 , A0001, NO, 2017-06-14 02:03:26.0
3 , A0001, YES, 2017-06-14 02:03:26.0
4 , A0001, NO, 2017-06-14 02:03:26.0
5 , A0001, YES, 2017-06-14 02:03:26.0
6 , A0001, NO , 2017-06-14 02:03:26.0
mysql datetime
edited Jun 14 '17 at 6:47
Marco
3,74031724
asked Jun 14 '17 at 2:09
Shan K SShan K S
1
I have a Table, like the below, which has a timeline and values (like YES/NO) that are coming from different sensors. How do I do the following:
How many times there has a been a flip-flops (for YES to NO or NO to YES), in the below example it has happened 2 times.
1 , A0001, NO, 2017-06-14 02:03:26.0
2 , A0001, NO, 2017-06-14 02:03:26.0
3 , A0001, YES, 2017-06-14 02:03:26.0
4 , A0001, NO, 2017-06-14 02:03:26.0
5 , A0001, YES, 2017-06-14 02:03:26.0
6 , A0001, NO , 2017-06-14 02:03:26.0
mysql datetime
mysql datetime
edited Jun 14 '17 at 6:47
Marco
3,74031724
asked Jun 14 '17 at 2:09
Shan K SShan K S
1
edited Jun 14 '17 at 6:47
Marco
3,74031724
asked Jun 14 '17 at 2:09
Shan K SShan K S
1
edited Jun 14 '17 at 6:47
Marco
3,74031724
edited Jun 14 '17 at 6:47
Marco
3,74031724
edited Jun 14 '17 at 6:47
Marco
3,74031724
3,74031724
asked Jun 14 '17 at 2:09
Shan K SShan K S
1
asked Jun 14 '17 at 2:09
Shan K SShan K S
1
asked Jun 14 '17 at 2:09
Shan K SShan K S
1
1
bumped to the homepage by Community♦ 2 mins ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
bumped to the homepage by Community♦ 2 mins ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
To clarify: There have been two changes from 'NO' to 'YES', and two changes from 'YES' to 'NO'. That's a total of 4 changes. Are you only interested in changes in 1 direction, or perhaps only interested in round trips ('NO' => 'YES' => 'NO'). Also, are all sensors binary, or could some changes involve more than two possible values? Sample desired output, showing handling for more than two possible values if relevant, would help some.
– RDFozz
Jun 14 '17 at 16:48
add a comment |
To clarify: There have been two changes from 'NO' to 'YES', and two changes from 'YES' to 'NO'. That's a total of 4 changes. Are you only interested in changes in 1 direction, or perhaps only interested in round trips ('NO' => 'YES' => 'NO'). Also, are all sensors binary, or could some changes involve more than two possible values? Sample desired output, showing handling for more than two possible values if relevant, would help some.
– RDFozz
Jun 14 '17 at 16:48
To clarify: There have been two changes from 'NO' to 'YES', and two changes from 'YES' to 'NO'. That's a total of 4 changes. Are you only interested in changes in 1 direction, or perhaps only interested in round trips ('NO' => 'YES' => 'NO'). Also, are all sensors binary, or could some changes involve more than two possible values? Sample desired output, showing handling for more than two possible values if relevant, would help some.
– RDFozz
Jun 14 '17 at 16:48
To clarify: There have been two changes from 'NO' to 'YES', and two changes from 'YES' to 'NO'. That's a total of 4 changes. Are you only interested in changes in 1 direction, or perhaps only interested in round trips ('NO' => 'YES' => 'NO'). Also, are all sensors binary, or could some changes involve more than two possible values? Sample desired output, showing handling for more than two possible values if relevant, would help some.
– RDFozz
Jun 14 '17 at 16:48
add a comment |
1 Answer
1
active
oldest
votes
Using Sql Server,
declare @t table(id int,col1 varchar(30), col2 varchar(30),col3 datetime)
insert into @t (id,col1,col2,col3) VALUES
(1 , 'A0001' , 'NO', '2017-06-14 02:03:26.0')
,(2 , 'A0001' , 'NO', '2017-06-15 02:03:26.0')
,(3 , 'A0001' , 'YES','2017-06-16 02:03:26.0')
,(4 , 'A0001' , 'NO', '2017-06-17 02:03:26.0')
,(5 , 'A0001' , 'YES','2017-06-18 02:03:26.0')
,(6 , 'A0001', 'NO' ,'2017-06-19 02:03:26.0')
;With CTE1 as
(
select *
,ROW_NUMBER()over(PARTITION by col1 order by col3)rn from @t
)
, cte
AS (
SELECT
rn
,col1
,col2
,col3
,0 changecount
FROM cte1
where rn=1
UNION ALL
SELECT
t.rn
,t.col1
,t.col2
,t.col3
,CASE
WHEN t.col2 <> t1.col2
THEN changecount + 1
ELSE changecount
END
FROM cte1 t
INNER JOIN cte t1 ON t.col1 = t1.col1
AND t.rn = t1.rn + 1
)
SELECT *
FROM cte
answered Jun 14 '17 at 5:48
KumarHarshKumarHarsh
95859
How does this answer OP if you're usingsql serverand the question is tagged forMySQL?
– Prabhat G
Jun 14 '17 at 8:39
add a comment |
Your Answer
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "182"
};
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: false,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
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
});
}
});
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%2fdba.stackexchange.com%2fquestions%2f176227%2ffinding-count-of-occurrences-time-gap-between-a-timeline-in-mysql%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
Using Sql Server,
declare @t table(id int,col1 varchar(30), col2 varchar(30),col3 datetime)
insert into @t (id,col1,col2,col3) VALUES
(1 , 'A0001' , 'NO', '2017-06-14 02:03:26.0')
,(2 , 'A0001' , 'NO', '2017-06-15 02:03:26.0')
,(3 , 'A0001' , 'YES','2017-06-16 02:03:26.0')
,(4 , 'A0001' , 'NO', '2017-06-17 02:03:26.0')
,(5 , 'A0001' , 'YES','2017-06-18 02:03:26.0')
,(6 , 'A0001', 'NO' ,'2017-06-19 02:03:26.0')
;With CTE1 as
(
select *
,ROW_NUMBER()over(PARTITION by col1 order by col3)rn from @t
)
, cte
AS (
SELECT
rn
,col1
,col2
,col3
,0 changecount
FROM cte1
where rn=1
UNION ALL
SELECT
t.rn
,t.col1
,t.col2
,t.col3
,CASE
WHEN t.col2 <> t1.col2
THEN changecount + 1
ELSE changecount
END
FROM cte1 t
INNER JOIN cte t1 ON t.col1 = t1.col1
AND t.rn = t1.rn + 1
)
SELECT *
FROM cte
answered Jun 14 '17 at 5:48
KumarHarshKumarHarsh
95859
How does this answer OP if you're usingsql serverand the question is tagged forMySQL?
– Prabhat G
Jun 14 '17 at 8:39
add a comment |
Using Sql Server,
declare @t table(id int,col1 varchar(30), col2 varchar(30),col3 datetime)
insert into @t (id,col1,col2,col3) VALUES
(1 , 'A0001' , 'NO', '2017-06-14 02:03:26.0')
,(2 , 'A0001' , 'NO', '2017-06-15 02:03:26.0')
,(3 , 'A0001' , 'YES','2017-06-16 02:03:26.0')
,(4 , 'A0001' , 'NO', '2017-06-17 02:03:26.0')
,(5 , 'A0001' , 'YES','2017-06-18 02:03:26.0')
,(6 , 'A0001', 'NO' ,'2017-06-19 02:03:26.0')
;With CTE1 as
(
select *
,ROW_NUMBER()over(PARTITION by col1 order by col3)rn from @t
)
, cte
AS (
SELECT
rn
,col1
,col2
,col3
,0 changecount
FROM cte1
where rn=1
UNION ALL
SELECT
t.rn
,t.col1
,t.col2
,t.col3
,CASE
WHEN t.col2 <> t1.col2
THEN changecount + 1
ELSE changecount
END
FROM cte1 t
INNER JOIN cte t1 ON t.col1 = t1.col1
AND t.rn = t1.rn + 1
)
SELECT *
FROM cte
answered Jun 14 '17 at 5:48
KumarHarshKumarHarsh
95859
How does this answer OP if you're usingsql serverand the question is tagged forMySQL?
– Prabhat G
Jun 14 '17 at 8:39
add a comment |
Using Sql Server,
declare @t table(id int,col1 varchar(30), col2 varchar(30),col3 datetime)
insert into @t (id,col1,col2,col3) VALUES
(1 , 'A0001' , 'NO', '2017-06-14 02:03:26.0')
,(2 , 'A0001' , 'NO', '2017-06-15 02:03:26.0')
,(3 , 'A0001' , 'YES','2017-06-16 02:03:26.0')
,(4 , 'A0001' , 'NO', '2017-06-17 02:03:26.0')
,(5 , 'A0001' , 'YES','2017-06-18 02:03:26.0')
,(6 , 'A0001', 'NO' ,'2017-06-19 02:03:26.0')
;With CTE1 as
(
select *
,ROW_NUMBER()over(PARTITION by col1 order by col3)rn from @t
)
, cte
AS (
SELECT
rn
,col1
,col2
,col3
,0 changecount
FROM cte1
where rn=1
UNION ALL
SELECT
t.rn
,t.col1
,t.col2
,t.col3
,CASE
WHEN t.col2 <> t1.col2
THEN changecount + 1
ELSE changecount
END
FROM cte1 t
INNER JOIN cte t1 ON t.col1 = t1.col1
AND t.rn = t1.rn + 1
)
SELECT *
FROM cte
answered Jun 14 '17 at 5:48
KumarHarshKumarHarsh
95859
Using Sql Server,
declare @t table(id int,col1 varchar(30), col2 varchar(30),col3 datetime)
insert into @t (id,col1,col2,col3) VALUES
(1 , 'A0001' , 'NO', '2017-06-14 02:03:26.0')
,(2 , 'A0001' , 'NO', '2017-06-15 02:03:26.0')
,(3 , 'A0001' , 'YES','2017-06-16 02:03:26.0')
,(4 , 'A0001' , 'NO', '2017-06-17 02:03:26.0')
,(5 , 'A0001' , 'YES','2017-06-18 02:03:26.0')
,(6 , 'A0001', 'NO' ,'2017-06-19 02:03:26.0')
;With CTE1 as
(
select *
,ROW_NUMBER()over(PARTITION by col1 order by col3)rn from @t
)
, cte
AS (
SELECT
rn
,col1
,col2
,col3
,0 changecount
FROM cte1
where rn=1
UNION ALL
SELECT
t.rn
,t.col1
,t.col2
,t.col3
,CASE
WHEN t.col2 <> t1.col2
THEN changecount + 1
ELSE changecount
END
FROM cte1 t
INNER JOIN cte t1 ON t.col1 = t1.col1
AND t.rn = t1.rn + 1
)
SELECT *
FROM cte
answered Jun 14 '17 at 5:48
KumarHarshKumarHarsh
95859
answered Jun 14 '17 at 5:48
KumarHarshKumarHarsh
95859
answered Jun 14 '17 at 5:48
KumarHarshKumarHarsh
95859
answered Jun 14 '17 at 5:48
KumarHarshKumarHarsh
95859
95859
How does this answer OP if you're usingsql serverand the question is tagged forMySQL?
– Prabhat G
Jun 14 '17 at 8:39
add a comment |
How does this answer OP if you're usingsql serverand the question is tagged forMySQL?
– Prabhat G
Jun 14 '17 at 8:39
How does this answer OP if you're using
sql server and the question is tagged for MySQL ?– Prabhat G
Jun 14 '17 at 8:39
How does this answer OP if you're using
sql server and the question is tagged for MySQL ?– Prabhat G
Jun 14 '17 at 8:39
add a comment |
Thanks for contributing an answer to Database Administrators Stack Exchange!
- 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.
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%2fdba.stackexchange.com%2fquestions%2f176227%2ffinding-count-of-occurrences-time-gap-between-a-timeline-in-mysql%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
To clarify: There have been two changes from 'NO' to 'YES', and two changes from 'YES' to 'NO'. That's a total of 4 changes. Are you only interested in changes in 1 direction, or perhaps only interested in round trips ('NO' => 'YES' => 'NO'). Also, are all sensors binary, or could some changes involve more than two possible values? Sample desired output, showing handling for more than two possible values if relevant, would help some.
– RDFozz
Jun 14 '17 at 16:48