返回没有匹配条目的所有日期

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:

我已经尝试了一些我在SO上找到的其他解决方案,但它们似乎不适用于我的架构,或者我只是在使用SQL。所以假设我有两个表,表1是这样的:

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:

从本质上讲,一个人将有一个特定日期的条目。他们也可以有一个日期的多个条目。表2是一系列日期,例如:

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!

我想要做的是获得一个结果集,该结果集显示表2中缺少一个条目的人的日期,理想情况下也是该人的姓名。任何线索?谢谢!

1 个解决方案

#1

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

1 个解决方案

#1

更多相关文章

随机推荐