在 MSSQL 日期范围内或最接近日期范围内选择

假设我们有一张桌子

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') 
           ;

productID 是另一个表 Product 的外键

这张表有我们产品的价格。

问题是假设今天的日期是 2018-12-19（2018 年 12 月 19 日）

与默认日期相比，我们如何获得日期（2018-12-19）的当前价格 EndDate 不为空，价格 EndDate 将为空

问题 1 如何根据 EndDate 获得具有所需输出的价格趋势，如下所示

因此在我们的例子中，基于结束日期的最接近的日期是正确的顺序：

   -1- EndDate 2018-12-20 ( 20 Decemeber 2018 )  ProductPriceID : 2   Price : 26
   -2- EndDate 2018-12-27 ( 27 Decemeber 2018 )  ProductPriceID : 1   Price : 23
   -3- EndDate 2018-12-30 ( 30 Decemeber 2018 )  ProductPriceID : 4   Price : 22
   -4- EndDate 2019-1-01 ( 1 January 2019 )      ProductPriceID : 5   Price : 27    
   -5- EndDate NULL                              ProductPriceID : 3   Price : 21

如果范围内没有价格，则默认值为空字段的日期：因此 [EndDate NULL ProductPriceID : 3 Price : 21]

问题 2 是如何根据 EndDate 上面提到的优先级获得当前价格

当前想要的价格是：26

但是如果我这样做

 /****** Script for SelectTopNRows command from SSMS  ******/
SELECT TOP (1000) [ProductPriceId]
      ,[ProductId]
      ,[Price]
      ,[StartDate]
      ,[EndDate]
  FROM [ExcelDumps].[dbo].[ProductPrice] As p
  Where  
   (
    p.[EndDate] >= '2018-12-18' 
    OR
    p.[EndDate] is null 
   )
   order by p.[EndDate] asc

我得到：

-----------------------------------------------------------------------
ProductPriceId  ProductId   Price   StartDate   EndDate
--------------------------------------------------------------------------
3               1           21.0000 2018-12-10  NULL
2               1           26.0000 2018-12-18  2018-12-19 
1               1           23.0000 2018-12-13  2018-12-27
4               1           22.0000 2018-12-28  2018-12-30
5               1           27.0000 2019-01-01  2019-01-18

但是 null 应该在最后

问题3：以当前价格检索整个产品列表：

我试过了什么

/****** Script for SelectTopNRows command from SSMS  ******/

;with ctaCurrentPrice As
(
  -- tried gettign current price for product 
    SELECT *
  FROM [ExcelDumps].[dbo].[ProductPrice] As p
  Where  
   ( 
    p.[EndDate] >= '2018-12-18' 
    OR
    p.[EndDate] is null 
   ) 
)
Select * , 
    (
      Select Top 1 Price From  ctaCurrentPrice where ProductId = pro.[ProductId]
    ) As Price ,
    (
     Select Top 1 StartDate From  ctaCurrentPrice where ProductId = pro.[ProductId]
    ) As StartDate,
    (
     Select Top 1 EndDate From  ctaCurrentPrice where ProductId = pro.[ProductId]
    ) As EndDate
 From Product As pro

得到什么

ProductId 名称 价格 开始日期 结束日期

1       Burger  23.0000 2018-12-13  2018-12-27
2       Coke 2L NULL    NULL        NULL

其中想要的是

ProductId 名称 价格 开始日期 结束日期

1       Burger  26.0000 2018-12-18  2018-12-19 
2       Coke 2L NULL    NULL        NULL

编辑 2：

我找到了解决方案希望

SELECT Top 1 *
  FROM [ExcelDumps].[dbo].[ProductPrice] As p
  Where  
    p.[ProductId] = 1
    AND
   ( 
    p.[EndDate] >= '2018-12-18' 
    OR
    p.[EndDate] is null 
   ) 
   order by case when p.[EndDate] is null then 2 else 1 end, p.[EndDate]  asc