SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Assing point to race times


Assing point to race times

Author
Message
ps_vbdev
ps_vbdev
SSC Eights!
SSC Eights! (985 reputation)SSC Eights! (985 reputation)SSC Eights! (985 reputation)SSC Eights! (985 reputation)SSC Eights! (985 reputation)SSC Eights! (985 reputation)SSC Eights! (985 reputation)SSC Eights! (985 reputation)

Group: General Forum Members
Points: 985 Visits: 712
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?
Phil Parkin
Phil Parkin
SSC Guru
SSC Guru (50K reputation)SSC Guru (50K reputation)SSC Guru (50K reputation)SSC Guru (50K reputation)SSC Guru (50K reputation)SSC Guru (50K reputation)SSC Guru (50K reputation)SSC Guru (50K reputation)

Group: General Forum Members
Points: 50541 Visits: 21152
What is an "assing point"?

Edit - ah, 'assign'!


Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

Please surround any code or links you post with the appropriate IFCode formatting tags. It helps readability a lot.
ps_vbdev
ps_vbdev
SSC Eights!
SSC Eights! (985 reputation)SSC Eights! (985 reputation)SSC Eights! (985 reputation)SSC Eights! (985 reputation)SSC Eights! (985 reputation)SSC Eights! (985 reputation)SSC Eights! (985 reputation)SSC Eights! (985 reputation)

Group: General Forum Members
Points: 985 Visits: 712
yes that's a great spot thanks, "Assing point to race times" should read "Assigning Points to race times".
Phil Parkin
Phil Parkin
SSC Guru
SSC Guru (50K reputation)SSC Guru (50K reputation)SSC Guru (50K reputation)SSC Guru (50K reputation)SSC Guru (50K reputation)SSC Guru (50K reputation)SSC Guru (50K reputation)SSC Guru (50K reputation)

Group: General Forum Members
Points: 50541 Visits: 21152
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?


Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

Please surround any code or links you post with the appropriate IFCode formatting tags. It helps readability a lot.
ps_vbdev
ps_vbdev
SSC Eights!
SSC Eights! (985 reputation)SSC Eights! (985 reputation)SSC Eights! (985 reputation)SSC Eights! (985 reputation)SSC Eights! (985 reputation)SSC Eights! (985 reputation)SSC Eights! (985 reputation)SSC Eights! (985 reputation)

Group: General Forum Members
Points: 985 Visits: 712
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.
vinu512
vinu512
Hall of Fame
Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)

Group: General Forum Members
Points: 3579 Visits: 1626
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 ;-)
ps_vbdev
ps_vbdev
SSC Eights!
SSC Eights! (985 reputation)SSC Eights! (985 reputation)SSC Eights! (985 reputation)SSC Eights! (985 reputation)SSC Eights! (985 reputation)SSC Eights! (985 reputation)SSC Eights! (985 reputation)SSC Eights! (985 reputation)

Group: General Forum Members
Points: 985 Visits: 712
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?

:-)
vinu512
vinu512
Hall of Fame
Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)

Group: General Forum Members
Points: 3579 Visits: 1626
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 ;-)
vinu512
vinu512
Hall of Fame
Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)

Group: General Forum Members
Points: 3579 Visits: 1626

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 ;-)
ps_vbdev
ps_vbdev
SSC Eights!
SSC Eights! (985 reputation)SSC Eights! (985 reputation)SSC Eights! (985 reputation)SSC Eights! (985 reputation)SSC Eights! (985 reputation)SSC Eights! (985 reputation)SSC Eights! (985 reputation)SSC Eights! (985 reputation)

Group: General Forum Members
Points: 985 Visits: 712
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search