Assing point to racce times - SQL Server Forums

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

slihp
Yak Posting Veteran

61 Posts

Posted - 2013-04-02 : 10:00:14

yep that works, thanks :-)

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources