SQL using WHERE from a group or RANK (part 2)

Question

I recently posted a question about SQL Where Statement / Grouping:

SQL statement using WHERE from a group or RANK

Now I have a few follow-up actions.

As in the previous question, suppose I have a table of 35,000 rows with these columns:

Sales Representative | Parent Account ID | Account ID | Total contract value | the date

Each line is individual by account identifier, but several account identifiers may fall under the parent account identifier.

Like the answers to the first question, this is likely to be a table w / i table. So, firstly, everything should be grouped by Sales Rep. From this, everything should be grouped by the identifier of the parent account, where the total total contract value for all accounts is> = 10,000. Then everything will be displayed and evaluated by the total identifier of the parent account identifier, and I need 35 senior parent account identifiers by the agent .

So, the first two rows of data may look like this:

  Sales Rep |  Parent Account ID |  Account ID |  Total Contract Value |  Date |  Rank
 John Doe |  ParentABC12345 |  ABC425 |  5,000 |  1/2/2013 | 1
 John Doe |  ParentABC12345 |  ABC426 |  10,000 |  1/2/2013 | 1
 John Doe |  ParentDJE12345 |  DJE523 |  11,000 |  1/2/2013 | 2
 John Doe |  ParentFBC12345 |  FBC6723 |  4,000 |  1/2/2013 | 3
 John Doe |  ParentFBC12345 |  FBC6727 |  4,000 |  1/2/2013 | 3

Please note that ranking works based on the parent account ID. The DJE523 account ID has the single largest TCV, but it ranks second b / c, the grouped value of the parent account ID ParentABC12345 is larger. Thus, there would be a ranking of 35 parent account identifiers, but in this rating they could be called more than 100 lines of actual data.

Any thoughts?

Answer 1

Always a pleasure to follow. The "Parent Rank" is added as an INNER JOIN .

Edit: as Dan Bracus correctly mentioned, my first answer was incorrect. I modified the request to fulfill the correct conditions. I also applied time slots to the parent account.

 DECLARE @minimumValue decimal(20,2) = 10000 DECLARE @numberOfAccounts int = 35 DECLARE @from datetime = '1/1/2013' DECLARE @till datetime = DATEADD(MONTH, 1, @from) SELECT [sub].[Sales Rep], [sub].[Rank], [sub].[Account ID], [sub].[Total Contract Value], [sub].[Parent Account ID], [sub].[Total], [sub].[ParentRank] FROM ( SELECT [s].[Sales Rep], [s].[Account ID], [s].[Total Contract Value], DENSE_RANK() OVER (PARTITION BY [s].[Sales Rep] ORDER BY [s].[Total Contract Value] DESC) AS [Rank], [p].[Parent Account ID], [p].[Total], [p].[ParentRank] FROM [Sales] [s] INNER JOIN ( SELECT [Parent Account ID], SUM([Total Contract Value]) AS [Total], RANK() OVER(ORDER BY SUM([Total Contract Value]) DESC) AS [ParentRank] FROM [Sales] WHERE[Date] > @from AND [Date] < @till GROUP BY [Parent Account ID] HAVING SUM([Total Contract Value]) > @minimumValue ) AS [p] ON [s].[Parent Account ID] = [p].[Parent Account ID] WHERE [Date] > @from AND [Date] < @till ) AS [sub] WHERE [sub].[Rank] <= @numberOfAccounts ORDER BY [Sales Rep] ASC, [ParentRank] ASC, [Rank] ASC 

And here is the new violin .

Answer 2

I think this will do it for you if you are using SQL Server:

 Select top 35 SalesRep, ParentAccountId, sum(TotalContractValue) from Table group by SalesRep, ParentAccountId order by sum(TotalContractValue) desc