Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

Troubleshooting
Research
Professional Opinions
Ask a Question
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

troubleshooting Question

SQL Outer Apply Correlated Query

Avatar of SweetingA
SweetingA asked on
Microsoft SQL Server 2008
5 Comments1 Solution196 ViewsLast Modified:
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.