[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 115
  • Last Modified:

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
0
SweetingA
Asked:
SweetingA
  • 5
  • 3
1 Solution
 
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
 
PortletPaulCommented:
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
 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

 
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
 
PortletPaulCommented:
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
 
PortletPaulCommented:
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

Featured Post

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

  • 5
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now