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

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]

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

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

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