Rückkehr Zeilen in einem resulset, wenn kein datasatz für ein bestimmtes date gefunden

Ich habe eine Abfrage erstellt, um folgende Ausgabe zurückzugeben.

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 

Meine aktuelle Abfrage ist wie folgt.

 (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) 

Erforderliche Ausgabe ist im folgenden Format

 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 

Wie soll ich die Abfrage schreiben, um Zeilen mit date und Tag zurückzugeben, wenn keine Ergebnismenge für diese datesausgabe zurückgegeben wird.

Sie benötigen einen Tisch, um als Nachschlagtabelle für die Termine und Tage zu fungieren, die fehlen, um das Spektrum der data in den Abfrageergebnissen zu decken. Du kannst so eins schaffen:

 -- 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 

Dies erzeugt eine Temp-Tabelle mit dem Wertebereich mit den niedrigsten und höchsten data in Ihren Ergebnissen, die diese Werte enthält:

 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 

Sie müssen dann mit dieser Tabelle verknüpfen und alle NULL Werte durch 0 replace:

 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 

Wenn Sie noch nicht über eine datatabelle in Ihrem System verfügen, fügen Sie einen hinzu. Sie können dieses t-sql-Skript verwenden:

 CREATE TABLE TblDates(TheDate date not null); DECLARE @StartDate date, @NumberOfDates int, @Counter int SELECT @StartDate = GETDATE(), -- or whatever date you want @NumberOfDates = 100, -- or whatever number of dates you want after start date @Counter = 0; WHILE(@Counter < @NumberOfDates) BEGIN INSERT INTO TblDates(TheDate) VALUES (DATEADD(d, @Counter, @StartDate) END 

Nach dem Ausführen dieses Skripts solltest du einen datatisch verwenden. Dann alles, was Sie tun müssen, ist das:

 SELECT TheDate, DATEPART(D, TheDate) As Day, ISNULL(T1.SaleQty, 0) As SaleQty, ISNULL(T2.PurchaseQty, 0) As PurchaseQty, ISNULL(T3.TransferQty, 0) As TransferQty FROM tblDates LEFT JOIN SaleTable T1 ON(TheDate = T1.Date) LEFT JOIN PurchaseTable T2 ON(TheDate = T2.Date) LEFT JOIN TransferTable T3 ON(TheDate = T3.Date) 

Aktualisieren

Nun, da ich darüber nachdenke, können Sie eine Zifferntabelle verwenden, um eine cte zu erstellen, die Ihre data halten wird (das Erstellen der Zifferntabelle ist ziemlich einfach und ähnlich wie das Skript, das ich für die datatabelle geschrieben habe)

 With DatesCTE(TheDate) AS SELECT DATEADD(D, TheNumber, @startDate) FROM NumbersTable WHERE DATEADD(D, TheNumber, @StartDate) < @EndDate 

Update 2

Wie Selva TS in einem Kommentar zu dieser Antwort erwähnt, ist es möglich, ein date cte auch ohne eine Zifferntabelle zu generieren, mit rekursiven cte wie folgt:

 DECLARE @StartDate datetime, @EndDate datetime SELECT @StartDate = DATEADD(YEAR, -1, GETDATE()), @EndDate = GETDATE() ;WITH Calendar AS( SELECT @StartDate dateidx UNION ALL SELECT dateidx + 1 FROM Calendar WHERE dateidx + 1 < @EndDate )