CODEWITHSUNDEEP
sqlsql-server
rickkr
rickkr

Reputation: 144

Return all dates that do not have a matching entry

I've tried some of the other solutions I've found on SO, but they don't seem to work with my schema, or I just suck at SQL. So let's say I have two tables, table 1 is something like this:

LastName | FirstName | Date

Doe        John        7/07/14
Doe        John        7/07/14
Doe        John        7/08/14
Bond       James       7/07/14
Bond       James       7/09/14
Jane       Mary        7/08/14

Essentially, a person will have an entry for a certain date. they can also have multiple entries for one date. table 2 is a range of dates, such as:

Date
7/06/14
7/07/14
7/08/14
7/09/14

What I want to do is get a result set that shows for what days in table 2 is a person missing an entry, ideally with the person's name as well. Any leads? Thanks!

Upvotes: 2

Views: 41

Answers (1)

rs.
rs.

Reputation: 27427

Try this,

;WITH CTE AS
(
  --create list of all names and date combinations from both table
  SELECT DISTINCT A.LastName, A.FirstName, B.Date_col
  FROM Table1 A, Table2 B
)
--select rows that are missing dates in your first table
SELECT X.* FROM CTE X
LEFT OUTER JOIN Table1 Y 
    ON X.LastName = Y.LastName 
    AND X.FirstName = Y.FirstName 
    AND X.Date_col = Y.Date_Col
WHERE Y.LastName IS NULL

Upvotes: 3

Related Questions