SQL-查找列日期是否至少部分包含日期范围

我认为这可以作为 缺口和岛屿 问题。考虑以下输入数据:(与op的示例数据相同,加上两行)

id  company_id  status_id   effective_date
-------------------------------------------
1   10          1           2016-12-15
2   10          1           2016-12-30 
3   10          5           2017-02-04
4   10          4           2017-02-08
5   11          5           2017-06-05
6   11          1           2018-04-30

可以使用以下查询:

SELECT t.id, t.company_id, t.status_id, t.effective_date, x.cnt
FROM company_status_history AS t
OUTER APPLY 
(
   SELECT COUNT(*) AS cnt
   FROM company_status_history AS c
   WHERE c.status_id = 1 
         AND c.company_id  = t.company_id 
         AND c.effective_date < t.effective_date
) AS x
ORDER BY company_id, effective_date

得到:

id  company_id  status_id   effective_date  grp
-----------------------------------------------
1   10          1           2016-12-15      0
2   10          1           2016-12-30      1
3   10          5           2017-02-04      2
4   10          4           2017-02-08      2
5   11          5           2017-06-05      0
6   11          1           2018-04-30      0

现在你可以识别 status = 1 岛屿使用:

;WITH CTE AS 
(
    SELECT t.id, t.company_id, t.status_id, t.effective_date, x.cnt
    FROM company_status_history AS t
    OUTER APPLY 
    (
       SELECT COUNT(*) AS cnt
       FROM company_status_history AS c
       WHERE c.status_id = 1 
             AND c.company_id  = t.company_id 
             AND c.effective_date < t.effective_date
    ) AS x
)
SELECT id, company_id, status_id, effective_date,
       ROW_NUMBER() OVER (PARTITION BY company_id ORDER BY effective_date) - 
       cnt AS grp
FROM CTE 

输出:

id  company_id  status_id   effective_date  grp
-----------------------------------------------
1   10          1           2016-12-15      1
2   10          1           2016-12-30      1
3   10          5           2017-02-04      1
4   10          4           2017-02-08      2
5   11          5           2017-06-05      1
6   11          1           2018-04-30      2

计算字段 grp 将帮助我们识别这些岛屿:

;WITH CTE AS 
(
    SELECT t.id, t.company_id, t.status_id, t.effective_date, x.cnt
    FROM company_status_history AS t
    OUTER APPLY 
    (
       SELECT COUNT(*) AS cnt
       FROM company_status_history AS c
       WHERE c.status_id = 1 
             AND c.company_id  = t.company_id 
             AND c.effective_date < t.effective_date
    ) AS x
), CTE2 AS 
(
   SELECT id, company_id, status_id, effective_date,
          ROW_NUMBER() OVER (PARTITION BY company_id ORDER BY effective_date) - 
          cnt AS grp
   FROM CTE
)
SELECT company_id, 
       MIN(effective_date) AS start_date, 
       CASE 
          WHEN COUNT(*) > 1 THEN DATEADD(DAY, -1, MAX(effective_date))
          ELSE MIN(effective_date)
       END AS end_date
FROM CTE2 
GROUP BY company_id, grp
HAVING COUNT(CASE WHEN status_id = 1 THEN 1 END) > 0

输出:

company_id  start_date  end_date
-----------------------------------
10          2016-12-15  2017-02-03 
11          2018-04-30  2018-04-30 

您只需要知道上面那些与指定间隔重叠的记录。

Demo here 有点复杂的用例。

也许这就是你要找的?对于这类问题,您需要加入表的两个实例,在本例中,我只是按ID加入下一个记录,这可能不完全正确。为了做得更好,您可以使用诸如row_number之类的窗口函数创建一个新的ID,并根据您的需求条件对表进行排序。

如果此行为状态1且其日期在日期范围检查之前 下一行,如果它的日期在日期范围内

declare @range_st date = '2017-01-01'
declare @range_en date = '2017-12-31'

select 
  case
    when csh1.status_id=1 and csh1.effective_date<@range_st 
      then 
        case
          when csh2.effective_date between @range_st and @range_en then true
          else false
        end
    else NULL
  end
from company_status_history csh1
  left join company_status_history csh2
    on csh1.id=csh2.id+1

执行第二个标准:

“如果此行为状态1,且其日期在日期范围检查之后 前一行,如果其日期在日期范围内。”

declare @range_st date = '2017-01-01'
declare @range_en date = '2017-12-31'

select 
  case
    when csh1.status_id=1 and csh1.effective_date<@range_st 
      then 
        case
          when csh2.effective_date between @range_st and @range_en then true
          else false
        end
    when csh1.status_id=1 and csh1.effective_date>@range_en 
      then 
        case
          when csh3.effective_date between @range_st and @range_en then true
          else false
        end
    else null -- ¿?
  end
from company_status_history csh1
  left join company_status_history csh2
    on csh1.id=csh2.id+1
  left join company_status_history csh3
    on csh1.id=csh3.id-1

我建议使用CTE和窗口函数行编号。通过这个,您可以找到所需的记录。一个例子:

DECLARE @t TABLE(
  id INT
  ,company_id INT
  ,status_id INT
  ,effective_date DATETIME
)

INSERT INTO @t VALUES
(1, 10, 1, '2016-12-30 00:00:00.000')
,(2, 10, 5, '2017-02-04 00:00:00.000')
,(3, 11, 5, '2017-06-05 00:00:00.000')
,(4, 11, 1, '2018-04-30 00:00:00.000')


DECLARE @StartDate DATETIME = '2017-01-01';
DECLARE @EndDate DATETIME = '2017-12-31';

WITH cte AS(
SELECT *
      ,ROW_NUMBER() OVER (PARTITION BY company_id ORDER BY effective_date) AS rn
  FROM @t
),
cteLeadLag AS(
SELECT c.*, ISNULL(c2.effective_date, c.effective_date) LagEffective, ISNULL(c3.effective_date, c.effective_date)LeadEffective
  FROM cte c
  LEFT JOIN cte c2 ON c2.company_id = c.company_id AND c2.rn = c.rn-1
  LEFT JOIN cte c3 ON c3.company_id = c.company_id AND c3.rn = c.rn+1
)
SELECT 'Included' AS RangeStatus, *
  FROM cteLeadLag
  WHERE status_id = 1
    AND effective_date BETWEEN @StartDate AND @EndDate
UNION ALL
SELECT 'Following' AS RangeStatus, *
  FROM cteLeadLag
  WHERE status_id = 1
    AND effective_date > @EndDate
    AND LagEffective BETWEEN @StartDate AND @EndDate
UNION ALL
SELECT 'Trailing' AS RangeStatus, *
  FROM cteLeadLag
  WHERE status_id = 1
    AND effective_date < @EndDate
    AND LeadEffective BETWEEN @StartDate AND @EndDate

我首先选择所有具有超前和滞后日期的记录,然后对所需时间跨度中包含的内容执行检查。

试试这个,不言自明。回答问题的这一部分:

我想回答一个问题“让所有的公司 至少在2017年1月1日这段时间内处于状态1的某一点上- 2017年12月31日”

1. 如果您希望找到状态1中任何时刻的ID,并且在请求的时间段内有记录:

      SELECT *
FROM company_status_history
WHERE id IN
    ( SELECT Id
     FROM company_status_history
     WHERE status_id=1 )
  AND effective_date BETWEEN '2017-01-01' AND '2017-12-31'

2. 如果要查找状态1中的ID并在该期间内:

     SELECT *
FROM company_status_history
WHERE status_id=1
  AND effective_date BETWEEN '2017-01-01' AND '2017-12-31'