April 24, 2008 at 4:26 am
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:
http://www.boltfile.com/directdownload/db_create_script.sql
Full database backup:
http://www.boltfile.com/directdownload/database.bak
Database diagram:
http://www.boltfile.com/directdownload/diagram_0.pdf
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 22.3
2008-03-28 Example Retailer 11.8
2008-03-30 Example Retailer 22.1
2008-04-01 Example Retailer 11.43
2008-04-03 Example Retailer 11.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
April 24, 2008 at 5:26 am
you can research the AVG and MAX aggregate functions. There are plenty of samples in BOL.
April 24, 2008 at 6:58 am
You can use something like this for your first question:
declare @Date datetime
select @Date = '3/28/08'
;with MostRecent (RetailerID, PriceDate) as
(select id, max(date)
from dbo.pricehistory
where date <= @date
group by id)
select id, price
from dbo.PriceHistory
inner join MostRecent
on id = retailerid
and date = pricedate
The CTE (which can also be done as a derived table in the From clause), will find the most recent date that is on or before the selected date, and the join will limit the rows returned by the outer query.
One thing to watch out for in queries like this is that DateTime has time in it. So, if you want prices set on 3/28/08, and they were set at 3 PM, you have to compensate for the fact that "3/28/08" means midnight to SQL. So, if you have that kind of situation, you'll want to add one day to the date that's put in (if it doesn't have a time on it), and then use less-than, instead of less-than-or-equal-to.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 25, 2008 at 4:52 am
If possible I would go about adding a new column with EndDate which you would update every time there is a new record inserted (typical OLAP approach). It's better for performance then always reevaluating all EndDates dynamically.
Regards,
Hanslindgren
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply