Re: How do write a query... - Mailing list pgsql-sql

From Bruno Wolff III
Subject Re: How do write a query...
Date
Msg-id 20050606162100.GA30453@wolff.to
Whole thread Raw
In response to How do write a query...  (Alain Reymond <arwebmail@skynet.be>)
List pgsql-sql
On Wed, Jun 01, 2005 at 18:00:49 +0200, Alain Reymond <arwebmail@skynet.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.



pgsql-sql by date:

Previous
From: Richard Huxton
Date:
Subject: Re: What is faster?
Next
From: Markus Bertheau ☭
Date:
Subject: SQL equivalent to nested loop