Log in  ::  Register  ::  Not logged in

 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
 Forum Newbie Group: General Forum Members Last Login: Wednesday, October 09, 2013 5:41 AM Points: 9, Visits: 26
 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
 SSChampion Group: General Forum Members Last Login: Wednesday, December 04, 2013 2:23 PM Points: 10,854, Visits: 10,012
 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
 SSCommitted Group: General Forum Members Last Login: Thursday, December 05, 2013 8:25 AM Points: 1,694, Visits: 19,540
 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;` ____________________________________________________How to get the best help on a forumhttp://www.sqlservercentral.com/articles/Best+Practices/61537Never approach a goat from the front, a horse from the rear, or a fool from any direction.
Post #1443360
 Posted Thursday, April 18, 2013 1:08 AM
 Forum Newbie Group: General Forum Members Last Login: Wednesday, October 09, 2013 5:41 AM Points: 9, Visits: 26
 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
 SSChampion Group: General Forum Members Last Login: Wednesday, December 04, 2013 2:23 PM Points: 10,854, Visits: 10,012
 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