当未找到特定日期的记录时，返回结果集中的行

我创建了一个查询来返回以下输出。

Date          Day      Sale Qty     Purchase Qty      Transfer Qty
------------------------------------------------------------------
05/04/2015    1           11             0                0
07/04/2015    3            0            16                0
08/04/2015    4           12            14               17
11/04/2015    7            1             2                0

我当前的查询如下。

(select T1.Date,T1.Day,T1.SaleQty,0 as PurchaseQty,0 as TransferQty from SaleTable T1)
union all
(select T2.Date,T2.Day,0 as SaleQty,T2.PurchaseQty,0 as TransferQty from PurchaseTable T2)
union all
(select T3.Date,T3.Day,0 as SaleQty,0 as PurchaseQty,T3.TransferQty from TransferTable T3)

所需的输出采用以下格式

Date          Day      Sale Qty     Purchase Qty      Transfer Qty
------------------------------------------------------------------
05/04/2015    1           11             0                0
06/04/2015    2            0             0                0
07/04/2015    3            0            16                0
08/04/2015    4           12            14               17
09/04/2015    5            0             0                0
10/04/2015    6            0             0                0
11/04/2015    7            1             2                0

当没有为该日期输出返回结果集时，我应该如何编写查询以返回包含日期和日期的行。

您需要一个表作为缺失日期和天数的查找表，以覆盖查询结果中的日期范围。您可以像这样创建一个：

-- add a temp table for your sample data
CREATE TABLE #Results
    ([Date] datetime, [Day] int, [Sale Qty] int, [Purchase Qty] int, [Transfer Qty] int)
;
-- insert your sample data
INSERT INTO #Results
    ([Date], [Day], [Sale Qty], [Purchase Qty], [Transfer Qty])
VALUES
    ('2015-04-05 00:00:00', 1, 11, 0, 0),
    ('2015-04-07 00:00:00', 3, 0, 16, 0),
    ('2015-04-08 00:00:00', 4, 12, 14, 17),
    ('2015-04-11 00:00:00', 7, 1, 2, 0)
;
-- find the max date
DECLARE @MaxDate DATETIME = (SELECT TOP 1 [Date] FROM #Results ORDER BY [Date] DESC)
-- recursive cte to build the date & day lookup table
;WITH cte AS (
    -- cte anchor is the min date and day = 1
    SELECT MIN([Date]) AS DateValue, 1 AS [Day] 
    FROM #Results
    UNION ALL
    -- uses dateadd to increment days until @MaxDate reached
    SELECT DATEADD(DAY, 1, cte.DateValue), [Day] +1
    FROM cte
    WHERE DATEADD(DAY, 1, cte.DateValue) <= @MaxDate
)
-- inserts values into temp lookup table
SELECT * 
INTO #DateLookup
FROM cte

这将创建一个临时表，其中包含使用保存这些值的结果中的最低和最高日期的值范围：

DateValue                Day
----------------------------
2015-04-05 00:00:00.000  1
2015-04-06 00:00:00.000  2
2015-04-07 00:00:00.000  3
2015-04-08 00:00:00.000  4
2015-04-09 00:00:00.000  5
2015-04-10 00:00:00.000  6
2015-04-11 00:00:00.000  7

然后，您需要链接到该表并替换任何NULL价值观与0像这样：

SELECT #DateLookup.[DateValue] AS [Date],
       #DateLookup.[Day] ,
       COALESCE([Sale Qty],0) AS [Sale Qty],
       COALESCE([Purchase Qty],0) AS [Purchase Qty],
       COALESCE([Transfer Qty],0) AS [Transfer Qty]
FROM #DateLookup
LEFT JOIN #Results ON #DateLookup.DateValue = #Results.[Date]

-- some tidy up
DROP TABLE #Results
DROP TABLE #DateLookup