SQLTeam.com | Weblogs | Forums

T-sql 2012 using a join with a parameter value


#1

I have the following t-sql 2012 that uses are union all statement to make it work. I would like to make this sql
easier without have to use a Union all statement. I would like to be able to join #EnrollmentLastYear or
#EnrollmentLastYear temp tables to the main sql depending upon if . I would like to
either use 'JOIN #EnrollmentLastYear AS EnrollmentLastYear' or 'JOIN #EnrollmentCurrent AS CurrentEnrollment'
depending upon the paramter value of '@endyear = 2018 or 2017'.

Thus can you tell me how to accomplish my goal with the idea that I think using t-sql 2012 and/or the
t-sql 2012 you would recommend.

Here is the sql I have so far:

USE TST

Declare @endYear int= 2018
DECLARE @lastYear int = (select endYear - 1 from schoolYear where active = 1)
DECLARE @currentYear int = (select endYear from schoolYear where active = 1)

SELECT Enrollment.personID, School.Name as [Last School Attended],
MAX(Enrollment.endDate) as EndDate,Enrollment.endStatus
, MAX(Enrollment.startDate) AS startDate,Enrollment.endYear
into #EnrollmentLastYear
FROM TST.DBO.Enrollment Enrollment
JOIN TST.DBO.Calendar Calendar ON Calendar.calendarID =
Enrollment.calendarID
JOIN TST.DBO.School School ON School.schoolID =Calendar.schoolID
WHERE Enrollment.endYear = @lastYear
AND Enrollment.active=1
AND Enrollment.endDate between Calendar.startDate and Calendar.endDate
GROUP BY Enrollment.personID, School.Name,
Enrollment.endDateEnrollment.endStatus,Enrollment.startDate,Enrollment.endYear
ORDER BY Enrollment.personID, School.Name,
Enrollment.endDateEnrollment.endStatus,Enrollment.startDate,Enrollment.endYear

SELECT Enrollment.personID, School.Name as [Last School Attended],
MAX(Enrollment.endDate) as EndDate,Enrollment.endStatus
, MAX(Enrollment.startDate) AS startDate,Enrollment.endYear
into #EnrollmentCurrent
FROM TST.DBO.Enrollment Enrollment
JOIN TST.DBO.Calendar Calendar ON Calendar.calendarID =
Enrollment.calendarID
JOIN TST.DBO.School School ON School.schoolID =Calendar.schoolID
WHERE Enrollment.endYear = @currentYear
AND Enrollment.active=1
AND Enrollment.endDate between Calendar.startDate and Calendar.endDate
GROUP BY Enrollment.personID, School.Name,
Enrollment.endDateEnrollment.endStatus,Enrollment.startDate,Enrollment.endYear
ORDER BY Enrollment.personID, School.Name,
Enrollment.endDateEnrollment.endStatus,Enrollment.startDate,Enrollment.endYear

SELECT person.studentnumber,Ident.lastname + ', ' + Ident.firstname as [Student
Name],School.Name,EnrollmentLastYear.endDate,EnrollmentLastYear.endStatus,
creditsEarned ,EnrollmentLastYear.endYear
FROM TST.dbo.Enrollment AS Enrollment
JOIN TST.dbo.Calendar AS Calendar
On Calendar.CalendarID=Enrollment.CalendarID
JOIN TST.dbo.School School
ON School.schoolID=Calendar.schoolID

JOIN #EnrollmentLastYear AS EnrollmentLastYear
ON Enrollment.PersonID = EnrollmentLastYear.PersonID
AND Enrollment.startDate = EnrollmentLastYear.startDate
AND Enrollment.endYear =EnrollmentLastYear.endYear
AND EnrollmentLastYear.endStatus in ('202','205')
AND EnrollmentLastYear.endYear = @endYear
AND EnrollmentLastYear.PersonID not in
(select CurrentEnrollment.personID
from TST.dbo.Enrollment AS Enrollment
join #EnrollmentCurrent AS CurrentEnrollment
ON Enrollment.PersonID = CurrentEnrollment.PersonID
AND Enrollment.startDate = CurrentEnrollment.startDate
AND Enrollment.endYear =CurrentEnrollment.endYear
AND Enrollment.endYear = @currentYear
)
JOIN TST.dbo.Person AS Person
ON Enrollment.personID = Person.personID
JOIN TST.dbo.[Identity] AS Ident
ON Ident.identityID = Person.currentIdentityID
AND Ident.personID = Person.personID
JOIN (SELECT t.personID,SUM(creditsEarned) as creditsEarned
FROM dbo.TranscriptCredit t
INNER JOIN dbo.TranscriptCourse tc
ON tc.transcriptID = t.transcriptID AND t.personID = tc.personID
WHERE tc.grade in ('09','10','11','12')
GROUP BY t.personID
) as TransSumm
ON TransSumm.personID = Person.personID

  GROUP BY person.studentnumber,Ident.lastname + ', '  + Ident.firstname 
    , School.Name,EnrollmentLastYear.endDate,EnrollmentLastYear.endStatus 
	,creditsEarned, EnrollmentLastYear.endYear 		 

UNION ALL
SELECT person.studentnumber,Ident.lastname + ', ' + Ident.firstname as [Student
Name], School.Name, CurrentEnrollment.endDate, CurrentEnrollment.endStatus
,creditsEarned ,CurrentEnrollment.endYear
FROM TST.dbo.Enrollment AS Enrollment
JOIN TST.dbo.Calendar AS Calendar
On Calendar.CalendarID=Enrollment.CalendarID
JOIN TST.dbo.School School
ON School.schoolID=Calendar.schoolID

JOIN #EnrollmentCurrent AS CurrentEnrollment
ON Enrollment.PersonID = CurrentEnrollment.PersonID
AND Enrollment.startDate = CurrentEnrollment.startDate
AND Enrollment.endYear =CurrentEnrollment.endYear
AND CurrentEnrollment.endStatus in ('202','205')
AND CurrentEnrollment.endYear = @endYear

JOIN TST.dbo.Person AS Person
ON Enrollment.personID = Person.personID
JOIN TST.dbo.[Identity] AS Ident
ON Ident.identityID = Person.currentIdentityID
AND Ident.personID = Person.personID
JOIN (SELECT t.personID,SUM(creditsEarned) as creditsEarned
FROM dbo.TranscriptCredit t
INNER JOIN dbo.TranscriptCourse tc
ON tc.transcriptID = t.transcriptID AND t.personID = tc.personID
WHERE tc.grade in ('09','10','11','12')
GROUP BY t.personID
) as TransSumm
ON TransSumm.personID = Person.personID

  GROUP BY person.studentnumber,Ident.lastname + ', '  + Ident.firstname , 
   CurrentEnrollment.grade, School.Name, CurrentEnrollment.endDate
   , CurrentEnrollment.endStatus,creditsEarned, CurrentEnrollment.endYear 		 

DROP TABLE #EnrollmentLastYear
DROP TABLE #EnrollmentCurrent

t-sql 2012 using a join with a parameter value


#2

figured out my own answer.