January 30, 2013 at 2:52 pm
This problem has probably been covered a few times, but I'm not finding a solution to my problem.
Currently... I am doing something like this to find the effective price of a product <= date of choice.
select products.product_id, products.product_name, product_prices.product_price, product_prices.effective_date
fromproducts
join (selecta.*
from product_prices a
join (select product_id, max(effective_date) effective_date
from product_prices
where effective_date <= '2012/07/30'
group by product_id
) b on a.product_id = b.product_id and a.effective_date = b.effective_date
) product_prices on products.product_id = product_prices.product_id
Can anybody offer me any insight on how I could output the products price over time?
I.E.,
Product Name | Product Price | Sale Date | Price Effective Date
A | 6.99 | January 2012 | January 2012
A | 6.99 | February 2012 | January 2012
A | 6.99 | March 2012 | January 2012
A | 6.99 | April 2012 | January 2012
A | 12.99 | May 2012 | May 2012
A | 12.99 | June 2012 | May 2012
A | 12.99 | July 2012 | May 2012
A | 18.99 | August 2012 | August 2012
A | 18.99 | September 2012 | August 2012
Now, the products table has many products... and the product prices table has at least one entry for each product with an effective date for for that product. Some products have multiple entries as products change prices monthly for them.
My query above will show me products for a particular day and the price for them, but how would I write a query to show me all the products and the prices and their effective price as the time of sale (like the output I noted above)?
January 30, 2013 at 3:27 pm
Can you script your schema and some sample data for product_prices?
This seems like a typical logging table selection, but I want to make sure I understand your data layout precisely.
Edit: I couldn't quite figure out what you mean by this:
My query above will show me products for a particular day and the price for them, but how would I write a query to show me all the products and the prices and their effective price as the time of sale (like the output I noted above)?
I'm not sure I understand this request. Time of sale would be for a specific datetime, or you could report all changes for a particular product and/or a price at <time period> type of report. Now, pulling all products at a particular time is no more difficult really than what was done above just it needs to be done for all products instead of a particular one.
I'm not really sure what you're trying to get out of it as your final goal.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
January 30, 2013 at 4:29 pm
I'll script it out tomorrow when I am back at that computer... but essentially what I am looking for is.
I have a bunch of products, all of which change prices by effective date during the year.
I would like to see a list of all products and the prices they sold at during the year.
Evil Kraig F (1/30/2013)
Can you script your schema and some sample data for product_prices?Edit: I couldn't quite figure out what you mean by this:
My query above will show me products for a particular day and the price for them, but how would I write a query to show me all the products and the prices and their effective price as the time of sale (like the output I noted above)?
I'm not sure I understand this request. Time of sale would be for a specific datetime, or you could report all changes for a particular product and/or a price at <time period> type of report. Now, pulling all products at a particular time is no more difficult really than what was done above just it needs to be done for all products instead of a particular one.
I'm not really sure what you're trying to get out of it as your final goal.
When I use the code that has the join on itself, it brings back the price where the max(effective_date) <= August 2012 (or July 30, 2012 in the example). That works in that scenario.
I guess another way to say what I am trying to accomplish is, I would like to see a price list of all the products per month to see change in pricing over the course of a year or four years or eight years or beginning of time to now.
February 1, 2013 at 7:32 am
CREATE TABLE [dbo].[tblProduct](
[product_id] [nvarchar](50) NOT NULL,
[product_name] [nvarchar](50) NULL,
[dtmStartDate] [date] NULL,
[dtmEndDate] [date] NULL,
CONSTRAINT [PK_tblProduct] PRIMARY KEY CLUSTERED
(
[product_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[tblProductPrices](
[price_id] [nvarchar](50) NOT NULL,
[product_id] [nvarchar](50) NOT NULL,
[effective_date] [date] NOT NULL,
[price] [decimal](18, 0) NULL,
CONSTRAINT [PK_tblProductPrices] PRIMARY KEY CLUSTERED
(
[price_id] ASC,
[product_id] ASC,
[effective_date] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
February 1, 2013 at 7:34 am
tblProductPrice Data
price_id | product_id | effective_date | price
GUID | A | 6.99 | January 2012
GUID | A | 12.99 | May 2012
GUID | A | 18.99 | August 2012
GUID | B | 34.99 | January 2012
GUID | B | 37.99 | March 2012
GUID | B | 19.99 | April 2012
GUID | B | 37.99 | May 2012
GUID | C | 1.99 | January 2012
GUID | D | 99.99 | January 2012
February 1, 2013 at 9:04 am
Your DDL is great. It would help, though, if you'd provide some sample inserts. The "sample data" you've provided isn't structured to insert and doesn't match the datatypes you've got defined for some of the columns.
See the article http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url] about best practices for getting help on the forums. You're a good way there but we need a bit more to provide the most effective help.
February 1, 2013 at 9:34 am
Right... I was creating dummy tables to eliminate all the extra columns/rows, but deleted them before I output some of the data. I'll run it up again to get some output data in the right format.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply