RickCJ7 RickCJ7 - 1 month ago 15
SQL Question

SQL query within SQL query on sum

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:

SELECT
e.PIN,
e.FirstName,
e.LastName,
e.Uniform,
e.AL_Cap,
ev.Value AS '10/1 Balance',
(SELECT
SUM(value)
FROM EmployeeVacations
WHERE CreationDate >= '2016-10-01'
AND Vacation_Type = 'Taken'
AND Vacation_Kind = 'AL'
AND EmployeeId = 13)
AS Taken
FROM employees e,
EmployeeVacations ev
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


This works if I pick a single employee. If I remove the "where EmployeeId = 13", I get a list of all the employees with the sum of everyone's total vacation in every row (like 1,300 hours). How do I break it down so it only shows the Taken for each employee specifically?

Answer

Just guessing that you also might want the sum rather than the single forward records... Here is a query that aggregates EmployeeVacations per EmployeeId:

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;

Please ...

  • use explicit joins instead of the pre-1992 comma-separated joins for readability and for being less prone to errors.
  • use double quotes for alias names; single quotes are for string literals.
  • use 'yyyymmdd' for dates; it is the supported date literal format in SQL Server.