Results 1 to 4 of 4
  1. #1
    Join Date
    Dec 2013
    Location
    North East England
    Posts
    4

    Unanswered: Cursor SQL Query in Crystal Reports 2008

    Hi

    I am still new at this so I apologise if this is in the wrong section. I need to produce a report the produces the most recent data between 2 dates. I have started 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 this is even possible).

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

    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 any questions you have assuming I understand them.

    Thanks in adance for any help.

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Crystal Reports is a reporting tool. The answer to this question depends on your database engine. Which database engine are you using?

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    Dec 2013
    Location
    North East England
    Posts
    4

    Thanks

    Thanks for the quick reply and please disregard any other requests for info I may have posted. Like I said I am quite new to all this.

    We use a Crystal linked into an Oracle database engine.

    Cheers

  4. #4
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    A test case (CREATE TABLE and INSERT INTO sample records, as well as results you expect from these data) would help.

    You said:
    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.
    "Outside of time period" refers to Jun (20)13, right? Why wouldn't you check how many tests "M Red" did not until Jun 2013, but 31.03.2013 and then decide whether he did enough (more than 100) tests or not? Discarding him for a reason you stated doesn't seem to be fair.

    As of coding problems you have: Oracle offers powerful SQL engine and many things can be done with SQL, without using PL/SQL. "Temporary tables" you mention are rarely used (Oracle <> SQL Server). See whether WITH factoring clause does any good, if you need something like "temporary tables" (actually, these are another way of using subqueries); I don't know, maybe I'm on the wrong track.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •