Student can go to classes and how many hours he spent on class recorded in Student_Hours table. Student is considered exited from school if he/she doesn’t have any hours recorded in 100 days. Need to go through ‘Student_Hours’ table and find all the times he exited, in below example, student 1 has first contact hours on 1/1/2017 and last contact hours on 5/1/2017 and there are no contact hours within 100 days from this date and exit date is 8/9/2017 (5/1/17 + 100).
Need to add 100 days to first contact hour date, find are there any hours between this date range, if so take last contact hours date add 100 days to this date and look for hours in this date range, if nothing found then add 100 days to last contact hours date and make it an exit date.
indent preformatted text by 4 spaces
Student
Id Name
1 a
2 b
3 c
4 d
5 e
indent preformatted text by 4 spaces
Class
Id Name
10 c10
20 c20
30 c30
indent preformatted text by 4 spaces
Student_Hours
Student_Id Class_Id Hours_Date Hours
1 10 1/1/2017 2
1 10 3/1/2017 5
1 10 5/1/2017 6
1 20 12/1/2017 9
2 10 1/1/2017 1
2 10 1/31/2017 2
2 10 2/31/2017 2
2 10 7/5/2017 2
2 10 11/1/2017 4
2 10 3/1/2018 4
Output data
Student_Id Last Contact Hrs Date Exit Date
1 5/1/2017 8/9/2017
2 7/5/2017 10/31/2017
2 11/1/2017 2/9/2018
TIA