title: Count times date occurs between two dates in a second table
body: <p>I have two tables [Charges] and [Defects] and want to produce [Desired Query Output] where the output counts the occurrances of defect when [Charges].ChargeDate is between (and including) [Defects].OpenDate and [Defects].CloseDate. For [Defects] table, a close date of NULL means it has not closed yet. Seems simple enough, but I haven't found a good example of how to do this. Can you help??</p>
  <p>I'm using SQL Server version 12.</p>
  <p>[Charges]</p>
  <pre><code>Order  Charge  ChargeDate
  1      1.2     07/10/2020
  1      0.6     07/15/2020
  6      0.002   07/20/2020
  8      0.13    07/01/2020
  8      1.1     06/18/2020
  8      0.3     06/19/2020
  10     2.3     06/24/2020
  </code></pre>
  <p>[Defects]</p>
  <pre><code>Order  DefectID  OpenDate     CloseDate
  1      25        06/01/2020   NULL
  1      27        07/09/2020   07/12/2020
  1      30        05/01/2020   07/20/2020
  8      45        06/19/2020   06/19/2020
  8      47        06/12/2020   07/05/2020
  8      48        06/19/2020   NULL
  10     49        06/24/2020   NULL
  </code></pre>
  <p>[Desired Query Output]</p>
  <pre><code>Order  Charge  ChargeDate   DefectCnt
  1      1.2     07/10/2020   3
  1      0.6     07/15/2020   2
  6      0.002   07/20/2020   0
  8      0.13    07/01/2020   2
  8      1.1     06/18/2020   1
  8      0.3     06/19/2020   3
  10     2.3     06/24/2020   1
  </code></pre>