연속 날짜 범위를 결합

SQL 서버 2008 R2를 사용하여,

나는 일단 날짜가 다음 시작 날짜 옆에 주어진 최대 날짜 범위에 날짜 범위를 결합하기 위해 노력하고있어.

데이터는 다른 고용에 관한 것입니다. 일부 직원들은 고용을 종료 수 있으므로 나중에 합류했다. 이들 두 가지의 고용 (예를 들어, ID 5)로 계산한다. 어떤 사람들은이 경우에는 총 한 고용 (예를 들어 ID 30)로 고려되어야한다, (ENDDATE 및 STARTDATE 목 - 투 - 목) 서로 후 실행, 고용의 다른 유형이있다.

종료되지 않은 고용 기간이 null 인 ENDDATE 있습니다.

몇 가지 예는 아마 계몽 :

declare @t as table  (employmentid int, startdate datetime, enddate datetime)

insert into @t values
(5, '2007-12-03', '2011-08-26'),
(5, '2013-05-02', null),
(30, '2006-10-02', '2011-01-16'),
(30, '2011-01-17', '2012-08-12'),
(30, '2012-08-13', null),
(66, '2007-09-24', null)

-- expected outcome
EmploymentId StartDate   EndDate
5            2007-12-03  2011-08-26
5            2013-05-02  NULL
30           2006-10-02  NULL
66           2007-09-24  NULL

나는 다른 '섬 및 갭 "기술을 노력했지만이 하나를 깰 수 없었다.

해결법

==============================

1.당신이 날짜 '31211231'의 내 사용으로 볼 수있는 이상한 비트 그냥 "노 종료 날짜"시나리오를 처리 할 수있는 매우 큰 날짜입니다. 내가 범위를 결합하는 간단한 재귀 공통 테이블 표현식을 사용했습니다, 그래서 난 당신이 정말 직원 당 많은 날짜 범위가되지 않습니다 가정 하였다.

당신이 날짜 '31211231'의 내 사용으로 볼 수있는 이상한 비트 그냥 "노 종료 날짜"시나리오를 처리 할 수있는 매우 큰 날짜입니다. 내가 범위를 결합하는 간단한 재귀 공통 테이블 표현식을 사용했습니다, 그래서 난 당신이 정말 직원 당 많은 날짜 범위가되지 않습니다 가정 하였다.

더 빨리 실행하려면 시작 앵커 쿼리 (인당) 이전 범위까지 연결되지 않습니다 만 날짜를 유지합니다. 나머지는 그냥 나무가 걸어 날짜 범위 및 범위를 증가하고있다. 마지막 GROUP BY는 ANCHOR (employmentid, STARTDATE) 조합을 시작별로 구축에만 최대 날짜 범위를 유지합니다.

SQL 바이올린

MS SQL 서버 2008 스키마 설정 :

create table Tbl (
  employmentid int,
  startdate datetime,
  enddate datetime);

insert Tbl values
(5, '2007-12-03', '2011-08-26'),
(5, '2013-05-02', null),
(30, '2006-10-02', '2011-01-16'),
(30, '2011-01-17', '2012-08-12'),
(30, '2012-08-13', null),
(66, '2007-09-24', null);

/*
-- expected outcome
EmploymentId StartDate   EndDate
5            2007-12-03  2011-08-26
5            2013-05-02  NULL
30           2006-10-02  NULL
66           2007-09-24  NULL
*/

쿼리 1 :

;with cte as (
   select a.employmentid, a.startdate, a.enddate
     from Tbl a
left join Tbl b on a.employmentid=b.employmentid and a.startdate-1=b.enddate
    where b.employmentid is null
    union all
   select a.employmentid, a.startdate, b.enddate
     from cte a
     join Tbl b on a.employmentid=b.employmentid and b.startdate-1=a.enddate
)
   select employmentid,
          startdate,
          nullif(max(isnull(enddate,'32121231')),'32121231') enddate
     from cte
 group by employmentid, startdate
 order by employmentid

결과 :

| EMPLOYMENTID |                        STARTDATE |                       ENDDATE |
-----------------------------------------------------------------------------------
|            5 |  December, 03 2007 00:00:00+0000 | August, 26 2011 00:00:00+0000 |
|            5 |       May, 02 2013 00:00:00+0000 |                        (null) |
|           30 |   October, 02 2006 00:00:00+0000 |                        (null) |
|           66 | September, 24 2007 00:00:00+0000 |                        (null) |

