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

Larry Bristersr. DeveloperAsked:
Who is Participating?
 
Pawan KumarDatabase ExpertCommented:
Which columns you need in the output

Please try this solution-

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
WHERE DATEDIFF(MINUTE,ProcTime,GETDATE()) <= 25 AND Pass = 0

Open in new window


OUTPUT

/*------------------------
OUTPUT
------------------------*/

(6 row(s) affected)
IndividualID              RecType          ProcTime                Pass
------------------------- ---------------- ----------------------- -----
123                       Tour             2017-11-08 04:06:57.903 0
123                       Tour             2017-11-08 04:01:57.903 0
123                       Tour             2017-11-08 03:59:57.903 0
123                       Tour             2017-11-08 03:49:57.903 0
123                       Tour             2017-11-08 03:46:57.903 0

(5 row(s) affected)

Open in new window


Or if you just need IndividualID  and     RecType , you can use below

Solution

SELECT IndividualID,RecType
FROM   @table
WHERE DATEDIFF(MINUTE,ProcTime,GETDATE()) <= 25 AND Pass = 0
GROUP BY IndividualID,RecType

Open in new window


output

/*------------------------
OUTPUT
------------------------*/

(6 row(s) affected)
IndividualID            RecType
----------------------- -------
123                     Tour

(1 row(s) affected)

Open in new window

0
 
Larry Bristersr. DeveloperAuthor Commented:
Not at my pc
I need all unique IndividualID's that has 5 consecutive fails in that IndividualID's most recent 25 minute period
0
 
Pawan KumarDatabase ExpertCommented:
Cool.. please try the solutions i have given. The last one should work for you.
0
 
Larry Bristersr. DeveloperAuthor Commented:
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]
1
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.