SELECT   dbo.TestScore.personID as district_student_id, cast(CASE WHEN LEFT(test.code, 2) = 'A-' THEN 'ACCESS ' + test.name WHEN LEFT(test.code, 5) 
                      = 'WAPT-' THEN 'WAPT ' + test.name ELSE test.name END as varchar(25)) AS name, coalesce(dbo.TestScore.date,test2.enddate,NULL) as date
, dbo.TestScore.scaleScore as scale_score, dbo.TestScore.result, test2.enddate as end_date
FROM         enrollment inner join
 dbo.TestScore on enrollment.personid = testscore.personid and enrollment.active=1 and enrollment.enddate is null
 INNER JOIN
                      dbo.Test ON dbo.TestScore.testID = dbo.Test.testID
left outer join test test2 on test.parentid=test2.testid


WHERE     (dbo.TestScore.date IS NOT NULL) OR
                      (dbo.TestScore.scaleScore IS NOT NULL) OR
                      (dbo.TestScore.result IS NOT NULL) 