==============================

2.

SET NOCOUNT ON

DECLARE @T TABLE(ID INT,FromDate DATETIME, ToDate DATETIME)

INSERT INTO @T(ID,FromDate,ToDate)
SELECT 1,'20090801','20090803' UNION ALL
SELECT 2,'20090802','20090809' UNION ALL
SELECT 3,'20090805','20090806' UNION ALL
SELECT 4,'20090812','20090813' UNION ALL
SELECT 5,'20090811','20090812' UNION ALL
SELECT 6,'20090802','20090802'


SELECT ROW_NUMBER() OVER(ORDER BY s1.FromDate) AS ID,
       s1.FromDate, 
       MIN(t1.ToDate) AS ToDate 
FROM @T s1 
INNER JOIN @T t1 ON s1.FromDate <= t1.ToDate 
  AND NOT EXISTS(SELECT * FROM @T t2 
                 WHERE t1.ToDate >= t2.FromDate
                   AND t1.ToDate < t2.ToDate) 
WHERE NOT EXISTS(SELECT * FROM @T s2 
                 WHERE s1.FromDate > s2.FromDate
                   AND s1.FromDate <= s2.ToDate) 
GROUP BY s1.FromDate 
ORDER BY s1.FromDate

==============================

3.모든 중복 기간을 결합하는 수정 된 스크립트. 예를 들면 01.01.2001-01.01.2010 05.05.2005-05.05.2015

모든 중복 기간을 결합하는 수정 된 스크립트. 예를 들면 01.01.2001-01.01.2010 05.05.2005-05.05.2015

한주기를 줄 것이다 : 01.01.2001-05.05.2015

tbl.enddate 완료해야합니다

;WITH cte
  AS(
SELECT
  a.employmentid
  ,a.startdate
  ,a.enddate
from tbl a
left join tbl c on a.employmentid=c.employmentid
    and a.startdate > c.startdate
    and a.startdate <= dateadd(day, 1, c.enddate)
WHERE c.employmentid IS NULL

UNION all

SELECT
  a.employmentid
  ,a.startdate
  ,a.enddate
from cte a
inner join tbl c on a.startdate=c.startdate
    and (c.startdate = dateadd(day, 1, a.enddate) or (c.enddate > a.enddate and c.startdate <= a.enddate))
)
select distinct employmentid,
          startdate,
          nullif(max(enddate),'31.12.2099') enddate
from cte
group by employmentid, startdate

==============================

4.윈도우 함수보다는 재귀 CTE를 사용하는 다른 솔루션

윈도우 함수보다는 재귀 CTE를 사용하는 다른 솔루션

SELECT 
    employmentid, 
    MIN(startdate) as startdate, 
    NULLIF(MAX(COALESCE(enddate,'9999-01-01')), '9999-01-01') as enddate
FROM (
    SELECT 
        employmentid, 
        startdate, 
        enddate,
        DATEADD(
            DAY, 
            -COALESCE(
                SUM(DATEDIFF(DAY, startdate, enddate)+1) OVER (PARTITION BY employmentid ORDER BY startdate ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING), 
                0
            ),
            startdate
    ) as grp
    FROM @t
) withGroup
GROUP BY employmentid, grp
ORDER BY employmentid, startdate

이것은 모든 연속 행에 대해 동일 할 것 GRP 값을 계산하여 작동합니다. 이것은에 의해 달성된다 :

SELECT *, DATEDIFF(DAY, startdate, enddate)+1 as daysSpanned FROM @t

SELECT *, COALESCE(
    SUM(daysSpanned) OVER (
        PARTITION BY employmentid 
        ORDER BY startdate 
        ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING
    )
    ,0
)  as cumulativeDaysSpanned
FROM (
    SELECT *, DATEDIFF(DAY, startdate, enddate)+1 as daysSpanned FROM @t
) inner1

SELECT *, DATEADD(DAY, -cumulativeDaysSpanned, startdate) as grp
FROM (
    SELECT *, COALESCE(
        SUM(daysSpanned) OVER (
            PARTITION BY employmentid 
            ORDER BY startdate 
            ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING
        )
        ,0
    )  as cumulativeDaysSpanned
    FROM (
        SELECT *, DATEDIFF(DAY, startdate, enddate)+1 as daysSpanned FROM @t
    ) inner1
) inner2

결과에

+--------------+-------------------------+-------------------------+-------------+-----------------------+-------------------------+
| employmentid | startdate               | enddate                 | daysSpanned | cumulativeDaysSpanned | grp                     |
+--------------+-------------------------+-------------------------+-------------+-----------------------+-------------------------+
| 5            | 2007-12-03 00:00:00.000 | 2011-08-26 00:00:00.000 | 1363        | 0                     | 2007-12-03 00:00:00.000 |
+--------------+-------------------------+-------------------------+-------------+-----------------------+-------------------------+
| 5            | 2013-05-02 00:00:00.000 | NULL                    | NULL        | 1363                  | 2009-08-08 00:00:00.000 |
+--------------+-------------------------+-------------------------+-------------+-----------------------+-------------------------+
| 30           | 2006-10-02 00:00:00.000 | 2011-01-16 00:00:00.000 | 1568        | 0                     | 2006-10-02 00:00:00.000 |
+--------------+-------------------------+-------------------------+-------------+-----------------------+-------------------------+
| 30           | 2011-01-17 00:00:00.000 | 2012-08-12 00:00:00.000 | 574         | 1568                  | 2006-10-02 00:00:00.000 |
+--------------+-------------------------+-------------------------+-------------+-----------------------+-------------------------+
| 30           | 2012-08-13 00:00:00.000 | NULL                    | NULL        | 2142                  | 2006-10-02 00:00:00.000 |
+--------------+-------------------------+-------------------------+-------------+-----------------------+-------------------------+
| 66           | 2007-09-24 00:00:00.000 | NULL                    | NULL        | 0                     | 2007-09-24 00:00:00.000 |
+--------------+-------------------------+-------------------------+-------------+-----------------------+-------------------------+

SELECT 
    employmentid, 
    MIN(startdate) as startdate, 
    NULLIF(MAX(COALESCE(enddate,'9999-01-01')), '9999-01-01') as enddate
FROM (
    SELECT *, DATEADD(DAY, -cumulativeDaysSpanned, startdate) as grp
    FROM (
        SELECT *, COALESCE(
            SUM(daysSpanned) OVER (
                PARTITION BY employmentid 
                ORDER BY startdate 
                ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING
            )
            ,0
        )  as cumulativeDaysSpanned
        FROM (
            SELECT *, DATEDIFF(DAY, startdate, enddate)+1 as daysSpanned FROM @t
        ) inner1
    ) inner2
) inner3
GROUP BY employmentid, grp
ORDER BY employmentid, startdate

원하는 결과를 얻으려면

+--------------+-------------------------+-------------------------+
| employmentid | startdate               | enddate                 |
+--------------+-------------------------+-------------------------+
| 5            | 2007-12-03 00:00:00.000 | 2011-08-26 00:00:00.000 |
+--------------+-------------------------+-------------------------+
| 5            | 2013-05-02 00:00:00.000 | NULL                    |
+--------------+-------------------------+-------------------------+
| 30           | 2006-10-02 00:00:00.000 | NULL                    |
+--------------+-------------------------+-------------------------+
| 66           | 2007-09-24 00:00:00.000 | NULL                    |
+--------------+-------------------------+-------------------------+

모두의 제한이 필요는

from https://stackoverflow.com/questions/15783315/combine-consecutive-date-ranges by cc-by-sa and MIT license

'SQL' 카테고리의 다른 글

[SQL] SQL 서버 내부 CLR 어셈블리 C #을 (0)	2020.07.11
[SQL] 어떻게 분할 문자열에 문자로 SQL 서버에서 별도의 컬럼에 (0)	2020.07.11
[SQL] 별칭을 허용 성능에 미치는 영향은 HAVING 절에 사용되는 (0)	2020.07.11
[SQL] 어떻게 동시 쿼리는 MySQL 데이터베이스에서 처리? (0)	2020.07.11
[SQL] 무엇 SQL 데이터베이스는 CHECK 제약 조건에 서브 쿼리를 지원합니까? (0)	2020.07.11

복붙노트

[SQL] 연속 날짜 범위를 결합

연속 날짜 범위를 결합

해결법

2.

3.모든 중복 기간을 결합하는 수정 된 스크립트. 예를 들면 01.01.2001-01.01.2010 05.05.2005-05.05.2015

4.윈도우 함수보다는 재귀 CTE를 사용하는 다른 솔루션

'SQL' 카테고리의 다른 글

티스토리툴바