Hello Experts,
I am writing a script to compare range from 2 tables and wanted to find the range which is present in 1 table only.
For Example,
CREATE TABLE #T1(Emp_ID INTEGER, START_T INTEGER, END_T INTEGER)
INSERT INTO #T1 VALUES (1,2,3), (1,3,6),(1,6,8)
CREATE TABLE #T2(Emp_ID INTEGER, START_T INTEGER, END_T INTEGER)
INSERT INTO #T2 VALUES (1,1,2), (1,3,5),(1,7,8)
SELECT * FROM #T1
SELECT * FROM #T2
Table T1 and T2 hold the working hours for Emp_ID1.
I want to fetch the following missing ranges :
Range START_T (1) END_T(2) is not present in Table T1
Range START_T (2) END_T(3) & START_T (5) END_T(7) is not present in Table T2
CREATE TABLE #T1
( Emp_ID integer
, START_T integer
, END_T integer
);
INSERT INTO #T1
VALUES
(1,2,3)
, (1,3,6)
, (1,6,8);
CREATE TABLE #T2
( Emp_ID integer
, START_T integer
, END_T integer
);
INSERT INTO #T2
VALUES
(1,1,2)
, (1,3,5)
, (1,7,8);
SELECT t1.Emp_ID t1_Emp_ID
, t1.START_T t1_START_T
, t1.END_T t1_END_T
, t2.Emp_ID t2_Emp_ID
, t2.START_T t2_START_T
, t2.END_T t2_END_T
FROM
#T1 t1
FULL OUTER JOIN
#T2 t2 ON (t1.START_T BETWEEN t2.START_T AND t2.END_T
AND t1.END_T BETWEEN t2.START_T AND t2.END_T)
OR (t2.START_T BETWEEN t1.START_T AND t1.END_T
AND t2.END_T BETWEEN t1.START_T AND t1.END_T)
WHERE t1.Emp_ID IS NULL OR t2.Emp_ID IS NULL;
DROP TABLE #T1;
DROP TABLE #T2;