Count times date occurs between two dates in a second table

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??

I’m using SQL Server version 12.

[Charges]

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

[Defects]

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

[Desired Query Output]

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

Answer

You can use a correlated subquery or a lateral join:

select 
    c.*, 
    (
        select count(*) 
        from defects d 
        where 
            d.order = c.order 
            and c.ChargeDate >= d.OpenDate
            and (d.CloseDate is null or c.ChargeDate <= d.CloseDate)
    ) as DefectCnt
from charges c