Re: SQL Date/Interval Casting Puzzle

Some thing like this ? SELECT ... FROM ... WHERE MOD( ( ((YEAR(begin_date) * 12) + MONTH(begin_date)) - ((ParameterYear * 12) + ParameterMonth) ), ParameterInterval) = 0 AND ( ((YEAR(begin_date) * 12) + MONTH(begin_date)) - ((ParameterYear * 12) + ParameterMonth) ) < 0 >>> Red Valsen <red_valsen@yahoo.com> 11/17 10:52 pm >>> One of my non-Informix developers is writing a report and needs to find records in an Informix table with begin_date X number of months, or a multiple of X number of months, prior to a static date. In his own words: I Just need to fill in the Infomix syntax of it. Input: ParameterMonth ParameterYear ParameterInterval DB Field : BeginDate Criteria for selecting a record: BeginDate must be a multiple of ParameterInterval months in the past. ie. if the interval is 5 then the begin date must be 5 months ago..ten months ago...15 months ago............. to be included What I am looking to do is something along these lines...forgive the pseudocode 1. Create a date from the input year and month (Call it NewDate for this example) 2.Use some kind of a datediff function to return the # of months between the potentially selected record's begin date and NewDate 3. Divide the integer returned from the datediff function by the ParameterInterval 4. If the Modulus(Remainder) is 0 then the record gets selected the pseudo code would look something like this Select * from tables where Modulus (datediff (month,date(parameteryear,Parametermonth,01),BeginDate)/ParameterInterval) =0 Any takers on turning this into real Informix SQL? I haven't been able to get the SQL to work because I haven't been able to figure how to cast a difference between two dates into an integer representing months in order to take a modulus of the difference. This is the rudimentary SQL I came up with: select date("01/01/2000") const_dt, begin_date original_dt, extend(date("01/01/2000") - 5 units month, year to month) constdt_varduratn, extend(begin_date, year to month) extended_orig_dt from <tablename> where extend(begin_date, year to month) = extend(date("01/01/2000") - 5 units month, year to month); It'll do it for the first batch 5 months back, but, obviously, not 10 or 15 or 20, etc. Is there a solution to this puzzle?

Udaman! We had to tweak the SQL a bit, but it worked! Here's what the final clause looked like: MOD((((year("01/01/2000") * 12) + month("01/01/2000")) - ((YEAR(begin_date) * 12) + MONTH(begin_date))), 1) = 0 AND ((((year("01/01/2000") * 12) + month("01/01/2000")) - ((YEAR(begin_date) * 12) + MONTH(begin_date))) > 0) The hard-coded date strings will be passed in. Next time you're in DC we'll take you to Capitol City Brewery for a few pints. Richard harnden wrote: > Some thing like this ? > > SELECT ... > FROM ... > WHERE MOD( > ( ((YEAR(begin_date) * 12) + MONTH(begin_date)) > - ((ParameterYear * 12) + ParameterMonth) > ), ParameterInterval) = 0 > AND ( ((YEAR(begin_date) * 12) + MONTH(begin_date)) > - ((ParameterYear * 12) + ParameterMonth) > ) < 0> > >>> Red Valsen <red_valsen@yahoo.com> 11/17 10:52 pm >>> > One of my non-Informix developers is writing a report and needs to find > records in an Informix table with begin_date X number of months, or a > multiple of X number of months, prior to a static date. In his own > words: > > I Just need to fill in the Infomix syntax of it. > > Input: > ParameterMonth > ParameterYear > ParameterInterval > > DB Field : > BeginDate > > Criteria for selecting a record: > BeginDate must be a multiple of ParameterInterval months in the past. > ie. if the interval is 5 then the begin date must be 5 months ago..ten > months ago...15 months ago............. to be included > > What I am looking to do is something along these lines...forgive the > pseudocode > 1. Create a date from the input year and month (Call it NewDate for this > example) > > 2.Use some kind of a datediff function to return the # of months between > the potentially selected record's begin date and NewDate > > 3. Divide the integer returned from the datediff function by the > ParameterInterval > > 4. If the Modulus(Remainder) is 0 then the record gets selected > > the pseudo code would look something like this > > Select * from tables where > Modulus (datediff (month,> date(parameteryear,Parametermonth,01),BeginDate)/ParameterInterval) =0 > > Any takers on turning this into real Informix SQL? > > I haven't been able to get the SQL to work because I haven't been able > to figure how to cast a difference between two dates into an integer > representing months in order to take a modulus of the difference. This > is the rudimentary SQL I came up with: > > select date("01/01/2000") const_dt, > begin_date original_dt, > extend(date("01/01/2000") - 5 units month, year to month) > constdt_varduratn, > extend(begin_date, year to month) extended_orig_dt > from <tablename> > where extend(begin_date, year to month) = > extend(date("01/01/2000") - 5 units month, year to month); > > It'll do it for the first batch 5 months back, but, obviously, not 10 or > 15 or 20, etc. > > Is there a solution to this puzzle?