如何比较多表sql查询中的日期

Question

请看一下我的表格、查询和数据检索场景。我无法比较/检查从表到另一个表之间的日期。我的逻辑是记录合同的开始日期和结束日期之间的事件(它应该在合同的生效日期和到期日期之间)

Table:Incident:
+-------------+-------------+---------+
| incident_id | date_logged | item_id |
+-------------+-------------+---------+
|    10074329 | 2015-09-01  |   63247 |
|    10074869 | 2016-01-31  |   63247 |
|    10074399 | 2016-01-20  |   63247 |
|    10075274 | 2016-02-07  |   63247 |
|    10035727 | 2013-08-02  |   47558 |
|    10050239 | 2014-07-08  |   47558 |
|    10076340 | 2016-02-25  |   47558 |
+-------------+-------------+---------+

Table:Item:
+---------+-------------+
| item_id |   item_sc   |
+---------+-------------+
|   63247 | 313150069   |
|   47558 | 2S27500EVW  |
+---------+-------------+
Table:item_maint
+---------------+---------+---------------+-------------+-------------+
| item_maint_id | item_id | maint_crct_id | start_date  |  end_date   |
+---------------+---------+---------------+-------------+-------------+
|           701 |   63247 |          2132 | 2015-11-11  | 2016-11-10  |
|           702 |   63247 |          1819 | 2014-11-11  | 2015-11-10  |
|           703 |   47558 |           921 | 2013-05-01  | 2016-09-08  |
+---------------+---------+---------------+-------------+-------------+
Table:maint_crct
+---------------+-----------------+----------------+-------------+
| maint_crct_id |  maint_crct_sc  | effective_date | expiry_date |
+---------------+-----------------+----------------+-------------+
|          1819 | ACSS-2015-0011  | 2014-11-11     | 2015-11-10  |
|          2132 | ACSS-2015-0091  | 2015-11-11     | 2016-11-10  |
|           921 |  ACSS-2013-0066 | 2013-05-01     | 2016-09-08  |
+---------------+-----------------+----------------+-------------+

我的问题是：

Declare @contract varchar(50); set @contract='ACSS-2015-0011'
declare @from date;set @from='2014-01-01'
declare @To date; set @To='2015-09-01'
select  dbo.incident.incident_id,dbo.incident.date_logged,dbo.item.item_id,
 dbo.item.item_sc,dbo.maint_crct.maint_crct_id, dbo.maint_crct.maint_crct_sc
FROM dbo.incident INNER JOIN
dbo.item ON dbo.incident.item_id = dbo.item.item_id INNER JOIN
dbo.item_maint INNER JOIN  dbo.maint_crct ON dbo.item_maint.maint_crct_id = 
dbo.maint_crct.maint_crct_id
ON dbo.incident.item_id = dbo.item_maint.item_id    
WHERE dbo.maint_crct.maint_crct_sc=@contract

如何在WHERE子句中添加@from和@To date来检查maint_crct表的effective_date和expiry_dates之间的logged_dates。

Expected Result:
+-------------+-------------+---------+-----------+---------------+----------------+
| incident_id | date_logged | item_id |  item_sc  | maint_crct_id | maint_crct_sc  |
+-------------+-------------+---------+-----------+---------------+----------------+
|    10074329 | 2015-09-01  |   63247 | 313150069 |          1819 | ACSS-2015-0011 |
+-------------+-------------+---------+-----------+---------------+----------------+

Answer 1

您可以将日期与这些运算符中的任何一个进行比较，所有这些运算符都记录在MSDN：< > <= >= BETWEEN中。

由于您在问题中发布的SQL没有做任何尝试，所以我认为这是您被卡住的部分。