T-SQL query to get desired output-CodePudding

I have 2 below tables called T1 and T2

T1 having data as follows

 DateList
2021-11-06
2021-11-07
2021-11-08
2021-11-09

T2 having data as follows

EmpId   ApplyDate
1   2021-11-07
1   2021-11-09
2   2021-11-09

Now i need t-sql statement that will give date which is not there in T2 for each EmpId. Output should look as below

DateList    EmpId
2021-11-06  1
2021-11-08  1
2021-11-06  2
2021-11-07  2
2021-11-08  2

CodePudding user response：

We can use a cross join between the two tables to generate a reference table containing all date/employee pairs. Then, left anti-join this table to T2 to find all pairs which are not present.

SELECT d.DateList, e.EmpId
FROM T1 d
CROSS JOIN (SELECT DISTINCT EmpId FROM T2) e
LEFT JOIN T2
    ON T2.DateList = d.DateList AND
       T2.EmpId = e.EmpId
WHERE
    T2.DateList IS NULL
ORDER BY
    d.DateList,
    e.EmpId;