[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
Solved

# Map Product and Price by Date

Posted on 2012-08-17
Medium Priority
330 Views
Hello,

give me some hints please on how to get this thing working.

I have 2 Tables
Table Price contains the the Price for the product and productid - this looks likte the following example:

ID ActiveDate Price
1   20120101  9
1   20120220  11
1   20120401  10
1   20120601  12
2   20120101   9
2   20120201   8
2   20120301   9

Table Produkt

ID  ActiveDate
1    20120130
1    20120219
1    20120430
2    20120530

of course there is a primary key for each table...

So the Price is changing over time aswell as the product.
I need to map price and product so the activeprice needs to be within the activeproduct daterange.

Sample:
ID Price.ActiveDate  Product.Activedate  Price.Price
1   20120401            20120430                10

how do i do that?
Thanks for the help!
0
Question by:T-Virus

LVL 12

Accepted Solution

Habib Pourfard earned 1000 total points
ID: 38304709
hope this helps:
``````SELECT  ID,
ActiveDate ProductActiveDate,
( SELECT TOP 1
ActiveDate
FROM      Price
WHERE     Product.Activedate >= Price.ActiveDate
AND dbo.Product.ID = Price.ID
ORDER BY  Price.ActiveDate DESC
) PriceActiveDate,
( SELECT TOP 1
Price
FROM      Price
WHERE     Product.Activedate >= Price.ActiveDate
AND dbo.Product.ID = Price.ID
ORDER BY  Price.ActiveDate DESC
) Price
FROM    Product
ORDER BY ID,  ProductActiveDate DESC
``````
0

LVL 1

Assisted Solution

maggiec58 earned 1000 total points
ID: 38304771
This isn't pretty, but it works.
select product.pid, product.activeDate as "product date", price,
(select MAX(activedate) from Price where Activedate <= product.activeDate and price.pid = product.pid) as "Price Date"
from product, Price where product.pid = price.pid and price.Activedate = (select MAX(activedate) from Price where Activedate <= product.activeDate and price.pid = product.pid)

pid      product date      price      Price Date
1      2012-01-30 00:00:00      9      2012-01-01 00:00:00
1      2012-02-19 00:00:00      9      2012-01-01 00:00:00
1      2012-04-30 00:00:00      10      2012-04-01 00:00:00
2      2012-05-30 00:00:00      9      2012-03-01 00:00:00
0

Author Closing Comment

ID: 38304906
Thats was quick - thanks !
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
###### Suggested Courses
Course of the Month19 days, 5 hours left to enroll