Background
Students courses in Schoolworks are represented by a Start Date and End date.
We store the student’s course booking in an object called Booking__c
Student Courses might look like:
Booking__c
Student Id | Start Date | End Date | Status |
|---|---|---|---|
1234 | 25/3/24 | 19/4/24 | Booked |
2345 | 5/2/24 | 21/3/24 | Booked |
3456 | 3/1/24 | 13/11/24 | Booked |
4567 | 13/2/24 | 10/10/24 | Cancelled |
5678 | 26/3/24 | 28/3/24 | Booked |
Each booking has a status - ‘Booked’ and ‘Cancelled’.
One of the most common queries needed in Schoolworks, is one to find all students ‘here’ (i.e. considered to be in school) across a period.
The period might be a month (1/3/24 to 31/3/24), week (24/5/24 to 28/5/24) or any period dates.
A student is deemed to be ‘here' (i.e. ‘in school’) if their course dates are across the period dates.
e.g. 3 students are in school (above) for period 25/3/24 to 29/3/24.
Development task
Please write a Salesforce Apex method, with input parameters - periodStartDate, periodEndDate, that returns a list of students 'here' across the period, from the object Booking__c, which has the fields, Student_Id, Booking_Start_Date__c, Booking_End_Date__c, Status__c. where Booking Start/End dates are across PeriodStart/End dates and Status = "Booked".
Please demonstrate development best practices, including considerations for NULL, Error management, comments and anything else you consider to be important in your submission.
Imagine this code, has to work, like a rocket launched to the moon.
Extension task
Additionally, please exclude any students from the list, who may be on holiday, every day, across the whole periodStartDate, periodEndDate.
If the student is ‘in school’ on any day during the period and they are also ‘on holiday’ for some, but not all, of the periodStartDate, periodEndDate then they are deemed to be ‘in school’
Holidays are stored in another object Holidays__c with fields, Student_Id, Holiday_Start_Date__c and Holiday_End_Date__c, Status__c.
The status for a valid holiday is "Booked".
Holiday__c
Student Id | Start Date | End Date | Status |
|---|---|---|---|
1234 | 1/3/24 | 1/3/24 | Booked |
1234 | 2/3/24 | 3/3/24 | Booked |
2345 | 19/2/24 | 21/2/24 | Booked |
1234 | 26/3/24 | 26/3/24 | Booked |
3456 | 20/3/24 | 26/3/24 | Booked |
3456 | 27/3/24 | 30/4/24 | Booked |
5678 | 27/3/24 | 27/3/24 | Booked |
e.g. 2 students are in school for the period 25/3/24 to 29/3/24 if we now exclude those on holiday for this period.
Further questions
Please feel free to ask for further questions for clarification of the task.
Your submission
This can be submitted in a format you think is appropriate. It can be a txt file, PDF, or working example in a test ORG.
You can also submit your questions and assumptions.
Please consider usual best practices in your submission.
We will consider part answers as well with any questions or assumptions, in particular for the extension task.
What we are looking for
Imagine you are being asked to deliver this task for one of our customers.
Your approach to the task, further questions (if needed for clarification), assumptions, best practices and then accuracy are all important in our evaluation of your submission.