I have two tables, Employees and EmployeeVacations. I am trying to do a SQL query to get the sum of how much vacation time each employee has taken and their current balance as of today. Here is my current SQL query:
ev.Value AS '10/1 Balance',
WHERE CreationDate >= '2016-10-01'
AND Vacation_Type = 'Taken'
AND Vacation_Kind = 'AL'
AND EmployeeId = 13)
FROM employees e,
WHERE e.Id = ev.EmployeeId
AND ev.IsHistory = 0
AND ev.Vacation_Type = 'Forward'
AND ev.Vacation_Kind = 'AL'
AND EmployeeId = 13
ORDER BY e.LastName, e.FirstName
Just guessing that you also might want the sum rather than the single forward records... Here is a query that aggregates
select e.pin, e.firstname, e.lastname, e.uniform, e.al_cap, ev.forward_sum as "10/1 balance", ev.taken_sum as taken from employee e left join ( select employeeid, sum(case when vacation_type = 'Forward' and ishistory = 0 then value else 0 end) as forward_sum, sum(case when vacation_type = 'Taken' and creationdate >= '20161001' then value else 0 end) as taken_sum, from employeevacations where vacation_kind = 'AL' group by employeeid ) ev on ev.employeeid = e.employeeid order by e.lastname, e.firstname;