I would like to be able to run a very complex SQL, ultimately in an SP, where I take a number of parameters and do some cute date arithmetic. The idea is to allow the user to be able to create multiple recurring bookings, and the UI allows them to change:
1. The number of intervals between events.
2. The type of interval: Days, Weeks or Months.
3. The number of bookings to create

I can do the logic easily in Delph, but I would like to get this running server side. At the moment, if I create an event every 2 weeks, to recur 4 times, I get dates of...
Day 1: 23/11/2012
Day 2: 7/12/2012
Day 3: 21/12/2012
Day 4: 4/01/2013
...and the SQL to do each date in turn is aQry.SQL.Add('INSERT INTO Run (RunID, StartDT, EndDT, EventType) VALUES (:RunID, :StartDT, :EndDT, :EventType);

How can I do that in SQL? I guess I wouid have to pass the interval value, i.e. the number of days between each event, but I wonder if there is a better way.

I would appreciate any assistance

Peter

I would separate the work between two stored procedures, one to
duplicate an event to another date, and another to do the logic of how
many events to duplicate and what interval.  You'll have to create the
second one based on your knowledge of your application and parameters,
but here's one way how you could implement the event duplication
procedure that does the date math:


CREATE PROCEDURE "DuplicateEvent" (IN "OrigRunID" (IN "OrigRunID"
INTEGER, IN "IncAmount" INTEGER, IN "IncType" VARCHAR)
BEGIN
  DECLARE sql VARCHAR DEFAULT
    'INSERT INTO Run (StartDT, EndDT, EventType)
     SELECT StartDT + INTERVAL ''' + CAST(IncAmount AS VARCHAR) + ''' '
+ IncType + ',
     SELECT EndDT + INTERVAL ''' + CAST(IncAmount AS VARCHAR) + ''' ' +
IncType + ',
     EventType FROM Run WHERE RunID = ?';

  SET LOG MESSAGE TO sql;

  EXECUTE IMMEDIATE sql USING OrigRunID;
END

This assumes RunID is an autoincrement integer, but if you need to store
that ID or need to generate it manually for whatever reason, you'll need
to add a parameter and pass that in, of course.

Anyway, using the INTERVAL date operator in EDB SQL is pretty flexible,
and casting parameters and building your SQL on-the-fly makes this possible.

--
David Cornelius
Cornelius Concepts

On 11/23/12 03:03, Peter wrote:
> I would like to be able to run a very complex SQL, ultimately in an SP, where I take a number of parameters and do some cute date arithmetic. The idea is to allow the user to be able to create multiple recurring bookings, and the UI allows them to change:
> 1. The number of intervals between events.
> 2. The type of interval: Days, Weeks or Months.
> 3. The number of bookings to create
>
> I can do the logic easily in Delph, but I would like to get this running server side. At the moment, if I create an event every 2 weeks, to recur 4 times, I get dates of...
> Day 1: 23/11/2012
> Day 2: 7/12/2012
> Day 3: 21/12/2012
> Day 4: 4/01/2013
> ..and the SQL to do each date in turn is aQry.SQL.Add('INSERT INTO Run (RunID, StartDT, EndDT, EventType) VALUES (:RunID, :StartDT, :EndDT, :EventType);
>
> How can I do that in SQL? I guess I wouid have to pass the interval value, i.e. the number of days between each event, but I wonder if there is a better way.
>
> I would appreciate any assistance
>
> Peter
>

David

Yes, the RunID is a generated ID field and only exists in the sample sql to obtain the ID after the statement has been run. The INTERVAL concept was nagging me; I knew there was something like that. SET LOG MESSAGE was new to me, so I'm grateful for the insight.

I have the Delphi code running, so it would probably not make much difference if I called the SP that you designed from the Delphi loop. I mean it would be better from a design point of view, in terms of separating the back end from the UI, but it wouldn't make much difference speed wise. I think.

Thanks for the code, much appreciated.

Peter