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

pdsfdshx  于 2023-10-15  发布在  其他
关注(0)|答案(2)|浏览(96)

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?

uidvcgyl

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
ovfsdjhp

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

相关问题