title: SQL Query to pull date range based on dates in a table
body: <p>I have some SQL code which is contained in an SSRS report and when run pulls a list of student detentions for a set period such as a week or month but I have been asked to get the report to run automatically from the start of the current school term to the date the report has been run.  Is this possible?  We have 3 terms per year and the dates change  each year.  The report has multiple subscriptions which will run weekly and filter to students in particular day houses and years so we ideally need the report to update itself.</p>
  
  <p>We have a table in our database titled TblSchoolManagementTermDates which includes txtStartDate and txtFinishDate columns for each term.</p>
  
  <p>The date of the detention is stored in the column detPpl.dDetentionDate</p>
  
  <p>The full SQL code I am currently using is:</p>
  
  <pre><code>SELECT ppl.txtSchoolID AS SchoolID,
     detPpl.TblDisciplineManagerDetentionsPupilsID AS DetentionID,
     ppl.txtSurname AS Surname,
     ppl.txtForename AS Forename,
     ppl.txtPrename AS PreferredName,
     ppl.intNCYear AS Year,
     ppl.txtAcademicHouse AS House,
     schTermDates.intSchoolYear AS AcademicYear,
     schTerms.txtName AS TermName,
     CAST(schTermDates.intSchoolYear AS CHAR(4)) + '/' +
           RIGHT(CAST(schTermDates.intSchoolYear + 1 AS CHAR(4)), 2) AS AcademicYearName,
     detPpl.dDetentionDate AS DetentionDate,
     detSessions.txtSessionName AS DetentionName,
     detPpl.txtOffenceDescription AS OffenceDescription,
     LEFT(Staff.Firstname, 1) + '. ' + Staff.Surname AS PutInBy,
     detPpl.intPresent AS AttendedDetention
  FROM dbo.TblPupilManagementPupils AS ppl
   INNER JOIN
   dbo.TblDisciplineManagerDetentionsPupils AS detPpl
   ON detPpl.txtSchoolID = ppl.txtSchoolID
   INNER JOIN
   dbo.TblDisciplineManagerDetentionsSessions AS detSessions
   ON detPpl.intDetentionSessionID = detSessions.TblDisciplineManagerDetentionsSessionsID
   INNER JOIN
   dbo.TblStaff AS Staff
   ON Staff.User_Code = detPpl.txtSubmittedBy
   INNER JOIN
   dbo.TblSchoolManagementTermDates AS schTermDates
   ON detPpl.dDetentionDate BETWEEN schTermDates.txtStartDate AND schTermDates.txtFinishDate
   INNER JOIN
   dbo.TblSchoolManagementTermNames AS schTerms
   ON schTermDates.intTerm = schTerms.TblSchoolManagementTermNamesID
   LEFT OUTER JOIN
   dbo.TblDisciplineManagerDetentionsCancellations AS Cancelled
   ON Cancelled.intSessionID = detPpl.intDetentionSessionID
   AND Cancelled.dDetDate = detPpl.dDetentionDate
  WHERE (ppl.txtAcademicHouse = 'Challoner') AND (Cancelled.TblDisciplineManagerDetentionsCancellationsID IS NULL) AND (CAST(detPpl.dDetentionDate AS DATE) &gt;= CAST (GETDATE()-28 AS DATE))
  </code></pre>
  
  <p>ORDER BY ppl.txtSurname, ppl.txtForename, detPpl.dDetentionDate</p>