SQL - Select all rows between two string rows

Question

Is it possible to return all rows between matched rows?

What I'm trying to is query an audit table where jobs writes an audit table. There is a clear start audit message and end audit message as well as info between. Is it possible to get a select statement to return all the rows between the "start" audit entry and "end" entry?

Example of data.

DATE                  TIME   USER                ENTRY                                                                              
----------------------- -------- -------------------- --------------------------------------------------------------------
2015-04-13 07:30:15.150 07:30:15 CmdLne               SOME JOB STARTED FOR PROCESSING DATE 13/04/2015                       
2015-04-13 07:31:15.150 07:31:15 CmdLne               PROCESSED 10 WHATEVERS
2015-04-13 07:32:25.150 07:32:25 CmdLne               PROCESSED 10 SOMETHINGS
2015-04-13 07:33:33.150 07:33:33 CmdLne               PROCESSED 40 XYZ
2015-04-13 07:33:34.150 07:33:34 CmdLne               SOME JOB FINISHED FOR PROCESSING DATE 13/04/2015

Because it's unknown how many audit entries there will be during an audit write it has to be able to select everything between the "start" entry and "end" entry. Is this possible?

Do you know the start / end entry or the entry-code for the non-start-rows?? — Hockenberry
– Hockenberry, Commented Apr 19, 2015 at 21:38
Can there be overlapping audited events, i.e. can users Fred and Wilma be doing work that causes the audit table to have a mix of records for both of them? Can a single user cause overlapping audited events, e.g. a long running report and importing new data, and, if so, how would you sort them out? — HABO
– HABO, Commented Apr 20, 2015 at 2:21

Stephan · Accepted Answer · 2015-04-20 01:48:05Z

2

Here's a simple and clean solution. If you have any questions or need anything else let me know.

SELECT  A.[DATE],
        A.[TIME],
        A.[User],
        A.[Entry]
FROM @Table A
CROSS APPLY(SELECT MIN([Date]) FROM @Table WHERE [Entry] LIKE 'Some Job%') CA_min(start_dt)
CROSS APPLY(SELECT MAX([Date]) FROM @Table WHERE [Entry] LIKE 'Some Job%') CA_max(end_dt)
WHERE [DATE] BETWEEN start_dt AND end_dt

answered Apr 20, 2015 at 1:48

Stephan

6,0581 gold badge19 silver badges26 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Stephan Over a year ago

CROSS APPLY Is basically acting like a subquery, but using cross apply allows me to reference it in the where clause. Are you familiar with subqueries?

Hockenberry · Accepted Answer · 2015-04-19 21:50:57Z

0

Try some ORDER-BY with ASC / DESC The

WITH Data (Date, Time, User, Entry) AS
    (
    SELECT Date, Time, User, Entry
    FROM data
    ORDER BY DATE ASC, TIME ASC
    ) 

SELECT TOP ((SELECT COUNT(*) FROM Data) - 2) *
FROM (
    SELECT TOP ((SELECT COUNT(*) FROM Data) - 1) *
    FROM Data
    ORDER BY Date ASC, Time ASC  -- all rows without the end-row
    ) tmp
ORDER BY Date ASC, Time DESC -- all rows without the start-row

Or something simpler with a WHERE-Clause.

SELECT Date, Time, User, Entry
FROM data
WHERE Entry NOT LIKE '%JOB STARTED%' 
  AND Entry NOT LIKE '%JOB FINISHED%'

edited Apr 19, 2015 at 21:50

answered Apr 19, 2015 at 21:44

Hockenberry

4623 silver badges10 bronze badges

Comments

M.Ali · Accepted Answer · 2015-04-19 21:51:28Z

Test Data

DECLARE @Table TABLE ([DATE] Datetime ,[TIME] TIME, [USER] VARCHAR(100) , [ENTRY] VARCHAR(1000))
INSERT INTO @Table VALUES                                                                              
('2015-04-13 07:30:15.150','07:30:15','CmdLne','SOME JOB STARTED FOR PROCESSING DATE 13/04/2015'),                       
('2015-04-13 07:31:15.150','07:31:15','CmdLne','PROCESSED 10 WHATEVERS'),
('2015-04-13 07:32:25.150','07:32:25','CmdLne','PROCESSED 10 SOMETHINGS'),
('2015-04-13 07:33:33.150','07:33:33','CmdLne','PROCESSED 40 XYZ'),
('2015-04-13 07:33:34.150','07:33:34','CmdLne','SOME JOB FINISHED FOR PROCESSING DATE 13/04/2015') ,
('2015-04-13 07:30:15.150','07:30:15','Powershell','SOME JOB STARTED FOR PROCESSING DATE 13/04/2015'),                       
('2015-04-13 07:31:15.150','07:31:15','Powershell','PROCESSED 10 WHATEVERS'),
('2015-04-13 07:32:25.150','07:32:25','Powershell','PROCESSED 10 SOMETHINGS'),
('2015-04-13 07:33:33.150','07:33:33','Powershell','PROCESSED 40 XYZ'),
('2015-04-13 07:33:34.150','07:33:34','Powershell','SOME JOB FINISHED FOR PROCESSING DATE 13/04/2015')

Query

SELECT t1.*
FROM @Table t1
WHERE t1.[DATE] >= ( SELECT TOP 1 [DATE] FROM @Table
                     WHERE [ENTRY] LIKE '%SOME JOB STARTED%'
                      AND  t1.[USER] = [USER])
 AND t1.[DATE] <= ( SELECT TOP 1 [DATE] FROM @Table
                     WHERE [ENTRY] LIKE '%SOME JOB FINISHED%'
                      AND  t1.[USER] = [USER])
 AND t1.[USER] = 'CmdLne'  --<--

Vasily · Accepted Answer · 2015-04-20 00:25:55Z

Data sample

DECLARE @Table TABLE
    (
      [DATE] DATETIME ,
      [TIME] TIME ,
      [USER] VARCHAR(100) ,
      [ENTRY] VARCHAR(1000)
    )
INSERT  INTO @Table
VALUES  ( '2015-04-13 07:30:15.150', '07:30:15', 'CmdLne',
          'SOME JOB STARTED FOR PROCESSING DATE 13/04/2015' ),
        ( '2015-04-13 07:31:15.150', '07:31:15', 'CmdLne',
          'PROCESSED 10 WHATEVERS' ),
        ( '2015-04-13 07:32:25.150', '07:32:25', 'CmdLne',
          'PROCESSED 10 SOMETHINGS' ),
        ( '2015-04-13 07:33:33.150', '07:33:33', 'CmdLne', 'PROCESSED 40 XYZ' ),
        ( '2015-04-13 07:33:34.150', '07:33:34', 'CmdLne',
          'SOME JOB FINISHED FOR PROCESSING DATE 13/04/2015' ) ,
        ( '2015-04-13 07:30:15.150', '07:30:15', 'Powershell',
          'SOME JOB STARTED FOR PROCESSING DATE 13/04/2015' ),
        ( '2015-04-13 07:31:15.150', '07:31:15', 'Powershell',
          'PROCESSED 10 WHATEVERS' ),
        ( '2015-04-13 07:32:25.150', '07:32:25', 'Powershell',
          'PROCESSED 10 SOMETHINGS' ),
        ( '2015-04-13 07:33:33.150', '07:33:33', 'Powershell',
          'PROCESSED 40 XYZ' ),
        ( '2015-04-13 07:33:34.150', '07:33:34', 'Powershell',
          'SOME JOB FINISHED FOR PROCESSING DATE 13/04/2015' )

Final query

SELECT  *
FROM    ( SELECT    ROW_NUMBER() OVER ( PARTITION BY CONVERT(DATE, T.DATE),
                                        T.[USER] ORDER BY T.DATE ) AS RN ,
                    *
          FROM      @Table AS T
        ) T
WHERE   T.RN NOT IN ( 1, 2 )

Collectives™ on Stack Overflow

SQL - Select all rows between two string rows

4 Answers 4

1 Comment

Comments

Test Data

Query

Comments

Data sample

Final query

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

Comments

Test Data

Query

Comments

Data sample

Final query

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related