Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » ElevateDB Technical Support » Support Forums » ElevateDB SQL » View Thread |
Messages 1 to 3 of 3 total |
Incrementing date parameters |
Fri, Nov 23 2012 6:03 AM | Permanent Link |
Peter | 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 |
Fri, Nov 23 2012 10:56 AM | Permanent Link |
David Cornelius Cornelius Concepts | 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 > |
Fri, Nov 23 2012 10:27 PM | Permanent Link |
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 |
This web page was last updated on Friday, April 26, 2024 at 06:09 PM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |