August 23, 2013 at 11:20 am
Hello
I need one help to develop query
CREATE TABLE #Student
(
StudentID varchar(09),
Race varchar(2),
CurrentSchoolCode int,
CurrentGradeCode varchar(2),
SchoolYearCode int
)
INSERT INTO #Student VALUES ('001233069','W',909,'11',2013)
CREATE TABLE #CurrentSchedule
(
StudentID varchar(09),
CourseID varchar(10),
SchoolYearCode int
)
INSERT INTO #CurrentSchedule VALUES ('001233069','001113',2013)
INSERT INTO #CurrentSchedule VALUES ('001233069','009999',2013)
INSERT INTO #CurrentSchedule VALUES ('001233069','544024',2013)
INSERT INTO #CurrentSchedule VALUES ('001233069','550054',2013)
INSERT INTO #CurrentSchedule VALUES ('001233069','560012',2013)
INSERT INTO #CurrentSchedule VALUES ('001233069','580070',2013)
INSERT INTO #CurrentSchedule VALUES ('001233069','580064',2013)
CREATE TABLE #LastYearMark
(
StudentID varchar(09),
CourseID varchar(10),
SchoolYearCode int,
Mark varchar(02)
)
INSERT INTO #LastYearMark VALUES ('001233069','520018',2013,'E')
INSERT INTO #LastYearMark VALUES ('001233069','550031',2013,'A')
INSERT INTO #LastYearMark VALUES ('001233069','586603',2013,'B')
INSERT INTO #LastYearMark VALUES ('001233069','000116',2013,'B')
INSERT INTO #LastYearMark VALUES ('001233069','550001',2013,'A')
SELECT S.StudentID,S.Race,S.CurrentSchoolCode,s.CurrentGradeCode,c.CourseID AS [Current Year Schedule Course],LM.CourseID AS [Last Year's Course],LM.Mark AS[Last Year's Mark] FROM #Student s
JOIN #CurrentSchedule C
ON S.StudentID = C.StudentID
JOIN #LastYearMark LM
ON LM.StudentID = S.StudentID
WHERE C.CourseID = '001113'
I got output as below
StudentIDRaceCurrentSchoolCodeCurrentGradeCodeCurrent Year Schedule CourseLast Year's CourseLast Year's Mark
001233069W90911001113520018E
001233069W90911001113550031A
001233069W90911001113586603B
001233069W90911001113000116B
001233069W90911001113550001A
desired output
StudentIDRaceCurrentSchoolCodeCurrentGradeCodeCurrent Year Schedule CourseLast Year's CourseLast Year's Mark
001233069W90911001113520018E
001233069W90911009999550031A
001233069W90911544024586603B
001233069W90911550054000116B
001233069W90911560012550001A
is that possible?
if Yes, please help me to do so.
Thanks
August 23, 2013 at 4:20 pm
Can you explain the business rules for which CourseID in #CurrrentSchedule with which CourseID in #LastYearMark?
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply