Click here to Skip to main content
16,016,076 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I Need to create a pivot table in which first column contain the employee name and next column header contain the all the date of a month and last column will show how many present days

can you help me

SQL
ALTER procedure [dbo].[EmpAttandance] @iMon int,@iyear int
as
begin
-- Getting all distinct dates into a temporary table #Dates
SELECT DISTINCT ADate INTO #Dates
FROM AttandanceTran where DATEPART(MM,ADate) =@iMon and DATEPART(YYYY,ADate)=@iyear
ORDER BY ADate


-- The number of days will be dynamic. So building
-- a comma seperated value string from the dates in #Dates
DECLARE @cols NVARCHAR(4000)
SELECT  @cols = COALESCE(@cols + ',[' + CONVERT(varchar, Adate, 106)
				+ ']','[' + CONVERT(varchar, Adate, 106) + ']')
FROM    #Dates
ORDER BY Adate

-- Building the query with dynamic dates
DECLARE @qry NVARCHAR(4000)
SET @qry =
'SELECT * FROM
(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)) + ' )emp
PIVOT (Max(Type) FOR ADate IN (' + @cols + '))  AS Type'


-- Executing the query
EXEC(@qry)


-- Dropping temporary tables
DROP TABLE #Dates

end
Posted
Updated 9-Apr-14 20:41pm
v2
Comments
Sandip Paul 491984 10-Apr-14 2:55am    
I need to show number of present days!!!
Sandip Paul 491984 10-Apr-14 2:56am    
count(Type) gives me total of Present and Absent days.I need only present days

1 solution

Main note is: Replace relationship based on WHERE clause with JOIN statement
Replace:
SQL
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:
SQL
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[^]
 
Share this answer
 
Comments
Sandip Paul 491984 10-Apr-14 3:25am    
I have tried this but it gives sum of present days and absent days .I need only sum of present days
Sandip Paul 491984 10-Apr-14 3:26am    
count(at.Type) gives the total of present days and absent days.I need count of present days

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900