SELECT effective price based on dates

Question

I am currently working on a supermarket database design where I have to have retrieve the effective price of the product based on the dates.

For example, price of Product A is $9.50 and it will be $10 effective from 1 July. However, this month is still June, so the price of Product A will still be the original price which is $9.50

The problem that I am facing is I am not able to select only one record from each product but all of them.

I am currently using SQL Server.

Price Table:

product_id  | product_name  | price   | effective_date 
=======================================================
1           | Product A     | 8.00    | 1-5-2020
1           | Product A     | 9.50    | 1-6-2020
1           | Product A     | 10.00   | 1-7-2020
2           | Product B     | 4.00    | 5-6-2020
2           | Product B     | 4.50    | 5-7-2020

Codes:

SELECT Product.id, Product.product_name,Price_Table.price, Price_Table.effective_date,
(select top 1 price from Price_Table 
where Price_Table.product_id=Product.id and getdate()>= effective_date 
order by effective_date desc) 'Latest Price'
FROM Price_Table, Product
WHERE 
Product.id=Price_Table.product_id

Result:

id    | product_name  | price   | effective_date | Latest Price 
----------------------------------------------------------------
1     | Product A     | 8.00    | 1-5-2020       | 9.50 
1     | Product A     | 9.50    | 1-6-2020       | 9.50 
1     | Product A     | 10.00   | 1-7-2020       | 9.50 
2     | Product B     | 4.00    | 1-6-2020       | 4.00
2     | Product B     | 4.50    | 1-7-2020       | 4.00

Expected Result:

id    | product_name  | price   | effective_date | Latest Price 
----------------------------------------------------------------
1     | Product A     | 9.50    | 1-6-2020       | 9.50 
2     | Product B     | 4.00    | 1-6-2020       | 4.00

Based on the result, it shows all the records which is wrong. Expected result is the one result I hope to get.

Can anyone help me with this? Thanks!

Does this answer your question? [Selecting a Record With MAX Value](https://stackoverflow.com/questions/8387587/selecting-a-record-with-max-value) — Fabio, Jun 11 '20 at 02:15
This is common scenario, and solution is always a subquery or `CROSS APPLY` suggested in the answer which is a type of sub query. — Fabio, Jun 11 '20 at 02:22
@Fabio but still `CROSS APPLY` will not able to retrieve the price of the product (returns NULL) if the product does not appear in the price table. (in the case if original price of the product is stored in product table while the price table is a table to store updated price of the product and mainly to keep track of the price history) — wink, Jun 11 '20 at 04:17

Gordon Linoff · Accepted Answer · 2020-06-11T02:33:53.593

0

You can use cross apply:

SELECT p.*, pt.*
FROM Product p OUTER APPLY
     (SELECT TOP (1) pt.*
      FROM Price_Table pt
      WHERE p.id = pt.product_id AND pt.effective_date <= GETDATE()
      ORDER BY pt.effective_date DESC
     ) pt;

edited Jun 11 '20 at 02:33

answered Jun 11 '20 at 02:17

Gordon Linoff

1,242,037
58
646
786

Wow! This actually works! But from what I understand from `cross apply`, it is similar to `inner join`. Is there a way that I can retrieve all the products from product table and also price table ( in the case that not all products are in the price table) ? – wink Jun 11 '20 at 02:31
@winkie . . . That is just an `outer apply` instead of `cross apply`. – Gordon Linoff Jun 11 '20 at 02:34
I see! Is it possible to use `outer apply` and `left join` in the same sql query statement? as I wish to combine [CASE expression returns wrong values during percentage calculation](https://stackoverflow.com/questions/62274219/case-expression-returns-wrong-values-during-percentage-calculation/62274929#62274929) with this statement. – wink Jun 11 '20 at 02:41
@winkie . . . Yes. These are all operators in the `FROM` clause and can be combined just like arithmetic operations (`+`, `*`, `-`, etc.) with numbers. – Gordon Linoff Jun 11 '20 at 10:36

SELECT effective price based on dates

1 Answers1