Distributing the greatest value by rank in SQL

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server 2008 - General » Distributing the greatest value by rank in...

41 posts, Page 1 of 5

1 2 3 4 5 »»»

Distributing the greatest value by rank in SQL

Rate Topic

Display Mode

Topic Options

Author

Message

lmeinke

Posted Friday, December 07, 2012 9:28 AM

SSC-Addicted

Group: General Forum Members
Last Login: Thursday, December 20, 2012 8:13 AM
Points: 465, Visits: 162

This seems as though it would be terribly simple...but I am stuck.

I need to distribute as "average sales" value among a group that is categorized by employee size, industry and a category.

A sample of the data:

Category Avg_Sales Emp_Size Industry SalesRank Emp_Rank [color=#FF0000]DesiredOutcome[/color]
01-Widgets $576 1 - 4 Accommodations 3 1 $[color=#FF0000]576[/color]
01-Widgets $832 5 - 9 Accommodations 1 2 $[color=#FF0000]832[/color]
01-Widgets $672 10 - 19 Accommodations 2 3 $[color=#FF0000]832[/color]
01-Widgets $451 20 - 49 Accommodations 5 4 $[color=#FF0000]832[/color]
01-Widgets $459 50 - 99 Accommodations 4 5 $[color=#FF0000]832[/color]

What I need to do is distribute the Avg_Sales value according to the Emp_Rank BUT ensure that the greaterEmp_Rank (i.e. larger company)
never has a smaller Avg_Sales value than a smaller Emp_Rank within the same Category and Industry.

I started this by adding the SalesRank and Emp_Rank columns thinking I could complete this with some case statements and some simple math.....I've hit a wall and any and all help is appreciated.

Thank you in Advance.
Lonnie M

Post #1394127

lmeinke

Posted Friday, December 07, 2012 9:31 AM

SSC-Addicted

Group: General Forum Members
Last Login: Thursday, December 20, 2012 8:13 AM
Points: 465, Visits: 162

I apologize for the format in which this question posted. Not only did the spacing between columns not follow when I "posted" my color for the desired column looks like html. Not sure how to correct this.

Post #1394131

sgmunson

Posted Friday, December 07, 2012 10:49 AM

UDP Broadcaster

Group: General Forum Members
Last Login: Wednesday, May 15, 2013 8:20 AM
Points: 1,446, Visits: 1,883

I wouldn't worry about the format right away. I need a better explanation of what you actually mean by "distribute". It's often best when posting problems to provide a clear, but detailed description of what data you have, what you want to do with it, AND a good picture of the expected results for a given sample set of data, with specific examples for things that might be exceptions to a given rule. Most importantly, ALL the "rules" that you need to operate with need to be specified.

That said, you appear to want to allocate sales volume across various product and industry categories, but exactly how isn't clear, other than that you need to have your listed rule apply to that allocation. Am I on the right track, or off on some unknown page?

Steve
(aka sgmunson)
Smile

Weight Loss Tips

Post #1394171

lmeinke

Posted Friday, December 07, 2012 11:35 AM

SSC-Addicted

Group: General Forum Members
Last Login: Thursday, December 20, 2012 8:13 AM
Points: 465, Visits: 162

I think you are on the mark with what I am after. The "formatting" thing was supposed to be the clear example you speak of.

The "Desired Column" is the far right column.

I need to distribute the "maximum" average sales amount among the Categories/Industry and Employee sizes. The variable here is employee size. As you can see all employee size's have an "Avg_Sales" value unique to them, I have to distribute these values so that the larger employee size never has a lesser Avg_Sales amount than the previous employee size. (Employee size 5-9 can never have a lesser average sales amount than the employee size 1-4).

Hope this helps.

Post #1394181

sgmunson

Posted Friday, December 07, 2012 12:04 PM

UDP Broadcaster

Group: General Forum Members
Last Login: Wednesday, May 15, 2013 8:20 AM
Points: 1,446, Visits: 1,883

And now you've added "maximum average sales amount" to the mix, which you have not defined. My problem is that I don't really know what the average sales numbers you have actually represent, nor do I know how or why they get applied to the various categories, employee-sizes, and industries. This is, no doubt, clear as a bell for you, but as I can't know what the numbers are supposed to mean, you have to explain that in order to have a shot at me knowing what, exactly, you're trying to do.

I know it can be both painful and difficult to have to explain every detail, but without being able to have a clear, specified, defined meaning for the words average, maximum, and distributed, as they apply to your particular situation, the rest might as well be gibberish. One persons maximum might be across a particular set of data that they haven't specifically mentioned, or another's might be across a specfic category of records, and be something that only someone working for the company they work for would know. All too often, the requests for help here contain very vague statements that even if computers could speak English, they might still easily be thrown for the proverbial loop...

Does this help clarify the level of detail needed? Sometimes, two people just clash on communication style...

Steve
(aka sgmunson)
Smile

Weight Loss Tips

Post #1394189

lmeinke

Posted Friday, December 07, 2012 12:21 PM

SSC-Addicted

Group: General Forum Members
Last Login: Thursday, December 20, 2012 8:13 AM
Points: 465, Visits: 162

Thank you again for your response. I'm painfully aware how difficult it can be to communicate, but I appreciate your effort!

I believe a clearly illustrated matrix/spreadsheet would explain it best but I cannot provide this as this software seems to remove all white spaces.

1) Each row of data contains an industry/category/employee size and an "actual" average sales amount.
2) I need to distribute a (what I referred to as "maximum") sales amount to each row of data.
3) The amount I need to distribute may be the row's actual average sales amount or the average sales amount from the smaller employee size (whichever is greater)
4) What cannot happen is that a larger employee size (such as 50-99 employees) would have a smaller average sales amount than a smaller employee size (such as 1-4 employees). If that were to happen (which it does in reality) the larger employee size would assume the average sales amount of the smaller employee size.
5) Example (I'll use .... as placeholders for space to help with readability)

Industry.............Category.....Employees.....Avg_Sales....."DESIRED OUTPUT"
Accomodations....Widgets........1-4..............100..................100
Accomodations....Widgets........5-9..............75...................100
Accomodations....Widgets........10-19...........175.................175
Accomodations....Widgets........20-49...........225.................225
Accomodations....Widgets........50-99...........200.................225

Post #1394196

Lowell

Posted Friday, December 07, 2012 12:54 PM

SSChampion

Group: General Forum Members
Last Login: Today @ 5:41 AM
Points: 11,645, Visits: 27,740

based on the first post, here's the table, but i think that's already been created by an existing select statement;

we need data like this to generate a working query to generate the desired output.

With SomeSampleData (Category,Avg_Sales,Emp_Size,Industry,SalesRank,Emp_Rank )
AS
(

SELECT '01-Widgets',' $576',' 1 - 4',' Accommodations',' 3',' 1' UNION ALL
SELECT '01-Widgets',' $832',' 5 - 9',' Accommodations',' 1',' 2' UNION ALL
SELECT '01-Widgets',' $672',' 10 - 19',' Accommodations',' 2',' 3' UNION ALL
SELECT '01-Widgets',' $451',' 20 - 49',' Accommodations',' 5',' 4' UNION ALL
SELECT '01-Widgets',' $459',' 50 - 99',' Accommodations',' 4',' 5'

)

select * from SomeSampleData

Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son

Post #1394205

lmeinke

Posted Friday, December 07, 2012 2:01 PM

SSC-Addicted

Group: General Forum Members
Last Login: Thursday, December 20, 2012 8:13 AM
Points: 465, Visits: 162

Thank you for taking the time to put into T-SQL. I should have done this to begin with. Hopefully with this and the above post (2 prior) the situation will be understandable.

Post #1394239

Jeff Moden

Posted Friday, December 07, 2012 4:15 PM

SSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 7:51 PM
Points: 32,910, Visits: 26,800

lmeinke (12/7/2012)

Thank you for taking the time to put into T-SQL. I should have done this to begin with. Hopefully with this and the above post (2 prior) the situation will be understandable.

This requires ordered data. The only thing I can see that relects the correct order is the combination of Industry, Category, and Employees with the caveat that Employees must be converted to something sortable. For most code, that's going to require a very expensive triangular join to even come close to doing this in a set-based fashion (and, no... Recursive CTEs are not set-based and are frequently slower than a cursor in situations such as this).

My question to you is, can the table be modified to accept one or two persisted calculated columns to make this query lightning quick? If not, then I need to know how many rows the table will have in it so I can test an alternate method for performance.

--Jeff Moden

"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Post #1394283

lmeinke

Posted Monday, December 10, 2012 7:20 AM

SSC-Addicted

Group: General Forum Members
Last Login: Thursday, December 20, 2012 8:13 AM
Points: 465, Visits: 162

Jeff,
Thank you for your response. Yes, the table can be modified at this point. I started down that road by adding numeric columns representing employee size and avg_sales but am simply at a loss how to "use" them.

Post #1394608

« Prev Topic | Next Topic »

41 posts, Page 1 of 5

1 2 3 4 5 »»»

Permissions