Results 1 to 4 of 4
  1. #1
    Join Date
    Nov 2004
    Posts
    6

    Unanswered: History query question

    A new question.

    I have a school history table: ID_HISTORY, ID_STUDENT, ID_SCHOOL, DATE_CREATED.

    When I give a date like 01/01/2007 up in the query, the query need to search the records in the school history table where the id from the students is null. And when the id is null it need to find the id from the student from a record with the same id from the school and a date that is less but higher than the other record I gave up.

    I already tried to do this but with no luck. It always give me a error.

    SELECT A.ID_HISTORY, A.ID_STUDENT, A.ID_SCHOOL, A.DATE_CREATED FROM SCHOOL_HISTORY A WHERE A.DATE_CREATED =
    (
    SELECT MAX(B.DATE_CREATED) FROM SCHOOL_HISTORY B WHERE B.ID_SCHOOL = A.ID_SCHOOL AND CAST(B.DATE_CREATED AS DATE) <= '01/01/2007' AND
    (
    SELECT C.ID_STUDENT FROM SCHOOL_HIST C WHERE CAST(C.DATE_CREATED AS DATE) = '01/01/2007' AND C.ID_SCHOOL = A.ID_SCHOOL
    )
    IS NULL
    )

    Sorry for my bad English

    Thanks in advance

    Nyh
    Last edited by David_nyh; 09-04-07 at 15:01.

  2. #2
    Join Date
    Nov 2004
    Posts
    6
    A new question. See post above.

  3. #3
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    I do not understand what you want. Maybe if you give an example of the data in the table and what the query should produce.


    Andy

  4. #4
    Join Date
    Dec 2005
    Posts
    273
    Do you mean something like

    SELECT DISTINCT A.ID_HISTORY , A.ID_SCHOOL , A.DATE_CREATED, B.ID_STUDENT
    FROM
    SCHOOL_HISTORY A ,
    SCHOOL_HISTORY B
    WHERE A.ID_STUDENT IS NULL
    AND A.ID_SCHOOL = B.ID_SCHOOL
    AND B.DATE_CREATED > A.DATE_CREATED
    AND B.DATE_CREATED < your-given-date
    AND B.ID_STUDENT IS NOT NULL;



    You want to find records where the student Id is null
    WHERE A.ID_STUDENT IS NULL
    and for those records you want to find rows with the same school id
    AND A.ID_SCHOOL = B.ID_SCHOOL
    and a date that is less [than the specified date]
    AND B.DATE_CREATED < your-given-date
    but higher the date of the other record given
    AND B.DATE_CREATED > A.DATE_CREATED
    and, of course, the student id should be given in that record
    AND B.ID_STUDENT IS NOT NULL

Posting Permissions

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