title: How to compare dates in multiple tables sql query
body: <p>Pleas have look at my tables and queries and data retrieving scenario.
  i am failing to compare/check the dates  between with from to another tables.
  my logic is to get the incidents logged between from and To dates of a contracts(It should be between effective &amp; Expiry date of that contract)</p>
  
  <pre><code>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  |
  +---------------+-----------------+----------------+-------------+
  </code></pre>
  
  <p>My Query:</p>
  
  <pre><code>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
  </code></pre>
  
  <p>How to add @from and @To date in WHERE clause to check the logged_dates in between effective_date &amp; expiry_dates of maint_crct table.</p>
  
  <pre><code>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 |
  +-------------+-------------+---------+-----------+---------------+----------------+
  </code></pre>