SQL - Select all rows between two string rows

问题:

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?

回答1:

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

回答2:

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%'

回答3:

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'  --<-- 

回答4:

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 )