sql server programming:
Here's one way to tackle this:
SELECT
t.FuelID,
t.PriceDate AS PriceDate_from,
(
SELECT MIN(t1.PriceDate)
FROM YourTable t1
WHERE
t1.FuelID = t.FuelID
AND t1.FuelID > t.FuelID
) AS PriceDate_to,
t.Price
FROM YourTable t
.... this won't properly deal with multiple subintervals with the same price
and some other issues--I'm not sure if you have those or not. Shameless
plug: The book in my signature has a large section dedicated to the topic of
dealing with time intervals like this :)
--
Adam Machanic
SQL Server MVP
Author, "Expert SQL Server 2005 Development"
http://www.apress.com/book/bookDisplay.html?bID=10220 [quoted text, click to view] "Bauhaus" <niemandhier@pandora.be> wrote in message
news:l8Tgi.8078$2X.6757@biebel.telenet-ops.be...
> 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
>
> I tried this:
>
> SELECT FuelID, min(FuelDate) AS Pricedate_from, max(FuelDate) AS
> PriceDate_to FROM Price GROUP BY FuelID;
>
> Problem is, when I put Price in the select, I get the error 'Price not
> part
> of an aggregate function' :s
> Eitherway, it doesnt work, I only have one FuelDate_from and one
> FuelDate_to
> if I use min & max. While there should be several from...to... dates for a
> particular fuel.
>
> How can I solve this ?
>
>
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
I tried this:
SELECT FuelID, min(FuelDate) AS Pricedate_from, max(FuelDate) AS
PriceDate_to FROM Price GROUP BY FuelID;
Problem is, when I put Price in the select, I get the error 'Price not part
of an aggregate function' :s
Eitherway, it doesnt work, I only have one FuelDate_from and one FuelDate_to
if I use min & max. While there should be several from...to... dates for a
particular fuel.
How can I solve this ?
