Detail Records into Summary Format via SP

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)

Detail Records into Summary Format via SP

Author

Topic

gsaunders
Starting Member

6 Posts

Posted - 2008-12-09 : 09:55:11

Hello,

(SQL 2000 SP4) We are looking to solve a problem we run into somewhat regularly where we need to get Detail Records into a special format for reporting.

Traditionally we have used the middle tier business code to traverse the detail data and create a reporting table to do this for more complicated processes, but I wanted to post this and get some feedback from the SQL gurus.

Here is the primary detail table: (We do join it with other tables, but for this example let's deal with just the raw detail and I'll put some fields in that would normally be part of the join for simplicity)


CREATE TABLE [dbo].[EmployeeTime] (
	[EmployeeTimeID] [int] NOT NULL ,
	[EmployeeID] [int] NOT NULL ,
	[EmployeeName] [varchar] (20) NOT NULL,
	[EmployeeNo] [int] NOT NULL ,
	[LastName] [varchar] (20) NOT NULL,
	[ProjectID] [int] NOT NULL ,
	[CostDistribution] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[CostType] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[ClassNo] [int] NULL ,
	[WeekNo] [int] NULL ,
	[DayNo] [int] NULL ,
	[DateEnt] [datetime] NULL ,
	[RegHours] [float] NULL ,
	[OtherHours] [float] NULL ,
	[OtherHoursType] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO

What I need to do is end up with data that looks like the following table for reporting:


CREATE TABLE [dbo].[EmployeeTimeRecap] (
	[EmployeeName] [varchar] (20) NOT NULL,
	[EmployeeNo] [int] NOT NULL ,
/* Each day is the total hours per day for each employee */
	[Sun_RegHours] [float] NULL,
	[Mon_RegHours] [float] NULL,
	[Tues_RegHours] [float] NULL,
	[Wed_RegHours] [float] NULL,
	[Thur_RegHours] [float] NULL,
	[Fri_RegHours] [float] NULL,
	[Sat_RegHours] [float] NULL,
	[Total_RegHours] [float] NULL
	[Sun_OtherHours] [float] NULL,
	[Mon_OtherHours] [float] NULL,
	[Tues_OtherHours] [float] NULL,
	[Wed_OtherHours] [float] NULL,
	[Thur_OtherHours] [float] NULL,
	[Fri_OtherHours] [float] NULL,
	[Sat_OtherHours] [float] NULL,
	[Total_OtherHours] [float] NULL
) ON [PRIMARY]
GO

The report will look something like this: (I don't know how this will look in this post, but here goes)


EmpName | EmpNo | Sun | Mon | Tues | Wed | Thur | Fri | Sat | Total
JonDoe  | 3085  |  0  |  8  |  1.5 | 9.5 | 9.5  | 4.5 |  0  |  33
	         |  0  |  0  |  0   |  0  |  0   |  0  |  7  |  7

Essentially the first line is Employee x with regular hours on the first line and if there are other hours we will just have the report store them on a second line below the regular hours.

Now traditionally we have just had the middle tier or the client step through the filtered detail table and generate a usable table for the report writer. That is sort of the case above.

What I am trying to come up with is a Stored Procedure that will do the same. I am certain I could probably use temporary tables and use cursors to do this sort of looping in the stored procedure, but I am wondering if I am missing out on some other way of doing this in SQL.

I looked at doing grouping, but the best I can come up with is an intermediate table that I can build from the following query:


SELECT     EmployeeNo, EmployeeName, DATEPART(dw,DateEnt) AS DayOfWeek, 
		SUM(RegHours) AS TotalRegHours, SUM(OtherHours) AS TotalOtherHours
GROUP BY EmployeeNo, EmployeeName, DATEPART(dw,DateEnt)
ORDER BY Employee.LastName

Which looks like this:


CREATE TABLE [dbo].[EmployeeTimeIntermediate] (
	[EmployeeNo] [int] NOT NULL ,
	[EmployeeName] [varchar] (20) NOT NULL,
	[DayOfWeek] [int], NOT NULL,
	[Total_RegHours] [float] NULL
	[Total_OtherHours] [float] NULL
) ON [PRIMARY]
GO

So I would have data like this:


EmpNo	EmpName	Day	Reg	Oth
1549	Jon Doe      	2	8.0	NULL
1549	Jon Doe      	3	NULL	8.0

Anyway, I was wondering what some of you have done when trying to get a detail table into a similar type of summary format that a standard Grouping will not solve.

Thanks,

Greg

gsaunders
Starting Member

6 Posts

Posted - 2008-12-09 : 11:24:50

This was suggested which works:


SELECT EmployeeName, EmployeeNo,
	SUM(CASE WHEN dw=1 THEN RegHours ELSE 0 END) AS Sun_RegHours,
	SUM(CASE WHEN dw=2 THEN RegHours ELSE 0 END) AS Mon_RegHours,
	SUM(CASE WHEN dw=3 THEN RegHours ELSE 0 END) AS Tues_RegHours,
	SUM(CASE WHEN dw=4 THEN RegHours ELSE 0 END) AS Wed_RegHours,
	SUM(CASE WHEN dw=5 THEN RegHours ELSE 0 END) AS Thur_RegHours,
	SUM(CASE WHEN dw=6 THEN RegHours ELSE 0 END) AS Fri_RegHours,
	SUM(CASE WHEN dw=0 THEN RegHours ELSE 0 END) AS Sat_RegHours,
	SUM(RegHours) AS Total_RegHours,
	SUM(CASE WHEN dw=1 THEN OtherHours ELSE 0 END) AS Sun_OtherHours,
	SUM(CASE WHEN dw=2 THEN OtherHours ELSE 0 END) AS Mon_OtherHours,
	SUM(CASE WHEN dw=3 THEN OtherHours ELSE 0 END) AS Tues_OtherHours,
	SUM(CASE WHEN dw=4 THEN OtherHours ELSE 0 END) AS Wed_OtherHours,
	SUM(CASE WHEN dw=5 THEN OtherHours ELSE 0 END) AS Thur_OtherHours,
	SUM(CASE WHEN dw=6 THEN OtherHours ELSE 0 END) AS Fri_OtherHours,
	SUM(CASE WHEN dw=0 THEN OtherHours ELSE 0 END) AS Sat_OtherHours,
	SUM(OtherHours) AS Total_OtherHours
FROM (
	SELECT EmployeeName, EmployeeNo, RegHours, OtherHours,
		DATEPART(dw,DateEnt) AS dw
	FROM EmployeeTime
) x GROUP BY EmployeeName, EmployeeNo

Does this look like the best way of doing it?
Does anyone have a recommendation of something they think is better?
Also I noticed the GROUP BY has an x in front of it. I have never noticed this before... without it generates an error. What does this x mean?

Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-09 : 11:30:16

[code]SELECT EmployeeNo, EmployeeName,
SUM(CASE WHEN DATEPART(dw,DateEnt)=1 THEN RegHours ELSE 0 END) AS SunRegHours,
SUM(CASE WHEN DATEPART(dw,DateEnt)=2 THEN RegHours ELSE 0 END) AS MonRegHours,
SUM(CASE WHEN DATEPART(dw,DateEnt)=3 THEN RegHours ELSE 0 END) AS TueRegHours,
SUM(CASE WHEN DATEPART(dw,DateEnt)=4 THEN RegHours ELSE 0 END) AS WedRegHours,
SUM(CASE WHEN DATEPART(dw,DateEnt)=5 THEN RegHours ELSE 0 END) AS ThuRegHours,
SUM(CASE WHEN DATEPART(dw,DateEnt)=6 THEN RegHours ELSE 0 END) AS FriRegHours,
SUM(CASE WHEN DATEPART(dw,DateEnt)=7 THEN RegHours ELSE 0 END) AS SatRegHours,
SUM(RegHours) AS TotalRegHours,
SUM(CASE WHEN DATEPART(dw,DateEnt)=1 THEN OtherHours ELSE 0 END) AS SunOtherHours,
SUM(CASE WHEN DATEPART(dw,DateEnt)=2 THEN OtherHours ELSE 0 END) AS MonOtherHours,
SUM(CASE WHEN DATEPART(dw,DateEnt)=3 THEN OtherHours ELSE 0 END) AS TueOtherHours,
SUM(CASE WHEN DATEPART(dw,DateEnt)=4 THEN OtherHours ELSE 0 END) AS WedOtherHours,
SUM(CASE WHEN DATEPART(dw,DateEnt)=5 THEN OtherHours ELSE 0 END) AS ThuOtherHours,
SUM(CASE WHEN DATEPART(dw,DateEnt)=6 THEN OtherHours ELSE 0 END) AS FriOtherHours,
SUM(CASE WHEN DATEPART(dw,DateEnt)=7 THEN OtherHours ELSE 0 END) AS SatOtherHours,
SUM(OtherHours) AS TotalOtherHours
GROUP BY EmployeeNo, EmployeeName
ORDER BY Employee.LastName[/code]

gsaunders
Starting Member

6 Posts

Posted - 2008-12-09 : 11:30:19

Never mind on the "x" question... that is a table alias. Brain fart.

gsaunders
Starting Member

6 Posts

Posted - 2008-12-09 : 11:42:16

quote:

Originally posted by visakh16

SELECT     EmployeeNo, EmployeeName, 
SUM(CASE WHEN DATEPART(dw,DateEnt)=1 THEN RegHours ELSE 0 END) AS SunRegHours,
SUM(CASE WHEN DATEPART(dw,DateEnt)=2 THEN RegHours ELSE 0 END) AS MonRegHours,
SUM(CASE WHEN DATEPART(dw,DateEnt)=3 THEN RegHours ELSE 0 END) AS TueRegHours,
SUM(CASE WHEN DATEPART(dw,DateEnt)=4 THEN RegHours ELSE 0 END) AS WedRegHours,
SUM(CASE WHEN DATEPART(dw,DateEnt)=5 THEN RegHours ELSE 0 END) AS ThuRegHours,
SUM(CASE WHEN DATEPART(dw,DateEnt)=6 THEN RegHours ELSE 0 END) AS FriRegHours,
SUM(CASE WHEN DATEPART(dw,DateEnt)=7 THEN RegHours ELSE 0 END) AS SatRegHours, 
SUM(RegHours) AS TotalRegHours,
SUM(CASE WHEN DATEPART(dw,DateEnt)=1 THEN OtherHours ELSE 0 END) AS SunOtherHours,
SUM(CASE WHEN DATEPART(dw,DateEnt)=2 THEN OtherHours ELSE 0 END) AS MonOtherHours,
SUM(CASE WHEN DATEPART(dw,DateEnt)=3 THEN OtherHours ELSE 0 END) AS TueOtherHours,
SUM(CASE WHEN DATEPART(dw,DateEnt)=4 THEN OtherHours ELSE 0 END) AS WedOtherHours,
SUM(CASE WHEN DATEPART(dw,DateEnt)=5 THEN OtherHours ELSE 0 END) AS ThuOtherHours,
SUM(CASE WHEN DATEPART(dw,DateEnt)=6 THEN OtherHours ELSE 0 END) AS FriOtherHours,
SUM(CASE WHEN DATEPART(dw,DateEnt)=7 THEN OtherHours ELSE 0 END) AS SatOtherHours, 
SUM(OtherHours) AS TotalOtherHours
GROUP BY EmployeeNo, EmployeeName
ORDER BY Employee.LastName

Even cleaner. Thanks.

I was told to look at a link on CrossTabs and Pivots so I am going to check out that out as well and see if it offers another option as well... but I recall reading somewhere to avoid using them if you can.

Thanks.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-09 : 11:53:38

http://www.mssqltips.com/tip.asp?tip=937

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-12-10 : 08:20:08

http://sqlblogcasts.com/blogs/madhivanan/archive/2008/08/27/dynamic-pivot-in-sql-server-2005.aspx

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources