December 9, 2008 at 7:51 am
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:
EmpNoEmpNameDayRegOth
1549Jon Doe 28.0NULL
1549Jon Doe 3NULL8.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
December 9, 2008 at 9:24 am
This was suggested by someone 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
December 9, 2008 at 9:26 am
Give this article a read.
http://www.sqlservercentral.com/articles/T-SQL/63681/
The X is the derived table alias, doesn't have anything to do with the GROUP BY.
December 9, 2008 at 9:28 am
the x is an alias; when you have a select in parenthesis, you need an alias in order to reference it;if you change the "x" to "mySubQuery" it makes more sense when you reread it.
select mySubQuery.* from
(
select stuff
from mytable
group by otherstuff
) mySubQuery
where mySubQuery.stuff = @myfildet
Lowell
December 9, 2008 at 9:30 am
Lowell (12/9/2008)
the x is an alias; when you have a select in parenthesis, you need an alias in order to reference it;if you change the "x" to "mySubQuery" it makes more sense when you reread it.select mySubQuery.* from
(
select stuff
from mytable
group by otherstuff
) mySubQuery
where mySubQuery.stuff = @myfildet
I was just getting ready to post to say it was the alias... I was having a brain fart.
Thanks.
December 9, 2008 at 9:33 am
Garadin (12/9/2008)
Give this article a read.http://www.sqlservercentral.com/articles/T-SQL/63681/
The X is the derived table alias, doesn't have anything to do with the GROUP BY.
I'll give that link a look... I have never had to do Cross Tabs or pivots in the past with SQL so this will be good to read.
In regards to the "X" I realized it was the table alias just moments after posting it... it just looked funny as I never use an x for an alias.
Thanks.
December 9, 2008 at 9:46 am
This is even cleaner to me:
SELECT EmployeeName, EmployeeNo,
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 EmployeeName, EmployeeNumber
Unless I need to order by a different field like LastName which means I either need to include it in the Group.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply