SQL Newbie here who works in a school. Still learning how to do things. Can someone offer some guidance on how best to write a query that has multiple where statements.

This query will look at our current students and exclude those in year 13 who will be definitely leaving us, exclude the students who have notified us that they are leaving from other year groups but will need to include students that we have been told are joining us. This will mean I can run the report now to get a list of students who will be here at the start of the new school year in September.

I have 2 tables that I am pulling data from.

The first is dbo.TblPupilManagementPupils and the columns I am using are:

• txtSchoolID (unique student identifier)
• txtSurname
• txtForename
• intNCYear (student year group)
• txtAcademicHouse (student house)
• txtForm (student tutor/form group)
• intSystemStatus (-1 means student has left, 0 mean they haven’t started yet and 1 means they are current)
• intEnrolmentSchoolYear (the year the student will join the College e.g 2024)
• txtEnrolmentTerm (which school term the student will join eg Winter, Spring, Summer)
• txtAdmissionsStatus (identifies whether they are definitely going to join us. This would be “6. Accepted”)
• txtLeavingDate (is a datetime column showing an exact leaving date)

The second is dbo.TblPupilManagentCustomFieldValue and the columns I am using are:

• txtSchoolID (unique student identifier)
• txtValue (lists a number of custom data fields per student. This would filter to the value “Definite Leaver”)

The following query returns all of the students who are here now and will be staying in September:

SELECT ppl.txtSurname, ppl.txtForename, ppl.intNCYear, ppl.txtAcademicHouse, ppl.txtForm
FROM TblPupilManagementPupils AS PPL
WHERE ppl.intSystemStatus = '1' AND ppl.intNCYear < 13 
ORDER BY ppl.txtSurname, ppl.txtForename

The following query returns all of the students who are currently at the school but have notified us that they are leaving:

SELECT        PPL.txtSurname, PPL.txtForename, PPL.txtLeavingDate, TblPupilManagementCustomFieldValue.txtValue AS LeavingType
FROM            TblPupilManagementPupils AS PPL LEFT OUTER JOIN
                         TblPupilManagementCustomFieldValue ON PPL.txtSchoolID = TblPupilManagementCustomFieldValue.txtSchoolId
WHERE        (TblPupilManagementCustomFieldValue.txtValue = 'Definite Leaver') AND (CONVERT(VARCHAR(25), PPL.txtLeavingDate, 126) LIKE '2024-08-31%')

The following query returns all of the students who will be joining us at the start of the next school year:

SELECT ppl.txtSurname, ppl.txtforename, ppl.intEnrolmentSchoolYear, ppl.txtEnrolmentTerm, ppl.txtAdmissionsStatus
FROM dbo.TblPupilManagementPupils AS ppl
WHERE ppl.intsystemstatus = 0 AND ppl.intEnrolmentSchoolYear = '2024' AND ppl.txtAdmissionsStatus = '6. Accepted' AND ppl.txtEnrolmentTerm = 'Michaelmas'

I have tried a few different ways of combining these queries and none have worked. If anyone can give me some pointers on how to write this query I would be grateful.

Many thanks

Stuart

I have tried coalesce select sub queries and writing my query in different ways but cannot get an accurate list. I know the exact number of students as I have calculated this manually.