asked on

MS SQL Get bad data from list

I have a process that handles inbound data and processes.

If there is a fail it writes the following into a table

ID RecordType DateTime and Pass/Fail .

I need a select that gets me a list of things the last 5 records where...
The id and RecordType match
The time span <= 25 minutes
And Pass = 0

Where that happens...
Do not reprocess that id (or group of ID's)
Do something ELSE with that ID.

An example that would meet that criteria..
In my @table below... there are 5 "fails" in th last <=25 minutes for UD 123

DECLARE @table TABLE
    (
        IndividualID VARCHAR(100) ,
        RecType VARCHAR(100) ,
        ProcTime DATETIME ,
        Pass BIT
    );
INSERT INTO @table ( IndividualID ,
                     RecType ,
                     ProcTime ,
                     Pass )
VALUES ( '123', 'Tour', DATEADD(mi, -3, GETDATE()), 0 ) ,
       ( '123', 'Tour', DATEADD(mi, -8, GETDATE()), 0 ) ,
       ( '123', 'Tour', DATEADD(mi, -10, GETDATE()), 0 ) ,
       ( '00235', 'Reservation', DATEADD(mi, -11, GETDATE()), 1 ) ,
       ( '123', 'Tour', DATEADD(mi, -20, GETDATE()), 0 ) ,
       ( '123', 'Tour', DATEADD(mi, -23, GETDATE()), 0 );

SELECT *
FROM   @table;

Open in new window

ASKER CERTIFIED SOLUTION

Pawan Kumar

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Larry Brister

ASKER

Not at my pc
I need all unique IndividualID's that has 5 consecutive fails in that IndividualID's most recent 25 minute period

Pawan Kumar

Cool.. please try the solutions i have given. The last one should work for you.

Larry Brister

ASKER

Very close

I added the "Having" statement at the bottom because there has to be a minimum of 5 fails in that last 25 minutes

[code]DECLARE @table TABLE
(
IndividualID VARCHAR(100) ,
RecType VARCHAR(100) ,
ProcTime DATETIME ,
Pass BIT
);
INSERT INTO @table ( IndividualID ,
RecType ,
ProcTime ,
Pass )
VALUES ( '123', 'Tour', DATEADD(mi, -3, GETDATE()), 0 ) ,
( '123', 'Tour', DATEADD(mi, -8, GETDATE()), 0 ) ,
( '123', 'Tour', DATEADD(mi, -9, GETDATE()), 0 ) ,
( '00235', 'Reservation', DATEADD(mi, -11, GETDATE()), 1 ) ,
( '123', 'Tour', DATEADD(mi, -20, GETDATE()), 0 ) ,
( '123', 'Tour', DATEADD(mi, -27, GETDATE()), 0 );

SELECT *
FROM @table
WHERE DATEDIFF(MINUTE, ProcTime, GETDATE()) <= 25
AND Pass = 0;

SELECT IndividualID ,
RecType
FROM @table
WHERE DATEDIFF(MINUTE, ProcTime, GETDATE()) <= 25
AND Pass = 0
GROUP BY IndividualID ,
RecType
HAVING COUNT(ProcTime) >= 5;[/code]