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?

Answer 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