I have a table which contains total number of leaves for all the active employees.

See more:

SQL

server

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

Here i want to replace 24 for all the employees with the total number of leaves they get. Some have 24, some have 16, so i want the exact number of leaves to be read from the leavebank table and used here instead of 24.