[小菜一碟] SQL Server 帶入任意的起迄日期,產生起迄日期之間的每個日期。

假定老闆提了一個需求,他想要看每個分店每天的銷售額,如果當天沒有銷售額的分店則銷售額顯示為 0,這個需求我們從訂單資料庫裡面彙總出來就可以呈現了,但是它有一個點在於萬一某個分店在某天沒有訂單,那麼該分店在訂單資料庫是沒有資料的,則該分店的該天銷售額必須標為 0,類似這樣的需求經常在我的程式設計生涯中出現。

這樣的需求的關鍵點在於最終彙總呈現出來的結果,必須要每個日期都有,而資料庫中又不是都有每個日期的資料,因此我們必須產生起迄日期之間的每個日期,然後以這個結果去做 Outer Join,這個 SQL 語法我一直沒有寫得很好,不過就在這幾天在 Google 其他問題的時候,無意中看到一個搜尋結果 better way to generate months/year table,它解決產生日期的方法比我的好太多了,我就把它記下來了。

文中用 [master].dbo.spt_values 這個系統資料表來協助我們產生日期,語法如下:

DECLARE @BeginningDate DATE = '2019-04-01'
DECLARE @EndDate DATE = '2019-05-01'

SELECT TOP (DATEDIFF(DAY, @BeginningDate, @EndDate))
    DATEADD(DAY, sv.number, @BeginningDate) AS [Date]
FROM [master].dbo.spt_values sv WITH (NOLOCK)
WHERE sv.[type] = 'P'
ORDER BY sv.number

[master].dbo.spt_values 這個資料表一個特殊的資料表,官方沒有文件在介紹這個資料表,我們只能從部落格或論壇當中去找到有人在介紹它,這是我找到看起來比較完整的解釋,不過現在大都拿它裡面 0~2047 的序數資料來使用。

因為缺乏鞏固的地位,所以在未來某個 SQL Server 版本會被刪除也說不定,因此我們除了用 [master].dbo.spt_values 系統資料表之外,我們也可以挑選一個資料筆數比預期要產生的天數還要大的資料表,哪個資料表都無所謂,搭配 ROW_NUMBER() 函數也可以產生日期。

DECLARE @BeginningDate DATE = '2019-04-01'
DECLARE @EndDate DATE = '2019-05-01'

SELECT TOP (DATEDIFF(DAY, @BeginningDate, @EndDate))
    DATEADD(DAY, ROW_NUMBER() OVER (ORDER BY df.Id) - 1, @BeginningDate) AS [Date]
FROM DogFood df WITH (NOLOCK)

這樣子的寫法,不只日期,年或月也可以。

最後只要將分店資料表跟這個日期結果做 CROSS JOIN,就可以產生每個分店的每個日期的結果,再來就看要拿這個結果去 JOIN 什麼資料來彙總數據。

C# 指南 ASP.NET 教學 ASP.NET MVC 指引
Azure SQL Database 教學 SQL Server 教學 Xamarin.Forms 教學