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

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

SQl Correlated Query

Hi Experts,

I received a solution from PortletPaul which looked perfect for my application but on detailed checking is not exactly what i require (sorry PortletPaul, my mistake - your solution was great).

The solution is derived from an SQL 2012 stored procedure detailed below.

The result is in the attached excel file (left hand table), the desired result is in the attached excel file (right hand table).

Any help most welcome.

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
--       AND id < (select max(ID) from dbo.tbl_MachineData)
     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
--       AND id < (select max(ID) from dbo.tbl_MachineData)
     ORDER BY t2.Date)
 ) AS oa2 (StartDate, StopDate)
0
SweetingA
Asked:
SweetingA
  • 10
  • 5
  • 4
1 Solution
 
SweetingAAuthor Commented:
Sorry forgot to attach excel file
SQL-Output.xls
0
 
PortletPaulCommented:
Hi, there is only one 40 row table in the spreadsheet that I can see
it would also help if there were column headings.
482	2014-01-26 14:58:20	Continental    	Red       	I10            	18121133            	M330121   	16	16	17.5	17.5	25	5	1	TRUE	NULL	NULL	NULL	T29120A   
483	2014-01-26 14:58:30	Continental    	Red       	I10            	18121133            	M330121   	16	16	17.5	17.5	26	1	1	TRUE	NULL	NULL	NULL	T29120A   
484	2014-01-26 14:58:40	Continental    	Red       	I10            	18121133            	M330121   	16	16	17.5	17.5	26	3	1	TRUE	NULL	NULL	NULL	T29120A   
485	2014-01-26 14:58:50	Continental    	Red       	I10            	18121133            	M330121   	16	16	17.5	17.5	27	2	1	TRUE	NULL	NULL	NULL	T29120A   
486	2014-01-26 14:59:00	Continental    	Red       	I10            	18121133            	M330121   	16	16	17.5	17.5	27	2	1	TRUE	NULL	NULL	NULL	T29120A   
487	2014-01-26 14:59:10	Continental    	Red       	I10            	18121133            	M330121   	16	16	17.5	17.5	28	2	1	TRUE	NULL	NULL	NULL	T29120A   
488	2014-01-26 14:59:20	Continental    	Red       	I10            	18121133            	M330121   	16	16	17.5	17.5	29	1	1	TRUE	NULL	NULL	NULL	T29120A   
489	2014-01-26 14:59:30	Continental    	Red       	I10            	18121133            	M330121   	16	16	17.5	17.5	29	1	1	TRUE	NULL	NULL	NULL	T29120A   
490	2014-01-26 14:59:40	Continental    	Red       	I10            	18121133            	M330121   	16	16	17.5	17.5	30	1	1	TRUE	NULL	NULL	NULL	T29120A   
491	2014-01-26 14:59:50	Continental    	Red       	I10            	18121133            	M330121   	16	16	17.5	17.5	30	1	1	TRUE	NULL	NULL	NULL	T29120A   
492	2014-01-26 15:00:00	Continental    	Red       	I10            	18121133            	M330121   	16	16	17.5	17.5	31	3	1	TRUE	NULL	NULL	NULL	T29120A   
493	2014-01-26 15:00:10	Continental    	Red       	I10            	18121133            	M330121   	16	16	17.5	17.5	31	4	1	TRUE	NULL	NULL	NULL	T29120A   
494	2014-01-26 15:00:20	Continental    	Red       	I10            	18121133            	M330121   	16	16	17.5	17.5	32	2	1	TRUE	NULL	NULL	NULL	T29120A   
495	2014-01-26 15:00:30	Continental    	Red       	I10            	18121133            	M330121   	16	16	17.5	17.5	33	3	1	TRUE	NULL	NULL	NULL	T29120A   
496	2014-01-26 15:00:40	Continental    	Red       	I10            	18121133            	M330121   	16	16	17.5	17.5	33	4	1	TRUE	NULL	NULL	NULL	T29120A   
497	2014-01-26 15:00:50	Continental    	Red       	I10            	18121133            	M330121   	16	16	17.5	17.5	34	2	1	TRUE	NULL	NULL	NULL	T29120A   
498	2014-01-26 15:01:10	Continental    	Red       	I10            	18121133            	M330121   	16	16	17.5	17.5	34	0	2	FALSE	2014-01-26 15:00:50	2014-01-26 15:01:40	NULL	T29120A   
499	2014-01-26 15:01:30	Continental    	Red       	I10            	18121133            	M330121   	16	16	17.5	17.5	34	0	2	FALSE	2014-01-26 15:00:50	2014-01-26 15:01:40	NULL	T29120A   
500	2014-01-26 15:01:40	Continental    	Red       	I10            	18121133            	M330121   	16	16	17.5	17.5	36	1	2	TRUE	NULL	NULL	NULL	T29120A   
501	2014-01-26 15:01:50	Continental    	Red       	I10            	18121133            	M330121   	16	16	17.5	17.5	37	1	2	TRUE	NULL	NULL	NULL	T29120A   
502	2014-01-26 15:02:00	Continental    	Red       	I10            	18121133            	M330121   	16	16	17.5	17.5	38	1	2	TRUE	NULL	NULL	NULL	T29120A   
503	2014-01-26 15:02:10	Continental    	Red       	I10            	18121133            	M330121   	16	16	17.5	17.5	38	1	2	TRUE	NULL	NULL	NULL	T29120A   
504	2014-01-26 15:02:20	Continental    	Red       	I10            	18121133            	M330121   	16	16	17.5	17.5	39	1	2	TRUE	NULL	NULL	NULL	T29120A   
505	2014-01-26 15:02:30	Continental    	Red       	I10            	18121133            	M330121   	16	16	17.5	17.5	39	2	2	TRUE	NULL	NULL	NULL	T29120A   
506	2014-01-26 15:02:40	Continental    	Red       	I10            	18121133            	M330121   	16	16	17.5	17.5	40	2	2	TRUE	NULL	NULL	NULL	T29120A   
507	2014-01-26 15:02:50	Continental    	Red       	I10            	18121133            	M330121   	16	16	17.5	17.5	41	1	2	TRUE	NULL	NULL	NULL	T29120A   
508	2014-01-26 15:03:00	Continental    	Red       	I10            	18121133            	M330121   	16	16	17.5	17.5	41	1	2	TRUE	NULL	NULL	NULL	T29120A   
509	2014-01-26 15:03:10	Continental    	Red       	I10            	18121133            	M330121   	16	16	17.5	17.5	42	1	2	TRUE	NULL	NULL	NULL	T29120A   
510	2014-01-26 15:03:20	Continental    	Red       	I10            	18121133            	M330121   	16	16	17.5	17.5	42	1	2	TRUE	NULL	NULL	NULL	T29120A   
511	2014-07-26 15:03:30	Continental    	Red       	I10            	18121133            	M330121   	16	16	17.5	17.5	43	1	2	TRUE	NULL	NULL	NULL	T29120A   
512	2014-07-26 15:03:40	Continental    	Red       	I10            	18121133            	M330121   	16	16	17.5	17.5	43	1	2	TRUE	NULL	NULL	NULL	T29120A   
513	2014-07-26 15:03:50	Continental    	Red       	I10            	18121133            	M330121   	16	16	17.5	17.5	44	1	2	TRUE	NULL	NULL	NULL	T29120A   
514	2014-07-26 15:04:00	Continental    	Red       	I10            	18121133            	M330121   	16	16	17.5	17.5	45	1	2	TRUE	NULL	NULL	NULL	T29120A   
515	2014-07-09 15:04:10	Continental    	Red       	I10            	18121133            	M330121   	16	16	17.5	17.5	45	1	2	TRUE	NULL	NULL	NULL	T29120A   
516	2014-07-09 15:04:20	Continental    	Red       	I10            	18121133            	M330121   	16	16	17.5	17.5	45	2	3	FALSE	2014-07-09 15:04:10	2014-07-09 15:04:30	NULL	T29120A   
517	2014-07-09 15:04:30	Continental    	Red       	I10            	18121133            	M330121   	16	16	17.5	17.5	46	2	3	TRUE	NULL	NULL	NULL	T29120A   
518	2014-07-09 15:04:40	Continental    	Red       	I10            	18121133            	M330121   	16	16	17.5	17.5	47	2	3	TRUE	NULL	NULL	NULL	T29120A   
519	2014-07-09 15:04:50	Continental    	Red       	IN8            	18160103            	M330121   	16	16	17.5	17.5	47	0	4	FALSE	2014-07-09 15:04:40	2014-07-09 15:05:10	NULL	T29060A   
520	2014-07-09 15:05:10	Continental    	Red       	IN8            	18160103            	M330121   	16	16	17.5	17.5	48	1	4	TRUE	NULL	NULL	NULL	T29060A   
528	2014-07-10 00:00:00	Continental    	Red       	IN8            	18160103            	M330121   	16	16	17.5	17.5	48	0	5	FALSE	2014-07-09 15:05:10	2014-07-26 15:03:30	NULL	T29060A   

Open in new window

0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
The result is in the attached excel file (left hand table), the desired result is in the attached excel file (right hand table).
I can only see one worksheet with those 40 rows that PortletPaul posted,
0
Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

 
SweetingAAuthor Commented:
I am a bit of a dummy, seems like i saved it on another pc - see attached excel file
SQLSampleData.xls
0
 
SweetingAAuthor Commented:
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
By the logic I would say that you will only need the discover StopDate because the StartDate it's always the Date.
UPDATE md
  SET StartDate = Date,
      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)
  ) AS oa2 (StopDate) 

Open in new window

0
 
SweetingAAuthor Commented:
Unfortunately thats not correct, the Startdate is not always the Date, you need to look a little more closely

Thanks
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
Oh yes, now I saw that.
So, the logic is only change the StartDate when RunStatus changes to FALSE? And need to have same StartDate until the RunStatus changes back to TRUE?
0
 
SweetingAAuthor Commented:
yes, thats right
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
I can't see a solution with a single update only.
Or you do it with a cursor or with a 2nd update. I prefer the second update:
UPDATE md
SET StartDate = Date,
      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)
  ) AS oa2 (StopDate) 


UPDATE md
SET md.StartDate = t2.StartDate,
      md.StopDate = t2.StopDate
FROM dbo.tbl_MachineData md
INNER JOIN dbo.tbl_MachineData t2
      WHERE t2.RunStatus = 'FALSE'
        AND t2.StartDate IS NOT NULL
        AND t2.Date > md.Date
WHERE t2.RunStatus = 'FALSE'
        AND t2.StartDate IS NULL

Open in new window


NOTE: Code not tested.
0
 
SweetingAAuthor Commented:
Hello Vitor

Nearly there but not quite

The stopdates are correct
The first startdate in each sequence is correct but susequent ones are wrong
The startdate has to be the same value until the flag changes, in this code the startdate is always = date and hence changes on every line.

Regards
Alan
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
Like I said, I didn't test the code. I don't have where to test it, so I'm figuring only in my mind.
Let's try using a similar query of the first update:

UPDATE md
SET StartDate = Date,
      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)
  ) AS oa2 (StopDate) 


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

Open in new window

0
 
SweetingAAuthor Commented:
Hello Vitor,

There is one too many brackets in the code, I have removed the one on line 6 which causes the initial problem but I still get an error on the outer apply line with 3 sections of code underlined on the last 3 lines (oa2 , t2.RunStatus and t2.StartDate).  The erro message is detailed below.

Msg 8159, Level 16, State 1, Procedure stp_UpdateStartDates, Line 54
'oa2' has fewer columns than were specified in the column list.

Thanks so far
0
 
SweetingAAuthor Commented:
It may be easier if I describe in logic what I need to do in SQL.....

IF RunStatus on the previous record = False And RunStatus on the current record = False Then
StartDate on current record = StartDate on previous record
Else If
RunStatus on the previous record = False And RunStatus on the current record = False Then
StartDate on current record = Date on current record

Thanks for the help so far, any other experts able to assist?
0
 
PortletPaulCommented:
The following relies on ID being in a sequence that can be relied on. If this is not true, then a common table expression (CTE) could be used before the update that uses row_number() to produce the required reliable sequence of numbers.

But assuming ID is sufficient, then the following simply adjusts the previous StartDate calculation by finding the "next up" Date, and using that instead. It appears the StopDate calculation was OK anyway so it doesn't need adjustment.

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 t3
                         WHERE id > (
                                     SELECT TOP (1)
                                           id
                                     FROM dbo.tbl_MachineData t2
                                     WHERE t2.RunStatus <> md.RunStatus
                                           AND t2.RunStatus = 'TRUE'
                                           AND t2.Date < md.Date
                                     --AND id < ( SELECT MAX(ID) FROM dbo.tbl_MachineData )
                                     ORDER BY
                                           t2.Date DESC
                               )
                         ORDER BY
                               id ASC
                         )
                       , (
                         SELECT TOP (1)
                               Date
                         FROM dbo.tbl_MachineData t2
                         WHERE t2.RunStatus <> md.RunStatus
                               AND t2.RunStatus = 'TRUE'
                               AND t2.Date > md.Date
                         --AND id < ( SELECT MAX(ID) FROM dbo.tbl_MachineData )
                         ORDER BY
                               t2.Date ASC
                         )
            ) AS oa2 (StartDate, StopDate)

select
  id
, convert(varchar,[Date],120) [Date]
, runstatus
, convert(varchar,[StartDate],120) [StartDate]
, convert(varchar,[StopDate],120) [StopDate]
from dbo.tbl_MachineData
;

Open in new window

Results:
|  ID |                DATE | RUNSTATUS |           STARTDATE |            STOPDATE |
|-----|---------------------|-----------|---------------------|---------------------|
| 483 | 2014-01-26 14:58:30 |      TRUE |              (null) |              (null) |
| 484 | 2014-01-26 14:58:40 |      TRUE |              (null) |              (null) |
| 485 | 2014-01-26 14:58:50 |      TRUE |              (null) |              (null) |
| 486 | 2014-01-26 14:59:00 |      TRUE |              (null) |              (null) |
| 487 | 2014-01-26 14:59:10 |      TRUE |              (null) |              (null) |
| 488 | 2014-01-26 14:59:20 |      TRUE |              (null) |              (null) |
| 489 | 2014-01-26 14:59:30 |      TRUE |              (null) |              (null) |
| 490 | 2014-01-26 14:59:40 |      TRUE |              (null) |              (null) |
| 491 | 2014-01-26 14:59:50 |      TRUE |              (null) |              (null) |
| 492 | 2014-01-26 15:00:00 |      TRUE |              (null) |              (null) |
| 493 | 2014-01-26 15:00:10 |      TRUE |              (null) |              (null) |
| 494 | 2014-01-26 15:00:20 |      TRUE |              (null) |              (null) |
| 495 | 2014-01-26 15:00:30 |      TRUE |              (null) |              (null) |
| 497 | 2014-01-26 15:00:40 |      TRUE |              (null) |              (null) |
| 498 | 2014-01-26 15:01:10 |     FALSE | 2014-01-26 15:01:10 | 2014-01-26 15:03:00 |
| 499 | 2014-01-26 15:01:30 |     FALSE | 2014-01-26 15:01:10 | 2014-01-26 15:03:00 |
| 500 | 2014-01-26 15:03:00 |      TRUE |              (null) |              (null) |
| 509 | 2014-01-26 15:03:10 |      TRUE |              (null) |              (null) |
| 510 | 2014-01-26 15:03:20 |      TRUE |              (null) |              (null) |
| 511 | 2014-07-26 15:03:30 |      TRUE |              (null) |              (null) |
| 512 | 2014-07-26 15:03:40 |      TRUE |              (null) |              (null) |
| 513 | 2014-07-26 15:03:50 |      TRUE |              (null) |              (null) |
| 514 | 2014-07-26 15:04:00 |      TRUE |              (null) |              (null) |
| 515 | 2014-07-09 15:04:10 |      TRUE |              (null) |              (null) |
| 516 | 2014-07-09 15:04:20 |     FALSE | 2014-07-09 15:04:20 | 2014-07-09 15:04:30 |
| 517 | 2014-07-09 15:04:30 |      TRUE |              (null) |              (null) |
| 518 | 2014-07-09 15:04:40 |      TRUE |              (null) |              (null) |
| 519 | 2014-07-09 15:04:50 |     FALSE | 2014-07-09 15:04:50 | 2014-07-09 15:05:10 |
| 520 | 2014-07-09 15:05:10 |      TRUE |              (null) |              (null) |

Open in new window

see: http://sqlfiddle.com/#!3/fbaf4/2
0
 
SweetingAAuthor Commented:
Almost perfect, it works until it reaches the last record and if the last record is false it should leave the stopdate empty, at the moment it just addsa random date.

Can i ask a very basic question please about the procedure to do it slightly differently......

If a column was added  to the table with "PreviousRunStatus" created by the lag function LAG(RunStatus,1,0) - would it then make it easier to compare to whats on the previous row?
0
 
PortletPaulCommented:
uncomment the piece we previously added for this issue, see line 33, take out the "--"

I have not checked if line 19 makes sense to be uncommented, that is for you to test.

Regarding another column with LAG(RunStatus,1,0); I would say maybe, but it would probably also need a lag() value on runstatus.

Do you have sql server 2012 or later? (PLEASE always include the actual version as a tag or in the question text.)
0
 
SweetingAAuthor Commented:
Apologies, i never realised it was there, yes works fine

I do use 2012 now as it has a few extra bits and pieces.

I struggle with LAG - never maanged to get it work in an update stored procedure, can only use it in a very basic query.  When the query is more complicated i normally get an error telling me it can;t draw the picture.

Thanks again for the help and the patience.
0
 
PortletPaulCommented:
Thank you. Cheers, Paul
0

Featured Post

Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

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