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
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;
Microsoft SQL ServerRemote AccessSQL
Last Comment
Larry Brister
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
Cool.. please try the solutions i have given. The last one should work for you.
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]
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]
I need all unique IndividualID's that has 5 consecutive fails in that IndividualID's most recent 25 minute period