A while back I got some real help with a problem in using SQL in Crystal Reports 2008 on an Oracle database and I was hoping someone could help me again. The SQL code below performs a specific task needed to produce a complex report but the business needs have changed and I am at a loss as to how to alter it. The code selects all examiners from a specific test centre who have conducted a selected minimum number of tests in a given date range and pulls only the data for the most recent specific amount of tests (it does a number of other things but this is the main criteria).

However it needs to now select all test centres where an examiner has met the minimum number of tests within a given date range and pull the specific minimum number of tests for each Examiner at each test centre. i.e. an examiner may have worked at 2 or 3 test centres and conducted the minimum number of tests at each centre and we need to know all these tests centres and then select all the data needed for each individual test centre.

An example would be an Examiner works at 6 test centres and conducts 50 tests at 2 test centres and 20 tests at the other 4 test centres. We want to search the data base and pull all the data for each of the test centres were the Examiner has conducted a minimum of 30 tests. So in theory we should only get the information for 2 test centres AND only the information for the most recent 30 tests at each of these test centres.

I have tried to just remove the Test Centre variable from the code but all this does is select the most recent test data for each Examiner who has conducted the minimum number of tests regardless of where they were conducted.

I think it needs another grouping or partition added but my SQL has become very rusty through lack of use and I cannot even think of a way to start. Oh. I should add that we do not have administrative access so cannot simply produce new tables hence the convoluted code below.

WITH TESTNUMBER AS

(SELECT TESTER.APP_ID,
TESTER.APP_REF_NO,
TESTER.EXAMINER_PERSON_ID,
TESTER.TEST_RESULT,
TESTER.TEST_TYPE,
PERIOD.PERIOD_DATE,
TESTER.TC_ID,
DTCNAME.TC_NAME,
DTCNAME.DISPLAY_ORDER,
PERSON.GENDER,
TESTER.VISITING_EXAMINER,
TESTER.TEST_CATEGORY_TYPE,
TESTER.TEST_ID,
TESTER.STAFF_NO,
TESTSLOT.SLOT_TIME,
TESTER.ROUTE_NUMBER,

ROW_NUMBER() OVER (PARTITION BY TESTER.STAFF_NO
ORDER BY TESTER.STAFF_NO DESC,
PERIOD.PERIOD_DATE DESC,
TESTSLOT.SLOT_TIME DESC) AS
RowNumber
FROM RSISOWN.TEST_EVENT TESTER
INNER JOIN RSISOWN.PERIOD PERIOD ON
TESTER.PERIOD_ID=PERIOD.PERIOD_ID
INNER JOIN RSISOWN.SLOT_TIME TESTSLOT ON
TESTER.SLOT_TIME_ID=TESTSLOT.SLOT_TIME_ID
INNER JOIN RSISOWN.PERSON PERSON ON
TESTER.PERSON_ID=PERSON.PERSON_ID
INNER JOIN RSISOWN.DTCS_TEST_CENTRE_NAME
DTCNAME ON TESTER.TC_ID=DTCNAME.TC_ID
WHERE TESTER.STAFF_NO in (
SELECT TESTER.STAFF_NO
FROM RSISOWN.TEST_EVENT TESTER
INNER JOIN RSISOWN.PERIOD PERIOD ON
TESTER.PERIOD_ID=PERIOD.PERIOD_ID
INNER JOIN RSISOWN.SLOT_TIME TESTSLOT ON
TESTER.SLOT_TIME_ID=TESTSLOT.SLOT_TIME_ID
INNER JOIN RSISOWN.PERSON PERSON ON
TESTER.PERSON_ID=PERSON.PERSON_ID
INNER JOIN RSISOWN.DTCS_TEST_CENTRE_NAME
DTCNAME ON TESTER.TC_ID=DTCNAME.TC_ID
WHERE (PERIOD.PERIOD_DATE >= {?DATE1} AND
PERIOD.PERIOD_DATE <={?DATE2}) and
TESTER.TC_ID={?DTC} AND TESTER.TEST_TYPE=ANY{?CAT} AND
TESTER.TEST_RESULT='F' AND
PERSON.GENDER=ANY{?GENDER} AND
TESTER.VISITING_EXAMINER=ANY{?VISITPERM}
GROUP BY TESTER.STAFF_NO
HAVING count(TESTER.STAFF_NO)>={?TESTNO}
)
AND (PERIOD.PERIOD_DATE>={?DATE1} AND PERIOD.PERIOD_DATE<={?DATE2})
AND TESTER.TC_ID={?DTC} AND
TESTER.TEST_TYPE=ANY{?CAT} AND
TESTER.TEST_RESULT='F' AND PERSON.GENDER=ANY{?GENDER} AND
TESTER.VISITING_EXAMINER=ANY{?VISITPERM}
)
SELECT * FROM TESTNUMBER
WHERE RowNumber BETWEEN 1 AND {?TESTNO}
ORDER BY STAFF_NO,RowNumber