如何在sql中搜索日期范围的序列 [英] How to search sequence of date ranges in sql

问题描述

尝试检查日期范围时，它不会返回值。这里，usr_id = 1的st_date和end_date是序列，所以它应该只返回usr_id = 1的值，并且不应该返回usr_id = 3，因为它不是序列。如果日期范围不是按顺序排列，则不应返回任何值。

When trying to check date ranges it does not return values. Here st_date and end_date for usr_id = 1 is sequence so it should return only usr_id = 1 values and should not return usr_id = 3 since it is not sequence . If the date range is not in sequence, it should not return any value.

CREATE TABLE #temp(st_date DATE,end_date DATE,usr_id INT)
    INSERT  #temp 
    VALUES ('2007-03-01  ','2015-01-31  ',1),
    ('2015-02-01  ','2015-03-29  ',1),
      ('2015-03-30 ','2017-04-01  ',1),
      ('2007-03-01  ','2014-01-31  ',2),
      ('2007-03-01  ','2015-01-31  ',3),
      ('2015-03-02  ','2017-04-01  ',3)
      --,('2015-02-01  ','2017-04-01  ',3)
    DECLARE @st_dt DATE = '2009-02-01 00:00:00',@end_dt DATE = '2017-01-01 00:00:00'
    
    SELECT * FROM #temp WHERE  @st_dt BETWEEN st_date AND end_date
    AND @end_dt BETWEEN st_date AND end_date
    
    	SELECT * 
    	FROM #temp t
        INNER JOIN #temp t2 ON t.usr_id = t2.usr_id AND t.end_date = DATEADD(dd, -1, t2.st_date)
    	WHERE   @st_dt BETWEEN t.st_date AND t.end_date
        AND @end_dt BETWEEN t2.st_date AND t2.end_date
    
    	DROP TABLE #temp

推荐答案

您想要如何匹配商品？

任何重叠？完全在这个范围内？



您当前的查询会在稍后进行。没有任何物品完全包含在日期范围内



如果您需要，我建议使用重叠：

How exactly do you want to match items?
Any overlap? entirely within the range?

Your current query does the later. None of your items are entirely contained within the date range

I would suggest using overlaps if that's what you need:

where st_date < @end_dt and end_date > @st_dt

PS：I用它来测试你的数据集：

PS: I used this to test your dataset:

SELECT *,@st_dt,@end_dt,
CASE  WHEN  @st_dt <= st_date THEN 1 ELSE 0 END,
CASE  WHEN  @st_dt BETWEEN st_date AND end_date THEN 1 ELSE 0 END,
CASE  WHEN  @end_dt >= end_date THEN 1 ELSE 0 END,
CASE  WHEN  @end_dt BETWEEN st_date AND end_date THEN 1 ELSE 0 END
from #TEMP

这篇关于如何在sql中搜索日期范围的序列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！