Problem with SQL query

  • Thread starter Thread starter Bauhaus
  • Start date Start date
B

Bauhaus

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 ?
 
I made a table with 3 columns like yours:
CREATE TABLE [dbo].[PriceTable] (
[FuelID] [varchar] (10) COLLATE Danish_Norwegian_CI_AS NULL ,
[PriceDate] [smalldatetime] NULL ,
[Price] [int] NULL
) ON [PRIMARY]

I made this query to produce what Ithink you are asking for:

select A.Pricedate as Fromdate, A.FuelID as FuelID,
Min(Pricetable.Pricedate) as ToDate from Pricetable,
(Select pricedate, FuelID from Pricetable) as A
where A.fuelID = Pricetable.fuelID and
Pricetable.PriceDate > A.Pricedate
group by A.pricedate, A.fuelID

There is a subquery within the query, named A. I take data out of this
subquery A as well as the table Pricetable. In the where-clause i put
together the conditions for conecting A with Pricetable.

This could be solved in many other ways.

Regards

Tore
 
Back
Top