SQL Query从2个不同的表中获取到期日期和生效日期之间的日期

问题描述：

我有这个问题与查询这两个表之间没有任何联系，我试图结合。表1的有效期和截止日必须与PollDate的表2相关联。 PollDate不得位于有效期和到期日之间。SQL Query从2个不同的表中获取到期日期和生效日期之间的日期

Table 1 
ClientID EffectiveDate  ExpirationDate 
1  2009-04-01 00:00:00.000 2009-12-18 00:00:00.000 
1  2010-02-12 00:00:00.000 2010-03-05 00:00:00.000 
1  2010-05-18 00:00:00.000 NULL 
1  2009-12-21 00:00:00.000 2010-02-08 00:00:00.000 
1  2010-12-19 00:00:00.000 2009-12-20 00:00:00.000 

Table 2 
ClientID PollDate 
1  2009-12-20 00:00:00.000 
1  2009-12-19 00:00:00.000 
1  2010-02-12 00:00:00.000 
1  2010-02-27 00:00:00.000 
1  2010-05-19 00:00:00.000 
1  2010-05-29 00:00:00.000 
1  2010-05-30 00:00:00.000 
1  2010-05-31 00:00:00.000 
1  2010-06-05 00:00:00.000 
1  2010-06-25 00:00:00.000 
1  2010-06-27 00:00:00.000 
1  2010-07-02 00:00:00.000 
1  2010-08-04 00:00:00.000 
1  2010-08-20 00:00:00.000 

Result 
ClientID inValidDate 
1  2009-12-20 00:00:00.000 
1  2009-12-19 00:00:00.000

有点困惑“..他们没有有他们之间的任何联系“他们不在同一个数据库？你在寻找某种RPC选项吗？ – 2012-02-11 00:56:10

不，他们在同一个数据库中。我的意思是说，这两个表都没有任何引用，即：外键。两个表的共同统治是clientID。 – n00bs 2012-02-13 15:14:48

答

的每一行匹配下面是一个轻微的变化@ AJP的结果 - 在ExpirationDate中只占NULL值：

CREATE TABLE #Table1 
(
    [ClientID] INT, 
    [EffectiveDate] DATETIME, 
    [ExpirationDate] DATETIME 
) 

INSERT INTO #Table1 
(
    [ClientID], 
    [EffectiveDate], 
    [ExpirationDate] 
) 
SELECT 1, '2009-04-01', '2009-12-18' UNION 
SELECT 1, '2010-02-12', '2010-03-05' UNION 
SELECT 1, '2010-05-18', NULL UNION 
SELECT 1, '2009-12-21', '2010-02-08' UNION 
SELECT 1, '2010-12-19', '2009-12-20' 

CREATE TABLE #Table2 
(
    [ClientID] INT, 
    [PollDate] DATETIME 
) 

INSERT INTO #Table2 
(
    [ClientID], 
    [PollDate] 
) 
SELECT 1, '2009-12-20' UNION 
SELECT 1, '2009-12-19' UNION 
SELECT 1, '2010-02-12' UNION 
SELECT 1, '2010-02-27' UNION 
SELECT 1, '2010-05-19' UNION 
SELECT 1, '2010-05-29' UNION 
SELECT 1, '2010-05-30' UNION 
SELECT 1, '2010-05-31' UNION 
SELECT 1, '2010-06-05' UNION 
SELECT 1, '2010-06-25' UNION 
SELECT 1, '2010-06-27' UNION 
SELECT 1, '2010-07-02' UNION 
SELECT 1, '2010-08-04' UNION 
SELECT 1, '2010-08-20' 

SELECT 
    t2.[ClientID], 
    t2.[PollDate] AS 'inValidDate' 
FROM 
    #Table1 AS t1 
JOIN 
    #Table2 AS t2 
ON 
    (t2.[PollDate] < t1.[EffectiveDate] 
OR t2.[PollDate] > ISNULL(t1.[ExpirationDate], '9999-12-31')) 
AND t1.ClientID = t2.ClientID -- Not clear from your question if this is necessary

如果您想在'ExpirationDate'中包含所有'NULL'值，可以使用't2。[PollDate]> ISNULL（t1。[ExpirationDate]，'1753- 01-01'））'而不是 – diaho 2012-02-11 00:45:21

感谢diaho，这是需要的。您的查询会在有效日期和到期日期内生成所有匹配PollDate的列表。我需要添加的是他们都是空的地方，因此排除不在两个日期之间的PollDates。 – n00bs 2012-02-13 16:51:29

答

不确定关于syntext，但你想做这样的事情。

select clientID, polDate as 'inValidDate' 
FROM Table1 t1 
INNER JOIN Table2 t2 
ON t2.PolDate not in between t1.EffectiveDate and t1.ExpirationDate

编辑：假设，如果到期日为空意味着政策将永不过期。

select clientID, polDate as 'inValidDate' 
FROM Table1 t1 
INNER JOIN Table2 t2 
ON t2.PolDate not in between t1.EffectiveDate and ISNULL(t1.ExpirationDate, '2999-01-01')

答

u能尝试：

select t2.ClientID, t2.PoolDate 
from Table1 t1, Table2 t2 
where t2.PollDate between t1.EffectiveDate and t1.ExpirationDate

，因为ü没有把在连接条件，表1中的每一行将与表2

我以为他说“PollDate不能在有效期和到期日之间”。是不是你的查询会导致之间？ – AJP 2012-02-11 00:38:59

，但问题的示例结果显示“无效日期” – vicker313 2012-02-11 00:44:48

SQL Query从2个不同的表中获取到期日期和生效日期之间的日期

相关推荐