question

Ryancerney avatar image
Ryancerney asked

Top 8 rows based on value with cost limits

I am looking to automate a process that I am using to pick dog sled teams for the Iditarod. A group of about 50 of us every year have a competition sort of like fantasy football or other sports where you pick 8 dog sled teams and you get points awarded to you based on how they place overall. Based on their current ranking, the teams cost x amount of dollars to have on your list of 8. You must remain under $100,000 total. What I started to do a few years ago is I take raw data from the Iditarod websites and I pull it into sql server and then I do some trend analysis and such. I then have a query to pull all of the current year racers and sort them by their trend calculation. I then pick out my 8 teams manually while having to ensure I stay under the 100k cap. What I am looking to do is automate that picking process so that the query will return me the top 8 teams while staying under the cap. What I don't want is for the query to give me the top 2 racers that are 90k total and then give me the bottom 6 racers that are 1500 a piece or whatever.

Here is the actual data, only 49 rows total. So, you can see their is a cost, a rank, and a trend calculation. I am thinking there has to be a way to write a query that would basically look at the sum of the trend across a group of 8 racers where the total cost is under 100k. It would likely have to do this calculation numerous times across the dataset so that it could determine which grouping of 8 has the highest trend calculation sum while maintaining the cost limit. This is obviously a trivial question and not a work related problem, but thought it would be fun to get some much bigger brains then mine working on a potential solution so I can automate my "mush madness" process :)


RACER_NAMECOSTRankTREND_POWER_CALCJeff Deeter16000180.075133Travis Beals25000120.062658Jessie Holmes23000130.058489Michelle Phillips21000150.050182Mille Porsild2800090.049422Ramey Smyth27000100.041991Paige Drobny22000140.036373Aaron Burmeister3200060.028302Joar Leifseth Ulsom4000030.023893Matthew Failor10000220.01628Mitch Seavey4500020.0147Brent Sass3300050.014591Richie Diehl26000110.014267Peter Kaiser3500040.012196Ryan Redington2900080.00925Anna Berington5000240.008707Kristy Berington5000250.008443Lev Shvarts5000230.008416Matt Hall20000160.005387Nicolas Petit3100070.004903Mats Pettersson14000190.0038Aaron Peck11000210.003342Ryne Olson5000260.001251Dallas Seavey5100010.000792Dan Kaduce19000170.000444Deke Naaktgeboren3000280Eric Kelly1000350Gerhardt Thiart1000460Hanna Lyrek1000420Lisbet Norris1000340Martin Buser3000300Martin Massicotte1000410Matt Paveglio1000440Amanda Otto1000390Anja Radano1000330Apayauq Reitan1000490Bridgett Watkins1000380Chad Stoddard3000310Joe Taylor1000400Joshua McNeal3000290Julie Ahnen1000370Kailyn Davis1000450Karin Hendrickson1000320KattiJo Deeter1000360Sean Williams1000430Sebastien Dos Santos Borges1000480Riley Dyche3000270Yuka Honda1000470Hugh Neff1200020-0.003167

sql server 2017analysisdatasets
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

2 Answers

·
anthony.green avatar image
anthony.green answered

Based on your sample data what is your expected result set?

1 comment
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Ryancerney avatar image Ryancerney commented ·

That's a great question. I think that is ultimately what I want answered through this query as I am just manually picking and choosing from the list right now based on that trend value and trying to stay below the 100k mark. I'm not as granular as I could be with a proper query to pull the data.

0 Likes 0 ·
anthony.green avatar image
anthony.green answered 
create table #temp (RACER_NAME VARCHAR(100),COST INT, [RANK] INT, TREND_POWER_CAL DECIMAL(30,15))
INSERT INTO #temp values
('Jeff Deeter',16000,18,0.075133),
('Travis Beals',25000,12,0.062658),
('Jessie Holmes',23000,13,0.058489),
('Michelle Phillips',21000,15,0.050182),
('Mille Porsild',28000,9,0.049422),
('Ramey Smyth',27000,10,0.041991),
('Paige Drobny',22000,14,0.036373),
('Aaron Burmeister',32000,6,0.028302),
('Joar Leifseth Ulsom',40000,3,0.023893),
('Matthew Failor',10000,22,0.01628),
('Mitch Seavey',45000,2,0.0147),
('Brent Sass',33000,5,0.014591),
('Richie Diehl',26000,11,0.014267),
('Peter Kaiser',35000,4,0.012196),
('Ryan Redington',29000,8,0.00925),
('Anna Berington',5000,24,0.008707),
('Kristy Berington',5000,25,0.008443),
('Lev Shvarts',5000,23,0.008416),
('Matt Hall',20000,16,0.005387),
('Nicolas Petit',31000,7,0.004903),
('Mats Pettersson',14000,19,0.0038),
('Aaron Peck',11000,21,0.003342),
('Ryne Olson',5000,26,0.001251),
('Dallas Seavey',51000,1,0.000792),
('Dan Kaduce',19000,17,0.000444),
('Deke Naaktgeboren',3000,28,0),
('Eric Kelly',1000,35,0),
('Gerhardt Thiart',1000,46,0),
('Hanna Lyrek',1000,42,0),
('Lisbet Norris',1000,34,0),
('Martin Buser',3000,30,0),
('Martin Massicotte',1000,41,0),
('Matt Paveglio',1000,44,0),
('Amanda Otto',1000,39,0),
('Anja Radano',1000,33,0),
('Apayauq Reitan',1000,49,0),
('Bridgett Watkins',1000,38,0),
('Chad Stoddard',3000,31,0),
('Joe Taylor',1000,40,0),
('Joshua McNeal',3000,29,0),
('Julie Ahnen',1000,37,0),
('Kailyn Davis',1000,45,0),
('Karin Hendrickson',1000,32,0),
('KattiJo Deeter',1000,36,0),
('Sean Williams',1000,43,0),
('Sebastien Dos Santos Borges',1000,48,0),
('Riley Dyche',3000,27,0),
('Yuka Honda',1000,47,0),
('Hugh Neff',12000,20,-0.003167)
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.