two queries summing into a single value

[Rabbit]

You can use a CASE statement to group your data.

[dougancil]

Rabbit,

With using CASE, wouldn't I have to do that in my first query? Because after that I've summed for "spec minutes" and because in the end, these things are broken out as individual elements rather than just a sum value. Or am I thinking incorrectly?

[Rabbit]

I don't know which level you would have to group your data at because I have no idea what your queries look like. You supplied two queries in the original question but from the sample results you've shown me, they have no connection to those queries. Plus, you've probably made changes and I have no idea as to what the current queries look like.

[dougancil]

Rabbit,

The sample results come from this query:

SELECT DISTINCT
[ScratchPad5].EmployeeNumber ,
SUM( case when [sumhours]>40
THEN 40
ELSE cast([sumhours] as numeric(12,2))
END ) AS TotalRegHours,
SUM( case when [sumhours]>40
THEN cast([sumhours]-40 as numeric(12,2))
ELSE 0
END ) AS TotalOT

into scratchpad7

FROM
ScratchPad5
GROUP BY
[ScratchPad5].EmployeeNumber ,
sumhours
order by employeenumber asc

which produces this result:

Agent Number Regular Time  Total O/T 
    8245       18.01          0

what I need it to do is two things:

1. To show results like this like post #9 which is sorting all data based on "code" in my table and showing them to the user.
2. If the "code" doesnt match ETO, Vacation, Sick Time then it will be a Misc, which is then summed.

[Rabbit]

How are you going to group by code when you don't have code in your data source?

[dougancil]

Rabbit,

In my first query you'll notice that I have this:

SELECT s1.Employeenumb er, s1.[Name], s2.Exceptiondat e, 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 @payrollstartda te And @payrollenddate
group by employeenumber, exceptiondate, code) as s2
ON s1.Employeenumb er = s2.Employeenumb er
order by exceptiondate asc

[Rabbit]

If that query is scratchpad5 (which I assume it is by the context but I don't know because you haven't said so) then you do the group by and you use the case statement on the aggregate level.