SQL Server 2017

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE [dbo].[Product](
    [ProductId] [int] NOT NULL,
    [Name] [varchar](50) NOT NULL
);

CREATE TABLE [ProductPrice]( 
    [ProductPriceId] [int] NOT NULL,
    [ProductId] [int] NOT NULL,
    [Price] [decimal](19, 4) NOT NULL,
    [StartDate] [datetime2](7) NOT NULL,
    [EndDate] [datetime2](7) NULL
);

INSERT INTO ProductPrice
    ([ProductPriceId] ,[ProductId], [Price], [StartDate], [EndDate])
VALUES 
    ( 1 ,1 , 23 , '2018-12-13' , '2018-12-27'), 
    ( 2 ,1 , 26 , '2018-12-18' , '2018-12-20'), 
    ( 3 ,1 , 21 , '2018-12-10' , null), 
    ( 4 ,1 , 22 , '2018-12-28' , '2018-12-30'), 
    ( 5 ,1 , 27 , '2019-1-01' , '2019-01-18')  
;


INSERT INTO [Product]
           ([ProductId]
           ,[Name])
     VALUES
           (1 ,'Burger') ,
           (2 ,'Coke 2L') 
           ;
7 rows affected

SELECT
    ProductPriceId,
    ProductId,
    Price,
    StartDate,
    EndDate
FROM
    ProductPrice
ORDER BY
    ProductId,
    StartDate


ProductPriceId
ProductId
Price
StartDate
EndDate


3
1
21.0000
2018-12-10 00:00:00.0000000
null

1
1
23.0000
2018-12-13 00:00:00.0000000
2018-12-27 00:00:00.0000000

2
1
26.0000
2018-12-18 00:00:00.0000000
2018-12-20 00:00:00.0000000

4
1
22.0000
2018-12-28 00:00:00.0000000
2018-12-30 00:00:00.0000000

5
1
27.0000
2019-01-01 00:00:00.0000000
2019-01-18 00:00:00.0000000


SELECT
    ProductPriceId,
    ProductId,
    Price,
    StartDate,
    EndDate
FROM
    ProductPrice
ORDER BY
    ProductId,
    CASE WHEN EndDate IS NULL THEN 1 ELSE 0 END,
    EndDate;


ProductPriceId
ProductId
Price
StartDate
EndDate


2
1
26.0000
2018-12-18 00:00:00.0000000
2018-12-20 00:00:00.0000000

1
1
23.0000
2018-12-13 00:00:00.0000000
2018-12-27 00:00:00.0000000

4
1
22.0000
2018-12-28 00:00:00.0000000
2018-12-30 00:00:00.0000000

5
1
27.0000
2019-01-01 00:00:00.0000000
2019-01-18 00:00:00.0000000

3
1
21.0000
2018-12-10 00:00:00.0000000
null


SELECT
    ProductPriceId,
    ProductId,
    Price,
    StartDate,
    EndDate
FROM
    ProductPrice
ORDER BY
    ProductId,
    COALESCE(EndDate, '99991231');


ProductPriceId
ProductId
Price
StartDate
EndDate


2
1
26.0000
2018-12-18 00:00:00.0000000
2018-12-20 00:00:00.0000000

1
1
23.0000
2018-12-13 00:00:00.0000000
2018-12-27 00:00:00.0000000

4
1
22.0000
2018-12-28 00:00:00.0000000
2018-12-30 00:00:00.0000000

5
1
27.0000
2019-01-01 00:00:00.0000000
2019-01-18 00:00:00.0000000

3
1
21.0000
2018-12-10 00:00:00.0000000
null


DECLARE @PriceDate datetime = '20181219';

SELECT
    p.ProductId,
    p.Name,
    pl.Price,
    pl.StartDate,
    pl.EndDate
FROM 
    Product p
OUTER APPLY (SELECT TOP 1
                 ProductPriceId,
                 ProductId,
                 Price,
                 StartDate,
                 EndDate
             FROM
                 ProductPrice
             WHERE
                 ProductId = p.ProductId
                 AND EndDate > @PriceDate 
             ORDER BY
                 ProductId,
                 COALESCE(EndDate, '99991231')) pl


ProductId
Name
Price
StartDate
EndDate


1
Burger
26.0000
2018-12-18 00:00:00.0000000
2018-12-20 00:00:00.0000000

2
Coke 2L
null
null
null

ProductPriceId	ProductId	Price	StartDate	EndDate
3	1	21.0000	2018-12-10 00:00:00.0000000	null
1	1	23.0000	2018-12-13 00:00:00.0000000	2018-12-27 00:00:00.0000000
2	1	26.0000	2018-12-18 00:00:00.0000000	2018-12-20 00:00:00.0000000
4	1	22.0000	2018-12-28 00:00:00.0000000	2018-12-30 00:00:00.0000000
5	1	27.0000	2019-01-01 00:00:00.0000000	2019-01-18 00:00:00.0000000

ProductId	Name	Price	StartDate	EndDate
1	Burger	26.0000	2018-12-18 00:00:00.0000000	2018-12-20 00:00:00.0000000
2	Coke 2L	null	null	null