You must Sign In to post a response.
  • Category: SQL Server

    Help me to write a SQL query

    Hi All,

    I have a table 'Tbl_Track' it has columns ID,Speed,TimeStamp.

    Eg Data:

    ID Speed Timestamp
    1 0 2015-01-12 16:38:44.000
    2 0 2015-01-12 16:45:44.000
    3 20 2015-01-12 16:46:00.000
    4 40 2015-01-12 16:50:44.000
    5 0 2015-01-12 16:51:00.000
    6 0 2015-01-12 16:52:00.000
    7 0 2015-01-12 16:53:00.000
    8 10 2015-01-12 16:54:00.000
    9 20 2015-01-12 16:55:00.000
    10 0 2015-01-12 16:56:00.000

    Now i need a query to find Min(TimeStamp), Max(TimeStamp) where the speed is 0

    result should be like
    Speed is 0 from 2015-01-12 16:38:44.000 to 2015-01-12 16:45:44.000
    Speed is 0 from 2015-01-12 16:51:00.000 to 2015-01-12 16:53:00.000
    Speed is 0 from 2015-01-12 16:56:00.000 to 2015-01-12 16:56:00.000
  • #755806
    Hi,

    Refer below sample code

    select Min(timestamp) from tablename where speed=0

    select Max(timestamp) from tablename where speed=0

    Hope this could be helpful to you to resolve the issue.

    --------------------------------------------------------------------------------
    Give respect to your work, Instead of trying to impress your boss.

    N@veen
    Blog : http://naveens-dotnet.blogspot.in/

  • #755810
    Hi,

    check the Querry like below sample COde:

    select Min(timestamp) as MinTimestamp, Max(timestamp) As MaxTimeStamp From Tablename where speed=0


    It will helpful for UU..

    Thanks,
    Chitaranjan

  • #755854
    Hi,

    Refer the below to get your excepted output

    CREATE TABLE speed
    (
    id INT,
    speed INT,
    [timestamp] DATETIME
    )

    INSERT INTO speed
    VALUES (1,
    0,
    '2015-01-12 16:38:44.000')

    INSERT INTO speed
    VALUES (2,
    0,
    '2015-01-12 16:45:44.000')

    INSERT INTO speed
    VALUES (3,
    20,
    '2015-01-12 16:46:00.000')

    INSERT INTO speed
    VALUES (4,
    40,
    '2015-01-12 16:50:44.000')

    INSERT INTO speed
    VALUES (5,
    0,
    '2015-01-12 16:51:00.000')

    INSERT INTO speed
    VALUES (6,
    0,
    '2015-01-12 16:52:00.000')

    INSERT INTO speed
    VALUES (7,
    0,
    '2015-01-12 16:53:00.000')

    INSERT INTO speed
    VALUES (8,
    10,
    '2015-01-12 16:54:00.000')

    INSERT INTO speed
    VALUES (9,
    20,
    '2015-01-12 16:55:00.000')

    INSERT INTO speed
    VALUES (10,
    0,
    '2015-01-12 16:56:00.000')

    SELECT *
    INTO #a
    FROM (SELECT *,
    ROW_NUMBER()
    OVER (
    partition BY speed
    ORDER BY speed, [timestamp] ) AS rownum
    FROM speed) AS foo

    SELECT rownum,
    speed,
    [timestamp] AS [Timestamp1],
    (SELECT [timestamp]
    FROM #a
    WHERE rownum = co.rownum + 1
    AND speed = co.speed) AS [Timestamp2]
    FROM #a CO
    WHERE rownum%2 = 1

  • #756185
    select
    min(timestamp)
    , Max(timestamp)
    from tablename
    where speed=0

    Thanks & Regards,
    Abhijith

    Mail - abhijith.pn@gmail.com
    Check My Blog - http://www.solvemytechissue.in/

  • #756186
    Hi,

    Please refer below code snippet:

    Select Min(TimeStamp) As MinTimeStamp, Max(TimeStamp) As MaxTimeStamp From Tbl_Track Where Speed=0;

    Hope it will be helpful to you.

    Thanks,
    Ram Prasad


  • Sign In to post your comments