FROM (SELECT [Patient Identifier], [Operator Index], Date, Time, ROW_NUMBER() OVER (PARTITION BY [Patient Identifier], Date
ORDER BY [Patient Identifier], Date, Time) AS RowNum
FROM Complete
WHERE [Operator Index] <= 89) AS a
WHERE RowNum <= 4
UNION
SELECT *
FROM (SELECT [Patient Identifier], [Operator Index], Date, Time, ROW_NUMBER() OVER (PARTITION BY [Patient Identifier], Date
ORDER BY [Patient Identifier], Date, Time) AS RowNum
FROM Complete
WHERE [Operator Index] >= 90) AS a
WHERE RowNum <= 2
This query returns values above 90 (I need 2 of them) or values between 80 and 89 (data is already filtered for only greater >=80) and I need 4 values above 80. I only need either 2 above 90 or 4 above 80, not both, and this query returns 2 above 90, but also the values between 80 and 89. If there are already 2 above 90, I do not want any values between 80 and 89. If there are 4 above 80, I do not need any additional values. If the are two above 80 and 1 above 90, I will take all of them (max I will ever take is 4).Can you give me sample data to work on?|||Patient IdentifierPatient InitialsDateTimeOperator Index
0517_00003GHV18-Oct-0611:4891
0517_00003GHV18-Oct-0611:50100
0517_00004JMH17-Oct-0611:4189
0517_00004JMH17-Oct-0611:5093
0517_00004JMH17-Oct-0611:5291
0517_00004JMH17-Oct-0612:0093
0534_00003JS21-Nov-0612:35100
0534_00003JS21-Nov-0612:46100
0534_00004ChM20-Nov-0610:49100
0534_00004ChM20-Nov-0610:51100
0534_00006JK4-Dec-069:38100
0534_00006JK4-Dec-069:4784
0534_00006JK4-Dec-069:5093
0534_00007TL29-Nov-069:2298
0534_00007TL29-Nov-069:34100
0539_00001PGL9-Oct-069:39100
0539_00001PGL9-Oct-069:4395
0539_00002DWR27-Oct-0610:0491
0539_00002DWR31-Oct-0611:4092
0539_00002DWR31-Oct-0611:4196
0539_00002DWR31-Oct-0611:4292
0539_00003JmL30-Nov-069:1496
0539_00003JmL30-Nov-069:1897|||I figured it out! Thanks!|||Here is the code I wrote and it is not correct although it appears to be correct at first. I was validating my data and discovered on several instances a value of 80 (something) is there instead of 90 (something).
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] BETWEEN 80 AND 89
GROUP BY [Patient Identifier], Date
HAVING COUNT(*) >= 4) AS t8 ON t8.[Patient Identifier] = t9.[Patient Identifier] AND t8.Date = t9.Date INNER JOIN
[First Step] 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]
