June 24, 2009 at 2:54 pm
I have 4 item classes. Class A = 80% of receipts, Class B=15%, Class C=4% and Class D= 1%. So I have totaled up the receipts for the last 12 months and I have come up with individual percentages of receipts, for example Item 1111=.06, Item 1112=.04, Item 1113=.02, and so on. Right now I take the output and sort it by the percentage of receipts to find the top 80%, top 15%, top 4% and top 1%, then I can update the data with that information. What I would like is for SQL to say, hey, here are your items that make up the top 80% of receipts and they should be Item Class A. Here are your items that make up the top 15% of receipts and they should be Item Class B, etc..
Obviously I'll need a running total and I thought about using a cross join for this, but I'm not sure and I'm having trouble visualizing it at the moment. Does anyone have a better idea?
Thanks
Adam
June 24, 2009 at 2:57 pm
Could you provide some sample data and the expected result for those data please.
The link in my signature should be helpful for this.
June 24, 2009 at 7:30 pm
Could I ask also that you confirm that we should compute the running totals in descending percentage order?
For example, if the available percentages were 35%, 20%, 15%, 10% and 5% to get to 50% we should take 35% + 20% and not 20% + 15% + 10% + 5%...oh and if we can't get to the percentage target exactly (as in this case) should we go 'over' or 'under'.
Thanks
Paul
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
June 24, 2009 at 7:59 pm
Paul White (6/24/2009)
Could I ask also that you confirm that we should compute the running totals in descending percentage order?For example, if the available percentages were 35%, 20%, 15%, 10% and 5% to get to 50% we should take 35% + 20% and not 20% + 15% + 10% + 5%...oh and if we can't get to the percentage target exactly (as in this case) should we go 'over' or 'under'.
Thanks
Paul
Correct. And over is fine. I am thinking I need to create some row counters and sum up the totals of the rows equal to or less than the row number if that makes any sense. I think I have it worked out in my head. Let's just hope I do not forget it by the time I get to my desk tomorrow.
June 24, 2009 at 8:18 pm
That would work, but I have a feeling Flo is about to delight and amaze everyone with a rather nifty running-totals implementation.
Just a guess :w00t:
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply