November 10, 2009 at 7:53 am
OK, here goes... I have a database that tracks ALL training and associated hours credited for each training session for our 1150 employees.
We are required by our auditor to produce a year end report that shows what training courses each employee attended, along with their credited hours for the year. They have a required 24 hours of training each year.
What I need is a report that compiles the data in a printed report that shows each person, the training courses they attended and the hours for each course. I then need it to total the hours at the end of each employee name before moving to the next name in the list.
EXAMPLE Table:
NAME Course # Course Name Hours
Smith, Bill AD1567 Animal Dental Care 8
Smith, Bill ALG9989 Alligator Farming 16
Smith, Bill CGR9876 Cat Grooming 8
Total Hours 32
Jones, Alias PET3456 Pet Care, Basic 16
Jones, Alias PETNU43 Pet Nutrition 40
Total Hours 56
Now, I can get the total hours using the SELECT SUM(Hours) AS Total Hours, Using the GROUP BY LAST NAME, however, this only returns a single entry for each persons name with their total hours displayed.
Example of current return:
NAME Division Section Total Hours
Smith, Bill Intake Eval 32
Jones, Alias Rehab Med 56
I need a complete list of their training with the total hours. However, when I plug in the column name that contains the course name - I get the individual rows, the hours credited for that session, but nothing is totaled in the SELECT SUM(HOURS) AS TOTAL HOURS column.
Here is my current SQL Statement that retuns every name in the dbase, with a single row and total hours...
SELECT SUM([Course Hours]) AS Hours, Last, First, MI, Serial, Division, Section, Jurisdiction
FROM tblTrainingRecords
GROUP BY Last, First, MI, Serial, Division, Section, Jurisdiction
ORDER BY Last
Not good enough for the auditor - I need a report that shows ALL training courses and the total hours.....
Any suggestions?
November 10, 2009 at 8:10 am
You can try WITH ROLLUP option with GROUP BY. But it will give you one extra row for each employee having the total hours.
-Vikas Bindra
November 10, 2009 at 8:26 am
Interesting...
Not concerned about an extra row as long as it's blank or could be used as a separator between name groups..
Could you provide an example of how it would be used in my SQL statement?
SELECT SUM([Course Hours]) AS Hours, Last, First, MI, Serial, Division, Section, Jurisdiction
FROM tblTrainingRecords
GROUP BY Last, First, MI, Serial, Division, Section, Jurisdiction
ORDER BY Last
In addition, when I try to include the course names, It seems to negate the SUM function and lists all training hours in individual rows assoicated with each entry. Does this make sense? Will the WITH ROLLUP function resolve this?
Thank you for the information so far, it's been helpful
Tom
November 10, 2009 at 9:28 am
here's one way to do it; in this case, i put the total Hours a s acolumn in the same row...not normalized, repetative, but the data is there.
this is based of your original structure...if i had the actual table definitions and sample data, i would have refined it better:
CREATE TABLE #EXAMPLE(
NAME varchar(30),
Course# varchar(30),
CourseName varchar(30),
Hours decimal(6,2) )
INSERT INTO #EXAMPLE
SELECT 'Smith, Bill','AD1567','Animal Dental Care', 8 UNION ALL
SELECT 'Smith, Bill','ALG9989','Alligator Farming', 16 UNION ALL
SELECT 'Smith, Bill','CGR9876','Cat Grooming', 8 UNION ALL
SELECT 'Jones, Alias','PET3456','Pet Care, Basic', 16 UNION ALL
SELECT 'Jones, Alias','PETNU43','Pet Nutrition', 40
select
#EXAMPLE.*,
MyAlias.TotalHours
from #EXAMPLE
left outer join (select [NAME],SUM(Hours) as TotalHours from #Example Group By [Name]) MyAlias
On #EXAMPLE.NAME = MyAlias.Name
--Results:
/*
NAME Course# CourseName Hours TotalHours
------------- -------- ------------------- ------ -----------
Smith, Bill AD1567 Animal Dental Care 8.00 32.00
Smith, Bill ALG9989 Alligator Farming 16.00 32.00
Smith, Bill CGR9876 Cat Grooming 8.00 32.00
Jones, Alias PET3456 Pet Care, Basic 16.00 56.00
Jones, Alias PETNU43 Pet Nutrition 40.00 56.00
*/
Lowell
November 10, 2009 at 9:32 am
Thank you,
That seems to simplify my process quite a bit.
I'll give that a run on my tables and see what happens!
Tom
November 13, 2009 at 12:46 pm
Based on the example above, you could explore some new features such as multiple row inserts and CTEs with OVER and PARTITION BY, to accomplish the same thing. Obviously the row_number() function isn't providing any added value here, but it's fun to use, just because it's so easy. 🙂 At any rate, it could look like this:
[font="Courier New"]DECLARE @Employees TABLE (
EmployeeID INT NOT NULL,
EmployeeName varchar(30),
CourseNumber varchar(30),
CourseName varchar(30),
CourseHours decimal(6,2)
)
INSERT INTO @Employees (EmployeeID, EmployeeName, CourseNumber, CourseName, CourseHours)
VALUES(1, 'Smith, Bill','AD1567','Animal Dental Care', 8 )
, (1, 'Smith, Bill','ALG9989','Alligator Farming', 16)
, (1, 'Smith, Bill','CGR9876','Cat Grooming', 8)
, (2, 'Jones, Alias','PET3456','Pet Care, Basic', 16)
, (2, 'Jones, Alias','PETNU43','Pet Nutrition', 40)
;
WITH cte AS
(
SELECT [EmployeeID]
, SUM(CourseHours) AS TotalHours
, row_number() OVER (PARTITION BY [EmployeeID]
ORDER BY [EmployeeID] ASC) AS rownum
FROM @Employees
GROUP BY [EmployeeID]
)
SELECT e.*, t.TotalHours
FROM cte t
JOIN @Employees e
ON e.[EmployeeID] = t.[EmployeeID]
ORDER BY 1
/*
EmployeeID EmployeeName CourseNumber CourseName CourseHours TotalHours
1 Smith, Bill AD1567 Animal Dental Care 8.00 32.00
1 Smith, Bill ALG9989 Alligator Farming 16.00 32.00
1 Smith, Bill CGR9876 Cat Grooming 8.00 32.00
2 Jones, Alias PET3456 Pet Care, Basic 16.00 56.00
2 Jones, Alias PETNU43 Pet Nutrition 40.00 56.00
*/[/font]
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply