Getting prior test scores in the same query

Question

I am trying to get prior scores and prior test date of a student in the same query. My current query works fine until the prior score falls exactly on a 28 day difference. The issue is when the scores fall on 29,30 or 31 day. Here is my current query:

SELECT 
a.STUDENTID, 
b.CURRENTSCORE,
b.CURRENTSCOREDT, 
    (select CURRENTSCORE 
      from SCORES 
      where STUDENT = a.STUDENTID
        and ENDDTE=trunc(a.DATECOL)-28 
        and COURSE=b.COURSE and rownum =1
       ) as PRIORSCORE, 
trunc(b.ENDDTE)-28 as PRIORCOREDT,
b.COURSE
FROM 
  TABLE1 a,
  SCORES b
WHERE
 TRUNC(a.DATECOL) = (SELECT MAX(TRUNC(ENDDTE)) 
                       FROM SCORES
                     ) 
and a.STUDENTID = b.STUDENT 
and b.COURSE='Course1';

How can this be made a bit more dynamic so I get the prior scores irrespective of when the exam/test was taken.

Please show table schemas, and some sample data. – OldProgrammer Nov 22 '13 at 19:29 — OldProgrammer, Nov 22 '13 at 19:29

score -1 · Answer 1 · answered Nov 22 '13 at 20:26

I think something similar could work:

with rankedscores as (
(select
        scores.*, 
        rank() over (partition by studentid order by datecol desc) daterank 
from
        scores
where
        COURSE='Course1' 
)
select
        a.STUDENTID,      
        b.CURRENTSCORE,   
        b.CURRENTSCOREDT, 
        c.CURRENTSCORE priorscore,
        c.CURRENTSCOREDT priorscoredt
from
        table1 a,
        rankedscores b,
        rankedscores c
where
        a.STUDENTID = b.student(+) and
        a.STUDENTID = c.student(+) and
        nvl(b.daterank,1) = 1 and
        nvl(c.daterank,2) = 2

(Not tested...)

Hmm. Why is the -1? Did I miss something? – Lajos Veres Nov 23 '13 at 09:26 — Lajos Veres, Nov 23 '13 at 09:26
I did not -1 it. Not sure why it got a -1 . – ozzboy Nov 25 '13 at 13:33 — ozzboy, Nov 25 '13 at 13:33

Getting prior test scores in the same query

1 Answers1