Solved

SQL Group by question

Posted on 2013-11-19
2
336 Views
Last Modified: 2013-11-19
I have a query for SSRS to detail employee bonuses for the past 3 years.

I achieved the appropriate bonus amounts using case statements, but I want the result in one row for each employee.  Currently, with the attached query, it reports in 3 rows.  If I take the Payroll Date out of the Group by clause, it errors.
groupby.txt
0
Comment
Question by:urthrilled
2 Comments
 
LVL 24

Accepted Solution

by:
chaau earned 500 total points
ID: 39660929
to remove the datepart component from grouping you need to include it inside the aggregate function, like this:
SELECT     PREH.FirstName, PREH.Employee, PREH.udDivision, PREH.LastName, PREH.GLCo, PREH.HrlyRate, 
              sum(case  when (datepart(yyyy,PRDT.PREndDate)=(2012-2)) then PRDT.Amount else 0 end) as TwoPrvYr,
		sum(case  when (datepart(yyyy,PRDT.PREndDate)=(2012-1)) then PRDT.Amount else 0 end) as PrvYr,
		 sum(case  when (datepart(yyyy,PRDT.PREndDate)=(2012)) then PRDT.Amount else 0 end) as Year

FROM         HRRM INNER JOIN
                      PREH ON HRRM.HRCo = PREH.PRCo AND HRRM.HRRef = PREH.Employee INNER JOIN
                      HRPC ON PREH.PRCo = HRPC.HRCo AND HRRM.PositionCode = HRPC.PositionCode INNER JOIN
                      HQCO ON PREH.GLCo = HQCO.HQCo LEFT OUTER JOIN
                      PRDT ON PREH.PRCo = PRDT.PRCo AND PREH.PRGroup = PRDT.PRGroup AND PREH.Employee = PRDT.Employee

where  PREH.ActiveYN = 'Y' AND (NOT (PREH.GLCo IN (90, 201))) AND (PRDT.EDLCode = 10) and datepart(yyyy,PRDT.PREndDate) between (2012)-2 and (2012)
GROUP BY PREH.GLCo, PREH.udDivision, PREH.LastName, PREH.FirstName, PREH.HrlyRate, PREH.Employee
ORDER BY PREH.GLCo, PREH.udDivision, PREH.LastName, PREH.Employee

Open in new window


On a side note, this statement:
 datepart(yyyy,PRDT.PREndDate) between (2012)-2 and (2012)

Open in new window

is not great in terms of performace, as SQL Server will not be able to construct index. It will be better to compare dates with dates, like this:
PRDT.PREndDate >= '20100101' and PRDT.PREndDate < '20130101'

Open in new window

(this statement will grab all dates for years 2010, 2011, 2012)
0
 
LVL 4

Author Closing Comment

by:urthrilled
ID: 39661111
Thank you!  I am used to manipulating with Crystal and am just gaining the in depth SQL experience to eloquently create SSRS reports.  I do like them so much better, but old dogs can't always readily see new tricks!
0

Featured Post

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
SSRS reports 5 32
SSRS Report Help - Excel format 5 83
Allow users from Trusted Domain to Access Resources 1 46
Add total to a tablix with ReportItem values 15 55
Steps to solve SSRS SQL 2008 R2 User Access Control (UAC) Permission Error With the introduction of SQL Server 2008 R2 and Vista (Windows 7 as well) came new enhanced security features. One of the features included was User Access Control (UAC) t…
Hi, I have heard from my friends that it’s not possible to create Label Printing report using SSRS. I am amazed after hearing this words not possible in SSRS. I googled lot and found that it is possible to some of people know about the Report Bui…
Along with being a a promotional video for my three-day Annielytics Dashboard Seminor, this Micro Tutorial is an intro to Google Analytics API data.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question