Skip to Content
0
Former Member
Oct 06, 2011 at 01:38 PM

sql query problem which causes crystal reports in visual studio to freeze

53 Views

Anyone know why this query posted here freezes up? if i remove s.enddate IS NULL from the query is when it freezes up. This is used on an infinite campus database for which i only have read only access so no posting anything that requires write access please!

IT may just be a flaw in my query which it has been before but it only stops responding if i remove the above query and it only started this after i added the comments subreport if i remove it then the action above doesn't cause it to freeze in visual studio.

I do have a subreport for comments that is in the report too. Heres the main query:

SELECT Distinct sc.name,sc.address,sc.city,sc.state,sc.zip,sc.phone,gt.name AS Task, ca.StartDate,

ca.EndDate, s.lastname','s.firstname AS Student, s.studentnumber,e.grade,t.name AS Term,

gs.score AS [Current Grade],cg.Cumgpa AS GPA,c.name '-'c.number As Course, se.teacherdisplay,gs.personid,

MAX(CASE WHEN hl.enddate IS NULL AND ccs.guardian = 1 AND ccs.relatedBy = 'Household' AND ccs.mailing = 1 THEN ccs.addressline1 END) AS AddressLine1,

MAX(CASE WHEN hl.enddate IS NULL AND ccs.guardian = 1 AND ccs.relatedBy = 'Household' AND ccs.mailing = 1 THEN ccs.addressline2 END) AS [City/State],gs.personid

FROM GradingScore gs

LEFT JOIN dbo.student s ON s.personid = gs.personid

JOIN v_CensusContactSummary ccs ON ccs.personid = s.personid

JOIN dbo.enrollment e ON e.enrollmentid = s.enrollmentid AND e.calendarid = gs.calendarid

LEFT JOIN dbo.Term t ON t.termid = gs.termid

JOIN dbo.section se ON se.sectionid = gs.sectionid

JOIN v_CumGPA cg on cg.personID = s.personID aND cg.calendarID = s.calendarID

JOIN gradingtask gt ON gt.taskid = gs.taskID

JOIN dbo.course c ON c.courseid = se.courseid

JOIN dbo.roster r ON r.sectionid = se.sectionid

JOIN calendar ca ON ca.calendarID = gs.calendarID

JOIN School sc ON sc.schoolID = ca.schoolid

LEFT JOIN householdlocation hl ON hl.householdid = ccs.householdid

--Change Term Name to match term name needed or if in a report turn it into a Parameter

WHERE s.enddate IS NULL AND gs.calendarID IN

(select calendarid

from dbo.calendar c

JOIN dbo.schoolyear sy on sy.endyear = c.endyear

WHERE c.schoolID = 2)

GROUP BY s.studentnumber,s.lastname, s.firstname,e.grade, t.name, gt.name, gs.score, cg.cumgpa, c.name, c.number, se.teacherdisplay, ca.startDate, ca.enddate,

sc.name, sc.address, sc.city, sc.state, sc.zip, sc.phone,gs.personid

order by [student],[Term],[Course]

Edited by: jeffman2 on Oct 6, 2011 3:40 PM

Edited by: jeffman2 on Oct 6, 2011 3:41 PM

Edited by: jeffman2 on Oct 6, 2011 3:53 PM

Edited by: jeffman2 on Oct 6, 2011 3:56 PM

Edited by: jeffman2 on Oct 6, 2011 3:56 PM