如何在Crystal或SQL中筛选居住日期范围与指定范围重叠的租户？

需求：需要筛选出在指定日期范围（{?Start Date} 至 {?End Date}）内居住在设施中的租户，后续统计数量的逻辑已实现。规则如下：

• 租户有movein（入住日期）和moveout（退房日期）字段，若租户仍居住在此，moveout为NULL
• 示例：租户Bob入住日期为1/1/2020，退房日期为5/30/2021，查询1/1/2021至3/1/2022期间的租户，Bob应该被纳入结果

错误尝试代码 #

公式字段 RangeAnalysis #

if ({View_Tenants.movein} <= {?Start Date} and
   {@MoveoutNull} >= {?Start Date})
or
  ({View_Tenants.movein} <= {?End Date} and
   {@MoveoutNull} >= {?End Date})
or
  ({View_Tenants.movein} >= {?Start Date} and
   {@MoveoutNull} <= {?Start Date})
then 1 else 0

公式字段 MoveoutNull #

if isnull({View_Tenants.moveout}) then currentdate

筛选条件 #

RangeAnalysis = 1

（也曾尝试将NULL替换为未来日期，但结果仍不准确）

正确解决方案 #

核心逻辑是判断租户的居住周期与查询日期范围是否存在重叠，只要满足以下两个条件即可：

1. 租户入住日期不晚于查询的结束日期（movein <= {?End Date}）
2. 租户退房日期不早于查询的开始日期（若退房日期为NULL，则视为仍在居住，自动满足）

修正后的RangeAnalysis公式（无需依赖MoveoutNull） #

{View_Tenants.movein} <= {?End Date} 
and 
(
    not isnull({View_Tenants.moveout}) and {View_Tenants.moveout} >= {?Start Date}
    or 
    isnull({View_Tenants.moveout})
)

若继续使用MoveoutNull公式的修正版本 #

{View_Tenants.movein} <= {?End Date} 
and 
{@MoveoutNull} >= {?Start Date}

（注：MoveoutNull可以保持原逻辑，或替换为一个足够远的未来日期，比如Date(9999,12,31)，避免因currentdate导致的边界问题）

筛选条件 #

保持使用 RangeAnalysis = 1 即可完成筛选

内容的提问来源于stack exchange，提问作者kayross27