具有多个日期范围和完全连接的SQL查询

Question

我有一个在多个数据库表上运行完全联接的SQL查询。查询是基于用户输入的动态查询，但对于示例，我只包含了一些可能的输入。

我遇到的这个问题是，我需要按多个日期范围进行过滤，如果查询结果落在任何一个日期范围内，则返回查询结果。

我现在的查询是：

SELECT rank=COUNT(*)
   FROM    [LOM].[dbo].[lom_problem] problem
       FULL JOIN [LOM].[dbo].[lom_batch] batch on problem.lom_number = batch.lom_number
       FULL JOIN [LOM].[dbo].[lom_specimen] specimen on problem.lom_number = specimen.lom_number
       FULL JOIN [LOM].[dbo].[Main_LOM_Form] main on problem.lom_number = main.lom_number
           WHERE problem.problem = '102' AND batch.batch IS NULL AND main.practice_code IN('HPMR', 'DOCTORs2')
           AND main.occurrence_date >=Convert(datetime,'01/04/2012') AND main.occurrence_date <= Convert(datetime,'01/05/2012')
           OR main.received_date>=Convert(datetime,'01/04/2012') AND main.received_date <= Convert(datetime,'01/05/2012')

但是，该查询返回的结果就好像我的查询是

SELECT rank=COUNT(*)
   FROM    [LOM].[dbo].[lom_problem] problem
       FULL JOIN [LOM].[dbo].[lom_batch] batch on problem.lom_number = batch.lom_number
       FULL JOIN [LOM].[dbo].[lom_specimen] specimen on problem.lom_number = specimen.lom_number
       FULL JOIN [LOM].[dbo].[Main_LOM_Form] main on problem.lom_number = main.lom_number
           WHERE problem.problem = '102' AND batch.batch IS NULL AND main.practice_code IN('HPMR', 'DOCTORs2')
   AND main.occurrence_date >=Convert(datetime,'01/04/2012') AND main.occurrence_date <= Convert(datetime,'01/05/2012')

和

SELECT rank=COUNT(*)
   FROM    [LOM].[dbo].[lom_problem] problem
       FULL JOIN [LOM].[dbo].[lom_batch] batch on problem.lom_number = batch.lom_number
       FULL JOIN [LOM].[dbo].[lom_specimen] specimen on problem.lom_number = specimen.lom_number
       FULL JOIN [LOM].[dbo].[Main_LOM_Form] main on problem.lom_number = main.lom_number
           WHERE main.received_date>=Convert(datetime,'01/04/2012') AND main.received_date <= Convert(datetime,'01/05/2012')

如何让查询返回此部分完好无损的结果：

SELECT rank=COUNT(*)
   FROM    [LOM].[dbo].[lom_problem] problem
       FULL JOIN [LOM].[dbo].[lom_batch] batch on problem.lom_number = batch.lom_number
       FULL JOIN [LOM].[dbo].[lom_specimen] specimen on problem.lom_number = specimen.lom_number
       FULL JOIN [LOM].[dbo].[Main_LOM_Form] main on problem.lom_number = main.lom_number
           WHERE problem.problem = '102' AND batch.batch IS NULL AND main.practice_code IN('HPMR', 'DOCTORs2')

在此基础上再加上对任意日期范围的过滤：

           AND main.occurrence_date >=Convert(datetime,'01/04/2012') AND main.occurrence_date <= Convert(datetime,'01/05/2012')
           OR main.received_date>=Convert(datetime,'01/04/2012') AND main.received_date <= Convert(datetime,'01/05/2012')

谢谢!

编辑:我需要根据main.occurrence_date和main.received_date的数据范围进行过滤。如果数据区域出现在一列或另一列中，我需要返回计数。但是，我现在的查询返回where语句第一部分的和，然后返回第二部分的结果(在OR之后)。我认为这可能只是一个语法/格式问题，但我不确定。

Answer 1

别忘了你的括号：

       WHERE problem.problem = '102' AND batch.batch IS NULL AND main.practice_code IN('HPMR', 'DOCTORs2')
       AND ((main.occurrence_date >=Convert(datetime,'01/04/2012') AND main.occurrence_date <= Convert(datetime,'01/05/2012'))
       OR (main.received_date>=Convert(datetime,'01/04/2012') AND main.received_date <= Convert(datetime,'01/05/2012'))

编辑:好的，让我们再试一次。我相信acermate433s的建议会让它更具可读性：

       WHERE problem.problem = '102' AND batch.batch IS NULL AND main.practice_code IN('HPMR', 'DOCTORs2')
       AND (main.occurrence_date BETWEEN Convert(datetime,'01/04/2012') AND Convert(datetime,'01/05/2012'))
       OR (main.received_date BETWEEN Convert(datetime,'01/04/2012') AND Convert(datetime,'01/05/2012'))

这就是你所想的吗？它将计算所有在指定范围内具有occurance_date的记录和所有在指定范围内具有received_date的记录，而不会对它们进行两次计数。