SQL statement - how to build a timeline graph

Question

Hi I have a table with following columns:

• ID
• student_id
• score (int)
• scanned_date
• close_date.

A script is run every week. it collects a score for each student every week. Each week the scores remain the same. When the script is run for the first time, it enters ID, Student_id, score, scanned_date and Null for Close_date for each student.

For each additional scan, if the score is same as last week's score then, the script does nothing. But if a new score is found, then it enter's the date in the close_date field and enters a new row containing id, student_id, score, scanned_date and Null for close date.

I'm trying to build a sql statement which will help me build a timeline graph. For each distinct scanned_date, it will return sum of all the scores for each student so that I can build a graph.

is that possible to do?

-Maria

Answer 1

The following query extracts all the distinct scanned dates and them joins them back to the table to find what records are active on each date. It then aggregates the results by date:

select dates.scanned_date,
       count(t.id) as numids,
       sum(score) as sumscore
from (select distinct scanned_date from t) as dates left outer join
     t
     on dates.scanned_date >= t.scanned_date and
        dates.scanned_date < t.close_date
group by dates.scanned_date
order by 1