Click here to Skip to main content
15,888,579 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
Posted an hour ago
i have records in database as fallows
C#
status                 timestamp

1                         123

1                         124

0                          125

0                         126

1                         127

1                        128

1                         129

0                         130

but i want the records 123 and 125 in same row and 127 and 130 in same row so on how to do it
Posted
Updated 9-Dec-14 7:06am
v5
Comments
Kornfeld Eliyahu Peter 9-Dec-14 5:45am    
Why? What they have in common?
Tomas Takac 9-Dec-14 5:55am    
What do you mena by "in the same row"? Could you post the output you expect?
Thanks7872 9-Dec-14 6:35am    
He meant something like
123,125
127,130....
But the big question is why?
Tomas Takac 9-Dec-14 7:16am    
I see it now - status changes are the key. He wants timestamp when 1 appeared first time along with timestamp when 0 appeared first time.
Sinisa Hajnal 9-Dec-14 5:59am    
You can use STUFF to add things you want in single comma separated field, but you have to be able to select those you need. From the data above, there is no way to do that.

1 solution

Seems like a classic Islands and Gaps problem with edges.
Try this:
SQL
with changes as (
    select  status - lag(status,1,0) over (order by timestamp) change
           ,timestamp
    from    Table1
    )
,up as (
    select  timestamp
           ,row_number() over (order by timestamp) rn
    from    changes
    where   change = 1
    )
,down as (
    select  timestamp
           ,row_number() over (order by timestamp) rn
    from    changes
    where   change = -1
    )
select  u.timestamp up,d.timestamp down,u.rn rn
from    up u
join    down d on u.rn = d.rn
order by rn

Here's[^] the fiddle.
This works on SQLServer 2012 and Oracle 10G, but earlier versions lack the Lag function. It can be implemented on those easy enough using rownumber and a self join.
So if you have an earlier version or another database you'll need to update your question with database and version.

Here's an updated solution for MySQL:
SQL
SELECT  up.TIMESTAMP up,down.TIMESTAMP down,up.rn
FROM    (
    SELECT  TIMESTAMP
        ,@r1 := @r1 + 1 AS rn
    FROM    (
        SELECT  t1.timestamp
        FROM    Table1 t1 
        left OUTER JOIN Table1 t2 ON t1.timestamp = t2.timestamp + 1
        WHERE   t1.status - ifnull(t2.status,0) = 1
        ORDER BY t1.timestamp
        ) t1
        ,(SELECT @r1 := 0) r
    ) up
    ,(
        SELECT  TIMESTAMP
        ,@r2 := @r2 + 1 AS rn
    FROM    (
        SELECT  t1.timestamp
        FROM    Table1 t1 
        left OUTER JOIN Table1 t2 ON t1.timestamp = t2.timestamp + 1
        WHERE   t1.status - ifnull(t2.status,0) = -1
        ORDER BY t1.timestamp
        ) t1
        ,(SELECT @r2 := 0) r
    ) down
WHERE   up.rn = down.rn
ORDER BY up.rn

Here's[^] the fiddle.
When you need to create a contraption like this it's a fitting time to say how much I despise MySQL.

Note: This code assumes that there are no gaps in the timestamp sequence.
If there are, you need to add a couple more IfNull
 
Share this answer
 
v3
Comments
chandubbbb 9-Dec-14 11:48am    
Hi Jörgen Andersson,
Thanks for solution
but i need to work it out in MySql how to write it.
Actually I am working on one of the gps tracking application where i need to generate the report for Ignition on and off status of vehicle as i mentioned 1,0 are igniton status. i should have to select on and off status of vehicle between the specified intervels .please once look into this if have time. thanks in advance
Jörgen Andersson 9-Dec-14 13:00pm    
Which version?
chandubbbb 9-Dec-14 21:47pm    
5.6
Jörgen Andersson 10-Dec-14 3:43am    
Updated solution
chandubbbb 16-Dec-14 12:01pm    
thanks for ur help ,the solution worked for me

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



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900