by 수학일 [SQL Query] 일자범위 중복제거 최신화 mssql [2021.02.09 16:57:15]
DECLARE @TABLE TABLE
(
IDX BIGINT,
START_DATE NVARCHAR(10),
END_DATE NVARCHAR(10),
DATA INT
)
INSERT INTO @TABLE
SELECT 1, '2021-01-01', '2021-03-31', 1000
UNION ALL
SELECT 2, '2021-02-01', '2021-05-31', 1000
UNION ALL
SELECT 3, '2021-05-01', '2021-07-31', 3000
UNION ALL
SELECT 4, '2021-07-01', '2021-08-30', 4000
UNION ALL
SELECT 5, '2021-10-01', '2022-01-15', 5000
해당 테이블의 데이터를 IDX가 큰 데이터를 우선으로 하여 정리하고싶습니다.
위 데이터를 정리하였을 때
시작일자 | 종료일자 | DATA |
---|---|---|
2021-01-01 | 2021-05-31 | 1000 |
2021-05-01 | 2021-06-31 | 3000 |
2021-07-01 | 2021-08-30 | 4000 |
2021-10-01 | 2022-01-15 | 5000 |
같은 DATA의 날짜가 겹치면 하나로 합치고
다른 DATA의 날짜가 겹치면 IDX가 큰것을 기준으로 조회되도록 하는 쿼리를 짜려고하는데
너무 어렵네요..
도와주실수있으신가요?
아래처럼 짜봤는데.. 괜히 어렵게 짠거 아닌지 모르겠군요
select start_date, end_date, data from ( select start_date, first_value(end_date) over (partition by data order by end_date desc) end_date, row_number() over (partition by data order by idx) rn, data from @TABLE) tmp where rn = 1
위와 같은 데이터만 있다면 아래처럼 해도 되겠지만 ^^;;
select min(start_date), max(end_date), data from @TABLE group by data
WITH t AS ( SELECT 1 idx, '2021-01-01' start_date, '2021-03-31' end_date, 1000 data UNION ALL SELECT 2, '2021-02-01', '2021-05-31', 1000 UNION ALL SELECT 3, '2021-05-01', '2021-07-31', 3000 UNION ALL SELECT 4, '2021-07-01', '2021-08-30', 4000 UNION ALL SELECT 5, '2021-10-01', '2022-01-15', 5000 UNION ALL SELECT 6, '2021-02-02', '2021-02-03', 1000 UNION ALL SELECT 7, '2021-02-05', '2021-02-07', 1000 UNION ALL SELECT 8, '2021-04-01', '2021-05-30', 1000 UNION ALL SELECT 9, '2021-06-01', '2021-06-30', 1000 ) SELECT MIN(start_date) start_date , MAX(end_date) end_date , data FROM (SELECT idx, start_date, end_date, data , SUM(flag) OVER(PARTITION BY data ORDER BY start_date, end_date) grp FROM (SELECT idx, start_date, end_date, data , CASE WHEN MAX(end_date) OVER(PARTITION BY data ORDER BY start_date, end_date ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) >= start_date THEN 0 ELSE 1 END flag FROM t ) a ) a GROUP BY data, grp ;