I have one table bibles that having two columns only i.e. is below:-
id chapter_name 1 Mathews 1 2 Mathews 2 3 Mathews 3 4 Mathews 4 ... .... ... .... 364 Revelation 22 //Total Records only 364 fixed. that will not increase
Bible Scheduling starts from 5 sept 2021 for 3 years and ends on 31 Aug 2024 i.e basically bible repeats three time in 3 years.
Now Suppose today date is 8 Sept 2021
id chapter_name date_1 date_2 date_3 1 Mathews 1 2021-09-05 2022-09-04 2023-09-03 2 Mathews 2 2021-09-06 2022-09-05 2023-09-04 3 Mathews 3 2021-09-07 2022-09-06 2023-09-05 4 Mathews 4 2021-09-08 2022-09-07 2023-09-06 ... .... ... ....
So now requested date is 8 spet 2021 i want to fetch the 4th record Mathews 4 and now suppose today date is 6 sept 2022 and now want to fetch the 3 row Mathews 3.
Note : these date columns are not stored in database. i have only two columns id, chapter_name.. Reason for not storing the dates because after three years i need to update again that dates to make scheduling works. Is there any way to fetch the rows?
Below answer works fine but I have two types of Bibles that starts from Id 1 to 364 another starts from 365 to 1456… 1st case 1 to 364 works.. but 365 to 1456 not works
Advertisement
Answer
If I understand correctly, then logic you want is:
where dateadiff(?, '2021-09-05') % 364 = id - 1
The ? is a parameter for the date you pass in.