Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 Assing point to racce times

Author  Topic 

slihp
Yak Posting Veteran

61 Posts

Posted - 2013-04-02 : 06:13:03
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?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-04-02 : 07:32:39
you can apply an UPDATE based on DENSE_RANK to give all rows with same time difference the same Position and Points values

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

slihp
Yak Posting Veteran

61 Posts

Posted - 2013-04-02 : 10:00:14
yep that works, thanks :-)
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-04-02 : 12:01:38
welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
   

- Advertisement -