SQL Server Forums
Profile | Register | 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
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

slihp
Yak Posting Veteran

55 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
52249 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

55 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
52249 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  
 New 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