Data range needed for sql query

SQL Server Administration
1

Hi all,
i have been lumped with a data extraction after our DB admin left in a hurry recently.
I am very new to this and just need a date range added to the following to produce output for this year, Jan 2016 to present day.
any help appreciated
SELECT dbo.Students.StudentNumber, dbo.CourseRegistrations.RegistrationDate, dbo.Sessions.Start, dbo.Courses.QualName, dbo.CourseRegistrations.CertNumber, dbo.Courses.CourseName, dbo.Competencies.UnitID, dbo.Competencies.Unit_Name
FROM (dbo.Competencies INNER JOIN dbo.QualComp ON dbo.Competencies.UnitID = dbo.QualComp.UnitID) INNER JOIN ((dbo.Courses INNER JOIN dbo.Sessions ON dbo.Courses.CourseID = dbo.Sessions.CourseID) INNER JOIN (dbo.Students INNER JOIN dbo.CourseRegistrations ON dbo.Students.StudentNumber = dbo.CourseRegistrations.StudentID) ON dbo.Sessions.CourseID = dbo.CourseRegistrations.CourseID) ON dbo.QualComp.QualName = dbo.Courses.QualName
WHERE (((dbo.CourseRegistrations.Active)=1));

cheers

2

I find all those nested JOINs a nightmare to decipher, might just be me though ... the code was probably mechanically-generated by something at some time.

I'm not convinced that the last set of nested joins is correct (well ... "optimal"), so I've made a small change and thus this suggestion needs testing

SELECT	STU.StudentNumber, CR.RegistrationDate, SESS.Start, COUR.QualName,
	CR.CertNumber, COUR.CourseName, COMP.UnitID, COMP.Unit_Name
FROM	dbo.Competencies AS COMP
	INNER JOIN dbo.QualComp AS QC
		ON COMP.UnitID = QC.UnitID
	INNER JOIN dbo.Courses AS COUR
		ON QC.QualName = COUR.QualName
	INNER JOIN dbo.Sessions AS SESS
		ON COUR.CourseID = SESS.CourseID
	INNER JOIN dbo.CourseRegistrations AS CR
		ON SESS.CourseID = CR.CourseID
	INNER JOIN dbo.Students AS STU
		ON STU.StudentNumber = CR.StudentID
WHERE CR.Active = 1;

The final nesting on your original code was:

		INNER JOIN
		(
			dbo.Students AS STU
			INNER JOIN dbo.CourseRegistrations AS CR
				ON STU.StudentNumber = CR.StudentID
		)
			ON SESS.CourseID = CR.CourseID
	)
		ON QC.QualName = COUR.QualName

which I think is back-to-front. Might be that the optimiser will generate exactly the same Query Plan, but I've written it the way that seems more "logical" to me!!

I have also added Aliases to the tables - referencing every column by having to include the Schema and Table name in full makes the whole thing very hard to read (and therefore harder to debug, and more chance of bugs going unnoticed)

I'm presuming that it is the RegistrationDate that this condition to be applied to? Change the WHERE clause tp :

WHERE CR.Active = 1
      AND CR.RegistrationDate >= '20160101'
      AND CR.RegistrationDate <  DATEADD(Day, 0, DATEDIFF(Day, 0, GetDate()) + 1);

This:

DATEADD(Day, 0, DATEDIFF(Day, 0, GetDate()) + 1);

will evaluate to "tomorrow" (midnight) so will select anything BEFORE, but not equal-to, "tomorrow" - including if the column you are comparing (Registration Date in this instance) includes a time element and has, say, a value just a few seconds before midnight. Perhaps the more obvious, layman's, choice would be to say "After 01-Jan-2016" and "On or before Today", but that has a problem if there is a time element when checking "On today", hence why "Before tomorrow" is the preferred test.

String Date and Datetime values in YYYYMMDD format are unambigous to SQL, so I have used 01-Jan-2016 as '20160101'. If you were to use a 12-31-2016 format that would be correctly interpreted by SQL only if the currently logged on user has the right Language, Server and other "indicators" for SQL to decide the date format will be MM-DD-YYYY, so best to avoid such "ambiguous" date/time formats. (I expect your Registration Date is a DATE and not a DATETIME, but my advice would be to adopt this style so that if/when you encounter a DateTime value your code is interchangeable)