'Most current price based on effective date

I am using SQL Server Management Studio and want to show the most current price for every day in a date calendar table.

There are two tables: the Calendar table has the date for every day like this:

enter image description here

the ItemPrices table has item number, effective date, and price columns:

enter image description here

I want to join these two tables so I get an output of every day of the year, the most current price based off of the effective date. An example of what I would like is below. I have been trying a cross apply and other things but can't quite get it. Any help would be extremely appreciated. Thanks!

Example: (The .. mean it would continue until the next row.)

enter image description here

sql-server


Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source

Related Questions