SchoolYard Picks

Estado: Closed
Premio: $300
Propuestas recibidas: 2
Ganador: Splaszczyk

Winner

Resumen del concurso

One SQL Query (no loops)
Find the best combination in each Sports.
Each Name can only belong to ONE Sport.
There are limits to the number of positions per sport - See Sport_Limits

The Random baseline is:
Row Labels Average of Skill
Chess 5.22
Cricket 4.45
Rugby 4.86
Swimming 4.77
Tennis 4.85
Grand Total 4.83

The winner will be the highest average percentage above this baseline. If there is a tie, the winner will be the fastest query in processing time.

We are looking for the highest per Team. NOT the highest overall.
Must work on MS SQL.

The output is a table eg :

Sport, Names, Value
Cricket,Smith,1
Cricket,Scott,3
etc...

Please submit your query for evaluation.

Habilidades recomendadas

Servidor de Microsoft SQL

Winner

Publica un concurso similar

Comentarios del empleador

“High quality work!”

kevindaly70, Australia.

Tablero de aclaración pública

sanjaybhansali
- 7 años atrás
I am late but still of you can check pls check...
- 7 años atrás
sanjaybhansali
- 7 años atrás
Sir I send my query on email...
- 7 años atrás
Splaszczyk
- 7 años atrás
Task was difficult i would say but i was able to produce right results with no loops and no cursors but i've spent lot of time on that. Would be fair if you #increaseprize by 50 AUD for instance. #increaseprize
- 7 años atrás
Splaszczyk
- 7 años atrás
I will post another script ( that uses no iterative logic this time ) for evaluation soon
- 7 años atrás
Driss35
- 7 años atrás
Hello, I'm new in freelancer.com. I found the solution but I don't know how to send the script to you. They mentioned that Supported files types are: JPG, PNG, GIF. Please help.
- 7 años atrás
1. Organizador del concurso
  - 7 años atrás
  Upload the image of the code, and send the code.
  - 7 años atrás
2. Organizador del concurso
  - 7 años atrás
  Post the code in a chat with me
  - 7 años atrás
Organizador del concurso
- 7 años atrás
Hi All, with a few hours left there are 2 entries that do obey the rules, HMASTechnologies and sanjaybhansali. Both produce the same result, neither of them are perfect. Close but not perfect. Where there is a big difference is in the performance of the 2 queries HMASTTechnologies is much faster. So the current leader is HMASTTechnologies.
- 7 años atrás
Organizador del concurso
- 7 años atrás
Guys please only submit your entry and code if it actually solves the problem. If you know that there are duplicate names, the limits are not kept or it does not find an optimal solution, then do not submit. It needs to be right. The business objective will not change no matter how hard this is to solve.
- 7 años atrás
1. KBitBusiness
  - 7 años atrás
  Are you sure, that there IS a solution?
  With the given requirements and without a loop I do not think you can compare permutations of data within a single query.
  - 7 años atrás
sanjaybhansali
- 7 años atrás
pls check entry #7 my final solution. hope this solve your reuirement.
- 7 años atrás
sanjaybhansali
- 7 años atrás
If your data will be correct then my query will give right result. I can give you two solution first solution give 48 record is same skill member cannot duplicate in every sport team. Second solution give 60 record. If same skill member can be duplicate. I upload my entry again for second solution also..
- 7 años atrás
sanjaybhansali
- 7 años atrás
Sir as in your data 60 record is possible if name can be duplicate on sport team. If you do not want duplicate name on each team then 48 record is correct based on top skill per team. Pls check your data think my query is write sir. For example member SANCHEZ have 9 skill in both sport team chess and cricket. Then this can be included in one sport team. There are 12 name which is same skill in different sport team.
- 7 años atrás
sanjaybhansali
- 7 años atrás
hell mr Splaszczyk , sum of all sports limit 11+15+20+6=52 not 60. but i think limit is for individual team member : as described by Contest holder : sport limit is the number of names that can join the particular sport. For example the Tennis team can only have 6 team members or names.
- 7 años atrás
1. Organizador del concurso
  - 7 años atrás
  Sports Limit
  Chess 8
  Cricket 11
  Rugby 15
  Swimming 20
  Tennis 6
  - 7 años atrás
2. Organizador del concurso
  - 7 años atrás
  You have left off the chess (8)
  - 7 años atrás
sanjaybhansali
- 7 años atrás
contest holder please clarify : If Rugby team have 15 sport limit , then only 15 member can be join with no duplicate with other.. please clarify this...
- 7 años atrás
1. Organizador del concurso
  - 7 años atrás
  Yes, that is right, there are 60 slots in total in one name cannot appear in in more than one slot.
  - 7 años atrás
