Click here to Skip to main content
15,168,085 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


SELECT SUM(CASE WHEN status = 'P' THEN 1  
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)  
GROUP BY EmpID 


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) =  
N'SELECT *  
FROM (SElECT EmployeeDetails.EmpID,EmployeeDetails.EmpName,EmployeesAttendance.Status,  
DATEPART(DAY, EmployeesAttendance.ReportingDate)as DDate  
FROM EmployeesAttendance Inner Join EmployeeDetails on EmployeesAttendance.EmpID=EmployeeDetails.Empid )  
emp  
PIVOT (MAX(Status) FOR DDate IN (' + @cols + ')) AS stat  
'  
-- Executing the query  
EXEC(@qry)



but i want that format which i mentioned above..please guide thanks....
Posted
Comments
OriginalGriff 21-Jan-19 4:11am
   
And?
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
   
And?
What have you - you yourself, not me or your classmates - tried? What happened when you did?
akhter86 21-Jan-19 6:52am
   
hmmm
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)
Gerry Schmitz 21-Jan-19 17:06pm
   
Dump the records from the first SELECT to see if you're getting what you expect.

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