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

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 Assing point to racce times
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

slihp
Yak Posting Veteran

61 Posts

Posted - 04/02/2013 :  06:13:03  Show Profile  Reply with Quote
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

India
52326 Posts

Posted - 04/02/2013 :  07:32:39  Show Profile  Reply with Quote
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 - 04/02/2013 :  10:00:14  Show Profile  Reply with Quote
yep that works, thanks :-)
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52326 Posts

Posted - 04/02/2013 :  12:01:38  Show Profile  Reply with Quote
welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000