filtering data correctly?

[AtCor]

SELECT [Patient Identifier], Date, [Operator Index], Time
FROM (SELECT ISNULL(t9.[Patient Identifier], t8.[Patient Identifier]) AS [Patient Identifier], ISNULL(t9.Date, t8.Date) AS Date, ISNULL(t9.Rows, t8.Rows)
AS Rows, c.[Operator Index], c.Time, ROW_NUMBER() OVER (PARTITION BY ISNULL(t9.[Patient Identifier], t8.[Patient Identifier]),
ISNULL(t9.Date, t8.Date)
ORDER BY c.Time) AS RowNum
FROM (SELECT [Patient Identifier], Date, 2 AS [Rows]
FROM [First Step]
WHERE [Operator Index] >= 90
GROUP BY [Patient Identifier], Date
HAVING COUNT(*) >= 2) AS t9 FULL JOIN
(SELECT [Patient Identifier], Date, 4 AS [Rows]
FROM [First Step]
WHERE [Operator Index] >= 80
GROUP BY [Patient Identifier], Date
HAVING COUNT(*) >= 4) AS t8 ON t8.[Patient Identifier] = t9.[Patient Identifier] AND t8.Date = t9.Date INNER JOIN
Complete AS c ON c.[Patient Identifier] = ISNULL(t9.[Patient Identifier], t8.[Patient Identifier]) AND c.Date = ISNULL(t9.Date, t8.Date)) AS d
WHERE d .RowNum <= d .[Rows]

Current Input
Patient ID DATE Time Operator Index
51700003 18OCT2006 11:48 91
51700003 18OCT2006 11:50 100
51700004 17OCT2006 11:41 89
51700004 17OCT2006 11:50 93
51700004 17OCT2006 11:52 91
51700004 17OCT2006 12:00 93

Current Output

Patient ID DATE Time Operator Index
0517_00003 18OCT2006 11:48 91
0517_00003 18OCT2006 11:50 100
0517_00004 17OCT2006 11:41 89
0517_00004 17OCT2006 11:50 93

It should be
Patient ID DATE Time Operator Index
51700003 18OCT2006 11:48 91
51700003 18OCT2006 11:50 100
51700004 17OCT2006 11:50 93
51700004 17OCT2006 11:52 91

The data is organized by patient id, date, time (ascending)
For a given patient id, on a certain data, testing was performed. A value between 80 and 100 is acceptable data. I need either the first 2 tests with a score above 90 or the first 4 tests above 80. (The tests are further sorted by time because the testing is time dependant. On some occassions, there is just too much data. What is wrong with my current query?

[iburyak]

I checked and it works try this:

[PHP]Declare @patient_res table ([Patient Identifier] int, Date varchar(10), Time varchar(10), [Operator Index] int)
Declare @PatientID int, @Date varchar(10)
DECLARE Patient CURSOR FOR
SELECT Distinct [Patient Identifier], Date
FROM [First Step]

OPEN Patient
FETCH NEXT FROM Patient INTO @PatientID, @Date

WHILE @@FETCH_STATUS = 0
BEGIN
IF (select Count(*) from [First Step] where [Patient Identifier] = @PatientID and Date = @Date and [Operator Index] > 89) > 1
INSERT INTO @patient_res
SELECT TOP 2 [Patient Identifier], Date, Time,[Operator Index]
FROM [First Step]
WHERE [Patient Identifier] = @PatientID and Date = @Date and [Operator Index] > 89
ORDER BY Time
ELSE IF (select Count(*) from [First Step] where [Patient Identifier] = @PatientID and Date = @Date and [Operator Index] between 80 and 89) > 3
INSERT INTO @patient_res
SELECT TOP 4 [Patient Identifier], Date, Time,[Operator Index]
FROM [First Step]
WHERE [Patient Identifier] = @PatientID and Date = @Date and [Operator Index] between 80 and 89
ORDER BY Time
ELSE
INSERT INTO @patient_res
SELECT TOP 4 [Patient Identifier], Date, Time,[Operator Index]
FROM [First Step]
WHERE [Patient Identifier] = @PatientID and Date = @Date
ORDER BY Time
FETCH NEXT FROM Patient INTO @PatientID, @Date

END

CLOSE Patient
DEALLOCATE Patient

select * from @patient_res [/PHP]

[iburyak]

You kept changing your nick and said that you already solved this problem so I didn't bother to post my answer before.

Good Luck.

[AtCor]

I thought that I had corrected the problem. The only issue that I am having with the current code is that if there are more than 2 values in the 90's, it is keeping all of them. I am close though. Thanks for all of your help.

[AtCor]

Patient ID Date Time Operator Index

55100003 09NOV2006 09:17 91
55100003 09NOV2006 09:18 100
55100003 14NOV2006 10:43 97 (should be discarded)
55100003 14NOV2006 10:44 94 (should be discarded)

[AtCor]

I think I figured it out, there is an error in my raw data. I need to go back and recompile that. Your codes works excellent. Thanks for all of your help.