SQL存储过程日期时间按位置过滤

我在SQL Server中的存储过程有问题，有一个包含两个datetime列的表，一个基于位置和实体进行过滤的开始时间和结束时间。

在位置报告中，设置了“开始时间”和“结束时间”的过滤条件时，仅考虑以下位置记录：开始时间至少等于“过滤开始时间”且结束时间不晚于“过滤结束时间”建立报告。例如，实际上John Doe从8:30到9:30在A室，然后从9:30到10:30在B室，则从9:00到10:00的位置报告将不包含任何记录。约翰·多伊（John Doe）的下落。

理想的行为是应该包括在“结束时间”之前开始并在“开始时间”之后结束的位置间隔（与各个位置记录相反）。就表示方式而言，对于上述John Doe，输出报告应在9:00至9:30的A室和B室9:30到10:00的John Doe中显示John Doe，并在位置间隔的限制不在过滤约束之内。

这是可能吗？如果需要任何其他信息，请告知我，目前我正在使用基本的AND locationchangehistory.starttime> = Starttime AND locationchangehistory.endtime <= @Endtime）

这是公司正在使用的完整存储过程，我希望格式正确显示：-

                     @Asset Varchar (MAX) = NULL OUTPUT, 
                     @Location Varchar (MAX) = NULL OUTPUT,
                     @Ward Varchar (MAX) = NULL OUTPUT,
                     @Zone Varchar (MAX) = NULL OUTPUT,
                     @Floor Varchar (MAX) = NULL OUTPUT,
                     @Starttime datetime OUTPUT,
                     @Endtime datetime OUTPUT,
                     @Top int,
                     @FacilityID int

                     AS
                     SELECT DISTINCT TOP (@Top)


                     location.name AS 'Location', 
                     monitoredentity.name AS 'Asset', 
                     zone.name AS 'Zone', 
                     floor.name AS 'Floor',
                     ward.name AS 'Area',
                     locationchangehistory.starttime AS 'Starttime', 
                     locationchangehistory.endtime AS 'Endtime', 
                     CONVERT(varchar(max), DATEDIFF(SECOND, 
                     locationchangehistory.starttime, locationchangehistory.endtime) / 3600) + ':' + RIGHT('0' + CONVERT(varchar(2), DATEDIFF(SECOND, locationchangehistory.starttime, 
                     locationchangehistory.endtime) % 3600 / 60), 2) + ':' + RIGHT('0' + CONVERT(varchar(2), DATEDIFF(SECOND, locationchangehistory.starttime, 
                     locationchangehistory.endtime) % 60), 2) AS 'TimeInPlace' 


                     FROM floor INNER JOIN
                     zone ON zone.floor = floor.id INNER JOIN
                     ward ON zone.id = ward.zone INNER JOIN
                     location ON ward.id = location.ward INNER JOIN
                     locationchangehistory ON location.id =                        locationchangehistory.location INNER JOIN
                     monitoredentity ON monitoredentity.id =    locationchangehistory.entity




                     WHERE 


                     (monitoredentity.type  =  4 
               AND   floor.facilityid = @FacilityID
               AND   zone.facilityid = @FacilityID
               AND   ward.facilityid = @FacilityID
               AND   Location.facilityid = @FacilityID
               AND   locationchangehistory.facility = @FacilityID
               AND   monitoredentity.facilityid = @FacilityID 
               AND  charindex(',' + cast(monitoredentity.id AS VARCHAR(MAX))                  + ',', ',' + @Asset + ',') > 0
               AND locationchangehistory.starttime >= @Starttime 
               AND locationchangehistory.endtime <= @Endtime)
               AND ((charindex(',' + cast(Location.id AS VARCHAR(MAX)) + ',', ',' + @location + ',') > 0 OR  charindex(',' + cast(Ward.id AS VARCHAR(MAX)) + ',', ',' + @Ward + ',') > 0
               OR  charindex(',' + cast(zone.id AS VARCHAR(MAX)) + ',', ',' + @Zone + ',') > 0) OR  charindex(',' + cast(floor.id AS VARCHAR(MAX)) + ',', ',' + @Floor + ',') > 0)




                ORDER by locationchangehistory.starttime DESC`