Re: SQL problem.. - Mailing list pgsql-general

Bauhaus <niemandhier@pandora.be> schrieb:

> Hello,
>
> I'm an Access/SQL novice and I have an sql problem:
>
> I have the following table Price:
>
> FuelID PriceDate   Price
> LPG    1/05/2007   0,2
> LPG    13/05/2007 0,21
> SPS     2/05/2007   1,1
> SPS     15/05/2007 1,08
>
> And I have to make the following query:
>
> FuelID PriceDate_from PriceDate_To Price
> LPG    1/05/2007         13/05/2007     0,2
> SPS     2/05/2007         15/05/2007    1,1
> LPG    13/05/2007                              0,21
> SPS     15/05/2007                             1,08


> How can I solve this ?

There are different solutions, one of them:

write a function like this:

create or replace function price_list(OUT _id text, OUT _date_from date, out _date_to date, OUT _price numeric(10,2))
returnssetof record as $$ 
declare _row record;
        _old date;
        _old_id text;
begin
        _old := NULL;
        _old_id := NULL;
        for _row in select * from price order by fuel_id, price_date
desc loop
                if _old_id != _row.fuel_id then
                        _date_to := NULL;
                else
                        _date_to := _old;
                end if;
                _old_id := _row.fuel_id;
                _id := _row.fuel_id;
                _date_from := _row.price_date;
                -- _date_to := _old;
                _price := _row.price;
                _old := _row.price_date;
                return next ;
        end loop;
end;
$$ language plpgsql;


Test:

test=*# \d price
          Table "public.price"
   Column   |     Type      | Modifiers
------------+---------------+-----------
 fuel_id    | text          |
 price_date | date          |
 price      | numeric(10,2) |

test=*# select * from price ;
 fuel_id | price_date | price
---------+------------+-------
 LPG     | 2007-05-01 |  0.20
 LPG     | 2007-05-13 |  0.21
 SPS     | 2007-05-02 |  1.10
 SPS     | 2007-05-15 |  1.08
(4 rows)



test=*# select * from price_list() order by _id, _date_from asc;
 _id | _date_from |  _date_to  | _price
-----+------------+------------+--------
 LPG | 2007-05-01 | 2007-05-13 |   0.20
 LPG | 2007-05-13 |            |   0.21
 SPS | 2007-05-02 | 2007-05-15 |   1.10
 SPS | 2007-05-15 |            |   1.08
(4 rows)

Other solution:

Alter your table and add a new column for the end-valid-date and write a
trigger. This trigger updates your table for every new inserted record.
(insert the new inserted date_from as date_to into the last record)

Finaly a link for you:
http://www.rueping.info/doc/Andreas%20R&ping%20--%202D%20History.pdf


Andreas
--
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect.                              (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly."    (unknow)
Kaufbach, Saxony, Germany, Europe.              N 51.05082°, E 13.56889°