SQL Outer Apply Correlated Query

Hi Experts,

PortletPaul kindly helped me with a complex SQL query (see below) which produces the results below.

UPDATE md
 SET StartDate = oa2.StartDate,
     StopDate = oa2.StopDate
 FROM dbo.tbl_MachineData md
 OUTER APPLY (SELECT
     (SELECT TOP (1) Date
     FROM dbo.tbl_MachineData t2
     WHERE t2.RunStatus <> md.RunStatus
     AND t2.RunStatus = 'TRUE'
     AND t2.Date < md.Date
     ORDER BY t2.Date DESC),
     (SELECT TOP (1) Date
     FROM dbo.tbl_MachineData t2
     WHERE t2.RunStatus <> md.RunStatus
     AND t2.RunStatus = 'TRUE'
       AND t2.Date > md.Date
     ORDER BY t2.Date)
 ) AS oa2 (StartDate, StopDate)

Results......

Date                                RunStatus      StartDate                     StopDate
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

On the face of it, all looks good but if i chnage the raget table so that the runstatus of the last row is false, the query falls down by adding a random stopdate.

Date                                RunStatus      StartDate                     StopDate
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      07/26/2014  12:42:52
08/15/2014  12:42:52      FALSE      08/15/2014  12:42:51      07/26/2014  12:42:52

If the last row is runstatus false i need to make sure no stop is added - the query is detailed below the output - should be....doesn't matter if there is no start date either.

Date                                RunStatus      StartDate                     StopDate
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      FALSE      08/15/2014  12:42:51      

Thanks for the help.
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.

PortletPaulfreelancerCommented:
former question here

Running the same query on a revised test set see http://sqlfiddle.com/#!3/62b46f/2
In that test the result is:
| ID |                          DATE | RUNSTATUS |                     STARTDATE |                      STOPDATE |
|----|-------------------------------|-----------|-------------------------------|-------------------------------|
|  1 | August, 15 2014 12:42:36+0000 |      TRUE |                        (null) |                        (null) |
|  2 | August, 15 2014 12:42:37+0000 |      TRUE |                        (null) |                        (null) |
|  3 | August, 15 2014 12:42:38+0000 |     FALSE | August, 15 2014 12:42:37+0000 | August, 15 2014 12:42:44+0000 |
|  4 | August, 15 2014 12:42:39+0000 |     FALSE | August, 15 2014 12:42:37+0000 | August, 15 2014 12:42:44+0000 |
|  5 | August, 15 2014 12:42:40+0000 |     FALSE | August, 15 2014 12:42:37+0000 | August, 15 2014 12:42:44+0000 |
|  6 | August, 15 2014 12:42:41+0000 |     FALSE | August, 15 2014 12:42:37+0000 | August, 15 2014 12:42:44+0000 |
|  7 | August, 15 2014 12:42:42+0000 |     FALSE | August, 15 2014 12:42:37+0000 | August, 15 2014 12:42:44+0000 |
|  8 | August, 15 2014 12:42:43+0000 |     FALSE | 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) |
| 10 | August, 15 2014 12:42:45+0000 |      TRUE |                        (null) |                        (null) |
| 11 | August, 15 2014 12:42:46+0000 |      TRUE |                        (null) |                        (null) |
| 12 | August, 15 2014 12:42:47+0000 |      TRUE |                        (null) |                        (null) |
| 13 | August, 15 2014 12:42:48+0000 |      TRUE |                        (null) |                        (null) |
| 14 | August, 15 2014 12:42:49+0000 |      TRUE |                        (null) |                        (null) |
| 15 | August, 15 2014 12:42:50+0000 |      TRUE |                        (null) |                        (null) |
| 16 | August, 15 2014 12:42:51+0000 |     FALSE | August, 15 2014 12:42:50+0000 |                        (null) |
| 17 | August, 15 2014 12:42:52+0000 |     FALSE | August, 15 2014 12:42:50+0000 |                        (null) ||

Open in new window


i.e. it matches the expected result.
0
SweetingAAuthor Commented:
High PortleyPaul,

You are right it works fine as long no lines have been deleted.  What you can't see in the example but its in the attacehd file is that i deleted a few lines so there are gaps in the ID numbers.  In this place only the data gets messed up.  Can SQL remember what was in the old ID numbers?  How do i gegt round this as there may be occasions where i need to delete lines.  Also as this file might get rather big, is there a way to only update data when there is no stop or start date already there.

Thanks for the hep.
SQL-Output.xls
0
PortletPaulfreelancerCommented:
It does not use ID in any of the logic, so skipping IDs doe to deletions is not relevant really.

Here is a result based on you latest sample. It does set the stopEnd date on the last record.
|  ID |                           DATE | RUNSTATUS |                      STARTDATE |                       STOPDATE |
|-----|--------------------------------|-----------|--------------------------------|--------------------------------|
| 482 | January, 26 2014 14:58:20+0000 |      TRUE |                         (null) |                         (null) |
| 483 | January, 26 2014 14:58:30+0000 |      TRUE |                         (null) |                         (null) |
| 484 | January, 26 2014 14:58:40+0000 |      TRUE |                         (null) |                         (null) |
| 485 | January, 26 2014 14:58:50+0000 |      TRUE |                         (null) |                         (null) |
| 486 | January, 26 2014 14:59:00+0000 |      TRUE |                         (null) |                         (null) |
| 487 | January, 26 2014 14:59:10+0000 |      TRUE |                         (null) |                         (null) |
| 488 | January, 26 2014 14:59:20+0000 |      TRUE |                         (null) |                         (null) |
| 489 | January, 26 2014 14:59:30+0000 |      TRUE |                         (null) |                         (null) |
| 490 | January, 26 2014 14:59:40+0000 |      TRUE |                         (null) |                         (null) |
| 491 | January, 26 2014 14:59:50+0000 |      TRUE |                         (null) |                         (null) |
| 492 | January, 26 2014 15:00:00+0000 |      TRUE |                         (null) |                         (null) |
| 493 | January, 26 2014 15:00:10+0000 |      TRUE |                         (null) |                         (null) |
| 494 | January, 26 2014 15:00:20+0000 |      TRUE |                         (null) |                         (null) |
| 495 | January, 26 2014 15:00:30+0000 |      TRUE |                         (null) |                         (null) |
| 496 | January, 26 2014 15:00:40+0000 |      TRUE |                         (null) |                         (null) |
| 497 | January, 26 2014 15:00:50+0000 |      TRUE |                         (null) |                         (null) |
| 498 | January, 26 2014 15:01:10+0000 |     FALSE | January, 26 2014 15:00:50+0000 | January, 26 2014 15:01:40+0000 |
| 499 | January, 26 2014 15:01:30+0000 |     FALSE | January, 26 2014 15:00:50+0000 | January, 26 2014 15:01:40+0000 |
| 500 | January, 26 2014 15:01:40+0000 |      TRUE |                         (null) |                         (null) |
| 501 | January, 26 2014 15:01:50+0000 |      TRUE |                         (null) |                         (null) |
| 502 | January, 26 2014 15:02:00+0000 |      TRUE |                         (null) |                         (null) |
| 503 | January, 26 2014 15:02:10+0000 |      TRUE |                         (null) |                         (null) |
| 504 | January, 26 2014 15:02:20+0000 |      TRUE |                         (null) |                         (null) |
| 505 | January, 26 2014 15:02:30+0000 |      TRUE |                         (null) |                         (null) |
| 506 | January, 26 2014 15:02:40+0000 |      TRUE |                         (null) |                         (null) |
| 507 | January, 26 2014 15:02:50+0000 |      TRUE |                         (null) |                         (null) |
| 508 | January, 26 2014 15:03:00+0000 |      TRUE |                         (null) |                         (null) |
| 509 | January, 26 2014 15:03:10+0000 |      TRUE |                         (null) |                         (null) |
| 510 | January, 26 2014 15:03:20+0000 |      TRUE |                         (null) |                         (null) |
| 511 |    July, 26 2014 15:03:30+0000 |      TRUE |                         (null) |                         (null) |
| 512 |    July, 26 2014 15:03:40+0000 |      TRUE |                         (null) |                         (null) |
| 513 |    July, 26 2014 15:03:50+0000 |      TRUE |                         (null) |                         (null) |
| 514 |    July, 26 2014 15:04:00+0000 |      TRUE |                         (null) |                         (null) |
| 515 |    July, 09 2014 15:04:10+0000 |      TRUE |                         (null) |                         (null) |
| 516 |    July, 09 2014 15:04:20+0000 |     FALSE |    July, 09 2014 15:04:10+0000 |    July, 09 2014 15:04:30+0000 |
| 517 |    July, 09 2014 15:04:30+0000 |      TRUE |                         (null) |                         (null) |
| 518 |    July, 09 2014 15:04:40+0000 |      TRUE |                         (null) |                         (null) |
| 519 |    July, 09 2014 15:04:50+0000 |     FALSE |    July, 09 2014 15:04:40+0000 |    July, 09 2014 15:05:10+0000 |
| 520 |    July, 09 2014 15:05:10+0000 |      TRUE |                         (null) |                         (null) |
| 528 |    July, 10 2014 00:00:00+0000 |     FALSE |    July, 09 2014 15:05:10+0000 |    July, 26 2014 15:03:30+0000 |

Open in new window

see http://sqlfiddle.com/#!3/7cb5e/1
0
PortletPaulfreelancerCommented:
I think you will always have this problem at this margin.

If the last record is FALSE you don't want the StopEnd because the next record might be FALSE
but the next record might be TRUE and you need that prior record updated anyway.

So the next update run will need to know at what record it stopped at last time, and update from one record before that point; Either way if it leaves the last record as NULL or if it sets the last record with a  StopEnd, that row isn't going to be correct until the next record has been inserted.

You could deliberately NOT update the last id in the table by adding something like:

where id < (select max(ID) from dbo.tbl_MachineData)

(& there are other fields in the table, perhaps they could help in this?)
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
SweetingAAuthor Commented:
Hi PortletPaul,

The additional where did the trick and its a safer solution for me, 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.