SELECT e.FullName AS 'EmployeeName', e.EmployeeNumber AS 'EmployeeNumber', d.DepartmentName AS 'Department', lb.Planned AS 'Planned', lb.Casual AS 'Casual', lb.Sick AS 'Sick', lb.LeavesBankID AS 'LeavesBankID' , e.EmployeeID AS 'EmployeeID', --(lb.Sick + lb.Planned + lb.Casual) AS 'Total' , --((lb.Sick + lb.Planned + lb.Casual) - SUM(el.TotalDays)) AS 'TTL', (24 - SUM(el.TotalDays)) AS 'Total' FROM LeaveBank lb INNER JOIN dbo.Employee e ON lb.EmployeeID = e.EmployeeID INNER JOIN department d ON e.DepartmentID = d.DepartmentID INNER JOIN dbo.EmployeeLeaves el ON el.EmployeeID = e.EmployeeID WHERE e.IsActive = 1 AND el.LeaveStatusID= 1 GROUP BY e.FullName,e.EmployeeNumber,d.DepartmentName,lb.Planned,lb.Sick,lb.Casual,lb.LeavesBankID,e.EmployeeID ORDER BY EmployeeID
MAX(el.TotalLeaveDays)
var
This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)