two queries summing into a single value

[dougancil]

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?

Thank you

Doug

[Rabbit]

You need to group by your code.

[dougancil]

Rabbit,

Do you mean group by in the first query?

[Rabbit]

No, you need to group in your aggregate query.

[dougancil]

Rabbit,

This is the only really aggregate I have ... this is the final query that I have that sums everything up and presents it to users:

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

[Rabbit]

Yeah, you need to group that by code if you want to return a row for each code.

[dougancil]

Rabbit,

At this point though in my calculation, all of the codes are "summed" which is why I thought I should do a group by before this.

here's a sample of my data from Scratchpad5:

8484 Adeline Gilliam 170.79 2.8465 0
8475 Andreika Houff 1533.27 25.5545 0
8467 Brenda Brown 2248.71 37.4785 0
8471 Brentton Thompson 2920.81 48.680166 0


Here's some data from scratchpad4:

8245 Yvonne Becerra 1/2/2011 Coaching Session 30

[Rabbit]

Even if you are pulling your data from an aggregated source, if you reaggregate like you are doing, the prior groupings are moot, it will regroup by your new group by settings. If you need the new aggregate to group by code then you need to tell it to group by code again. It doesn't matter that your source is grouped by code.

[dougancil]

Ok so then I guess my question after that would be, since I'm going to be doing a groupby for the codes, can I then sum them so that it would look something like this:

From Scratchpad3
Group By
[Scratchpad3].Code,

but then I'm also trying to sum them by employeenumber.So my data would look like this (for agent 8245 in this case)

Agent Number Regular Time  Total O/T ETO Sick Time Vacation Misc
    8245       18.01          0       0      0        0      30
 

[Rabbit]

Those fields don't exist in the code that you've shown. So I can't say whether or not you can achieve that layout.

You don't have to group by only one field. You can group by multiple fields.

[dougancil]

Rabbit,

Those fields are represented in this line of my first query as
"code"

select employeenumber, exceptiondate, code, sum(duration) as totalminutes

A user would only have so many codes ... ETO, Vacation, Sick Time everything else would be summed as Misc.

[Rabbit]

To get them across the top like that you'll need to pivot the data.

[dougancil]

I'm not aware of how to pivot the data. Can you offer some code as an example?

Thank you

Doug

[Rabbit]

From Microsoft: http://msdn.microsoft.com/en-us/library/ms177410.aspx

[dougancil]

Rabbit,

Ok so I now know that I have to pivot the data, but with the information that you've seen from my aggregate query, can I produce the results that I need?

Basically what I'm not sure of is how to filter all of the data so that if it doesnt match ETO, Vacation or Sick time, how to sum it as Misc.

[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.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

[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.