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

Question

>请看一下我的表和查询以及数据检索方案。我无法比较/检查与 from 到另一个表之间的日期。我的逻辑是让事件记录在合同的开始日期和结束日期之间（它应该在该合同的生效日期和到期日期之间）

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日期，以检查表effective_date和expiry_dates之间的logged_dates maint_crct。

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 没有尝试这样做，因此我假设这就是您坚持的部分。