[ms sql] 겹치는 날짜가 있는지 조회하기

- 임시 테이블 @tbl_date 를 생성후, (컬럼, test_id, start_dttm, end_dttm 생성)

@tbl_date에 TB_TEST 테이터를 insert

DECLARE @tbl_date table (TEST_ID VARCHAR(50), start_dttm DATETIME, end_dttm DATETIME);
INSERT INTO @tbl_date
SELECT TEST_ID, START_DTTM, END_DTTM FROM TB_TEST WHERE WORK_SHOP_ID = @V_WORK_SHOP_ID

 

- 변수를 @S @E  선언후 비교할 데이터를 insert

DECLARE @S DATETIME;
DECLARE @E DATETIME;
SET @S = @V_START_DTTM
SET @E = @V_END_DTTM

[참고: @V_START_DTTM,@V_END_DTTM 는 비교하고 싶은 데이터를 받은 변수]

 

- @DATE_CHECK 를 선언하고 @tbl_date 테이블에 들어있는 날짜 들과 비교해서 날짜가 겹치는 데이터를 count 한다. 

@DATE_CHECK 를 조회해보면 테이블에서 지정한 날짜가 겹치는 데이터 ROW 수가 출력된다.

DECLARE @DATE_CHECK TINYINT;
SET @DATE_CHECK =(
SELECT COUNT(*) FROM @tbl_date
WHERE
(
	(
		(@S BETWEEN start_dttm AND end_dttm)
		OR
		(@E BETWEEN start_dttm AND end_dttm)
	)
	OR
	(
		(start_dttm BETWEEN @S AND @E)
		OR
		(end_dttm BETWEEN @S AND @E)
	)	
));

 

풀 SQL

DECLARE @tbl_date table (TEST_ID VARCHAR(50), start_dttm DATETIME, end_dttm DATETIME);
INSERT INTO @tbl_date
SELECT TEST_ID, START_DTTM, END_DTTM FROM TB_TEST WHERE WORK_SHOP_ID = @V_WORK_SHOP_ID

DECLARE @S DATETIME;
DECLARE @E DATETIME;
SET @S = @V_START_DTTM
SET @E = @V_END_DTTM

DECLARE @DATE_CHECK TINYINT;
SET @DATE_CHECK =(
SELECT COUNT(*) FROM @tbl_date
WHERE
(
	(
		(@S BETWEEN start_dttm AND end_dttm)
		OR
		(@E BETWEEN start_dttm AND end_dttm)
	)
	OR
	(
		(start_dttm BETWEEN @S AND @E)
		OR
		(end_dttm BETWEEN @S AND @E)
	)	
));

 

 

[참고: 날짜가 같은 것까지 겹치는 것은 겹치는 것으로 간주 하지 않을때는 BETWEEN 대신 밑의 SQL로 계산한다.]

DECLARE @tbl_date table (TEST_ID VARCHAR(50), start_dttm DATETIME, end_dttm DATETIME);
INSERT INTO @tbl_date
SELECT TEST_ID, START_DTTM, END_DTTM FROM TB_TEST WHERE WORK_SHOP_ID = @V_WORK_SHOP_ID

DECLARE @S DATETIME;
DECLARE @E DATETIME;
SET @S = @V_START_DTTM
SET @E = @V_END_DTTM

DECLARE @DATE_CHECK TINYINT;
SET @DATE_CHECK =(
SELECT COUNT(*) FROM @tbl_date
WHERE
(
	(
		(@S > start_dttm AND @S < end_dttm)
		OR
		(@E > start_dttm AND @E < end_dttm)
	)
	OR
	(
		(start_dttm > @S AND start_dttm < @E)
		OR
		(end_dttm > @S AND end_dttm< @E)
	)	
));