Sfrgttk

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

.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty{ margin-bottom:0;
}

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

share|improve this question

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 | 

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

share|improve this question

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

share|improve this question

edited Jun 14 '17 at 6:47

Marco

3,74031724

asked Jun 14 '17 at 2:09

Shan K SShan K S

1

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

share|improve this question

edited Jun 14 '17 at 6:47

Marco

3,74031724

asked Jun 14 '17 at 2:09

Shan K SShan K S

1

share|improve this question

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

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 Answer
1

active

oldest

votes

0

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

share|improve this answer

answered Jun 14 '17 at 5:48

KumarHarshKumarHarsh

95859

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  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 | 

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


}
});

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

Name

Email

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

Name

Email

Required, but never shown

0

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

share|improve this answer

answered Jun 14 '17 at 5:48

KumarHarshKumarHarsh

95859

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  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 | 

0

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

share|improve this answer

answered Jun 14 '17 at 5:48

KumarHarshKumarHarsh

95859

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  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 | 

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

share|improve this answer

answered Jun 14 '17 at 5:48

KumarHarshKumarHarsh

95859

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

share|improve this answer

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