Click here to Skip to main content
15,940,550 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
I want this report of employee register.
 Date	1 	 2	 3  4  Total  Absent    Present	 Leave 

 Emp1	 A	 P	 P  PL   4      1	   2	   1
 Empi	 P	 A	 P  P    4      1	   3	   0 

i have two table

one is employee attendance (EmpID,INTIME,OUTTIME,ReportingDate,Status)
second is Employee Leaves (Empid,LeaveType(PL,CL,SL),StartDate,Endate,Noofday)

What I have tried:

First query is getting total of Present,Absent,HD,TDay in Employee Attendance table

WHEN status = 'HD' THEN 0.5 WHEN status = 'A' THEN 0 END) AS [T.P],  
SUM(CASE WHEN status = 'A' THEN 1 WHEN status = 'HD' THEN 0.5 END) AS [A],  
SUM(CASE WHEN status = 'P' THEN 1  
WHEN status = 'HD' THEN 1 WHEN status = 'A' THEN 1 END) AS [TDay ]  
FROM EmployeesAttendance  
--WHERE (ReportingDate BETWEEN @StartDate AND @Enddate)  

Second Query is pivot table,converting employee attendance col into rows.

SELECT DISTINCT ReportingDate INTO #Dates  
FROM EmployeesAttendance  
ORDER BY ReportingDate  
DECLARE @cols NVARCHAR(4000)  
SELECT @cols = COALESCE(@cols + ',[' + CONVERT(varchar, DATEPART(DAY, ReportingDate), 112)  
+ ']','[' + CONVERT(varchar,DATEPART(DAY, ReportingDate), 112) + ']')  
FROM #Dates  
ORDER BY ReportingDate  
DECLARE @qry NVARCHAR(4000) =  
FROM (SElECT EmployeeDetails.EmpID,EmployeeDetails.EmpName,EmployeesAttendance.Status,  
DATEPART(DAY, EmployeesAttendance.ReportingDate)as DDate  
FROM EmployeesAttendance Inner Join EmployeeDetails on EmployeesAttendance.EmpID=EmployeeDetails.Empid )  
PIVOT (MAX(Status) FOR DDate IN (' + @cols + ')) AS stat  
-- Executing the query  

but i want that format which i mentioned above..please guide thanks....
OriginalGriff 21-Jan-19 4:11am    
What have you tried?
Where are you stuck?
What help do you need?
akhter86 21-Jan-19 5:31am    
i want to mentioned leave type(PL,CL,SL) in attendance register ,i have created separate employee leave table ,here i stuck that how this table will join in employee attendance.
OriginalGriff 21-Jan-19 5:47am    
What have you - you yourself, not me or your classmates - tried? What happened when you did?
akhter86 21-Jan-19 6:52am    
CHill60 21-Jan-19 5:31am    
Are you and this member Member 12314309 - Professional Profile[^] one and the same? If so, please stop posting questions under both profiles.
If not, do realise that your tutor will notice that you have copied each others work.
Either way, heed the advice from MadMyche on How employee leaves day mentioned in attendance register[^] (@SLFC-Mike)

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