Hi

I am hoping you can help me with a problem I have with Crystal.

I need to collate the 100 most recent tests by each tester between two dates. Once the most recent orders list equals 100 I need the selection process to stop regardless of any other tests the tester may have done in the time period. Also if the tester has not completed at least 100 tests in the time period then the results for that tester need to be discarded.

I have managed a work around for this but what I really need to know is if there is a SQL sequence which will do the selection process I need leaving me with the selected data on which I can run further processes.

I did think this may be possible using a loop in SQL but do not know where to start with this.

I hope this makes sense but if not I will answer any questions.

Any help would be gratefully appreciated.

Cheers

Daveyt

Hi Sastry

Thanks for the quick response it was very helpful.

Using your response I have completed the following code:

SELECT TESTER.STAFF_NO, count(TESTER.STAFF_NO) AS TOTALTESTS
FROM Db1.TESTER TESTER INNER JOIN Db1.PERIOD PERIOD
ON TESTER.DATEOFTEST_ID=PERIOD.DATEOFTEST_ID
WHERE PERIOD.DATEOFTEST>={ts '2013-01-01 00:00:00'} AND PERIOD.DATEOFTEST<{ts '2014-01-01 00:00:00'}
GROUP BY TESTER.STAFF_NO
HAVING count(TESTER.STAFF_NO)>=100

This code produces a list of all testers who have completed over 100 tests but does not allow me to Select any other fields to run comparisons on. Any time I try to add additional fields into the code as part of the Select command I get the following error message:

---------------------------
Crystal Reports
---------------------------
Failed to retrieve data from the database.
Details: HY000:[Oracle][ODBC][Ora]ORA-00979: not a GROUP BY expression
[Database Vendor Code: 979 ]
---------------------------
OK   
---------------------------


Also I only need the 100 most recent tests information is there a way to specify this?

Cheers in advance.

Hi Sastry

Thanks again sorry to be monopolising your time it is really appreciated.

I see where you are going with this and think I can understand it but
(and you just knew there was going to be a but )
when I Select additional fields and then add them to the group by function I lose all the data I need to investigate.

The SQL below seems to work but I get zero records found:

SELECT PERIOD.DATEOFTEST, TESTER.TEST_RESULT, TESTER.STAFF_NO, count(TESTER.STAFF_NO) AS TOTALTESTS FROM Db1.TESTER TESTER
INNER JOIN Db1.PERIOD PERIOD ON TESTER.DATEOFTEST_ID=PERIOD.DATEOFTEST_ID
WHERE PERIOD.DATEOFTEST>={ts '2013-01-01 00:00:00'} AND PERIOD.DATEOFTEST<{ts '2014-01-01 00:00:00'}
GROUP BY PERIOD.DATEOFTEST, TESTER.TEST_RESULT, TESTER.STAFF_NO, count(TESTER.STAFF_NO)
HAVING count(TESTER.STAFF_NO)>=100
ORDER BY TESTER.STAFF_NO DESC

Any idea’s?