troubleshooting Question

MS SQL Get bad data from list

Avatar of Larry Brister
Larry BristerFlag for United States of America asked on
Microsoft SQL ServerRemote AccessSQL
4 Comments1 Solution103 ViewsLast Modified:
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;
ASKER CERTIFIED SOLUTION
Join our community to see this answer!
Unlock 1 Answer and 4 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 4 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros