i want to count present and absent of each employee in last column
query is below which i am using
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)
)
--INSERT INTO @T_days
SELECT DATENAME(DAY, [Date]) AS [TDateName] ,
LEFT(DATENAME(WEEKDAY, [Date]), 3) AS [TWeekDay] ,
CONVERT(DATE, [Date]) AS [TDate]
INTO #Temp_days
FROM DaysInMonth
WHERE --MONTH(Date) = MONTH(@date)
--AND
( @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 dbo.employees ep
INNER JOIN dbo.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 dbo.Tran_DeviceAttRec_New T
INNER JOIN ( SELECT *
FROM dbo.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;
--,k
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 *
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
--PRINT ( @query )
DROP TABLE #Temp_days
DROP TABLE #AttRec
