[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
3
Medium Priority
?
330 Views
Last Modified: 2012-08-17
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
Comment
Question by:T-Virus
3 Comments
 
LVL 12

Accepted Solution

by:
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 

Open in new window

0
 
LVL 1

Assisted Solution

by:maggiec58
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)

Results using your sample data

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

by:T-Virus
ID: 38304906
Thats was quick - thanks !
0

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

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

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question