posted 4 months ago
Only one SQL query required and that works, so no need to change the SQL. I provided the SQL for information only.
I have given two examples of what the SQL can return with an example of the output of the code that operates on it. I will explain it in another way.
The fields returned are:
The first example returned by the SQL is:
The code run against this data is run on a Tuesday so needs to return:
Another example is that the SQL returns:
The code run against this data is run on a Tuesday so needs to return:
Wed, N, Thu, Y, Fri, N, Sat, Y, Sun, N, Mon, Y, Tue, N
I suppose a third example would be if we have three start and end:
The code run against this data is run on a Tuesday so needs to return:
The design is:
Determine the start date (in this example the code is run on a Tuesday so the first day returned is a Wednesday)Return each day and Y/N until the end date on the row is reached
In the first example there is only one row without an end date so read stt_wednesday N, stt_thursday Y, stt_friday Y, stt_saturday N, stt_sunday N, stt_monday N, stt_tuesday Y
In the second example there are two rows with end/start dates that are equal so from the first row read stt_wednesday N, stt_thursday Y. Friday is on the 31/12/2021 which is the end date of this row so read the next row. The next row starts on the 31/12/2021 and has no end date so read stt_friday N, stt_saturday Y, stt_sunday N, stt_monday Y, stt_tuesday N
In the third example there are three rows with end/start dates equal so from the first row read stt_wednesday N, stt_thursday Y. Friday is on the 31/12/2021 which is the end date of this row so read the next row.
The next row starts on the 31/12/2021 and has an end date or 02/01/2022 so read stt_friday N, stt_saturday Y, . Sunday is on the 02/01/2022 which is the end date so read the next row.
The next row starts on 02/01/2022 and has no end date so read stt_sunday N, stt_monday Y, stt_tuesday N.
In this example:
The program was run on a Tuesday so the returned start date was a Wednesday; however, the program can be run on any day.The end date of one row was the same date as the start date of the next row. It is possible that the next start date could start later. Therefore, any days between the end date and start date are assigned "N". For example Row has an end date of 31/12/2021 (Friday) the next row has a start date of 03/01/2021 (Monday). Therefore, Friday, Saturday and Sunday are assigned "N" each and the Monday and Tuesday are assigned the values returned in the row (stt_monday and stt_tuesday).
I hope I have explained it as clearly as possible. This is very complicated which I why I am requesting help (I am not the smartest person in the room).
Kind regards,
Glyn