Compare two tables and find dates present in one and missing in another

Question

I have a month table tbl_month having only one column cal_dates which is list of dates for a month. And another table tbl_employee is having employee_code and attendance_date and it will have entry of employee code and the particular date which he submits the attendance. I need to find only the absent dates of each employee.

 TBL_MONTH               
 -------------           
ID CAL_DATES             
1   2017/01/01           
2   2017/01/02           
3   2017/01/03           
--  --                   
--  --                   
30  2017/01/30    


 TBL_EMPLOYEES              
 -------------           
EMP_ID ATTENDANCE_DATE             
001   2017/01/01           
001   2017/01/02           
002   2017/01/01  

Here employee 001 have not entered attendance for '2017/01/03' and employee 002 have not entered attendance for '2017/01/02' and '2017/01/03'. I want to fetch this missed dates alone. I'm using below query, I missed something here.

DECLARE   @SQLQuery AS NVARCHAR(MAX)
DECLARE   @PivotColumns AS NVARCHAR(MAX)
select @PivotColumns=COALESCE(@PivotColumns + ',','') + convert(varchar(20),CALENDAR_DATE,120)
FROM (SELECT  CAL_DATES FROM [dbo]. [TBL_MONTH]) AS PivotExample
select @PivotColumns

SET @SQLQuery = 'select B.EMP_ID,'+@PivotColumns+'
                from TBL_MONTH A
                RIGHT OUTER JOIN
                TBL_EMPLOYEE B
                WHERE A.CAL_DATES = B.ATTENDANCE_DATE
                PIVOT( max(A.CAL_DATES)
                FOR B.ATTENDANCE_DATES IN 
                ('+@PivotColumns+')
                ) AS P';

SELECT @SQLQuery

Answer 1

As far as you have not provided an expected result I've used this:

emp_id | 01/01/2017 | 02/01/2017 | 03/01/2017 | 04/01/2017 | 05/01/2017 | 06/01/2017 | 07/01/2017
:----- | ---------: | ---------: | ---------: | ---------: | ---------: | ---------: | ---------:
001    |          1 |          1 |          1 |          1 |          0 |          1 |          1
002    |          1 |          1 |          0 |          0 |          1 |          1 |          1
003    |          1 |          0 |          1 |          1 |          1 |          1 |          1

dbfiddle here

Where 1 means attendance and 0 not attendance.

I've used next script to dynamically generate the pivot table.

DECLARE   @SQLQuery AS NVARCHAR(MAX) = '';
DECLARE   @PivotColumns AS NVARCHAR(MAX) = null;

select  @PivotColumns = COALESCE(@PivotColumns + ',','')  + '[' + convert(varchar(20),cal_dates,103) + ']'
FROM    (select cal_dates from tbl_month) m;

SELECT @SQLQuery = 
'with allDates as
 (
 select      te.emp_id, tm.cal_dates
 from        tbl_month tm
 cross apply (select distinct emp_id
              from tbl_employees) te
 )
 select    emp_id,' + @PivotColumns + 
 'from      (select    allDates.emp_id, convert(varchar(20), allDates.cal_dates, 103) as cal_dates, 
                       case when te.attendance_date is null then 0 else 1 end as Assist
                       from      allDates
                       left join tbl_employees te
                       on        te.emp_id = allDates.emp_id
                       and       te.attendance_date = allDates.cal_dates) source
PIVOT
(sum(Assist) FOR cal_dates IN (' + @PivotColumns + ')) piv'

exec (@SQLQuery)

First CTE generates all possible dates using tbl_month, you should set some filter (date between MinDate and MaxDate) or it will use the whole calendar table.

with allDates as
(
select      te.emp_id, tm.cal_dates
from        tbl_month tm
cross apply (select distinct emp_id
             from tbl_employees) te
)

Data source

Using a LEFT JOIN with all possible dates, I've set a mark (0/1) depending on employees attendance.

select    allDates.emp_id, convert(varchar(20), allDates.cal_dates, 103) as cal_dates, 
          case when te.attendance_date is null then 0 else 1 end as Assist
from      allDates
left join tbl_employees te
on        te.emp_id = allDates.emp_id
and       te.attendance_date = allDates.cal_dates

Pivot columns

Keep in mind I've used CONVERT(varchar(20),cal_dates,103), as a result you get a date format as 'DD/MM/YYYY'. If you want to change it, remember to change both, pivot columns generator and data source select.

No attendance only

If you want to fetch 'Non attendance' only, you can filter data source on this way:

select    allDates.emp_id, convert(varchar(20), allDates.cal_dates, 103) as cal_dates, 
          case when te.attendance_date is null then 0 else 1 end as Assist
from      allDates
left join tbl_employees te
on        te.emp_id = allDates.emp_id
and       te.attendance_date = allDates.cal_dates
and       te.attendance_date is null 

Change dates by employess

Once you have defined the dynamic query, you can easily change final output, and interchange dates by employees.

DECLARE   @SQLQuery AS NVARCHAR(MAX) = '';
DECLARE   @PivotColumns AS NVARCHAR(MAX) = null;

select  @PivotColumns = COALESCE(@PivotColumns + ',','')  + '[' + emp_id + ']'
FROM    (select distinct emp_id from tbl_employees) m;

SELECT @SQLQuery = 
'with allDates as
 (
 select      te.emp_id, tm.cal_dates
 from        tbl_month tm
 cross apply (select distinct emp_id
              from tbl_employees) te
 )
 select    cal_dates,' + @PivotColumns + 
 'from      (select    allDates.emp_id, convert(varchar(20), allDates.cal_dates, 103) as cal_dates, 
                       case when te.attendance_date is null then 0 else 1 end as Assist
             from      allDates
             left join tbl_employees te
             on        te.emp_id = allDates.emp_id
             and       te.attendance_date = allDates.cal_dates) source
  PIVOT
  (sum(Assist) FOR emp_id IN (' + @PivotColumns + ')) piv'

exec (@SQLQuery)

This is the result:

cal_dates  | 001 | 002 | 003
:--------- | --: | --: | --:
01/01/2017 |   1 |   1 |   1
02/01/2017 |   1 |   1 |   0
03/01/2017 |   1 |   0 |   1
04/01/2017 |   1 |   0 |   1
05/01/2017 |   0 |   1 |   1
06/01/2017 |   1 |   1 |   1
07/01/2017 |   1 |   1 |   1

dbfiddle here

Answer 2

You didn't really specify what your intended output would look like, but here is something that you can use as an example which you can refine further:

--Setup demo data
IF OBJECT_ID('tempdb..#TblMonth') IS NOT NULL
    drop table #TblMonth
IF OBJECT_ID('tempdb..#TblEmployees') IS NOT NULL
    drop table #TblEmployees

--Load the calendar table for January 2017
DECLARE @StartDate DATE = '2017-01-01'
DECLARE @CutoffDate DATE = '2017-02-01'
CREATE TABLE #TblMonth (CAL_DATES DATE)
INSERT #TblMonth ([cal_dates])
SELECT d
FROM (
    SELECT d = DATEADD(DAY, rn - 1, @StartDate)
    FROM (
        SELECT TOP (DATEDIFF(DAY, @StartDate, @CutoffDate)) rn = ROW_NUMBER() OVER (
                ORDER BY s1.[object_id]
                )
        FROM sys.all_objects AS s1
        CROSS JOIN sys.all_objects AS s2
        -- on my system this would support > 5 million days
        ORDER BY s1.[object_id]
        ) AS x
    ) AS y;

--Create some employees
CREATE TABLE #TblEmployees (emp_id int, ATTENDANCE_DATES DATE)
INSERT INTO #TblEmployees 
VALUES(1,'2017-01-01'),(1,'2017-01-02'),(1,'2017-01-05'),(2,'2017-01-01')

DECLARE @SQLQuery AS NVARCHAR(MAX)
DECLARE @PivotColumns AS NVARCHAR(MAX)

SELECT @PivotColumns = 
    COALESCE(@PivotColumns + ',', '') + '[' + convert(VARCHAR(20), CAL_DATES, 120) + ']'
FROM (
    SELECT CAL_DATES
    FROM #TblMonth
    ) AS PivotExample

SET @SQLQuery = '
;WITH CTE_EMPLOYEES AS 
(
SELECT 
    B.EMP_ID AS EMP_ID, 
    ATTENDANCE_DATES 
FROM #TBLMONTH A RIGHT OUTER JOIN 
#TBLEMPLOYEES B ON 
A.CAL_DATES = B.ATTENDANCE_DATES 
GROUP BY B.EMP_ID, B.ATTENDANCE_DATES
)

select EMP_ID,' + 
@PivotColumns + 
' from CTE_EMPLOYEES PIVOT(max(ATTENDANCE_DATES) FOR ATTENDANCE_DATES IN (' + @PivotColumns + ') ) AS P';

--print @SqlQuery
exec (@SQLQuery)