April 2, 2013 at 4:14 am
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?
April 2, 2013 at 4:18 am
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
April 2, 2013 at 4:26 am
yes that's a great spot thanks, "Assing point to race times" should read "Assigning Points to race times".
April 2, 2013 at 5:28 am
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
April 2, 2013 at 5:34 am
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.
April 3, 2013 at 5:09 am
April 3, 2013 at 5:33 am
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?
🙂
April 3, 2013 at 6:12 am
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.
April 3, 2013 at 6:15 am
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. 🙂
April 3, 2013 at 6:23 am
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
April 3, 2013 at 6:31 am
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. 🙂
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply