Historical prices with data gaps

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Historical prices with data gaps

Author

Topic

dang12
Starting Member

12 Posts

Posted - 2008-04-24 : 06:26:14

Hi,

I have a SQL2005 db for tracking the prices of products at multiple retailers. The basic structure is, 'products' table lists individual products, 'retailer_products' table lists current prices of the products at multiple retailers, and 'price_history' table records when the price of a product changes at any retailer. The prices are checked from each retailer daily, but a row is added to the 'price_history' only when the price at the retailer changes.

Database create script:
[url]http://www.boltfile.com/directdownload/db_create_script.sql[/url]

Full database backup:
[url]http://www.boltfile.com/directdownload/database.bak[/url]

Database diagram:
[url]http://www.boltfile.com/directdownload/diagram_0.pdf[/url]

I have the following query to retrieve the price history of a given product at multiple retailers:

SELECT
price_history.datetimeofchange, retailer.name, price_history.price
FROM
product, retailer, retailer_product, price_history
WHERE
product.id = 'b486ed47-4de4-417d-b77b-89819bc728cd'
AND
retailer_product.retailerid = retailer.id
AND
retailer_product.associatedproductid = product.id
AND
price_history.retailer_productid = retailer_product.id

This gives the following results:

2008-03-08 Example Retailer 2 2.3
2008-03-28 Example Retailer 1 1.8
2008-03-30 Example Retailer 2 2.1
2008-04-01 Example Retailer 1 1.43
2008-04-03 Example Retailer 1 1.4

The question(s) I have are how can I:

1 - Get the price of a product at a given retailer at a given date/time
For example, get the price of the product at Retailer 2 on 03/28/2008. Table only contains data for Retailer 1 for this date, the behaviour I want is when there is no data available for the query to find the last data at which there was data from that retailer, and use the price from that point - i.e. so for this example the query should result in 2.3 as the price, given that was the last recorded price change from that retailer (03/08/2008).

2 - Get the average price of a product at a given retailer at a given date/time
In this case we would need to perform (1) across all retailers, then average the results

I'd really appretiate anyone's help on this :)

many thanks in advance,

dg

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-04-24 : 07:13:47

SELECT @Date, r.name, ph.price
FROM retailer r
INNER JOIN retailer_product rp
ON rp.[retailerid]=r.id
INNER JOIN price_history ph
ON ph.[retailer_productid]=rp.id
INNER JOIN (SELECT retailer_productid,MAX(datetimeofchange) as maxdate
FROM price_history 
WHERE datetimeofchange<=@Date
)temp
ON ph.retailer_productid=temp.retailer_productid
AND ph.datetimeofchange=temp.maxdate
WHERE r.name = @Retailer

SELECT p.name, AVG(ph.price)
FROM retailer r
INNER JOIN retailer_product rp
ON rp.[retailerid]=r.id
INNER JOIN product p
ON p.id=rp.[associatedproductid]
INNER JOIN price_history ph
ON ph.[retailer_productid]=rp.id
INNER JOIN (SELECT retailer_productid,MAX(datetimeofchange) as maxdate
FROM price_history 
WHERE datetimeofchange<=@Date
)temp
ON ph.retailer_productid=temp.retailer_productid
AND ph.datetimeofchange=temp.maxdate
GROUP BY p.name

dang12
Starting Member

12 Posts

Posted - 2008-04-24 : 07:39:17

Hi visakh16,

thanks very much for your help - really appretiated! :)

When I am running the following query I'm getting:

Msg 8120, Level 16, State 1, Line 9
Column 'price_history.retailer_productid' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

DECLARE @Date datetime
SELECT @Date = '28/03/2008'

DECLARE @Retailer nvarchar(250)
SELECT @Retailer = 'Example Retailer 2'

SELECT @Date, r.name, ph.price
FROM retailer r
INNER JOIN retailer_product rp
ON rp.[retailerid]=r.id
INNER JOIN price_history ph
ON ph.[retailer_productid]=rp.id
INNER JOIN (SELECT retailer_productid,MAX(datetimeofchange) as maxdate
FROM price_history
WHERE datetimeofchange<=@Date
)temp
ON ph.retailer_productid=temp.retailer_productid
AND ph.datetimeofchange=temp.maxdate
WHERE r.name = @Retailer

Is there something else I'm missing?

thanks again for your help :)

dg

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-04-24 : 07:45:37

Sorry missed a bit while posting

SELECT @Date, r.name, ph.price
FROM retailer r
INNER JOIN retailer_product rp
ON rp.[retailerid]=r.id
INNER JOIN price_history ph
ON ph.[retailer_productid]=rp.id
INNER JOIN (SELECT retailer_productid,MAX(datetimeofchange) as maxdate
FROM price_history 
WHERE datetimeofchange<=@Date
GROUP BY retailer_productid
)temp
ON ph.retailer_productid=temp.retailer_productid
AND ph.datetimeofchange=temp.maxdate
WHERE r.name = @Retailer

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-04-24 : 08:21:50

What was the value passed for @Date? Also it would be worthwhile if you set the date format to your passed on format using SET DATEFORMAT

ie if you are passing date value in ddmmyyyy format use

SET DATEFORMAT dmy on top of the query batch

dang12
Starting Member

12 Posts

Posted - 2008-04-24 : 09:30:57

Thanks again for your help the first query is working correctly now! :)

I'm now running the 2nd (to get the average price across all retailers at a given date/time:

DECLARE @Date datetime
SELECT @Date = '03/30/2008'

DECLARE @ProductId uniqueidentifier
SELECT @ProductId = 'b486ed47-4de4-417d-b77b-89819bc728cd'

SELECT @Date AS 'DateTime', p.name as 'Product', AVG(ph.price) AS 'AveragePrice'
FROM retailer r
INNER JOIN retailer_product rp
ON rp.[retailerid]=r.id
INNER JOIN product p
ON p.[id] = rp.associatedproductid
INNER JOIN price_history ph
ON ph.[retailer_productid]=rp.id
INNER JOIN (SELECT retailer_productid,MAX(datetimeofchange) as maxdate
FROM price_history
WHERE datetimeofchange<=@Date
GROUP BY retailer_productid
)temp
ON ph.retailer_productid=temp.retailer_productid
AND ph.datetimeofchange=temp.maxdate
WHERE
p.Id = @ProductId
GROUP BY p.name

This gives the following result:
2008-03-30 00:00:00.000 Bread 500 grams 1.95

from the data:
2008-03-08 00:00:00.000 Example Retailer 2 2.3
2008-03-28 00:00:00.000 Example Retailer 1 1.8
2008-03-30 00:00:00.000 Example Retailer 2 2.1
2008-04-01 00:00:00.000 Example Retailer 1 1.43
2008-04-03 00:00:00.000 Example Retailer 1 1.4

However as the date passed in in @Date is 03/30/2008 I would expect the average to be (2.3 + 1.8 + 2.1) / 3 = 2.06 - not sure where the 1.95 is coming from...

many thanks in advance,

dg

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-04-24 : 11:14:52

quote:
Originally posted by dang12

Thanks again for your help the first query is working correctly now! :)

I'm now running the 2nd (to get the average price across all retailers at a given date/time:

DECLARE @Date datetime
SELECT @Date = '03/30/2008'

DECLARE @ProductId uniqueidentifier
SELECT @ProductId = 'b486ed47-4de4-417d-b77b-89819bc728cd'

SELECT @Date AS 'DateTime', p.name as 'Product', AVG(ph.price) AS 'AveragePrice'
FROM retailer r
INNER JOIN retailer_product rp
ON rp.[retailerid]=r.id
INNER JOIN product p
ON p.[id] = rp.associatedproductid
INNER JOIN price_history ph
ON ph.[retailer_productid]=rp.id
INNER JOIN (SELECT retailer_productid,MAX(datetimeofchange) as maxdate
FROM price_history
WHERE datetimeofchange<=@Date
GROUP BY retailer_productid
)temp
ON ph.retailer_productid=temp.retailer_productid
AND ph.datetimeofchange=temp.maxdate
WHERE
p.Id = @ProductId
GROUP BY p.name

This gives the following result:
2008-03-30 00:00:00.000 Bread 500 grams 1.95

from the data:
2008-03-08 00:00:00.000 Example Retailer 2 2.3
2008-03-28 00:00:00.000 Example Retailer 1 1.8
2008-03-30 00:00:00.000 Example Retailer 2 2.1
2008-04-01 00:00:00.000 Example Retailer 1 1.43
2008-04-03 00:00:00.000 Example Retailer 1 1.4

However as the date passed in in @Date is 03/30/2008 I would expect the average to be (2.3 + 1.8 + 2.1) / 3 = 2.06 - not sure where the 1.95 is coming from...

many thanks in advance,

dg

I think i got you this time. So you want average of all prices before entered date even if we have more than price record for a retailer. can you change like this and try:-

SELECT p.name, AVG(ph.price)
FROM retailer r
INNER JOIN retailer_product rp
ON rp.[retailerid]=r.id
INNER JOIN product p
ON p.id=rp.[associatedproductid]
INNER JOIN price_history ph
ON ph.[retailer_productid]=rp.id
WHERE ph.datetimeofchange<=@Date
GROUP BY p.name

dang12
Starting Member

12 Posts

Posted - 2008-04-24 : 11:34:11

Not quite :)

The statement needed is:

"Get the average price across all retailers for the given product, at the given date/time"

really appretiate all your help! :)

dg

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-04-24 : 11:38:34

quote:
Originally posted by dang12

Not quite :)

The statement needed is:

"Get the average price across all retailers for the given product, at the given date/time"

really appretiate all your help! :)

dg

given date? thats meaans exactly on that date? or all that heppen on or before?

dang12
Starting Member

12 Posts

Posted - 2008-04-24 : 14:13:55

given date = exactly on that date :)

thanks again,

dg

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-04-24 : 15:00:39

SELECT p.name, AVG(ph.price)
FROM retailer r
INNER JOIN retailer_product rp
ON rp.[retailerid]=r.id
INNER JOIN product p
ON p.id=rp.[associatedproductid]
INNER JOIN price_history ph
ON ph.[retailer_productid]=rp.id
WHERE ph.datetimeofchange=@Date
GROUP BY p.name

dang12
Starting Member

12 Posts

Posted - 2008-04-25 : 04:47:49

Hi,

I think what I need is basically the average of the price column returned by the following query:

DECLARE @Date datetime
SELECT @Date = '04/28/2008'

DECLARE @Retailer nvarchar(250)
SELECT @Retailer = 'Example Retailer 2'

DECLARE @ProductId uniqueidentifier
SELECT @ProductId = 'b486ed47-4de4-417d-b77b-89819bc728cd'

SELECT @Date, r.name, ph.price, p.name
FROM retailer r
INNER JOIN retailer_product rp
ON rp.[retailerid]=r.id
INNER JOIN product p
ON p.[id] = rp.associatedproductid
INNER JOIN price_history ph
ON ph.[retailer_productid]=rp.id
INNER JOIN (SELECT retailer_productid,MAX(datetimeofchange) as maxdate
FROM price_history
WHERE datetimeofchange<=@Date
GROUP BY retailer_productid
)temp
ON ph.retailer_productid=temp.retailer_productid
AND ph.datetimeofchange=temp.maxdate
WHERE
p.Id = @ProductId

gives results:

2008-04-28 00:00:00.000 Example Retailer 2 2.1 Bread 500 grams
2008-04-28 00:00:00.000 Example Retailer 1 1.4 Bread 500 grams

and I need the average of all the prices returned - i.e. (2.1 + 1.4) / 2 = 1.75

Could you show me how to write a query that reads the above query into a temp table then performs an AVG on the price column, the result I'd hope to get would be:

2008-04-28 00:00:00.000 Bread 500 grams 1.75

many thanks

dg

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-04-25 : 07:10:49

SELECT @Date, p.name, avg(ph.price)
FROM retailer r
INNER JOIN retailer_product rp
ON rp.[retailerid]=r.id
INNER JOIN product p
ON p.[id] = rp.associatedproductid
INNER JOIN price_history ph
ON ph.[retailer_productid]=rp.id
INNER JOIN (SELECT retailer_productid,MAX(datetimeofchange) as maxdate
FROM price_history
WHERE datetimeofchange<=@Date
GROUP BY retailer_productid
)temp
ON ph.retailer_productid=temp.retailer_productid
AND ph.datetimeofchange=temp.maxdate
WHERE
p.Id = @ProductId
GROUP BY @Date,p.name

dang12
Starting Member

12 Posts

Posted - 2008-04-25 : 07:14:51

Thanks for your reply - that query gives:

Msg 164, Level 15, State 1, Line 44
Each GROUP BY expression must contain at least one column that is not an outer reference.

many thanks

dg

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-04-25 : 07:33:38

SELECT ph.datetimeofchange, p.name, avg(ph.price)
FROM retailer r
INNER JOIN retailer_product rp
ON rp.[retailerid]=r.id
INNER JOIN product p
ON p.[id] = rp.associatedproductid
INNER JOIN price_history ph
ON ph.[retailer_productid]=rp.id
INNER JOIN (SELECT retailer_productid,MAX(datetimeofchange) as maxdate
FROM price_history
WHERE datetimeofchange<=@Date
GROUP BY retailer_productid
)temp
ON ph.retailer_productid=temp.retailer_productid
AND ph.datetimeofchange=temp.maxdate
WHERE
p.Id = @ProductId
GROUP BY ph.datetimeofchange,p.name

dang12
Starting Member

12 Posts

Posted - 2008-04-25 : 07:43:26

I've slightly modified your query to show the retailer:

SELECT ph.datetimeofchange, p.name, avg(ph.price), r.name
FROM retailer r
INNER JOIN retailer_product rp
ON rp.[retailerid]=r.id
INNER JOIN product p
ON p.[id] = rp.associatedproductid
INNER JOIN price_history ph
ON ph.[retailer_productid]=rp.id
INNER JOIN (SELECT retailer_productid,MAX(datetimeofchange) as maxdate
FROM price_history
WHERE datetimeofchange<=@Date
GROUP BY retailer_productid
)temp
ON ph.retailer_productid=temp.retailer_productid
AND ph.datetimeofchange=temp.maxdate
WHERE
p.Id = @ProductId
GROUP BY ph.datetimeofchange,p.name, r.name

which gives:

2008-03-08 00:00:00.000 Bread 500 grams 2.3 Example Retailer 2
2008-03-28 00:00:00.000 Bread 500 grams 1.8 Example Retailer 1

However when I'm after is one query that takes those results and gives the average price across all retailers for the product specified in @ProductId for the date specified in @Date.

So if SELECT @Date = '03/28/2008' I would expect the query to result in 1 row showing:

2008-03-28 00:00:00.000 Bread 500 grams 2.05

Where 2.05 is calculated from (2.3 + 1.8) / 2 = 2.05

Essentially - how can I get the query to return 1 row that shows the average price across the results from the previous query?

many thanks

dg

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-04-25 : 08:01:34

[code]SELECT r.name,t.name,t.datetimeofchange,t.avgprice
FROM retailer r
INNER JOIN retailer_product rp
ON rp.[retailerid]=r.id
INNER JOIN (
SELECT p.id,ph.datetimeofchange, p.name, avg(ph.price) as avgprice
FROM product p
INNER JOIN price_history ph
ON ph.[retailer_productid]=rp.id
INNER JOIN (SELECT retailer_productid,MAX(datetimeofchange) as maxdate
FROM price_history
WHERE datetimeofchange<=@Date
GROUP BY retailer_productid
)temp
ON ph.retailer_productid=temp.retailer_productid
AND ph.datetimeofchange=temp.maxdate
GROUP BY ph.datetimeofchange,p.name,p.id)t
ON t.[id] = rp.associatedproductid
WHERE
p.Id = @ProductId[/code]

dang12
Starting Member

12 Posts

Posted - 2008-04-25 : 08:40:56

gives:

Msg 4104, Level 16, State 1, Line 46
The multi-part identifier "rp.id" could not be bound.
Msg 4104, Level 16, State 1, Line 46
The multi-part identifier "p.Id" could not be bound.

thanks again for your assistance :)

dg

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources