Main note is:
Replace relationship based on WHERE
clause with JOIN
statement
Replace:
SELECT Empcode,Name,Type,Adate,count(type) over(PARTITION BY empcode) AS Tot
FROM AttandanceTran,Empmast where Empmast.ECode=AttandanceTran.Ecode
and DATEPART(MM,ADate)=' + CAST(@imon as varchar(10)) + '
and DATEPART(YYYY,ADate)=' + CAST(@iyear as varchar(10))
with:
SELECT em.Empcode, em.Name, em.Type, at.Adate, COUNT(at.type) AS Tot
FROM AttandanceTran AS at INNER JOIN Empmast AS em ON at.ECode=em.ECode
WHERE DATEPART(MM,ADate)=@iMon AND DATEPART(YYYY,ADate)=@iyear
Do you see the difference? I'm using
JOIN[
^] statement to create relationship between both tables. Have a look here:
Visual Representation of SQL Joins[
^]
If you would like to get Grand total for column and/or row, please see:
SQL - Pivot with Grand Total Column and Row[
^]
Simple Way To Use Pivot In SQL Query[
^]