0
Answer

How to merge Case when condition in pivot Table?

Akhter HUssain

Akhter HUssain
6y
622
1
 I want this report of employee register.
 
 Date1  2 34 
 Total 
 Absent Present Leave
 Emp1 A P P  L  4 1 2 1
 Empi P A PP 41 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)
 
i have two query now i want to merge them ,
First query is getting total of Present,Absent,HD,TDay  
  1. SELECT SUM(CASE WHEN status = 'P' THEN 1  
  2. WHEN status = 'HD' THEN 0.5 WHEN status = 'A' THEN 0 END) AS [T.P],  
  3. SUM(CASE WHEN status = 'A' THEN 1 WHEN status = 'HD' THEN 0.5 END) AS [A],  
  4. SUM(CASE WHEN status = 'P' THEN 1  
  5. WHEN status = 'HD' THEN 1 WHEN status = 'A' THEN 1 END) AS [TDay ]  
  6. FROM EmployeesAttendance  
  7. --WHERE (ReportingDate BETWEEN @StartDate AND @Enddate)  
  8. GROUP BY EmpID  
Second Query is pivot table,converting employee attendance col into rows.
  1. SELECT DISTINCT ReportingDate INTO #Dates  
  2. FROM EmployeesAttendance  
  3. ORDER BY ReportingDate  
  4. DECLARE @cols NVARCHAR(4000)  
  5. SELECT @cols = COALESCE(@cols + ',[' + CONVERT(varchar, DATEPART(DAY, ReportingDate), 112)  
  6. + ']','[' + CONVERT(varchar,DATEPART(DAY, ReportingDate), 112) + ']')  
  7. FROM #Dates  
  8. ORDER BY ReportingDate  
  9. DECLARE @qry NVARCHAR(4000) =  
  10. N'SELECT *  
  11. FROM (SElECT EmployeeDetails.EmpID,EmployeeDetails.EmpName,EmployeesAttendance.Status,  
  12. DATEPART(DAY, EmployeesAttendance.ReportingDate)as DDate  
  13. FROM EmployeesAttendance Inner Join EmployeeDetails on EmployeesAttendance.EmpID=EmployeeDetails.Empid )  
  14. emp  
  15. PIVOT (MAX(Status) FOR DDate IN (' + @cols + ')) AS stat  
  16. '  
  17. -- Executing the query  
  18. EXEC(@qry)
Next Recommended Forum