Mysql Select Statement for getting multiple records traveldate from date to till date in where clause kmph 0 to 0

Question

0.00/5 (No votes)

See more:

SQL-Server-2012

I am trying to get the below select statement to work In mysql. Essentially
I am trying to get a
multiple rows per kmph (minimum 0 to end of 0) & get from date to till date where kmph = 'o'
to end of kmph=0.
my table data like below

deviceID    timestamp             speedkph
---------   ---------             ------
3456        '2014-10-26 19:10'    0
3456        '2014-10-26 19:12'    12
3456        '2014-10-26 19:15'    14
3456        '2014-10-26 19:16'    20
3456        '2014-10-26 19:19'    30
3456        '2014-10-26 19:20'    0
3456        '2014-10-26 19:21'    0
3456        '2014-10-26 19:22'    0
3456        '2014-10-26 19:23'    10
3456        '2014-10-26 19:24'    12
3456        '2014-10-26 19:25'    18
3456        '2014-10-26 19:26'    0
3456        '2014-10-26 19:27'    0
3457        '2014-10-26 19:22'    0
3457        '2014-10-26 19:23'    10
3457        '2014-10-26 19:24'    12
3457        '2014-10-26 19:25'    18
3457        '2014-10-26 19:26'    0
3457        '2014-10-26 19:28'    0

I need Output like below

deviceID    fromdate             Tilldate            Duration
---------   ---------            ------              -------
3456        '2014-10-26 19:10'  '2014-10-26 19:20'    00:09
3456        '2014-10-26 19:22'  '2014-10-26 19:26'    00:03
3457        '2014-10-26 19:22'  '2014-10-26 19:26'    00:03

can u please give me select statement anyone..
thanks for advance

Posted 7-Nov-14 19:17pm

pramodgorityala

Updated 7-Nov-14 19:40pm

DamithSL

v2

Add a Solution

Comments

Tomas Takac 8-Nov-14 2:53am

Your title says your database is MySQL but the tag says it's SQL Server 2012. So which one is it really? Please edit your title or your tags.

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Maciej Los · Answer 1 · 2014-11-09T00:31:00

Solution 2

As per my understanding, there is no chance to get output you wish to get, because there is no start and end tags and zero could be treated as a start or end tag.

I'd suggest to use cursor[^]. See: MySQL Cursor[^]. Cursors enables you to fetch data in WHILE loop[^].

Posted 9-Nov-14 0:31am

Maciej Los

Comments

Garth J Lancaster 9-Nov-14 6:36am

Im glad you said that mate - I was thinking, the dataset is going to have to be iterated through somehow, using a cursor on the DB side or ?? on the client side - you get '5' from me

Maciej Los 9-Nov-14 6:40am

Thank you, Garth ;)