Assing point to race times

  • I bulk insert a csv file (generated from our timing laptops at race events) into storage table in our database.

    the timing laptops assigns a position based on riders times so it looks something like this

    Position,rider,time difference

    1,a,00:00:00

    2,b,00:00:05

    3,c,00:00:10

    4,d,00:00:15

    5,e,00:00:15

    6,f,00:00:20

    as you can see below although rider d & e have the same time difference in the output file, they have been given different positions. When i bulk insert the csv i was using a case statement to apply a points based on the position field. This is wrong as you can see rider d & e have the same times so they should get the same points. so i really need to assign points based on the time difference.

    ID|Position|Rider|Diff |Points

    1 1 a 00:00:00 50

    2 2 b 00:00:05 47

    3 3 c 00:00:10 44

    4 4 d 00:00:15 43

    5 5 e 00:00:15 42

    6 6 f 00:00:20 41

    applying the business logic i have, in the above table rider d & e should both share position 4 and both have the same points as below

    ID|Position|Rider|Diff |Points

    1 1 a 00:00:00 50

    2 2 b 00:00:05 47

    3 3 c 00:00:10 44

    4 4 d 00:00:15 43

    5 4 e 00:00:15 43

    6 6 f 00:00:20 41

    is it possible to build logic into my bulk insert?

  • What is an "assing point"?

    Edit - ah, 'assign'!

    The absence of evidence is not evidence of absence
    - Martin Rees
    The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
    - Phil Parkin

  • yes that's a great spot thanks, "Assing point to race times" should read "Assigning Points to race times".

  • Surely the ideal thing here would be to fix the CSV file so that positions are shown correctly? But I presume that change is not possible, for some reason?

    The absence of evidence is not evidence of absence
    - Martin Rees
    The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
    - Phil Parkin

  • yes that is an option, and the best one so far as there will be a number corrections necessary i.e. duplicate riders, name spelling. but if possible to to do this at load it would be one less thing to do.

  • How do you decide the points??....What's the logic behind it?

    Vinu Vijayan

    For better and faster solutions please check..."How to post data/code on a forum to get the best help" - Jeff Moden[/url] 😉

  • stated in my post "When i bulk insert the csv i was using a case statement to apply a points based on the position field".

    the logic behind how the points are allocated is irrelevant to the problem i posted. which is how to deal with riders with same time during the data load, and even went to lengths

    to provide dummy data in my explanatory post

    I always find forum contributors that reply too posts picking up on spelling mistakes and asking irrelevant questions can never/don't have the ability to answer the problem posted. Now can you answered the question or are you just going to come back with a smart reply?

    🙂

  • ps_vbdev (4/3/2013)


    stated in my post "When i bulk insert the csv i was using a case statement to apply a points based on the position field".

    the logic behind how the points are allocated is irrelevant to the problem i posted. which is how to deal with riders with same time during the data load, and even went to lengths

    to provide dummy data in my explanatory post

    I always find forum contributors that reply too posts picking up on spelling mistakes and asking irrelevant questions can never/don't have the ability to answer the problem posted. Now can you answered the question or are you just going to come back with a smart reply?

    🙂

    If you are such a smartass then why don't you solve the problem yourself??....Why do you need help from others?

    We frequent forums to help people who post questions and to learn from the solutions posted. People here take time out of their busy schedules to work out a working solution for posters like you. It is very difficult to just read an explanation about a requirement and work out a solution for it. There may be differences in what we understand and what you had actually meant. That is when we ask questions to know the exact requirement so that both the posters and our time isn't wasted.

    I asked the question of the logic behind your points calculation because in the Case statement where you are calculating the points you can use a variation of the function "RANK" to get things done.....but since you are too smart to post relevant answers and are more interested in whining about other things.......I'd suggest you go figure it out for yourself.

    Vinu Vijayan

    For better and faster solutions please check..."How to post data/code on a forum to get the best help" - Jeff Moden[/url] 😉

  • I always find forum contributors that reply too posts picking up on spelling mistakes and asking irrelevant questions can never/don't have the ability to answer the problem posted.

    You've always looked in the wrong places then.

    Welcome to SSC. 🙂

    Here is an example

    Vinu Vijayan

    For better and faster solutions please check..."How to post data/code on a forum to get the best help" - Jeff Moden[/url] 😉

  • Expected reply.

    Not a smart arse, just tired off people with misplaced self-worth.

    below is an answer from a forum member for the same post

    "you can apply an UPDATE based on DENSE_RANK to give all rows with same time difference the same Position and Points values"

    but thanks for not helping

  • ps_vbdev (4/3/2013)


    Expected reply.

    Not a smart arse, just tired off people with misplaced self-worth.

    below is an answer from a forum member for the same post

    "you can apply an UPDATE based on DENSE_RANK to give all rows with same time difference the same Position and Points values"

    but thanks for not helping

    Good for you then. 🙂

    Vinu Vijayan

    For better and faster solutions please check..."How to post data/code on a forum to get the best help" - Jeff Moden[/url] 😉

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply