SQL Statement to lookup data on different rows

Hi Experts,

Looking for some advice.
Have an SQL table with time stamp column and a stop flag.
I would like to run a query that updates the table to input the stopstart and stopend timestamps.
Can't think of a way to so it.
Any help most welocme.

TimeStamp                        Stop      StopStart                                         StopEnd
08/15/2014  12:42:36      TRUE            
08/15/2014  12:42:37      TRUE            
08/15/2014  12:42:38      FALSE      08/15/2014  12:42:38      08/15/2014  12:42:43
08/15/2014  12:42:39      FALSE      08/15/2014  12:42:38      08/15/2014  12:42:43
08/15/2014  12:42:40      FALSE      08/15/2014  12:42:38      08/15/2014  12:42:43
08/15/2014  12:42:41      FALSE      08/15/2014  12:42:38      08/15/2014  12:42:43
08/15/2014  12:42:42      FALSE      08/15/2014  12:42:38      08/15/2014  12:42:43
08/15/2014  12:42:43      FALSE      08/15/2014  12:42:38      08/15/2014  12:42:43
08/15/2014  12:42:44      TRUE            
08/15/2014  12:42:45      TRUE            
08/15/2014  12:42:46      TRUE            
08/15/2014  12:42:47      TRUE            
08/15/2014  12:42:48      TRUE            
08/15/2014  12:42:49      TRUE            
08/15/2014  12:42:50      TRUE            
08/15/2014  12:42:51      FALSE      08/15/2014  12:42:51      08/15/2014  12:42:52
08/15/2014  12:42:52      FALSE      08/15/2014  12:42:51      08/15/2014  12:42:52
08/15/2014  12:42:53      TRUE            
08/15/2014  12:42:54      TRUE
SweetingAAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

RayData AnalystCommented:
Are you planning to manually update each 'timestamp' entry via a query??  Not sure I'm getting the context of the question here.

to update any row in a table it is simply

update tablename
set colname = value
where timestamp = '08/15/2014  12:42:44'    <--example    (set conditions to match what you want to update)
0
SweetingAAuthor Commented:
That i can do, what i need is the logic to update automatically to the result in the table

StartStop = TimeStamp when false changes to true
StopEnd = Last timestamp before true changes to false
Any continuous False records have same StopStart and StopEnd

Actually above table has true and false records reversed, sorry, should be

TimeStamp                      Stop      StopStart                                         StopEnd
08/15/2014  12:42:36      FALSE            
08/15/2014  12:42:37      FALSE          
08/15/2014  12:42:38      TRUE       08/15/2014  12:42:38      08/15/2014  12:42:43
08/15/2014  12:42:39      TRUE       08/15/2014  12:42:38      08/15/2014  12:42:43
08/15/2014  12:42:40      TRUE       08/15/2014  12:42:38      08/15/2014  12:42:43
08/15/2014  12:42:41      TRUE       08/15/2014  12:42:38      08/15/2014  12:42:43
08/15/2014  12:42:42      TRUE       08/15/2014  12:42:38      08/15/2014  12:42:43
08/15/2014  12:42:43      TRUE       08/15/2014  12:42:38      08/15/2014  12:42:43
08/15/2014  12:42:44      FALSE          
08/15/2014  12:42:45      FALSE            
08/15/2014  12:42:46      FALSE          
08/15/2014  12:42:47      FALSE            
08/15/2014  12:42:48      FALSE          
08/15/2014  12:42:49      FALSE        
08/15/2014  12:42:50      FALSE          
08/15/2014  12:42:51      TRUE      08/15/2014  12:42:51      08/15/2014  12:42:52
08/15/2014  12:42:52      TRUE      08/15/2014  12:42:51      08/15/2014  12:42:52
08/15/2014  12:42:53      FALSE          
08/15/2014  12:42:54      TRUE

Thanks
0
PortletPaulfreelancerCommented:
Is this sql 2012 or later?     The LEAD()/LAG() functions might be handy

is there a unique identifier for each row? (it will be needed)

The following is an attempt, it is close, but not an exact match, it produces this:
| ID |                     TIMESTAMP |  STOP | STOPSTART | STOPEND |                    XSTOPSTART |                      XSTOPEND |
|----|-------------------------------|-------|-----------|---------|-------------------------------|-------------------------------|
|  1 | August, 15 2014 12:42:36+0000 |  TRUE |    (null) |  (null) |                        (null) |                        (null) |
|  2 | August, 15 2014 12:42:37+0000 |  TRUE |    (null) |  (null) |                        (null) |                        (null) |
|  3 | August, 15 2014 12:42:38+0000 | FALSE |    (null) |  (null) | August, 15 2014 12:42:37+0000 | August, 15 2014 12:42:44+0000 |
|  4 | August, 15 2014 12:42:39+0000 | FALSE |    (null) |  (null) | August, 15 2014 12:42:37+0000 | August, 15 2014 12:42:44+0000 |
|  5 | August, 15 2014 12:42:40+0000 | FALSE |    (null) |  (null) | August, 15 2014 12:42:37+0000 | August, 15 2014 12:42:44+0000 |
|  6 | August, 15 2014 12:42:41+0000 | FALSE |    (null) |  (null) | August, 15 2014 12:42:37+0000 | August, 15 2014 12:42:44+0000 |
|  7 | August, 15 2014 12:42:42+0000 | FALSE |    (null) |  (null) | August, 15 2014 12:42:37+0000 | August, 15 2014 12:42:44+0000 |
|  8 | August, 15 2014 12:42:43+0000 | FALSE |    (null) |  (null) | August, 15 2014 12:42:37+0000 | August, 15 2014 12:42:44+0000 |
|  9 | August, 15 2014 12:42:44+0000 |  TRUE |    (null) |  (null) |                        (null) |                        (null) |
| 10 | August, 15 2014 12:42:45+0000 |  TRUE |    (null) |  (null) |                        (null) |                        (null) |
| 11 | August, 15 2014 12:42:46+0000 |  TRUE |    (null) |  (null) |                        (null) |                        (null) |
| 12 | August, 15 2014 12:42:47+0000 |  TRUE |    (null) |  (null) |                        (null) |                        (null) |
| 13 | August, 15 2014 12:42:48+0000 |  TRUE |    (null) |  (null) |                        (null) |                        (null) |
| 14 | August, 15 2014 12:42:49+0000 |  TRUE |    (null) |  (null) |                        (null) |                        (null) |
| 15 | August, 15 2014 12:42:50+0000 |  TRUE |    (null) |  (null) |                        (null) |                        (null) |
| 16 | August, 15 2014 12:42:51+0000 | FALSE |    (null) |  (null) | August, 15 2014 12:42:50+0000 | August, 15 2014 12:42:53+0000 |
| 17 | August, 15 2014 12:42:52+0000 | FALSE |    (null) |  (null) | August, 15 2014 12:42:50+0000 | August, 15 2014 12:42:53+0000 |
| 18 | August, 15 2014 12:42:53+0000 |  TRUE |    (null) |  (null) |                        (null) |                        (null) |
| 19 | August, 15 2014 12:42:54+0000 |  TRUE |    (null) |  (null) |                        (null) |                        (null) |

Open in new window

Here is the query:
SELECT
      *
FROM tablename
      OUTER APPLY (
                  SELECT (
                               SELECT TOP (1)
                                     timestamp
                               FROM tablename t2
                               WHERE t2.stop <> tablename.stop
                                     AND t2.stop = 'TRUE'
                                     AND t2.timestamp < tablename.timestamp
                               ORDER BY
                                     t2.timestamp DESC
                         )
                       , (
                               SELECT TOP (1)
                                     timestamp
                               FROM tablename t2
                               WHERE t2.stop <> tablename.stop
                                     AND t2.stop = 'TRUE'
                                     AND t2.timestamp > tablename.timestamp
                               ORDER BY
                                     t2.timestamp
                         )
            ) oa2 (xstopstart, xstopend)
;

http://sqlfiddle.com/#!3/46b1f/16

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

SweetingAAuthor Commented:
yes there is a unique identifier, i will have a play and see what i can get
0
SweetingAAuthor Commented:
Hi Paul,

Unfortunately as i am a newbie, you will have to explain what it is doing and how so that i can work out what i need to change.

All help most welcome.
0
PortletPaulfreelancerCommented:
If I knew what to do I would have done it :(

I am using 2 "correlated subqueries"

Fr each record in the table each of these is evaluated. They use values from the current row as references in the where clause (that is how they are "correlated"). Each of these seeks to find a timestamp in a record either before, or after, the current record. They order the results by timestamp, either ASC or DESC, and then only use the "TOP 1" row of the result. Hence these subqueries either get the xstopstart or xstopend.

Now the problem is that they don't get the records you want, but almost, and I cannot find a way (with this approach) where I can exactly match you expected result. Despite this you might want to reconsider that expected result.

I'm not sure what you are measuring but it is probably a process, and you are sampling at a certain rate. This could mean than that my results are actually more accurate. The stopstart occurs sometime between the recorded stop event and the record prior, and the stopend occurs sometime after the stop and before the restart. In other words there is a time duration between each record in the table (approx 1 second).

So I will leave it up to you to decide if this suggestion is useful or not.
0
PortletPaulfreelancerCommented:
oh, and please answer my first question: Is this sql 2012 or later?  
0
SweetingAAuthor Commented:
Sorry Paul, i haven't been on for a bit

Its SQL 2012
0
SweetingAAuthor Commented:
Actually the more i think about it, the more agree this is exactly what i want.

Thanks
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.