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
 Development Tools
 Reporting Services Development
 How to Combine lines of data of a report

Author  Topic 

werseyjersey
Starting Member

47 Posts
Posted - 2008-04-28 : 12:56:55
I am new to SQL Reporting Services 2005 and am trying to figure out how to get my report to display the number of hours report per employee. Let me explain:

I need to display the "total hours as productive hours for an employee", of these hours, the total that are regular, vacation, overtime, sick, holiday, training and the "other hours (sum of various hours that are NOT listed here)" that weigh into the "total hours as productive hours for an employee".

For example:
I have employee #1 who has a total of 89 hours worked for a particular reporting period. Of these 89 "total productive hours", they are broke down by:
Regular Hours = 43
Holiday Hours = 8
Sick Hours = 10
Vacation Hours = 10
Training Hours = 8
Other Hours = 10
Total Productive Hours = 89

I need this displayed within the report on a single line with various other data on the report.

I have a partially written query but can’t seem to figure out how to get it onto one line for everything. Your help will be appreciated. Here is the query I have so far. Additionally, I am unsure if the query needs the entire group by’s but was getting these are errors when I tried to run the query and did not include them.

SELECT ADP_EarningsDetail_1.FileNumber, ADP_EarningsDetail_1.EmpName AS 'Empl Name', ADP_EarningsDetail_1.Hours AS 'Actual Hours',
tPOSITION_CODES.FTEBudgetHours AS 'Bud Hours', tPERSONS.PersonFromEffectDate, tPERSONS.PersonToEffectDate,
tEMPLOYMENT_STATUS.EmploymentStatusFromEffectDate, tEMPLOYMENT_STATUS.EmploymentStatusToEffectDate,
tPERSON_POSITIONS.PositionFromEffectDate, tPERSON_POSITIONS.PositionToEffectDate, tPOSITION_CODES.PositionCodeFromEffectDate,
tPOSITION_CODES.PositionCodeToEffectDate, tJOB_CODES.JobCodeFromEffectDate, tJOB_CODES.JobCodeToEffectDate,
(SELECT CASE WHEN EarningsCode = 'OT' THEN SUM(CONVERT(int, Hours)) ELSE 0 END AS OTHours) AS OTHours,
CASE WHEN EarningsCode = 'REG' THEN SUM(CONVERT(int, Hours)) ELSE 0 END AS RegHours,
CASE WHEN EarningsCode = 'HOL' THEN SUM(CONVERT(int, Hours)) ELSE 0 END AS HolidayHours,
CASE WHEN EarningsCode = 'SIC' THEN SUM(CONVERT(int, Hours)) ELSE 0 END AS SickHours,
CASE WHEN EarningsCode = 'TRN' THEN SUM(CONVERT(int, Hours)) ELSE 0 END AS TrainingHours,
CASE WHEN EarningsCode = 'VAC' THEN SUM(CONVERT(int, Hours)) ELSE 0 END AS VacationHours, CASE WHEN EarningsCode NOT IN ('REG', 'OT',
'TRN', ' VAC', 'SIC', 'HOL', 'HLW') THEN SUM(CONVERT(int, Hours)) ELSE 0 END AS OtherHours, ADP_EarningsDetail_1.EarningsCode
FROM tPERSON_POSITIONS INNER JOIN
tEMPLOYMENT_STATUS ON tPERSON_POSITIONS.PersonIdNo = tEMPLOYMENT_STATUS.PersonIdNo INNER JOIN
tPOSITION_CODES ON tPERSON_POSITIONS.PositionIdNo = tPOSITION_CODES.PositionIdNo INNER JOIN
tPERSONS ON tPERSON_POSITIONS.PersonIdNo = tPERSONS.PersonIdNo INNER JOIN
tJOB_CODES ON tPOSITION_CODES.JobCodeIdNo = tJOB_CODES.JobCodeIdNo INNER JOIN
ADP_EarningsDetail AS ADP_EarningsDetail_1 ON tEMPLOYMENT_STATUS.EmpNo = ADP_EarningsDetail_1.FileNumber
WHERE (ADP_EarningsDetail_1.Hours IS NOT NULL) AND (tPERSONS.PersonToEffectDate = CONVERT(DATETIME, '3000-01-01 00:00:00', 102)) AND
(tEMPLOYMENT_STATUS.EmploymentStatusToEffectDate = CONVERT(DATETIME, '3000-01-01 00:00:00', 102)) AND
(tPERSON_POSITIONS.PositionToEffectDate = CONVERT(DATETIME, '3000-01-01 00:00:00', 102)) AND
(tPOSITION_CODES.PositionCodeToEffectDate = CONVERT(DATETIME, '3000-01-01 00:00:00', 102)) AND
(tJOB_CODES.JobCodeToEffectDate = CONVERT(DATETIME, '3000-01-01 00:00:00', 102))
GROUP BY ADP_EarningsDetail_1.EmpName, ADP_EarningsDetail_1.FileNumber, tPERSON_POSITIONS.PersonIdNo, ADP_EarningsDetail_1.EarningsCode,
ADP_EarningsDetail_1.Hours, tPOSITION_CODES.FTEBudgetHours, tPERSONS.PersonFromEffectDate, tPERSONS.PersonToEffectDate,
tEMPLOYMENT_STATUS.EmploymentStatusFromEffectDate, tEMPLOYMENT_STATUS.EmploymentStatusToEffectDate,
tPERSON_POSITIONS.PositionToEffectDate, tPERSON_POSITIONS.PositionFromEffectDate, tPOSITION_CODES.PositionCodeToEffectDate,
tPOSITION_CODES.PositionCodeFromEffectDate, tJOB_CODES.JobCodeToEffectDate, tJOB_CODES.JobCodeFromEffectDate


werseyjersey

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2008-04-28 : 13:12:23
Can you provide sample data of an employee and rules to calculate each of these times.
Go to Top of Page

werseyjersey
Starting Member

47 Posts
Posted - 2008-04-28 : 13:20:31
Let me see if I can get it together these are reported using Kronos!

werseyjersey
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2008-04-28 : 13:52:08
The hint is to group by EmployeeName or number and apply aggregate function sum over the hours field with case construct inside to get each of time value as seperate columns.
Go to Top of Page

werseyjersey
Starting Member

47 Posts
Posted - 2008-04-28 : 13:55:29
Don't know how to attach this so it will be in a spreadsheet format but hopefully this will work!

File Number Empl_Name Hours Budget Hours PersonFromEffectDate PersonToEffectDate EmploymentStatusFromEffectDate EmploymentStatusToEffectDate PositionFromEffectDate PositionToEffectDate PositionCodeFromEffectDate PositionCodeToEffectDate JobCodeFromEffectDate JobCodeToEffectDate OTHours RegHours HolidayHours SickHours TrainingHours VacationHours OtherHours EarningsCode
201241          Cinder, Rella NULL 0 1/22/2007 0:00 1/1/3000 0:00 1/22/2007 0:00 1/1/3000 0:00 1/22/2007 0:00 1/1/3000 0:00 1/2/1900 0:00 1/1/3000 0:00 1/2/1900 0:00 1/1/3000 0:00 0 0 0 0 0 0 NULL ONC       
201241          Cinder, Rella NULL 0 1/22/2007 0:00 1/1/3000 0:00 1/22/2007 0:00 1/1/3000 0:00 1/22/2007 0:00 1/1/3000 0:00 1/2/1900 0:00 1/1/3000 0:00 1/2/1900 0:00 1/1/3000 0:00 NULL 0 0 0 0 0 0 OT        
201241          Cinder, Rella 64         0 1/22/2007 0:00 1/1/3000 0:00 1/22/2007 0:00 1/1/3000 0:00 1/22/2007 0:00 1/1/3000 0:00 1/2/1900 0:00 1/1/3000 0:00 1/2/1900 0:00 1/1/3000 0:00 0 64 0 0 0 0 0 REG       
201241          Cinder, Rella 16         0 1/22/2007 0:00 1/1/3000 0:00 1/22/2007 0:00 1/1/3000 0:00 1/22/2007 0:00 1/1/3000 0:00 1/2/1900 0:00 1/1/3000 0:00 1/2/1900 0:00 1/1/3000 0:00 0 0 0 0 16 0 0 TRN       
201401          White, Snow 8          80 5/7/2007 0:00 1/1/3000 0:00 5/7/2007 0:00 1/1/3000 0:00 5/7/2007 0:00 1/1/3000 0:00 1/2/1900 0:00 1/1/3000 0:00 1/2/1900 0:00 1/1/3000 0:00 0 0 8 0 0 0 0 HOL       
201401          White, Snow NULL 80 5/7/2007 0:00 1/1/3000 0:00 5/7/2007 0:00 1/1/3000 0:00 5/7/2007 0:00 1/1/3000 0:00 1/2/1900 0:00 1/1/3000 0:00 1/2/1900 0:00 1/1/3000 0:00 NULL 0 0 0 0 0 0 OT        
201401          White, Snow 80         80 5/7/2007 0:00 1/1/3000 0:00 5/7/2007 0:00 1/1/3000 0:00 5/7/2007 0:00 1/1/3000 0:00 1/2/1900 0:00 1/1/3000 0:00 1/2/1900 0:00 1/1/3000 0:00 0 80 0 0 0 0 0 REG       
201401          White, Snow 10         80 5/7/2007 0:00 1/1/3000 0:00 5/7/2007 0:00 1/1/3000 0:00 5/7/2007 0:00 1/1/3000 0:00 1/2/1900 0:00 1/1/3000 0:00 1/2/1900 0:00 1/1/3000 0:00 0 0 0 10 0 0 0 SIC       
201401          White, Snow 70         80 5/7/2007 0:00 1/1/3000 0:00 5/7/2007 0:00 1/1/3000 0:00 5/7/2007 0:00 1/1/3000 0:00 1/2/1900 0:00 1/1/3000 0:00 1/2/1900 0:00 1/1/3000 0:00 0 0 0 0 0 70 70 VAC       
902062          Purple, Barney NULL 0 11/29/2007 0:00 1/1/3000 0:00 11/29/2007 0:00 1/1/3000 0:00 11/29/2007 0:00 1/1/3000 0:00 1/2/1900 0:00 1/1/3000 0:00 1/2/1900 0:00 1/1/3000 0:00 NULL 0 0 0 0 0 0 OT        
902062          Purple, Barney 16         0 11/29/2007 0:00 1/1/3000 0:00 11/29/2007 0:00 1/1/3000 0:00 11/29/2007 0:00 1/1/3000 0:00 1/2/1900 0:00 1/1/3000 0:00 1/2/1900 0:00 1/1/3000 0:00 0 16 0 0 0 0 0 REG       
902062          Purple, Barney 39         0 11/29/2007 0:00 1/1/3000 0:00 11/29/2007 0:00 1/1/3000 0:00 11/29/2007 0:00 1/1/3000 0:00 1/2/1900 0:00 1/1/3000 0:00 1/2/1900 0:00 1/1/3000 0:00 0 39 0 0 0 0 0 REG       
201759          Strait, George NULL 40 11/5/2007 0:00 1/1/3000 0:00 11/5/2007 0:00 1/1/3000 0:00 11/5/2007 0:00 1/1/3000 0:00 1/2/1900 0:00 1/1/3000 0:00 1/2/1900 0:00 1/1/3000 0:00 NULL 0 0 0 0 0 0 OT        
201759          Strait, George 80         40 11/5/2007 0:00 1/1/3000 0:00 11/5/2007 0:00 1/1/3000 0:00 11/5/2007 0:00 1/1/3000 0:00 1/2/1900 0:00 1/1/3000 0:00 1/2/1900 0:00 1/1/3000 0:00 0 80 0 0 0 0 0 REG       
201759          Strait, George 15         40 11/5/2007 0:00 1/1/3000 0:00 11/5/2007 0:00 1/1/3000 0:00 11/5/2007 0:00 1/1/3000 0:00 1/2/1900 0:00 1/1/3000 0:00 1/2/1900 0:00 1/1/3000 0:00 0 0 0 15 0 0 0 SIC       
201759          Strait, George 9          40 11/5/2007 0:00 1/1/3000 0:00 11/5/2007 0:00 1/1/3000 0:00 11/5/2007 0:00 1/1/3000 0:00 1/2/1900 0:00 1/1/3000 0:00 1/2/1900 0:00 1/1/3000 0:00 0 0 0 0 0 9 9 VAC       
201740          Bailey, LaRae 22         1064 10/29/2007 0:00 1/1/3000 0:00 10/29/2007 0:00 1/1/3000 0:00 10/29/2007 0:00 1/1/3000 0:00 1/2/1900 0:00 1/1/3000 0:00 1/2/1900 0:00 1/1/3000 0:00 22 0 0 0 0 0 0 OT        
201740          Bailey, LaRae 102        1064 10/29/2007 0:00 1/1/3000 0:00 10/29/2007 0:00 1/1/3000 0:00 10/29/2007 0:00 1/1/3000 0:00 1/2/1900 0:00 1/1/3000 0:00 1/2/1900 0:00 1/1/3000 0:00 0 102 0 0 0 0 0 REG       
900008          Mouse, Mickey 55         0 1/1/2007 0:00 1/1/3000 0:00 1/1/2007 0:00 1/1/3000 0:00 1/1/2007 0:00 1/1/3000 0:00 1/2/1900 0:00 1/1/3000 0:00 1/2/1900 0:00 1/1/3000 0:00 0 0 0 0 0 0 55 HRS       
900008          Mouse, Mickey NULL 0 1/1/2007 0:00 1/1/3000 0:00 1/1/2007 0:00 1/1/3000 0:00 1/1/2007 0:00 1/1/3000 0:00 1/2/1900 0:00 1/1/3000 0:00 1/2/1900 0:00 1/1/3000 0:00 NULL 0 0 0 0 0 0 OT        
900008          Mouse, Mickey NULL 0 1/1/2007 0:00 1/1/3000 0:00 1/1/2007 0:00 1/1/3000 0:00 1/1/2007 0:00 1/1/3000 0:00 1/2/1900 0:00 1/1/3000 0:00 1/2/1900 0:00 1/1/3000 0:00 0 NULL 0 0 0 0 0 REG       
902143          Shoeless, Joe NULL 10 12/26/2007 0:00 1/1/3000 0:00 12/26/2007 0:00 1/1/3000 0:00 12/26/2007 0:00 1/1/3000 0:00 1/2/1900 0:00 1/1/3000 0:00 1/2/1900 0:00 1/1/3000 0:00 NULL 0 0 0 0 0 0 OT        
902143          Shoeless, Joe 20         10 12/26/2007 0:00 1/1/3000 0:00 12/26/2007 0:00 1/1/3000 0:00 12/26/2007 0:00 1/1/3000 0:00 1/2/1900 0:00 1/1/3000 0:00 1/2/1900 0:00 1/1/3000 0:00 0 20 0 0 0 0 0 REG       
902143          Shoeless, Joe 36         10 12/26/2007 0:00 1/1/3000 0:00 12/26/2007 0:00 1/1/3000 0:00 12/26/2007 0:00 1/1/3000 0:00 1/2/1900 0:00 1/1/3000 0:00 1/2/1900 0:00 1/1/3000 0:00 0 36 0 0 0 0 0 REG       
000232          Purple, Rain 3          280 9/20/1999 0:00 1/1/3000 0:00 9/20/1999 0:00 1/1/3000 0:00 9/20/1999 0:00 1/1/3000 0:00 1/2/1900 0:00 1/1/3000 0:00 1/2/1900 0:00 1/1/3000 0:00 0 0 0 0 0 0 3 CAL       
000232          Purple, Rain 0          280 9/20/1999 0:00 1/1/3000 0:00 9/20/1999 0:00 1/1/3000 0:00 9/20/1999 0:00 1/1/3000 0:00 1/2/1900 0:00 1/1/3000 0:00 1/2/1900 0:00 1/1/3000 0:00 0 0 0 0 0 0 0 OT        
000232          Purple, Rain 40         280 9/20/1999 0:00 1/1/3000 0:00 9/20/1999 0:00 1/1/3000 0:00 9/20/1999 0:00 1/1/3000 0:00 1/2/1900 0:00 1/1/3000 0:00 1/2/1900 0:00 1/1/3000 0:00 0 40 0 0 0 0 0 REG       
000232          Purple, Rain 40         280 9/20/1999 0:00 1/1/3000 0:00 9/20/1999 0:00 1/1/3000 0:00 9/20/1999 0:00 1/1/3000 0:00 1/2/1900 0:00 1/1/3000 0:00 1/2/1900 0:00 1/1/3000 0:00 0 0 0 40 0 0 0 SIC       
902155          Winter, Storm NULL 640 12/31/2007 0:00 1/1/3000 0:00 12/31/2007 0:00 1/1/3000 0:00 12/31/2007 0:00 1/1/3000 0:00 1/2/1900 0:00 1/1/3000 0:00 1/2/1900 0:00 1/1/3000 0:00 NULL 0 0 0 0 0 0 OT        
902155          Winter, Storm 24         640 12/31/2007 0:00 1/1/3000 0:00 12/31/2007 0:00 1/1/3000 0:00 12/31/2007 0:00 1/1/3000 0:00 1/2/1900 0:00 1/1/3000 0:00 1/2/1900 0:00 1/1/3000 0:00 0 24 0 0 0 0 0 REG       
902155          Winter, Storm 40         640 12/31/2007 0:00 1/1/3000 0:00 12/31/2007 0:00 1/1/3000 0:00 12/31/2007 0:00 1/1/3000 0:00 1/2/1900 0:00 1/1/3000 0:00 1/2/1900 0:00 1/1/3000 0:00 0 0 0 0 40 0 0 TRN       
200623          Shower, Spring 48         40 10/1/2005 0:00 1/1/3000 0:00 10/1/2005 0:00 1/1/3000 0:00 5/1/2007 0:00 1/1/3000 0:00 1/2/1900 0:00 1/1/3000 0:00 1/2/1900 0:00 1/1/3000 0:00 48 0 0 0 0 0 0 OT        
200623          Shower, Spring 48         640 10/1/2005 0:00 1/1/3000 0:00 10/1/2005 0:00 1/1/3000 0:00 10/1/2005 0:00 4/30/2007 0:00 1/2/1900 0:00 1/1/3000 0:00 1/2/1900 0:00 1/1/3000 0:00 48 0 0 0 0 0 0 OT        
200623          Shower, Spring 128        40 10/1/2005 0:00 1/1/3000 0:00 10/1/2005 0:00 1/1/3000 0:00 5/1/2007 0:00 1/1/3000 0:00 1/2/1900 0:00 1/1/3000 0:00 1/2/1900 0:00 1/1/3000 0:00 0 128 0 0 0 0 0 REG       
200623          Shower, Spring 128        640 10/1/2005 0:00 1/1/3000 0:00 10/1/2005 0:00 1/1/3000 0:00 10/1/2005 0:00 4/30/2007 0:00 1/2/1900 0:00 1/1/3000 0:00 1/2/1900 0:00 1/1/3000 0:00 0 128 0 0 0 0 0 REG       
500017          Lion, May 616        40 7/30/2007 0:00 1/1/3000 0:00 7/30/2007 0:00 1/1/3000 0:00 7/30/2007 0:00 1/1/3000 0:00 1/2/1900 0:00 1/1/3000 0:00 1/2/1900 0:00 1/1/3000 0:00 0 0 0 0 0 0 616 ONC       
500017          Lion, May NULL 40 7/30/2007 0:00 1/1/3000 0:00 7/30/2007 0:00 1/1/3000 0:00 7/30/2007 0:00 1/1/3000 0:00 1/2/1900 0:00 1/1/3000 0:00 1/2/1900 0:00 1/1/3000 0:00 NULL 0 0 0 0 0 0 OT        
500017          Lion, May 80         40 7/30/2007 0:00 1/1/3000 0:00 7/30/2007 0:00 1/1/3000 0:00 7/30/2007 0:00 1/1/3000 0:00 1/2/1900 0:00 1/1/3000 0:00 1/2/1900 0:00 1/1/3000 0:00 0 80 0 0 0 0 0 REG       
   


the job (position) that is used is the "current position" which is defined as the job position on the date of the payroll (1/1/3000 denotes infinatey)
No null value conversions are to be made
Each earning code must be totalled and that total appears within the report (you need one line for each employee based on the earning total and there could be more than one of the same earning code per line--especially in the "other category")
If this doesn't help let me know.


werseyjersey
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2008-04-28 : 14:12:54
I think seeing your data since you have different fields holding different hours its enough you group by employee name and take sum of each

ie.
SELECT  Empl_Name,
SUM(RegHours) AS [Regular Hours],
SUM(HolidayHours) AS [HolidayHours],
SUM(SickHours) AS [SickHours],...
FROM YourTable
GROUP BY Empl_Name


the formatting for getting each of this in a line can be done at report level by placing the totals at different rows of a table.
Go to Top of Page

werseyjersey
Starting Member

47 Posts
Posted - 2008-04-29 : 13:08:29
Thanks visakh16, I'll give it a whirl.

werseyjersey
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site