1

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
2

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

  • McNets thank you .. This is the expected solution. Explanation is very clear. Only one doubt if we put "cross apply" condition to get distinct "emp_id" under cte expression, will this cause performance issue. since it has to deal with 1000 employees and for every employees it will create 30 calendar days. clear me on this. Rather than that it is perfect answer with clear solution.. – user24393 Mar 17 '17 at 9:08
  • You need to generate the whole calendar before to go further. One option could be a materialized view or a table with all combinations emp_id x day_of_month to avoid the use of this CTE. To improve performance I'd suggest a pre-build calendar table. – McNets Mar 17 '17 at 9:18
  • Yes i have created a procedure and inside it im creating a temp table to pre-build calendar table every time when the procedure invokes and right after creating it, am using the solution query to get the result. And after that dropping the temp table. – user24393 Mar 17 '17 at 9:47
0

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)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.