Complex Query Challenge

[Chalco]

Hi,

I am writing (or trying to) some sql that will return a number of records depending on the record concerned.

e.g

Table A has a record that was last applied date into the ledger (Date_Last_Applied) was 1-Jan-2003, and has to be applied monthly (also read daily, weekly, fortnightly, half yearly, yearly, one-off)

I want to return 7 records (as today is 30-Jul-2003) for that one record - 1-Jan-2003, 1-Feb-2003, 1-Mar-2003 etc

Any suggestions

PS,

There may be a number of records, each with different periods

 

[substitute]

I'm in a hurry so this is just a general approach not a carefully coded solution!

Put 2 tables in the query. One is your table A and the other is a table of integers (one field with values 0; 1; 2; 3; 4 etc) There is no join between the tables. This is known as a Cartesian Product

Construct an expression field from date_last_applied and the integer field that evaluates to a stream of dates, e.g. by using the dateadd() function to add the integer number of months to the date_last_applied.

extra_date: dateadd("m", [integers].[integer], [date_last_applied])

Put a condition on this field that it is, say, < date()

The query will return a row for dates at one month intervals between date_last_applied and 1st July (if you do this in the next day or two!)