Not sure i understand you correctly...
Seems you want to obtain OfferPrice, but when it's null, then you want to return RegularPrice. So, you have to use
COALESCE() function[
^].
SELECT COALESCE(OfferPrice, RegularPrice) AS ActualPrice
FROM Products
As to your needs, you have to use
left join[
^].
Check this:
DECLARE @tmp TABLE(SKU INT IDENTITY(1,1) NOT NULL, RegularPrice FLOAT, OfferStartDate DATETIME, OfferEndDate DATETIME, OfferPrice FLOAT)
DECLARE @d DATETIME = GETDATE()
INSERT INTO @tmp(RegularPrice, OfferStartDate, OfferEndDate, OfferPrice)
VALUES(100, '2017-01-01 00:00', '2017-12-31 23:59', 90),
(200, '2017-02-01 00:00', '2017-10-30 23:59', 190),
(300, '2017-03-01 00:00', '2017-10-15 23:59', 270),
(420, '2017-04-01 00:00', '2017-09-30 23:59', 390),
(280, '2017-05-01 00:00', '2017-11-30 23:59', 250)
SELECT A.SKU, COALESCE(B.OfferPrice, A.RegularPrice) AS CurrentPrice
FROM @tmp AS A
LEFT JOIN (
SELECT *
FROM @tmp
WHERE OfferStartDate<= @d AND OfferEndDate>=@d
) AS B ON A.SKU = B.SKU
Result:
SKU CurrentPrice
1 90
2 190
3 300 --regular price, an offer expired
4 420 --regular price, an offer expired
5 250
For further details, please see:
Deciding between COALESCE and ISNULL in SQL Server[
^]
Visual Representation of SQL Joins[
^]