Switch with multiple for loops and consequents new records
Hello,
for each person, I need to list all the academic years that person has been or is being hired, along with the startDate and endDate for each academic year resulting.
The only useful data already available from the contract are startDate and endDate.
Academic years start on September 1st and end on August 31st of the following year, while a contract can start and end any time or it can even be still running.
So if a contract starts on 01/09/2022 and ends on 30/06/2023, the list will only be made by one record:
- record 1: 2022-2023, startDate is 01/09/2022 and endDate is 30/06/2023.
Instead, if a contract starts on 01/09/2022 and ends on 01/10/2023, the list will be made of two records:
- record 1: 2022-2023, startDate is 01/09/2022, endDate is 31/08/2022
- record 2: 2023-2024, startDate is 01/09/2023, endDate is 01/10/2023
If the contract is longer, let's say it started on 01/09/2019 and still running, the list will be made of many records, like the following:
- record 1: 2019-2020, startDate is 01/09/2019, endDate is 31/08/2020
- record 2: 2020-2021, startDate is 01/09/2020, endDate is 31/08/2021
- record 3: 2021-2022, startDate is 01/09/2021, endDate is 31/08/2022
- record 4: 2022-2023. startDate is 01/09/2022, endDate is today
A solution for a simpler and similar situation was the on offered in this post https://forum.ninox.com/t/p8hwk0w/list-of-yearsmonthsweek but the need to define also academic years makes it more complicated to define
16 replies
-
Do you have a sample DB you can share so we can see the table structure and have some data to work with?
-
Quite straight forward. See attached sample database with code to generate records.
-
Here is my take on it. I try to cover empty end dates and start dates that happen mid school year. Open the contratti table to see the button.
I've commented the code in the button so I won't put them here.
Please excuse the bad italian names for the tables and fields.
Content aside
- 2 yrs agoLast active
- 16Replies
- 202Views
-
3
Following