Hi

This is my second attempt to post this.

I am quite new to this so if I have put this query into the wrong section please forgive me. I have been asked to produce a report that lists the most recent test information between 2 dates. I began by writing the following code:

SELECT
TESTER.STAFF_NO,
TESTER.TEST_REF,
PERIOD. DATE_OF_TEST,
TESTSLOT.SLOT_TIME
FROM dB1.TEST_EVENT TESTER
INNER JOIN dB1.PERIOD PERIOD ON TESTER.PERIOD_ID=PERIOD.PERIOD_ID
INNER JOIN dB1.TEST_ SLOT_TIME TESTSLOT ON TESTER.SLOT_TIME_ID=TESTSLOT.SLOT_TIME_ID
WHERE PERIOD. DATE_OF_TEST >={ts '2012-04-01 00:00:00'} AND PERIOD. DATE_OF_TEST <{ts '2013-03-31 00:00:00'}
ORDER BY TESTER.STAFF_NO Desc,
PERIOD. DATE_OF_TEST Desc,
TESTSLOT.SLOT_TIME Desc

The above code selects all the tests that a particular member of staff has conducted between the dates selected. It then sorts them into the staff member first, and the date and time of the test; all in reverse order.

What I need is to have this code select the most recent specific number of tests, say a snapshot of 100, in the time period given. If a staff member has not conducted enough tests to meet this figure in the time period their test data should be ignored. It is vital that we have the most recent test data for only the specific number of tests stated so we can run this through a number of equations we have to check the integrity of the staff’s testing.

I think I need to use a LOOP in a CURSOR unfortunately I do not know where to start. To add further problems we do not have administrator access to the databases and cannot create temporary data tables so we need this to be done in a single SQL action, if possible.

If this is a bit confusing or hard to follow I have number of examples to follow (we are currently only interested in testing conducted in the last financial year):

Staff Member & Period Tested & No of Tests
J Smith Apr 12 – Mar 13 200 Tests
J Smith has done at least the required number of tests but we only want his most recent 100.
B Lawson Apr 12 – Mar 13 58 Tests
B Lawson has not done enough tests in the period and his data should be ignored.
M Red Apr 12 – Jun 13 120 Tests
M Red has done enough tests but it has taken him until outside of the time period and therefore his data should be ignored.
R John Apr 12 – Jun 12 90 Tests
R John has not done enough tests in the period and his data should be ignored.
J McGowan Apr12 – Aug 12 690 Tests
J McGowan has done at least the required number of tests but again we only want his most recent 100.

I’ll answer questions that I can.

Thanks in advance for your help.