CODEWITHSUNDEEP
sqlsql-serverdatecasepartition
Xivan
Xivan

Reputation: 5

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

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,

Upvotes: 0

Views: 2204

Answers (3)

ram_sql
ram_sql

Reputation: 404

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

Upvotes: 0

BateTech
BateTech

Reputation: 6496

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

Upvotes: 2

Cam Bruce
Cam Bruce

Reputation: 5689

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

Upvotes: 0

Related Questions