Brother Arnold
Member
- Local time
- Today, 20:09
- Joined
- Apr 28, 2022
- Messages
- 39
For the life of me I can't get this to work. I need to identify on a 12 month rolling basis by month how many counsellors didn't have any appointments.
It has to take into account their start and end dates of employment. If the end date is blank they are still employed and if the counsellor is only employed for one day in a month (eg started on the 31st) they are still included in that month's total. The tblCounsellor has ID, start_date and end_date. The tblAppointment has the counsellorID and appt_date. If they don't have any appointments for a particular month then there will be no appointment records. The 12 months start from last month.
I know I can do this in code easy enough just by reading through each counsellor record and tally up which months they were inactive but employed. However I feel there should be a way to do this in a query but I don't know where to start plus my SQL skills are pants. Any ideas?
It has to take into account their start and end dates of employment. If the end date is blank they are still employed and if the counsellor is only employed for one day in a month (eg started on the 31st) they are still included in that month's total. The tblCounsellor has ID, start_date and end_date. The tblAppointment has the counsellorID and appt_date. If they don't have any appointments for a particular month then there will be no appointment records. The 12 months start from last month.
I know I can do this in code easy enough just by reading through each counsellor record and tally up which months they were inactive but employed. However I feel there should be a way to do this in a query but I don't know where to start plus my SQL skills are pants. Any ideas?