Recent PostsRecent Posts Popular TopicsPopular Topics
 Home Search Members Calendar Who's On

 TOP 25% rows by Weightage Rate Topic Display Mode Topic Options
Author
 Message
 Posted Wednesday, April 17, 2013 9:10 AM
 Grasshopper Group: General Forum Members Last Login: Saturday, October 8, 2016 12:02 AM Points: 11, Visits: 32
 I have a table as below. I need to find the members which are in the bottom quartile (25%) of the weights in each class`ClassID MemberID Weight A 1 10A 2 20A 3 40A 5 25A 6 15A 8 10B 1 50B 2 10B 3 60B 6 400`For above example, total weight of class A is 120. If I order by weight and pick the top ones such that their summed weight comprise less than or equal to 25% of total weight (25% of 120 = 30 for class A)I would pick Members 1 and 8. If I include member 6, it would exceed 25% (30)The output I need is`ClassID MemberID Weight A 1 10A 8 10B 1 50B 2 10B 3 60`What would be the best way of achieving this in TSQL (Sql server 2008)?Hope the question is clear
Post #1443331
 Posted Wednesday, April 17, 2013 10:00 AM
 SSCoach Group: General Forum Members Last Login: Today @ 9:15 AM Points: 16,139, Visits: 16,843
 You can do this using a running total. Take a look at this article that explains how to do a running total in sql.http://www.sqlservercentral.com/articles/T-SQL/68467/If you get stuck feel free to post back here. It is best if you can provide data in a consumable format. Since you are brand new around here I did this for you so you can see.`;with Data(ClassID, MemberID, Weight) as( select 'A', 1, 10 union all select 'A', 2, 20 union all select 'A', 3, 40 union all select 'A', 5, 25 union all select 'A', 6, 15 union all select 'A', 8, 10 union all select 'B', 1, 50 union all select 'B', 2, 10 union all select 'B', 3, 60 union all select 'B', 6, 400)` _______________________________________________________________Need help? Help us help you. Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.Need to split a string? Try Jeff Moden's splitter.Cross Tabs and Pivots, Part 1 – Converting Rows to Columns Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs Understanding and Using APPLY (Part 1)Understanding and Using APPLY (Part 2)
Post #1443354
 Posted Wednesday, April 17, 2013 10:04 AM
 SSCrazy Group: General Forum Members Last Login: Today @ 9:12 AM Points: 2,015, Visits: 22,613
 If you're using SQL Server 2012, you can do this`;with Data(ClassID, MemberID, Weight) as( select 'A', 1, 10 union all select 'A', 2, 20 union all select 'A', 3, 40 union all select 'A', 5, 25 union all select 'A', 6, 15 union all select 'A', 8, 10 union all select 'B', 1, 50 union all select 'B', 2, 10 union all select 'B', 3, 60 union all select 'B', 6, 400),CTE AS (SELECT ClassID,MemberID,Weight, SUM(Weight) OVER(PARTITION BY ClassID ORDER BY Weight) AS RunningTotalWeight, SUM(Weight) OVER(PARTITION BY ClassID) AS TotalWeight FROM Data)SELECT ClassID,MemberID,WeightFROM CTEWHERE RunningTotalWeight <= TotalWeight/4ORDER BY ClassID,MemberID;` ____________________________________________________Deja View - The strange feeling that somewhere, sometime you've optimised this query beforeHow to get the best help on a forumhttp://www.sqlservercentral.com/articles/Best+Practices/61537
Post #1443360
 Posted Thursday, April 18, 2013 1:08 AM
 Grasshopper Group: General Forum Members Last Login: Saturday, October 8, 2016 12:02 AM Points: 11, Visits: 32
 Thanks very much guys...Unfortunately I'm still on 2008I've tried the below and it works, but I'm not sure how to achieve this through the quirky update as advised in the article`;with Data(ClassID, MemberID, Weight) as( select 'A', 1, 10 union all select 'A', 2, 20 union all select 'A', 3, 40 union all select 'A', 5, 25 union all select 'A', 6, 15 union all select 'A', 8, 10 union all select 'B', 1, 50 union all select 'B', 2, 10 union all select 'B', 3, 60 union all select 'B', 6, 400)--SELECT* FROM Data ORDER BY ClassID,WeightSELECT d.classid,d.memberid,d.WeightFROM Data dINNER JOIN ( SELECT D2.ClassID,SUM(Weight) as total_weight FROM Data D2 GROUP BY D2.ClassID ) Totals ON Totals.ClassID = d.ClassID WHERE (SELECT SUM(weight) FROM Data D3 where D3.ClassID = d.ClassID and D3.weight <= d.weight) <= 0.25*Totals.total_weight`Help is much appreciated
Post #1443626
 Posted Thursday, April 18, 2013 7:49 AM
 SSCoach Group: General Forum Members Last Login: Today @ 9:15 AM Points: 16,139, Visits: 16,843
 chintan.floydian (4/18/2013)Thanks very much guys...Unfortunately I'm still on 2008I've tried the below and it works, but I'm not sure how to achieve this through the quirky update as advised in the article`;with Data(ClassID, MemberID, Weight) as( select 'A', 1, 10 union all select 'A', 2, 20 union all select 'A', 3, 40 union all select 'A', 5, 25 union all select 'A', 6, 15 union all select 'A', 8, 10 union all select 'B', 1, 50 union all select 'B', 2, 10 union all select 'B', 3, 60 union all select 'B', 6, 400)--SELECT* FROM Data ORDER BY ClassID,WeightSELECT d.classid,d.memberid,d.WeightFROM Data dINNER JOIN ( SELECT D2.ClassID,SUM(Weight) as total_weight FROM Data D2 GROUP BY D2.ClassID ) Totals ON Totals.ClassID = d.ClassID WHERE (SELECT SUM(weight) FROM Data D3 where D3.ClassID = d.ClassID and D3.weight <= d.weight) <= 0.25*Totals.total_weight`Help is much appreciatedThere are a few things you would have to do differently here for the quirky update to work. You would need to move this to a table instead of a cte. You need to have a clustered index as described in the article. The idea here is that you could use the quirky update to get your running total and then select rows where the total column is less than max of that column * 25. _______________________________________________________________Need help? Help us help you. Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.Need to split a string? Try Jeff Moden's splitter.Cross Tabs and Pivots, Part 1 – Converting Rows to Columns Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs Understanding and Using APPLY (Part 1)Understanding and Using APPLY (Part 2)
Post #1443872

 Permissions