Re: Time window query (fwd)

Hi, I wrote the message below on Thursday, but I don't think it has appeared on c.d.i. I'm not sure where my emailer sends stuff for news groups, so the chances are it is not working correctly and I'm sending the message again. Additionally, I've done some more thinking, and of course the three nested MOD operators in the SELECT condition are not really necessary. The following query produces the same answer as the triple-mod query on the test data: SELECT P.Prj_Code FROM Project P, ReviewPeriod R WHERE P.Prj_Begin < R.RVP_Begin AND MOD(1000*P.Prj_Cycle - (R.RVP_Begin - P.Prj_Begin), P.Prj_Cycle) BETWEEN 0 AND (R.RVP_End - R.RVP_Begin) ; With modulo arithmetic, you can move the modulo operators around and still end up with the same result, especially when they all have the same divisor. And you can certainly add any multiple of the divisor for the modulo operator without altering the final answer. The only trick with computed modulo operations (as opposed to mathematical modulo operations) is to ensure that negative numbers never get into the picture. The 1000*P.Prj_Cycle is precisely designed to ensure that there's never a negative in the calculation, unless you have projects more than about 3 years old with a daily review cycle. You could probably increase the constant to 10000 without much danger of overflow or negative numbers. -- Yours, Jonathan Leffler (Jonathan.Leffler@Informix.com) #include <disclaimer.h> Guardian of DBD::Informix v1.00.PC1 -- http://www.perl.com/CPAN "I don't suffer from insanity; I enjoy every minute of it!" ---------- Forwarded message ---------- Date: Thu, 21 Dec 2000 12:54:11 -0800 From: Jonathan Leffler <jleffler@informix.com> Reply-To: Jonathan Leffler <Jonathan.Leffler@informix.com> To: Red Valsen <red_valsen@yahoo.com> Newsgroups: comp.databases.informix Subject: Re: Time window query On Tue, 19 Dec 2000, Red Valsen wrote: >I need to determine whether a date falls within a future datespan based >on a given interval. For example, the division VP has several >development groups, each with projects which have different review >cycles (e.g. 2, 3, or 4 weeks). Based on each project begin date, he >wants to know which projects are up for review during a given week. Hmmm. That is a complex situation. Let's see if I've understood it correctly. We have a number of projects. Each project has a start date and a review cycle (which is probably, but not necessarily, a whole number of weeks). Given a week such as 5-9 February 2001, which projects have a review due during that week, and on which day is the review due? >I have a generic query: > >select proj_begin_date >from project where>begin_date > (today - X units year) and I think this is simply excluding projects more than X years old, which is approximately equivalent to only choosing current projects. I'd probably look to do this with a more explicit condition on the project status. >date(windowenddate) > date(windowbegindate) and and this validates that 9th February is after 5th February (so the parameters of the query are valid). >proj_begin_date < date(windowbegindate) and and the project has begun when the review is scheduled >((date(windowbegindate) - mod((windowbegindate) - proj_begin_date), >interval) + interval) <= date(windowenddate) >or >mod((date(windowbegindate) - proj_begin_date),interval) = 0 >or >mod((date(windowenddate) - proj_begin_date),interval) = 0) Hmmm, this is the core of the query. The 'interval' parameter is the number of days in the review cycle, rather than anything to do with the Informix INTERVAL data type. The second and third parts of the OR check whether the number of days from the project start date to the beginning or ending of the review period is an exact multiple of the projects review cycle. The first part of the OR conditions has me puzzled. I think there's an error in the parentheses; the MOD operator has only one argument with the query as written: ((date(windowbegindate) - mod((windowbegindate) - proj_begin_date), interval) + interval) <= date(windowenddate) I think I'd assume that windowenddate and windowbegindate are indeed dates, and I'd assume that the second argument to MOD is meant to be the ", interval", and can then simplify it to read: (windowbegindate - mod(windowbegindate - proj_begin_date, interval) + interval) <= windowenddate I'd then re-organize it to read: windowbegindate + (interval - mod(windowbegindate - proj_begin_date, interval)) <= windowenddate Now I'm just not sure what this does. The MOD operator gives the remainder when you divide the number of days between the beginning of the project and the beginning of the review period by the number of days in the review cycle. I'm having problems conceptualizing what this does... Let's assume I'm having a brain fart. How would I resolve the issue? We want: (project_begin_date + N * interval) BETWEEN windowbegindate AND windowenddate Where we don't yet know the value of N, and don't really need to know the exact value of N, but we do know N is an integer. We can safely assume that the number of days in the review period is less than the number of days in any project's review cycle. Let's use: D = windowenddate - windowbegindate and subtract windowbegindate from all terms (noting that windowbegindate is larger than project_begin_date): ((project_begin_date - windowbegindate) + N * interval) BETWEEN 0 AND D We note that the subtraction yields a negative number, and N must be big enough to make the term on the left of BETWEEN into a number between zero and interval. Here comes a MOD operator, but negative numbers are troublesome. So, let's try to make it all positive: MOD(xxxx, interval) BETWEEN 0 AND D What's xxxx? Another couple of MOD operations? xxxx == MOD(interval - MOD(windowbegindate - project_begin_date, interval), interval) You only need the outer of those two MOD operators when the inner MOD results in zero, which happens when the windowbegindate is an exact multiple of the interval from the project_begin_date, and is dealt with by the special cases. Maybe we can avoid the special cases (the other two terms in the original triple-OR clause) like this. This results in: MOD(MOD(interval - MOD(windowbegindate - project_begin_date, interval), interval), interval) BETWEEN 0 AND D >Assuming: > > intervals are inclusive of begin/end dates > X = absolute period of time > windowbegindate = date of start of future date span > windowenddate = date of end of future date span > interval = number of days for review cycle > >So, for example, for a monthly (ie 30 day) review cycle and window of >the first full week of February, 2001: > >select proj_begin_date >from project where>proj_begin_date > (today - 3 units year) and >date("2/5/2001") > date("2/9/2001") and >proj_begin_date < date("2/5/2001") >and >((date("2/5/2001") - mod((date("2/5/2001") - proj_begin_date), 30) +