Skip to Content

Archived discussions are read-only. Learn more about SAP Q&A

How get my report design working?

I have a report card report that i need to get working. I have the school name , address , and students name and report card information at top correct but the classes are not displaying correctly.

I will post the sql query here but if you need to see my report i can post a picture.

Sql query:

SELECT dbo.Course.number, dbo.Course.name, dbo.Calendar.endYear, dbo.Calendar.startDate, dbo.Calendar.endDate

FROM dbo.Calendar INNER JOIN

dbo.Course ON dbo.Calendar.calendarID = dbo.Course.calendarID

WHERE year(getdate()) BETWEEN year(dbo.Calendar.startDate) AND year(dbo.Calendar.endDate)

My objective is to get classes from a specific school year to show up for a specific student. The students courses and the calendar year are in different tables though. For instance, for john willy i want his 2010 -2011 classes to show up.

Former Member
replied

Hi,

I see two things that need to be worked out. The first is finding the proper table to work with. The second is to break down the query so you get the data you want.

CROSS JOINs create cartesians which will slow down your query because it's generating extra records if you do not include a proper where clause. I noticed the Calendar table isn't linked to a year so it. Things like this will cause you problems. If you can try to use INNER or OUTER joins.

From your first post you wanted to generate a report card listing all the courses for each student in the current school year. Break the query down into pieces. I made up the joins because you didn't link the tables so you will need to figure that out.

Start with the student like:

Select v_SchoolCurrent.name
, v_SchoolCurrent.address
, v_SchoolCurrent.city
, v_SchoolCurrent.state
, v_SchoolCurrent.zip
, v_SchoolCurrent.phone
, student.studentNumber
, student.lastName
, student.firstName
, student.middleName
From   
garrard.dbo.v_SchoolCurrent INNER JOIN garrard.dbo.student On 
	v_SchoolCurrent.UID = student.School_UID
WHERE v_SchoolCurrent.name='GARRARD COUNTY HIGH SCHOOL' 

This should return you a list of the students in your school.

Now try adding the calendar table to it:

Select v_SchoolCurrent.name
, v_SchoolCurrent.address
, v_SchoolCurrent.city
, v_SchoolCurrent.state
, v_SchoolCurrent.zip
, v_SchoolCurrent.phone
, student.studentNumber
, student.lastName
, student.firstName
, student.middleName
, Calendar.startDate
, Calendar.endDate
From   
(garrard.dbo.v_SchoolCurrent INNER JOIN garrard.dbo.student On 
	v_SchoolCurrent.UID = student.School_UID) INNER JOIN garrard.dbo.Calendar On 
		student.School_Year = Calendar.School_Year
Where 
WHERE v_SchoolCurrent.name='GARRARD COUNTY HIGH SCHOOL' And 
	Calendar.School_Year = 2011

Add the next table and check the results after each addition.

Good luck,

Brian

0 View this answer in context

Helpful Answer

by
Not what you were looking for? View more on this topic or Ask a question