Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Assing point to race times Expand / Collapse
Author
Message
Posted Tuesday, April 02, 2013 4:14 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Today @ 9:21 AM
Points: 49, Visits: 325
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?
Post #1437791
Posted Tuesday, April 02, 2013 4:18 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Yesterday @ 4:25 AM
Points: 4,828, Visits: 11,184
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.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.
Post #1437795
Posted Tuesday, April 02, 2013 4:26 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Today @ 9:21 AM
Points: 49, Visits: 325
yes that's a great spot thanks, "Assing point to race times" should read "Assigning Points to race times".
Post #1437811
Posted Tuesday, April 02, 2013 5:28 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Yesterday @ 4:25 AM
Points: 4,828, Visits: 11,184
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.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.
Post #1437827
Posted Tuesday, April 02, 2013 5:34 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Today @ 9:21 AM
Points: 49, Visits: 325
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.
Post #1437832
Posted Wednesday, April 03, 2013 5:09 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, April 08, 2014 2:40 AM
Points: 1,118, Visits: 1,573
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
Post #1438257
Posted Wednesday, April 03, 2013 5:33 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Today @ 9:21 AM
Points: 49, Visits: 325
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?

Post #1438268
Posted Wednesday, April 03, 2013 6:12 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, April 08, 2014 2:40 AM
Points: 1,118, Visits: 1,573
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
Post #1438285
Posted Wednesday, April 03, 2013 6:15 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, April 08, 2014 2:40 AM
Points: 1,118, Visits: 1,573

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
Post #1438289
Posted Wednesday, April 03, 2013 6:23 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Today @ 9:21 AM
Points: 49, Visits: 325
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


Post #1438290
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse