• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 332
  • Last Modified:

Map Product and Price by Date

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
T-Virus
Asked:
T-Virus
2 Solutions
 
Habib PourfardSoftware DeveloperCommented:
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
 
maggiec58Commented:
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
 
T-VirusAuthor Commented:
Thats was quick - thanks !
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now