Regularly I have to prepare summary data on the past for administration.
I use the query builder to do this. I have created a number of queries and I also use some that you and others have created.
The problem I’m facing is that many of my queries are working for the “current” year only. Apparently it’s a coding issue in all queries using […] FROM gibbonSchoolYear WHERE status=‘‘Current’’ […].
We conducted our first rollover a few weeks ago and therefore the current year changed. This kind of “breaks” my queries that I have to execute to get data of last year.
There are solutions to this problem, e.g. hard coding years into queries, working with database snapshots or simply changing the current year temporarily.
These solutions are not really satisfactory to me. Is there a better solution?
I wish I could simply login into the past year and have all my queries working for that year without hacking any. Wouldn’t that be great?
Roman, yes, logging into past years and having queries just work would be great. I am not sure how feasible that is though, as the year you are logged into is stored in PHP only, and so a pure SQL statement can’t get it. Something to think about.
For the moment, you can select the previous year with the following code:
WHERE gibbonSchoolYearID=(SELECT gibbonSchoolYearID FROM gibbonSchoolYear WHERE status=‘Past’ ORDER BY sequenceNumber DESC LIMIT 0,1)
The year before would then be
WHERE gibbonSchoolYearID=(SELECT gibbonSchoolYearID FROM gibbonSchoolYear WHERE status=‘Past’ ORDER BY sequenceNumber DESC LIMIT 1,1)
Hope this helps, and perhaps a more usable solution will come to light.