Find missing dates with a range for one table

[Starscream_2017]

Hi, I am trying to find the missing dates between a date range in one table.

I have found code on this site to do that but haven't been able to get it quite right.
The missing dates should be displayed for the table tblTRF_ProductionHistory.

Sample data for the tblTRF_ProductionHistory

2017-09-30
2017-10-02
2017-10-03
2017-10-04
2017-10-05
2017-10-06
2017-10-07
2017-10-08
2017-10-10
2017-10-11
2017-10-12

Missing dates in between are required such as 2017-10-01.

Code I am trying to refactor

Set nocount on

Create Table #t (ColD date)
DECLARE @bd date, @ed date
SET @bd = '2017-9-29'
SET @ed = '2017-10-15'
While @bd<=@ed
Begin
Insert #t (ColD) Values(@bd)
Set @bd=dateadd(day,1,@bd)
End

SELECT ph.MessageDate
FROM tblTRF_ProductionHistory ph
WHERE ph.MessageDate BETWEEN '2017-9-29' AND '2017-10-15'
AND ph.Active = 1
AND SourcePlant = 51
ORDER BY MessageDate

Select Distinct [Message] = a.ColD
From #t a Left Join tblTRF_ProductionHistory b
On a.ColD = b.MessageDate
WHERE b.MessageDate IS NULL

 

[SkipVought]

Hi,

Typically what you do is 1) have a table containing all the dates of interest that you would 2) join with an outer join with your current table on the date fields.

So you’d need to generate a 1).

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[simian336]

If I can clarify Skips post. He is referring to a Date table or sometimes also called a Calendar table. You can Google those and see dozens of examples.

Simi

 

[SkipVought]

...and I’d bet that your company has a calendar table, cuz every time they run a financial on an accounting month or accounting year, no-one’s punching in start and end dates, or figuring out the holiday dates for a pay period. Its all in the company calendar table.

If you ask around, they might have rehosted such a table in an accessible database for which you could get limited access.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[TamarGranor]

If you don't have a date table, you can easily generate all the dates in the specified range using a recursive CTE. This example then uses the list to ensure that you include all dates in a result that counts the number of orders by day,

DECLARE @StartDate DATETIME ;
DECLARE @EndDate DATETIME ;

SELECT @StartDate = MIN(Orderdate), 
       @EndDate = MAX(OrderDate)
  FROM [Sales].[SalesOrderHeader];

-- recursive CTE
WITH AllDates (tDate)
AS (
    SELECT @StartDate 
    UNION ALL
    SELECT DATEADD(DAY, 1, tDate)
    FROM AllDates
    WHERE tDate < @EndDate
    )

SELECT tDate,
       COUNT(SOH.SalesOrderID) AS OrderCount
FROM AllDates
  LEFT JOIN Sales.SalesOrderHeader AS SOH
    ON SOH.OrderDate >= tDate
    AND SOH.OrderDate < DATEADD(DAY, 1, tDate)
GROUP BY tDate
OPTION (MAXRECURSION 0);

Tamar