--Section 1 This section gets the list of students who were enrolled during the date range given.

SELECT DISTINCT
	S.StudentDimKey, S.StudentID, S.FirstName, S.LastName, SD.SchoolID, SD.NCSSchoolName
FROM 
	CLT_StudentDim S
	INNER JOIN CLT_StudentAssessmentRawFact SA ON S.StudentDimKey = SA.StudentDimKey
	INNER JOIN CLT_AssessmentDim A ON SA.AssessmentDimKey = A.AssessmentDimKey
	INNER JOIN CLT_SchoolDim SD ON S.SchoolID = SD.SchoolID
WHERE
	S.DistrictID = @DistrictID
	AND SA.EvaluationDate BETWEEN @StartDate AND @EndDate
	AND A.ProductKey = @ProductKey

--Section 2 This section gets the list of tests given between the start and end dates.

SELECT DISTINCT
	A.AssessmentDimKey, A.AssessmentName, A.AssessmentTypeName
FROM
	CLT_StudentAssessmentRawFact SA
	INNER JOIN CLT_AssessmentDim A ON SA.AssessmentDimKey = A.AssessmentDimKey
WHERE
	SA.EvaluationDate BETWEEN @StartDate AND @EndDate
	AND A.ProductKey = @ProductKey

-- Section 3 This section gets the studentdimkey, assessmentdimkey, and score for the test for
-- each test taken during the time frame

SELECT DISTINCT
	SA.AssessmentDimKey, SA.StudentDimKey, SA.Score, A.NumOfQuestions, A.Book, A.Step
FROM
	CLT_StudentAssessmentRawFact SA
	INNER JOIN CLT_AssessmentDim A ON SA.AssessmentDimKey = A.AssessmentDimKey
WHERE
	SA.EvaluationDate BETWEEN @StartDate AND @EndDate
	AND A.ProductKey = @ProductKey