Re: How do write a query...

On Wed, Jun 01, 2005 at 18:00:49 +0200,
Alain Reymond <arwebmail(at)skynet(dot)be> wrote:
> Hello,
>
> I have the following problem :
>
> I have a table like
> Id Num Date AValue
> 1 10 01/01/2005 50
> 2 10 31/05/2005 60
> 3 25 02/02/2005 55
> 4 25 15/03/2005 43
> 5 25 28/05/2005 62
> etc..
>
> Id is unique, Num is an identification number with duplicates possible,
> date is a ... date and Avalue... a value!
>
> If we have
> Id Num Date AValue
> Id1 Num1 Date1 AValue1
> Id2 Num1 Date2 AValue2
>
> The table is ordered on Num+Date.
> What I would like to calculate is (AValue2-AValue1) for a given Num
> (here num1).
>
> In this case, I would have to calculate
> 60-50 for Num 10
> and
> 43-55, 62-43 for Num 25.
>
> Do you have any idea if it can be done simply with a request...

I would suggest using a different design. Probably the easiest is to
put the start and end dates and values in one record.

Otherwise you could write a function or have your application read entries
for a particular Num value ordered by date and treat them as pairs.
If any intervals overlap then there really isn't a fix.