[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 376
  • Last Modified:

Find missing Date from all rows in SQL Server table

Hi Smart People!

I have a strange request...

What SQL syntax would I use to find those dates that are NOT present in my table?

++++++++++++++++++++++++++++++++++++
 The Scenario:
++++++++++++++++++++++++++++++++++++

I have a simple table called:
CRM_callLogs

The rows in the CRM_callLogs table are:
[creationDate], [userID]

Assumption:
There should be AT LEAST ONE row of data (i.e. entry) for EVERY day of the year.

Question:
What would be the sql syntax to search CRM_callLogs and return a list of any date
that is NOT in the table?

Thank you very much in advance!
Happy Coding!








0
ccptechs
Asked:
ccptechs
1 Solution
 
johnclarke123Commented:
You don't say what date range you want to search for, but this would do it:

CREATE PROC MissingDate(@start DATETIME, @end DATETIME)
AS
SET NOCOUNT ON
DECLARE @t TABLE(dates DATETIME)
IF @start <= @end
 BEGIN
 WHILE @start <= @end
 BEGIN
  INSERT INTO @t VALUES(@start)
  SET @start = DATEADD(d, 1, @start)
 END
 
 SELECT dates
 FROM CRM_callLogs
 RIGHT OUTER JOIN @t ON creationDate = dates
 WHERE creationDate IS NULL
 END
0
 
ccptechsAuthor Commented:
Thanks johnclarke123! Works like a charm!

=)

 
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now