vinu512 (4/2/2013)
Is there a relationship between the "Attend" table with the "Terms" table??.....By what you have shown in the Expected Output it sure seems there is but its not visible in the sample that you have provided.
In the sample data you have provided, there is no column in the "Attend" Table other than "ID" column which links it to the Terms Column............then how do you know, just on the basis of ID, which data belongs to what Term or what Test??
How did you get to the expected result that you posted??
From what I understood, the "Days Missed" column in your expected results data is according to Term and/or Test, so to get to that there should be some relationship between the "Attend" and the "Terms" tables.
Sorry I forgot to state the relationships of the tables. In setting up test data I pared these tables down to only have columns needed to calculate the days missed.
Remember that Attend contains data for the current school year and is reset (emptied) at the beginning of each year.
TestScores is the 'driver' table. I'll be selecting from this table "where TestName like 'CSA%' ". From the TestScores you link to Terms via TestScores.SchoolYR, LocID, Term to get the EndDate. Also from the TestScores you link to Attend via the ID (on TestScore.ID = Attend.ID).
Based on the Term the test was taken you get the end date (EndDate) of that term and use it in the calculation.
I can do this in two seperate queries using a temp table for each term but want to do this in one query if possible.
Here's the two query method that may help you understand the relationship better:
select ID, Missed = COUNT(dateabs)/8
from Attend
where dateabs <= '2012-10-12' --term 1 end date
group by ID
order by ID
I'd load this into a temp table and then run the following query to Update the DaysMissed.
update TestScores
set DaysMissed = Missed
from TestScores as s
join #Temp as t
on s.ID = t.ID
where s.TestName like 'CSA%'
and s.Term = 1
Then do this process for each Term.