Bauhaus <niemandhier@xxxxxxxxxx> 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)) returns setof 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°
