Results 1 to 4 of 4
  1. #1
    Join Date
    Aug 2008
    Posts
    2

    Unanswered: Determining First and Last Test Scores?

    Hello all,

    First time poster!

    I'm relatively new to SQL Server, coming from a FoxPro background. I've had little problems writing most of the SQL queries I've needed to write so far, except for the following problem:

    Task: If the score of the final assessment test is greater than the score of the first assessment test, then it gets categorized as “shows improvement”, otherwise not.

    ClientID AssmtDate Score TestOrder
    1234 2007-11-01 48 1
    1234 2007-11-02 52 2
    1234 2007-12-03 56 3
    1234 2008-01-05 47 4
    5678 2008-05-07 58 1
    5678 2008-05-17 58 2
    5678 2008-05-27 59 3
    5678 2008-06-07 54 4
    5678 2008-06-17 55 5
    5678 2008-06-27 57 6
    5678 2008-07-07 61 7

    Thus, Client 1234 did not show improvement (First: 48, Last: 47), whereas 5678 did (First: 58, Last: 61).

    NOTE: I added the TestOrder column to the original result set with "ROW_NUMBER() OVER (PARTITION BY ClientID ORDER BY AssmtDate) AS TestOrder" figuring that I will probably need something like this to determine first and last test.

    But where do I go from here? What am I missing?

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Actually, you can find the first and last by using the date field, right?
    George
    Home | Blog

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by georgev
    Actually, you can find the first and last by using the date field, right?
    right, along with two subqueries to get the scores for those dates
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Aug 2008
    Posts
    2
    After some deep thought I came up with a solution and thought I'd share it anyways...

    SELECT DISTINCT(A.ClientID),
    (SELECT B.score FROM #GAF B
    WHERE B.ClientID=A.ClientID
    AND B.AssmtDate = (SELECT MIN(AssmtDate) FROM #GAF C
    WHERE C.ClientID=A.ClientID)) AS [FirstTestScore],
    (SELECT D.score FROM #GAF D
    WHERE D.ClientID=A.ClientID
    AND D.AssmtDate = (SELECT MAX(AssmtDate) FROM #GAF E
    WHERE E.ClientID=A.ClientID)) AS [LastTestScore]
    FROM #GAF A

    Now I have a resultset with ClientID,FirstTestScore and LastTestScore.

Posting Permissions

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