我有两张table。一个定义时间间隔（开始和结束）。时间间隔的长度不相等。另一个包含产品id、产品的开始和结束日期。

TableOne: 
Interval   StartDateTime             EndDateTime
202020201  2020-01-01 00:00:00       2020-02-10 00:00:00
202020202  2020-02-10 00:00:00       2020-02-20 00:00:00 

TableTwo
ProductID  ProductStartDateTime      ProductEndDateTime
ASSDWE1    2018-01-04  00:12:00      2020-04-10  20:00:30
ADFGHER    2020-01-05  00:11:30      2020-01-19  00:00:00
ASDFVBN    2017-10-10  00:12:10      2020-02-23  00:23:23

我需要计算表二的产品在表一定义的时间间隔内的平均长度。如果产品存在于表一所示的整个时间间隔内，则该时间间隔内产品的长度定义为从开始日期到时间间隔结束的长度。
我试了以下方法

select 
a.*,
(select  
  AVG(datediff(day, b.ProductStartDateTime, IIF (b.ProductEndDateTime> a.EndDateTime, a.EndDateTime 
  ,b.ProductEndDateTime))) --compute average length of the products
  FROM #TableTwo b
   WHERE ( not (b.ProductEndDateTime <=  a.StartDateTime ) and not (b.ProductStartDateTime >=  a.EndDateTime) ) 
        -- select products that existed during interval from #TableOne
                     ) as AverageProductLength

from #TableOne a

我错了 "Multiple columns are specified in an aggregated expression containing an outer reference. If an expression being aggregated contains an outer reference, then that outer reference must be the only column referenced in the expression." 我想要的结果是：

Interval   StartDateTime             EndDateTime         AverageProductLength
    202020201  2020-01-01 00:00:00       2020-02-10 00:00:00    23 
    202020202  2020-02-10 00:00:00       2020-02-20 00:00:00    34.5

有什么办法我可以计算平均值吗？