t-sql 2012 selection based upon paramter value

• wendy elizabeth

  SSCoach

  Points: 16915

  More actions

  August 15, 2017 at 7:50 am

  #323063

  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

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  August 15, 2017 at 9:20 am

  #1955305

  wendy elizabeth - Tuesday, August 15, 2017 7:50 AM

  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

  I will need to look closer at the code, but something tells me you are doing more work than you need to for this.  The first two queries appear to populate two temporary tables based on a given date (the one you want to control the data, either previous year or current year).  You then use a union all query to select from one or the other but not both at the same time.  My question is why?  Your first two queries are the same except for the year of the data pulled.  Seems to me that should be the base for the query and the choice of what year is based on the value passed to that query.