Crystal Reports & SQL Reporting Services Training Consultants
Account Log in:
Username:
Password:
Being right is highly overrated. Even a stopped clock is right twice a day
 
Unknown
Institute of IT Trainers - Freelance Trainer of the Year 2006 & 2009
Liverpool Business Connect Member
  Maximum Impact Solutions Limited - Reporting Solutions, Creating Answers
Reporting Solutions - Creating Answers, Crystal Reports, Dashboarding (Xcelsius) & SQL Reporting Services

The Maximum Impact Solutions Blog Feed ME!!!!

11
November

How Do I Show the 3 Largest Sales Figures per Group on One Row in SQL Server Reporting Service 2008 R2

During a recent SQL Server Reporting Services 2008 R2 consultancy, the delegate wanted to know how to display the 3 highest values per group as a single record.


Solution:

The solution is the modification of the blog post How Do I Calculate the Top N Sales by Group.

Using the SQL statement to create 3 derived tables thats allows one to calculate separately the 1st, 2nd and 3rd largest sales value per group.


SELECT     Employee.[Employee ID], Employee.[First Name] + ' ' + Employee.[Last Name] AS [Employee Name], [1st].[Order Amount] AS Highest,
                      [2nd].[Order Amount] AS [2nd Highest], [3rd].[Order Amount] AS [3rd Highest]
FROM         Employee INNER JOIN
 (SELECT     TOP 100 PERCENT a.[Employee ID], a.[Order Amount], COUNT(*) AS Rank_No
   FROM          Orders AS a INNER JOIN
  Orders AS b ON a.[Employee ID] = b.[Employee ID] AND a.[Order Amount] <= b.[Order Amount]
GROUP BY a.[Employee ID], a.[Order Amount]
 HAVING      (COUNT(*) = 1)
   ORDER BY a.[Employee ID], COUNT(*)) AS [1st] ON

Employee.[Employee ID] = [1st].[Employee ID] INNER JOIN

(SELECT     TOP 100 PERCENT a.[Employee ID], a.[Order Amount], COUNT(*) AS Rank_No
  FROM          Orders AS a INNER JOIN
  Orders AS b ON a.[Employee ID] = b.[Employee ID] AND a.[Order Amount] <= b.[Order Amount]
 GROUP BY a.[Employee ID], a.[Order Amount]
  HAVING      (COUNT(*) = 2)
    ORDER BY a.[Employee ID], COUNT(*)) AS [2nd] ON

Employee.[Employee ID] = [2nd].[Employee ID] INNER JOIN

  (SELECT     TOP 100 PERCENT a.[Employee ID], a.[Order Amount], COUNT(*) AS Rank_No
 FROM          Orders AS a INNER JOIN
 Orders AS b ON a.[Employee ID] = b.[Employee ID] AND a.[Order Amount] <= b.[Order Amount]
   GROUP BY a.[Employee ID], a.[Order Amount]
  HAVING      (COUNT(*) = 3)
  ORDER BY a.[Employee ID], COUNT(*)) AS [3rd] ON Employee.[Employee ID] = [3rd].[Employee ID]

This SQL can then be added to a tablix within SQL Server Reporting Services 2008 R2


If you have any questions, leave us a comment below, or need any assistance, please do not hesitate to Contact Us
                           
1

Julia

02
December
You're welcome glad it helped
2

Training

21
August
Testing if this works thanks
3

Joshualiake

14
November
Writing a medical thesis or dissertation is a task done by almost all postgraduate and master's medical students. Dissertation is derived from the Latin word disserto which means discuss. It is essential to write successful medical papers such as medicine essays and medical thesis papers. There are several reasons as to why students write medicine essays. One of the reasons is to promote enhancement of critical judgment, research skills as well as analytical skills. Moreover, medicine essay writing produce students with the ability to 4evaluate and analyze data critically. The initial step for writing medicine essays is to choose a topic. A writer should have at least three topics to choose from. The topic has to be interesting, feasible and relevant. It is essential to write quality medicine essay. Hence, students need to have analytical skills and perfect writing skills. The writing skills will enable them write outstanding essay papers that can be highly regarded by instructors and professors. Teachers often require a lot and expect a lot from their students in terms of medicine essay writing. for this reason, students find essay writing to be an extremely difficult task and hence resort to buying custom medicine essays. A custom medicine essay has to be written by professional writers who are qualified in the field of nursing. Moreover, the custom medicine essay has to be original and plagiarism free. This means that it has to be written from scratch by experts with many years experience. The many years experience should enable a writer to write any form of medical paper including medical thesis, medicine essay and even medicine research paper. Moreover, experience will enable a writer to write a medicine essay that can guarantee academic success. Students get custom medicine essays from custom writing company. It is essential to choose the best company so that one can get the best custom medicine essay. The best and the most reliable medicine essay writing company should have some unique characteristics such as affordability and the ability to provide original and superior quality medicine essays. The other quality is that the company has to hire expert writers who can write quality medicine essays and other types of medical papers. The essays should not only be quality but also plagiarism free and free of grammatical and spelling mistakes. A custom medicine essay has a similar structure to any other academic essay assignment. It has an introduction that introduces the topic and tells the reader what the essay is all about. The second section is the body that has many paragraphs supporting the main topic. Finally there is the conclusion that briefly summarizes what has been discussed in the body section of the essay. Students should choose reliable writing companies so that they can get quality custom papers on several fields such as technology, sociology and law in addition to medicine field. Our custom writing company is the best company that all clients should rely on when in need of any given type of medicine paper. We provide quality papers that not only plagiarism free but also original. Moreover, our custom papers are affordable and able to guarantee academic excellence at all times. All our medical papers are reliable and sure of satisfying clients at all times.  

MAKE A COMMENT

Name *

Email Address *

Comment *