Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » DBISAM Technical Support » Support Forums » DBISAM General » View Thread |
Messages 1 to 5 of 5 total |
SQL Help |
Thu, Jun 26 2008 2:20 PM | Permanent Link |
"Eduardo [HPro]" | Hi
I have some data with the structure below Date Old Type Amount ------------------------------------------ 01/01/2008 0 Add 100 01/02/2008 100 Add 50 02/02/2008 150 Subtract 60 01/03/2008 90 Add 10 At this point the situation will be 90 + 10 = 100 but if I insert a new record for example between 01/02/2008 and 02/02/2008 is there any way to update the value of "Old" field using SQL ? I am already doing this using tables but with thousands or records and C/S this routine become very slow. Eduardo |
Wed, Jul 2 2008 9:52 AM | Permanent Link |
Dan Rootham | Eduardo,
<< I have some data with the structure below Date Old Type Amount ------------------------------------------ 01/01/2008 0 Add 100 01/02/2008 100 Add 50 02/02/2008 150 Subtract 60 01/03/2008 90 Add 10 >> It's not clear from your data structure whether there is also a timestamp, to indicate which is the latest record when you have more than one entry with the same date. Is there a timestamp column? Or a sequential record ID? And can we assume that your illustrative dates are dd/mm/yyyy format, rather than mm/dd/yyyy? Thanks, Dan Rootham [Team Elevate] |
Wed, Jul 2 2008 12:21 PM | Permanent Link |
"Eduardo [HPro]" | Dan
It was missing from the original post but The structure is Date Sequence Old Type Amount ------------------------------------------------ 01/01/2008 1 0 Add 100 01/02/2008 2 100 Add 50 02/02/2008 1 150 Subtract 60 01/03/2008 1 90 Add 10 The field "Sequence" is an integer field and you are right about the date format 'dd/mm/yyyy' Eduardo |
Fri, Jul 4 2008 6:38 AM | Permanent Link |
"John Hay" | Eduardo
> Date Sequence Old Type Amount > ------------------------------------------------ > 01/01/2008 1 0 Add 100 > 01/02/2008 2 100 Add 50 > 02/02/2008 1 150 Subtract 60 > 01/03/2008 1 90 Add 10 If you only add records to each day as a higher sequence number then UPDATE tablename SET old=old+ :insertedvalue where date > :inserteddate where insertedvalue is a parameter containing a positive value for "Add" and a negative value for "Subtract" and inserteddate is the date you inserted a record for. John |
Fri, Jul 4 2008 2:38 PM | Permanent Link |
"Eduardo [HPro]" | John
> If you only add records to each day as a higher sequence number then > >> UPDATE tablename SET old=old+ :insertedvalue where date > :inserteddate > > where insertedvalue is a parameter containing a positive value for "Add" > and > a negative value for "Subtract" and inserteddate is the date you inserted > a > record for. This is so easy and I could not see this solution. Thanks a lot Eduardo |
This web page was last updated on Tuesday, April 30, 2024 at 03:55 PM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |