Pivoting data - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Pivoting data

Author

Topic

dougancil
Posting Yak Master

217 Posts

Posted - 2011-02-21 : 12:57:15

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 Misc
8245 18.01 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:

ELECT 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

Can anyone please assist?

Thank you

Doug

dougancil
Posting Yak Master

217 Posts

Posted - 2011-02-22 : 10:59:11

I know that I need to use a CASE statement to get the results that I need, the question is where do I need to place the CASE statement?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources