I am trying to do two things ...I have a query that sums up all of an agent "exceptions" as special minutes and then presents them later as part of a sum.

What I need to do is to show all of the data as follows:

Agent Number Regular Time Total O/T ETO Sick Time Vacation Holiday Misc
8245 0 0 0 0 0 0 30
Currently how I'm seeing my end result is as follows:

Agent Number Regular Time Total O/T
8245 18.01 0

My two queries are as follows:

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 the only aggregate query I have is this (which is my last run 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

The codes that are needing to be sorted are:

ETO
Sick Time
Vacation
Holiday

and then if the code in my first query is any other value besides those, they are summed and labeled as "Misc"

I know that I need to pivot my data and also use CASE but I have no idea where.

Hi there...


I don't know so much about Pivoting data..But as I understand, Pivoting data is a technique that rotates data from a state of rows to a state of columns, possibly aggregating multiple source values into the same target row and column intersection. Some practical reasons to pivot data include formatting data for reporting purposes, calculating custom aggregations, and solving relational division problems.

So, am I right ???

Regards
Manish