INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Find missing dates with a range for one table

Find missing dates with a range for one table

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

RE: Find missing dates with a range for one table

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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Find missing dates with a range for one table

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

RE: Find missing dates with a range for one table

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

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Find missing dates with a range for one table

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,

CODE

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close