|
dougancil
Posting Yak Master
217 Posts |
 Posted - 2011-02-17 : 11:47:52
|
I have two queries:
SELECT s1.Employeenumber, s1.[Name], s2.Exceptiondate, code, s2.totalminutes into scratchpad4
FROM
(select distinct Employeenumber,[Name] from Scratchpad1) AS s1
inner JOIN
(select employeenumber, exceptiondate, code, sum(duration) as totalminutes
from scratchpad3
where exceptiondate between @payrollstartdate And @payrollenddate
group by employeenumber, exceptiondate, code) as s2
ON s1.Employeenumber = s2.Employeenumber
order by exceptiondate asc
and
SELECT [EmployeeNumber],[Name],SUM([Minutes]) AS summinutes,SUM([Minutes])/60 AS sumhours,
SUM(CASE WHEN Cat=2 THEN [Minutes] ELSE 0 END) AS specminutes
FROM
(
SELECT [EmployeeNumber],[Name],[Dateonly],[Minutes],1 AS Cat
FROM Scratchpad2
where dateonly between @payrollstartdate And @payrollenddate
UNION ALL
SELECT [EmployeeNumber],[Name],[ExceptionDate],[TotalMinutes],2
FROM ScratchPad4
)t
GROUP BY [EmployeeNumber],[Name]
and what these are doing is summing up values into "spec minutes" but what I'm looking to do is to sum for each type of code, either ETO, Vacation, Sick Time or Misc and show a value for each. Right now it's just showing spec minutes. In my first query, there is a code value for each. Can anyone offer a possible way to solve this issue? |
|
Two queries summing into a single value