sanjaybhansali
- 7 años atrás
please clarify question so that i can proceed further..
- 7 años atrás
Splaszczyk
- 7 años atrás
sanjaybhansali 60 is a SUM of all sport limits. You need to fill all teams with players wit best skill with no duplicate names. It's a bit surprising that many people don't understand what i believe author clearly specified. Requirements are really well precised ( but problem is damn difficult if we want not to use iterative logic) Btw i changed Limit column from original varchar(50) to int on my testing environment.
- 7 años atrás
sanjaybhansali
- 7 años atrás
my sql query is right or wrong any changes require sir > please let me know
- 7 años atrás
sanjaybhansali
- 7 años atrás
Rugby team have sport limit is 15 i think.. what is 60 allocation ?
- 7 años atrás
sanjaybhansali
- 7 años atrás
wait i try to give full script here
- 7 años atrás
Ver 1 mensaje mas
1. Organizador del concurso
  - 7 años atrás
  There are 15 members of the Rugby team, you should have 60 allocations as there are 60 slots avalible
  - 7 años atrás
2. sanjaybhansali
  - 7 años atrás
  yes but member cannot be duplicate in each sport team. thats why only 15 member
  - 7 años atrás
sanjaybhansali
- 7 años atrás
you got my query ?
- 7 años atrás
Splaszczyk
- 7 años atrás
Shouldn't be rather
Sport, Names, Value
Tennis BAKER 9
Tennis BROOKS 9
Tennis BUTLER 9
Tennis DIAZ 9
Tennis JONES 9
Chess Someone 9
Chess SomeoneElse 9
Chess SomeElse_2 8

and so on ...

As 9 is higher skill than 1 or zero I THINK

Btw what if it turns out that is impossible to implement it without iterative logic ?
- 7 años atrás
1. Organizador del concurso
  - 7 años atrás
  I was providing a structure example, not a results one. Yes 9 is better than 0 and it is possible.
  - 7 años atrás
sanjaybhansali
- 7 años atrás
please replace word [LESSTHENEQUAL] with less then equal sign in sql
- 7 años atrás
sanjaybhansali
- 7 años atrás
please chat me i can transfer file of script
- 7 años atrás
sanjaybhansali
- 7 años atrás
sir here is some problem in entry they did not accept : < charactor
- 7 años atrás
sanjaybhansali
- 7 años atrás
put second part under the first part
- 7 años atrás
sanjaybhansali
- 7 años atrás
please combine both part
- 7 años atrás
Organizador del concurso
- 7 años atrás
U see my comment, please send me the code, I think I only got some of it
- 7 años atrás
sanjaybhansali
- 7 años atrás
please check entry #4
- 7 años atrás
sanjaybhansali
- 7 años atrás
can you please send me what output table you expect as result
- 7 años atrás
1. Organizador del concurso
  - 7 años atrás
  My Description does state..The output is a table eg :
  
  Sport, Names, Value
  Cricket,Smith,1
  Cricket,Scott,3
  etc...
  - 7 años atrás
Driss35
- 7 años atrás
Hello, can we create a temporary table in this query ?
- 7 años atrás
1. Organizador del concurso
  - 7 años atrás
  Yes, as long as there is no loop
  - 7 años atrás
sanjaybhansali
- 7 años atrás
what is criteria for condition : Each Name can only belong to ONE Sport. ? based on skill column ?
- 7 años atrás
Splaszczyk
- 7 años atrás
Not as easy as it seemed to be. I guess everyone knows solution needs ranking functions. Itterative solution would be easy but I'm working on something flexible that uses no loops and cursors as well as itterative logic. I came to moment where i'm assigning all limit ( 60 ) in one query but stack with duplicates and can't get rid of them in where clause :(
- 7 años atrás
1. Organizador del concurso
  - 7 años atrás
  Yip, thats the core of the problem. A pain the backside!!
  - 7 años atrás
KBitBusiness
- 7 años atrás
Sorry, I can not reply directly to your answer.
My solution uses variables for the 5 sports.
Everything else is one query.

My question about flexibility is about:
Shall the query be designed to fit to 4 or 6 or more sport types without any needs to change? Or does the query only need to solve the exactly given problem?
- 7 años atrás
1. Organizador del concurso
  - 7 años atrás
  Thanks for your interest, this is a small example of a much bigger problem that I want to solve, in reality there are hundreds more sports and tens of thousands more names. I need a solution that can scale up. without the need for change.
  - 7 años atrás
csundararajan
- 7 años atrás
Wow! Am Working... will update you..
- 7 años atrás
monmohon
- 7 años atrás
working...
- 7 años atrás
KBitBusiness
- 7 años atrás
Hello,
I am not sure, what the query result shall bring as the output?
Do you want a name of the list with the names and sport teams?
And how flexible shall the query be?
Are variables allowed before the query?
- 7 años atrás
Ver 2 mensajes más
1. Organizador del concurso
  - 7 años atrás
  What I do not want is any type of loop. If there is a pre query, that ok.
  - 7 años atrás
2. Organizador del concurso
  - 7 años atrás
  What do you mean by flexible?
  - 7 años atrás
sergioobici
- 7 años atrás
hi, can you please specify what the sport limits are and what do you intend with team? thank you
- 7 años atrás