Using t-sql to lookup value based on dates in other table

Question

I have the following challenge. I have 2 tables. First table contains changes in values of bikes, at a certain moment (i.e. price catalogue). This means a certain price for a product is valid untl there is a new price within the table.

Product |   RowNr | Year |  Month | Value
------------------------------------------
Bike1   |    1    | 2009 |    8   |  100
Bike1   |    2    | 2010 |    2   |  400
Bike1   |    3    | 2011 |    4   |  300
Bike1   |    4    | 2012 |    9   |  100
Bike1   |    5    | 2013 |    2   |  500
Bike1   |    6    | 2013 |    5   |  200
Bike2   |    1    | 2013 |    1   | 5000
Bike2   |    2    | 2013 |    2   | 4000
Bike2   |    3    | 2014 |    6   | 2000
Bike2   |    4    | 2014 |   10   | 4000

The second table contains dates for which I would like to determine the value of a bike (based on the information in table 1).

Product | Date       | Value
-------------------------
Bike1   |  3/01/2008 |  ?
Bike1   | 04/30/2011 |  ?
Bike1   |  5/08/2009 |  ?
Bike1   | 10/10/2012 |  ?
Bike1   |  7/01/2014 |  ?

So line 1 and 3 should get value "400", line 2 "300", line 4 "100" and line 5 "200" etc.

Does anyone know how this can be achieved in T-SQL? I've already partitioned the first table, but could use some advice on the next steps.

Many thanks,

There are functions that let you get day of month such as `DATEPART(m, p2.Date)` or `MONTH(p2.Date)` so you can `inner join Products2 p2 on MONTH(p2.Date) = p1.Month and YEAR(p2.Date) = p1.Year` — AaronLS, Aug 15 '14 at 21:42
See cross apply in sql. It enables you to use sub query with parameters. You than would have to use top 1 to get desired results. As a note you should have dates in both tables for performance and comprehension. — Max, Aug 15 '14 at 21:47

score 2 · Accepted Answer · edited May 23 '17 at 10:26

You could do something like this, which will retrieve the most recent price catalogue value for the product, using the price that is less than or equal to the product table date.

SELECT p.product
    , p.date
    , valueAsOfDate = 
    (   SELECT TOP 1 c.value
        FROM priceCatalogue c
        WHERE c.product = p.product
            AND convert(date, 
                    convert(varchar(4), c.year) + '-' 
                    + convert(varchar(2), c.month)  
                    + '-1' 
                ) <= p.date
        --this order by will ensure that the most recent price is used
        ORDER BY c.year desc, c.month desc 
    )
FROM product p

This table structure is not ideal... you would be better off with an "AsOfDate" column in your priceCatalogue table, so that you do not have to cast the values in the priceCatalogue table as a date in order to compare. If this is new development, change the priceCatalogue table to have an asOfDate column that is a date data type. If this is an existing table that is populated from another data source, then you could look at adding a persisted computed column to the table. http://msdn.microsoft.com/en-us/library/ms188300.aspx

With asOfDate column on the productCatalogue table, you have a SARG-able query (What makes a SQL statement sargable? ) that can take advantage of indexes.

SELECT p.product
    , p.date
    , valueAsOfDate = 
    (   SELECT TOP 1 c.value
        FROM priceCatalogue c
        WHERE c.product = p.product
            AND c.asOfDate <= p.date
        --this order by will ensure that the most recent price is used
        ORDER BY c.year desc, c.month desc 
    )
FROM product p

That's working great! I also changed the columns to date type, as you recommended. Thank you very much for the help BateTech. — Xivan, Aug 16 '14 at 08:36

score 0 · Answer 2 · answered Aug 15 '14 at 21:44

just use the YEAR() and MONTH() functions to take those parts of the date, and join them on your versioned table.

select
from product p
inner join productVersion pv 
  on p.product = pv.product
  and Year(p.Date) = pv.Year
  and Month(p.Date) = pv.Month

score 0 · Answer 3 · answered Aug 16 '14 at 10:12

Xivan, I think for both your line 1 and 3 it should get value "100" as 3/1/2008 and 5/8/2009 is less then 8/xx/2009. As your table structure is not ideal, you have to create some computed columns for calculation.Hope the below query will work for you.

WITH cte
AS (
SELECT p.*
    ,(
        SELECT min(p1.rownr) rownr
        FROM product p1
        WHERE p1.rownr > p.rownr
            AND p.product = p1.product
        GROUP BY p1.product
        ) AS nrownr
    ,(
        SELECT max(p1.rownr) rownr
        FROM product p1
        WHERE p1.rownr < p.rownr
            AND p.product = p1.product
        GROUP BY p1.product
        ) AS prownr
FROM product p
)
        SELECT pd.*
        ,c.value
    FROM product_date pd
    LEFT JOIN cte c ON pd.product = c.product
    LEFT JOIN product p ON c.product = p.product
        AND c.nrownr = p.rownr
    LEFT JOIN product p1 ON c.product = p1.product
        AND c.prownr = p1.rownr
    WHERE (pd.DATE !> convert(DATE, convert(VARCHAR(4), (
                        CASE WHEN p.year IS NOT NULL THEN p.year ELSE 9999 END)) + '-'         + convert(VARCHAR(2), (
                        CASE WHEN p.month IS NOT NULL THEN p.month ELSE 12 END)) + '-' + '1')
            AND 
pd.DATE !< convert(DATE, convert(VARCHAR(4), c.year) + '-' + convert(VARCHAR(2), c.month) + '-' + '1'))
        OR 
(pd.DATE !> convert(DATE, convert(VARCHAR(4), (
                        CASE WHEN p1.year IS NOT NULL THEN NULL ELSE 2009 END)) + '-' +
convert(VARCHAR(2), ( CASE WHEN p1.month IS NOT NULL THEN NULL ELSE 8 END)) + '-' +'1')
            )

http://sqlfiddle.com/#!3/22c1d/2

Using t-sql to lookup value based on dates in other table

3 Answers3