[Last Call] Learn how to a build a cloud-first strategyRegister Now

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

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.
0
SweetingA
Asked:
SweetingA
  • 3
  • 2
1 Solution
 
PortletPaulCommented:
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
 
PortletPaulCommented:
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
 
PortletPaulCommented:
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
 
SweetingAAuthor Commented:
Hi PortletPaul,

The additional where did the trick and its a safer solution for me, thanks
0

Featured Post

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

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