Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
LonnieM
Starting Member
15 Posts |
 Posted - 2012-04-03 : 11:10:39
|
| Hello All,I am hoping someone can help me with an elegant and practical solution. I have sales data representing more than a million individual sale items and roughly 40 million rows of actual sales transactions. Because all sales transaction contain an invoice number I can group all sales by invoice and determine all parts sold on the same invoice. Approaching this with a sample of one (one item) it is relatively straight forward to select out all invoices that contain that particular item, count up (group) all other items also appearing on those invoices, then rank or score them by the frequency of their appearance.Now, I need to apply this logic to the entire database and a cursor seems illogical and would likely exceed the system resources.I'm looking for the smart way to approach this situation. I can take a subset of roughly 12,000 items as the core for this project but the same questions apply.Any and all help is appreciated.Thank you,Lonnie M |
|
|
X002548
Not Just a Number
15586 Posts |
|
|
LonnieM
Starting Member
15 Posts |
Posted - 2012-04-03 : 11:22:42
|
| Brett,The output I envision would be a scoring system where each part (row) would have the top 5 parts also sold with that part ranked by frequency. My major road block is how do I take this from one sku to more than one million skus in an intelligent fashion. The logic (using one sku) is straight forward....with one million skus....not so much.I can pare this down to roughly 12,000 skus to begin with and may do that. I'm not sure I want to actually set up a cube for this process but this may end up to be the only way.Thoughts?Thanks,Lonnie M |
 |
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2012-04-03 : 12:18:41
|
| This sounds like a business "Rule" of some typeI like to encapsulate all of my "Rules" into Views.That way, this one view can be used by many other processes (Stored Procedures) and all of the results would be the same, unlike trying to maintain the rule all over the place.What we need to help you, is what some sample output would look like, the tables that the data would come from and sample data (in DML form) that would comprise the sample output.If you post that, I'm sure the Yaks here can come up with a solution for you.btw..where are you from on the globe?Brett8-)Hint: Want your questions answered fast? Follow the direction in this linkhttp://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspxWant to help yourself?http://msdn.microsoft.com/en-us/library/ms130214.aspxhttp://weblogs.sqlteam.com/brettk/http://brettkaiser.blogspot.com/ |
|
|
|
|
|
|
|
|
Finding "like" or "related" items