SQL Server SQL Left Join on Criteria between dates? [duplicate]

pdsfdshx 于 2023-10-15 发布在其他

关注(0)|答案(2)|浏览(97)

This question already has answers here:

Join two tables date from first in range of dates from second (3 answers)
Closed 4 years ago.

I am writing a report to determine the Average Selling Price of Items compared to the list price for those items historically.

I can get all the actual sales prices from the Value Entry Table.

The system 'List Price' is in the Sales Price table.

The tricky part is the prices change so the list price for a given item is different depending on the date the item was sold. So to accurately report this going back I need to identify the list price as it was on the date sold.

The Sales Price table fortunately has a Start Date and End Date for the price.

So I'm trying to figure how to join the Sales Price Table onto the Value Entry Table where the Date the item was sold falls between the Start Date and End Date for a line in the Sales Price Table

EX

Item No    List Price     Start Date    End Date
1001       $1.00          01-01-18      02-28-18
1001       $1.25          03-01-18      05-31-18
1001       $1.50          06-01-18      08-31-18

Item 1001 Sold on 01-21-18 how do I get the $1.00 price to join on correctly?

sql-server

来源：https://stackoverflow.com/questions/52856814/sql-left-join-on-criteria-between-dates

2条答案

按热度按时间

uidvcgyl1#

An outer join should do:

select
    o.*,
    p.list_price
  from orders o
  left join sales_price p on p.item_no = o.item_no
    and o.sales_date between p.start_date and p.end_date

赞(0）回复(0）举报 2023-10-15

ovfsdjhp2#

You can use below query:

SELECT *
FROM TABLE t
WHERE '01-21-18' >= t.startdate
    AND '01-21-18' <= t.enddate
    AND t.item_no = 1001

赞(0）回复(0）举报 2023-10-15

我来回答

SQL Server SQL Left Join on Criteria between dates? [duplicate]

2条答案

相关问题

热门标签

最新问答