As Per your Sql query i make same temporary table and created same test query where it calculate the total of Absent and Present. Added the sub query within same select statement where you can use result for Pivot. Refer the test query and implement it by your logic.
SQL
CREATE TABLE #DaysInMonth([Date] DATETIME)
CREATE TABLE #employees (Card_No NVARCHAR(50),department_id INT,employee_fname VARCHAR(50),employee_code VARCHAR(50),designation_id INT)
CREATE TABLE #Tran_DeviceAttRec_New (Card_Number NVARCHAR(50),Att_PunchRecDate DATETIME,Punch_month DATETIME)
CREATE TABLE #emp_common_master(id INT,name VARCHAR(50))
INSERT INTO #DaysInMonth VALUES('2017-04-01'),('2017-04-02'),('2017-04-03'),('2017-04-04'),('2017-04-05'),('2017-04-06'),('2017-04-07'),('2017-04-08'),('2017-04-09'),('2017-04-10'),('2017-04-11'),('2017-04-12'),('2017-04-13'),('2017-04-14'),('2017-04-15'),('2017-04-16'),('2017-04-17'),('2017-04-18'),('2017-04-19'),('2017-04-20'),('2017-04-21'),('2017-04-22'),('2017-04-23'),('2017-04-24'),('2017-04-25'),('2017-04-26'),('2017-04-27'),('2017-04-28'),('2017-04-29'),('2017-04-30')
INSERT INTO #emp_common_master VALUES (1 , 'common master'),(2 , 'common master 2'),(3 , 'common master 3'),(4 , 'common master 4'),(5 , 'common master 5')
INSERT INTO #employees VALUES('111',1,'F Name1','E001',1),('112',1,'F Name2','E002',1),('113',2,'F Name3','E003',2),('114',2,'F Name4','E004',2),('115',3,'F Name5','E005',3),('116',3,'F Name6','E006',3)
INSERT INTO #Tran_DeviceAttRec_New VALUES('111','2017-04-01','2017-04-01'),('111','2017-04-02','2017-04-02'),('111','2017-04-03','2017-04-03'),('111','2017-04-04','2017-04-04'),('111','2017-04-05','2017-04-05'),('111','2017-04-06','2017-04-06'),('111','2017-04-07','2017-04-07'),('111','2017-04-08','2017-04-08'),('111','2017-04-10','2017-04-10'),('111','2017-04-11','2017-04-11'),('111','2017-04-12','2017-04-12'),('111','2017-04-13','2017-04-13'),('111','2017-04-14','2017-04-14'),('111','2017-04-15','2017-04-15'),('111','2017-04-17','2017-04-17'),('111','2017-04-18','2017-04-18'),('111','2017-04-19','2017-04-19'),('111','2017-04-20','2017-04-20')
,('112','2017-04-01','2017-04-01'),('112','2017-04-02','2017-04-02'),('112','2017-04-03','2017-04-03'),('112','2017-04-04','2017-04-04'),('112','2017-04-05','2017-04-05'),('112','2017-04-06','2017-04-06'),('112','2017-04-07','2017-04-07'),('112','2017-04-08','2017-04-08'),('112','2017-04-10','2017-04-10'),('112','2017-04-11','2017-04-11'),('112','2017-04-12','2017-04-12'),('112','2017-04-13','2017-04-13'),('112','2017-04-14','2017-04-14'),('112','2017-04-15','2017-04-15'),('112','2017-04-17','2017-04-17'),('112','2017-04-18','2017-04-18'),('112','2017-04-19','2017-04-19'),('112','2017-04-20','2017-04-20')
,('113','2017-04-01','2017-04-01'),('113','2017-04-02','2017-04-02'),('113','2017-04-03','2017-04-03'),('113','2017-04-04','2017-04-04'),('113','2017-04-05','2017-04-05'),('113','2017-04-06','2017-04-06'),('113','2017-04-07','2017-04-07'),('113','2017-04-08','2017-04-08')
,('114','2017-04-01','2017-04-01'),('114','2017-04-02','2017-04-02'),('114','2017-04-03','2017-04-03'),('114','2017-04-04','2017-04-04'),('114','2017-04-05','2017-04-05'),('114','2017-04-06','2017-04-06'),('114','2017-04-07','2017-04-07'),('114','2017-04-14','2017-04-14'),('114','2017-04-15','2017-04-15'),('114','2017-04-17','2017-04-17'),('114','2017-04-18','2017-04-18')
,('115','2017-04-01','2017-04-01'),('115','2017-04-02','2017-04-02'),('115','2017-04-03','2017-04-03'),('115','2017-04-13','2017-04-13'),('115','2017-04-14','2017-04-14'),('115','2017-04-15','2017-04-15'),('115','2017-04-17','2017-04-17'),('115','2017-04-18','2017-04-18'),('115','2017-04-19','2017-04-19'),('115','2017-04-20','2017-04-20')
,('116','2017-04-01','2017-04-01'),('116','2017-04-02','2017-04-02'),('116','2017-04-05','2017-04-05'),('116','2017-04-06','2017-04-06'),('116','2017-04-07','2017-04-07'),('116','2017-04-08','2017-04-08'),('116','2017-04-10','2017-04-10'),('116','2017-04-11','2017-04-11'),('116','2017-04-12','2017-04-12'),('116','2017-04-13','2017-04-13'),('116','2017-04-14','2017-04-14'),('116','2017-04-15','2017-04-15'),('116','2017-04-17','2017-04-17'),('116','2017-04-18','2017-04-18'),('116','2017-04-19','2017-04-19'),('116','2017-04-20','2017-04-20')
DECLARE @StartDate DATETIME = '04/04/2017'
,@EndDate DATETIME = '04/30/2017'
,@CardNo NVARCHAR(50) = ''
,@Depid INT = 0
DECLARE @date DATETIME;
SET @date = DATEADD(MONTH, DATEDIFF(MONTH, 0, @StartDate), 0);
WITH DaysInMonth
AS (SELECT @date AS DATE
UNION ALL
SELECT DATEADD(dd, 1, DATE)
FROM #DaysInMonth
WHERE MONTH(Date) = MONTH(@date)
OR Date <= CONVERT(DATE, @EndDate))
SELECT DATENAME(DAY, [Date]) AS [TDateName]
,LEFT(DATENAME(WEEKDAY, [Date]), 3) AS [TWeekDay]
,CONVERT(DATE, [Date]) AS [TDate]
INTO #Temp_days
FROM #DaysInMonth
WHERE (@StartDate = '' OR Date >= @StartDate)
AND (@EndDate = '' OR Date <= @EndDate)
SELECT *
INTO #AttRec
FROM (SELECT tblnew.TDateName
,tblnew.employee_code
,tblnew.employee_fname
,tblnew.card_no
,tblnew.TWeekDay
,tblnew.TDate
,atttbl.Card_Number
,atttbl.Att_PunchRecDate
,atttbl.Punch_month
,tblnew.DeptName
,tblnew.Designation
FROM (SELECT *
FROM (SELECT employee_code
,employee_fname
,card_no
, UPPER(dp.name) AS DeptName
,UPPER(desig.name) AS Designation
FROM #employees ep
INNER JOIN #emp_common_master dp ON dp.id = ep.department_id
LEFT JOIN #emp_common_master desig ON desig.id = ep.designation_id
WHERE (card_no = @CardNo OR @CardNo = '')
AND (dp.id = @Depid OR @Depid = 0)) tblemp
CROSS APPLY #Temp_days) tblnew
LEFT JOIN (SELECT DISTINCT T.Card_Number
,T.Att_PunchRecDate
,T.Punch_month
FROM #Tran_DeviceAttRec_New T
INNER JOIN (SELECT * FROM #employees
WHERE (card_no = @CardNo OR @CardNo = '')) E
ON T.Card_Number = E.card_no
LEFT JOIN #emp_common_master D ON D.id = E.department_id
) atttbl
ON CONVERT(VARCHAR(30), atttbl.Punch_month, 103) = CONVERT(VARCHAR(30), tblnew.TDate, 103)
AND atttbl.Card_Number = tblnew.card_no
) tblfinalrecord;
DECLARE @Days VARCHAR(MAX)= '';
SELECT @Days = @Days + ' ' + '[' + CONVERT(VARCHAR(20), [TDateName]) + '],'
FROM #Temp_days;
--Remove last ,
SET @Days = CASE @Days
WHEN NULL THEN NULL
ELSE (CASE LEN(@Days)
WHEN 0 THEN @Days
ELSE LEFT(@Days, LEN(@Days) - 1)
END)
END;
--SELECT @Days += ',[Total]';
DECLARE @query NVARCHAR(MAX)
SELECT @query = 'SELECT *
,(SELECT COUNT(*) FROM #AttRec as AT WHERE AT.card_no = [Card Number] AND (AT.Att_PunchRecDate IS NOT NULL OR (CAST(AT.TDate AS DATE) < CAST(GETDATE() AS DATE) AND TWeekDay <> ''Sun'' AND AT.Att_PunchRecDate IS NULL))) AS Total
,(SELECT COUNT(*) FROM #AttRec as AT WHERE AT.card_no = [Card Number] AND (Att_PunchRecDate is not null AND TWeekDay<> ''Sun'')) AS TotalPresent
,(SELECT COUNT(*) FROM #AttRec as AT WHERE AT.card_no = [Card Number] AND (TWeekDay<>''Sun'' AND Att_PunchRecDate is null AND CAST(TDate AS DATE) < CAST(GETDATE() AS DATE))) AS TotalAbsent
FROM (SELECT dense_rank() OVER (ORDER BY card_no) as [Sl. No.]
,card_no as [Card Number]
,employee_fname as [Employee Name]
,DeptName as [Department]
,Designation
,case when Att_PunchRecDate is not null then ''P''
when TWeekDay=''Sun'' then ''W''
WHEN CAST(TDate AS DATE) >= CAST(GETDATE() AS DATE) then '''' else ''A'' end as val
,TDateName
,'''' as Remark
FROM #AttRec) src
pivot
(
MAX(val)
for TDateName in (' + @Days + ')
) piv;'
EXEC sp_executesql @query
DROP TABLE #Temp_days
DROP TABLE #AttRec
DROP Table #DaysInMonth
DROP TABLE #employees
DROP TABLE #Tran_DeviceAttRec_New
DROP TABLE #emp_common_master
OUTPUT
Sl. No. |
Card Number |
Employee Name |
Department |
Designation |
Remark |
4 |
5 |
6 |
7 |
8 |
9 |
10 |
11 |
12 |
13 |
14 |
15 |
16 |
17 |
18 |
19 |
20 |
21 |
22 |
23 |
24 |
25 |
26 |
27 |
28 |
29 |
30 |
Total |
TotalPresent |
TotalAbsent |
1 |
111 |
F Name1 |
COMMON MASTER |
COMMON MASTER |
|
P |
P |
P |
P |
P |
W |
P |
P |
P |
P |
P |
P |
W |
P |
P |
P |
P |
|
|
W |
|
|
|
|
|
|
W |
15 |
15 |
0 |
2 |
112 |
F Name2 |
COMMON MASTER |
COMMON MASTER |
|
P |
P |
P |
P |
P |
W |
P |
P |
P |
P |
P |
P |
W |
P |
P |
P |
P |
|
|
W |
|
|
|
|
|
|
W |
15 |
15 |
0 |
3 |
113 |
F Name3 |
COMMON MASTER 2 |
COMMON MASTER 2 |
|
P |
P |
P |
P |
P |
W |
A |
A |
A |
A |
A |
A |
W |
A |
A |
A |
A |
|
|
W |
|
|
|
|
|
|
W |
15 |
5 |
10 |
4 |
114 |
F Name4 |
COMMON MASTER 2 |
COMMON MASTER 2 |
|
P |
P |
P |
P |
A |
W |
A |
A |
A |
A |
P |
P |
W |
P |
P |
A |
A |
|
|
W |
|
|
|
|
|
|
W |
15 |
8 |
7 |
5 |
115 |
F Name5 |
COMMON MASTER 3 |
COMMON MASTER 3 |
|
A |
A |
A |
A |
A |
W |
A |
A |
A |
P |
P |
P |
W |
P |
P |
P |
P |
|
|
W |
|
|
|
|
|
|
W |
15 |
7 |
8 |
6 |
116 |
F Name6 |
COMMON MASTER 3 |
COMMON MASTER 3 |
|
A |
P |
P |
P |
P |
W |
P |
P |
P |
P |
P |
P |
W |
P |
P |
P |
P |
|
|
W |
|
|
|
|
|
|
W |
15 |
14 |
1 |