Est-il possible de returnner toutes les lignes entre les lignes correspondantes?
Ce que j'essaie de faire est d'interroger une table d'audit où les travaux écrit une table d'audit. Il y a un message d'audit de début clair et un message d'audit final ainsi que des informations entre. Est-il possible d'get une instruction select pour returnner toutes les lignes entre l'input d'audit "start" et l'input "end"?
Exemple de données
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
Étant donné que le nombre d'inputs d'audit lors de l'audit est inconnu, il doit être capable de sélectionner tout ce qui se trouve entre l'input "start" et l'input "end". Est-ce possible?
Voici une solution simple et propre. Si vous avez des questions ou avez besoin de quelque chose d'autre, faites-le moi savoir.
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
Essayez quelques ORDER-BY
avec ASC / DESC
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
Ou quelque chose de plus simple avec une WHERE
WHERE.
SELECT Date, Time, User, Entry FROM data WHERE Entry NOT LIKE '%JOB STARTED%' AND Entry NOT LIKE '%JOB FINISHED%'
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')
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' --<--
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' )
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 )