December 1, 2011 at 7:05 am
I have 6 fields:
Eg
ID,dept,sum1,sum2,total and company
I have 6 distinct companies,
I need to create a report; Let’s see about one company
Top 10 of ID,dept,sum1,sum2,total based on top 10 total
Then the rest will be in one line with out id and dept
Result will be
1 ID,dept,sum1,sum2,total
2. ID,dept,sum1,sum2,total
‘
10 ID,dept,sum1,sum2,total
Other; sum1,sum2,total
the other row have the total of the rest of the company
------------
I need for 6 company the same thing?
Regards
December 1, 2011 at 7:25 am
Do you need the report to show one company at a time, or to show the top 10 and summary rows for each company in one report?
You could easily do this with a CTE and either the Rank() or Dense_Rank() function, depending on how you want to handle ties in the top 10. The outer query would be a union of the details from the top 10, and a sum of all the rest, using the ranking function column to determine which go where.
If you want all 6 companies in one report, you would partition the ranking function by company, and then the outer queries would probably use Outer Apply to get each company's data. If you want one company per report, use an input parameter for the company data to filter the CTE.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply