CODEWITHSUNDEEP
sqlsql-server
stzvggmd
stzvggmd

Reputation: 134

How to select value in a table based on date range that may change

I can't seem to find a question that specifically addresses the requirements of what I'm trying to do here, but apologies if this isn't the case.

I have an example table:

    type|start_date|end_date  |cost|

    bananas|2019-01-01|2019-01-31|100
    bananas|2019-02-01|2019-02-28|95
    juice  |null      |null      |55

And so on. The point of the table is that the 'food' or whatever else, can be a different price based on a different time range, however there are some items e.g. juice, that don't get affected when the date changes (i.e they stay constant).

In another table, I want to lookup a value based on the date range in the table above.

For example:

customerId|transaction_date|item   |quantity|

abc123    |2019-01-25      |bananas|4
abc126    |2019-02-06      |bananas|4
abc128    |2019-02-09      |juice  |1

So the first two customers bought bananas, but one in January and one in February. Then another customer bought juice. How can I return the correct corresponding cost based on my other table.

I have tried to do where date is BETWEEN the two, but this alienates any other dates that don't exist in the pricing table. I've also tried to use CASE to approach this, but to no avail.

What would be the best approach to this?

I would expect the result to yield a joined table with the following:

customerId|transaction_date|item   |quantity|cost|

abc123    |2019-01-25      |bananas|4       |100
abc126    |2019-02-06      |bananas|4       |95
abc128    |2019-02-09      |juice  |1       |55

Where cost column is joined by the criteria above?

Upvotes: 3

Views: 78

Answers (3)

Nick Allan
Nick Allan

Reputation: 406

You could use an outer apply:

  SELECT p.*,
  c.cost
  FROM Purchases AS p
      OUTER APPLY (
          SELECT TOP 1 c.cost
              FROM Product_Costs AS c
              WHERE p.transaction_date BETWEEN c.start_date AND c.end_date
                  AND c.type = p.Item
      ) AS c;

I've created a Sql Fiddle using your example data: http://sqlfiddle.com/#!18/e366b/2/0

Upvotes: 1

Matt
Matt

Reputation: 38

You could use ISNULL on the left join.

SELECT P.*, C.cost
FROM Purchases P 
LEFT JOIN Product_Costs C ON P.item = C.type 
      AND P.transaction_date >= ISNULL(C.start_date, P.transaction_date) 
      AND P.transaction_date <= ISNULL(C.end_date, P.transaction_date);

Modified Nick's fiddle: http://sqlfiddle.com/#!18/e366b/6

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1269953

You can use a correlated subquery:

select t2.*,
       (select t1.cost
        from table1 t1
        where t1.type = t2.type and
              t2.transaction_date >= t1.start_date and
              t2.transaction_date <= t1.end_date 
       ) as cost
from table2 t2;

Or, you can use a left join:

select t2.*, t1.cost
from table2 t2 left join
     table1 t1
     on t1.type = t2.type and
        t2.transaction_date >= t1.start_date and
        t2.transaction_date <= t1.end_date ;

EDIT:

You can handle NULL values with OR:

select t2.*, t1.cost
from table2 t2 left join
     table1 t1
     on t1.type = t2.type and
        (t2.transaction_date >= t1.start_date or
         t1.start_date is null
        ) and
        (t2.transaction_date <= t1.end_date or
         t1.end_date is null
        );

Upvotes: 1

Related Questions