在 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 

問題 1 如何根據 EndDate 獲得具有所需輸出的價格趨勢，如下所示

只需使用 COALESCE 將 NULL 日期值轉換為 31/12/9999，這是日期時間列允許的最大值。

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

產品價格 ID | 產品編號 | 價格 | 開始日期 | 結束日期 
-------------: | --------: | :------ | :------------------ | :------------------
 2 | 1 | 26.0000 | 18/12/2018 00:00:00 | 20/12/2018 00:00:00
 1 | 1 | 23.0000 | 13/12/2018 00:00:00 | 27/12/2018 00:00:00
 4 | 1 | 22.0000 | 28/12/2018 00:00:00 | 30/12/2018 00:00:00
 5 | 1 | 27.0000 | 01/01/2019 00:00:00 | 18/01/2019 00:00:00
 3 | 1 | 21.0000 | 2018 年 10 月 12 日 00:00:00 | *空值*               

問題 2 是如何根據 EndDate 上面提到的優先級獲得目前價格

然後使用 OUTER APPLY 連接來獲取匹配條件的第一行：

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;

產品編號 | 姓名 | 價格 | 開始日期 | 結束日期 
--------: | :------ | :------ | :------------------ | :------------------
 1 | 漢堡 | 26.0000 | 18/12/2018 00:00:00 | 20/12/2018 00:00:00
 2 | 可樂2L | *空* | *空* | *空值*               

db<>在這裡擺弄

引用自：https://dba.stackexchange.com/questions/226679