Sql-server – Compare two tables and find dates present in one and missing in another

join;pivotsql server

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

Best Answer

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

Related Question