We help IT Professionals succeed at work.

Modifying Data In Query To Sort

Bob Schneider
on
184 Views
Last Modified: 2014-11-20
I have a results query that gets sent to finishers of a race shortly after they finish.  They have two times, a chip time and a gun time.  They are usually a little bit different.  I want to be able to sort the data based on one of the two times listed (chip or gun).  Currently I sort by event place get the data using ReDim Preserve, and then do a bubble sort after the fact.  Is there a better way?  The root problem is that the times are in there as text ("00:12:34.567").  In order to do a good sort I convert them to seconds as I pull them out and then sort.  I am looking for a better approach.  Here is my code:

		x = 0
		ReDim ThisArr(2, 0)
		Set rs = Server.CreateObject("ADODB.Recordset")
		sql = "SELECT ParticipantID, FnlTime, ChipTime FROM IndResults WHERE RaceID = " & lRaceID & " ORDER BY EventPl"
		rs.Open sql, conn, 1, 2
		Do While Not rs.EOF
			ThisArr(0, x) = rs(0).Value
			ThisArr(1, x) = ConvertToSeconds(rs(1).Value)
            ThisArr(2, x) = ConvertToSeconds(rs(2).Value)
			x = x + 1
			ReDim Preserve ThisArr(2, x)
			rs.MoveNext
		Loop
		rs.Close
		Set rs = Nothing
		
		're-order results
		For y = 0 To UBound(ThisArr, 2) - 2
		    For m = y + 1 To UBound(ThisArr, 2) - 1
                If sRsltsSort = "chip" Then
		            If CSng(ThisArr(2, y)) > CSng(ThisArr(2, m)) Then
		                For n = 0 To 2
		                    TempArr(n) = ThisArr(n, y)
		                    ThisArr(n, y) = ThisArr(n, m)
		                    ThisArr(n, m) = TempArr(n)
		                Next
		            End If
                Else
		            If CSng(ThisArr(1, y)) > CSng(ThisArr(1, m)) Then
		                For n = 0 To 2
		                    TempArr(n) = ThisArr(n, y)
		                    ThisArr(n, y) = ThisArr(n, m)
		                    ThisArr(n, m) = TempArr(n)
		                Next
		            End If
                End If
		    Next
		Next
		
		For x = 0 To UBound(ThisArr, 2) - 1
			iMyOverallPl = CInt(iMyOverallPl) + 1
			If CLng(ThisArr(0, x)) = CLng(lPartID) Then Exit For
		Next

Open in new window

Comment
Watch Question

Phillip BurtonDirector, Practice Manager and Computing Consultant
Awarded 2014
Top Expert 2014

Commented:
To change a time to number of seconds, use DateDiff(Second,<<Start of Day>>,<<Day/Time>>)

such as the following:

declare @mydate as datetime
set @mydate = '2014-11-19 14:10:30'
select datediff(second,convert(date,@mydate),@mydate)

Open in new window


gives 51030, the number of seconds in 14 hours, 10 minutes and 30 seconds.
Scott FellDeveloper
CERTIFIED EXPERT
Fellow
Most Valuable Expert 2013
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
Big MontyWeb Ninja at large
CERTIFIED EXPERT
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
Bob SchneiderCo-Owner

Author

Commented:
Thanks.  

To summarize,

1) It should be datetime.  i kind of suspected that...a long time ago.  Poor design right out of the gate.  Are you saying I should convert the datatype within sql server.  Then, of course, the sort will be easy.  I will try that.  Hopefully the data isn't "corrupt" from a date/time standpoint given that it has been a varchar field for so long...who knows what kinds of issues that might find.  But boy would that solve things.

2) Bubblesort is bad.  Yeah I get that too but, unfortunately I can't sort from within the sql server query if I want it to sort by time and it views it as text.   Or can I?

3) Using datatables.  No, not on this.  This is just designed to get an individual's overall place, time (chip time or gun time) as well as their place by gender and age grouping.  Having said that, I am definitely going to convert the page that displays all results to a datatable.  Hopefully I can get that done prior to next week's big races following the schema that you two helped me with on the series standings.  That might be my next post if I can't figure it out.

4) Are stored procedures faster than asp queries or just more secure and persistent?

Comments are welcome and I can't express how much I appreciate you guys!!!!
Big MontyWeb Ninja at large
CERTIFIED EXPERT
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
Scott FellDeveloper
CERTIFIED EXPERT
Fellow
Most Valuable Expert 2013
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
Bob SchneiderCo-Owner

Author

Commented:
Big race is a week away...this is incredibly helpful...created indexes, upgraded to sql server 2012 web, and now this should help if it takes the bubble sort out:

  cast(FnlTime as datetime)
Scott FellDeveloper
CERTIFIED EXPERT
Fellow
Most Valuable Expert 2013
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
Scott FellDeveloper
CERTIFIED EXPERT
Fellow
Most Valuable Expert 2013

Commented:
Make sure that FnlTime is in a datetime format before casting it.
Bob SchneiderCo-Owner

Author

Commented:
Cast isn't working...getting an error.  Will this type of data convert to datetime: 20:04.20

I don't see anything that is outside of that structure.
Big MontyWeb Ninja at large
CERTIFIED EXPERT
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
Scott FellDeveloper
CERTIFIED EXPERT
Fellow
Most Valuable Expert 2013
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
Bob SchneiderCo-Owner

Author

Commented:
tried it...got an error that it resulted in an out-of-range value
Scott FellDeveloper
CERTIFIED EXPERT
Fellow
Most Valuable Expert 2013
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
Big MontyWeb Ninja at large
CERTIFIED EXPERT
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
Bob SchneiderCo-Owner

Author

Commented:
Scott, but that removes the option of sorting from within the db correct?
Scott FellDeveloper
CERTIFIED EXPERT
Fellow
Most Valuable Expert 2013
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
Bob SchneiderCo-Owner

Author

Commented:
Ok lets try again:  How can I take this sql statement
"SELECT FnlTime FROM IndResults"
and sort by time or datetime or time if the output typically looks like: 1:34.456

This would solve my sorting problem completely.  Otherwise I will have to change the field to time and I would like to avoid that if I can.
Big MontyWeb Ninja at large
CERTIFIED EXPERT
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
Bob SchneiderCo-Owner

Author

Commented:
Yes and I got a "out of range" error.

I ran this in EM:

SELECT        TOP (100) PERCENT EventPl, EventPl AS Expr1, FnlTime
FROM            dbo.IndResults
ORDER BY CONVERT(datetime, FnlTime)
Bob SchneiderCo-Owner

Author

Commented:
Also did it with convert(time, FnlTime)
Bob SchneiderCo-Owner

Author

Commented:
Sorry for all of the posts...also tried to create another column called FinalTime using the time data type and then copying all the FnlTime data over and I got a message "Bad variable type."
Scott FellDeveloper
CERTIFIED EXPERT
Fellow
Most Valuable Expert 2013
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
Bob SchneiderCo-Owner

Author

Commented:
Would it work if the '00:' were not at the front?
Bob SchneiderCo-Owner

Author

Commented:
I hate to be a pain but it looks like the data is "good."  My varchar field has length of 12 where yours is 20...II just don't see the issue.  And all of my data can be converted to seconds so...

I get the following "types" of return:
9:59.14
9:50:17.60
58:07.984
52:41
53:00
52:26.9
1:00:00.37

There are 44000 records.  Any way I could check them one at a time.  I already listed them out and don't see an issue.
Scott FellDeveloper
CERTIFIED EXPERT
Fellow
Most Valuable Expert 2013
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
Scott FellDeveloper
CERTIFIED EXPERT
Fellow
Most Valuable Expert 2013

Commented:
.... you don't have to right pad the fraction, just left pad the hours, mins, seconds.
Bob SchneiderCo-Owner

Author

Commented:
Ok so I can do that pretty easily.  And then I can sort from within sql server.  But the display would need to NOT be left-padded, so I would have to un-pad when displaying.  How much of the speed that I gained by not bubble-sorting would I lose by un-padding the display?
Bob SchneiderCo-Owner

Author

Commented:
Actually a change of plans.  First of all, how would I left-pad zeroes to get the correct time format?  I could write a function in asp but is there something in sql server that would work?

My plan is to add another field to my IndResults table called FinalTime that is a time datatype and replicate the FnlTime varchar data there and use that only for sorting.   Then I can still use FnlTime to display.
Scott FellDeveloper
CERTIFIED EXPERT
Fellow
Most Valuable Expert 2013
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
Bob SchneiderCo-Owner

Author

Commented:
I would think so, yes.  The vast majority of the times are between 10:00 and 59:59 but some exceed an hour and some are less than 10 minutes.  Correct me if I am wrong but it seems that the only workaround for me, other than to change the data type of the field and re-do all of my code anywhere this data is inserted, is to create the parallel field with the correct type and create a function that will convert to "string" times to "time" times with the correct number of zeroes and ":".  Please tell me I am wrong!  :)
Bob SchneiderCo-Owner

Author

Commented:
Ok I wrote code to convert this: 1:45:09

To this: 01:45:09

And still got a "Bad data type." error when I tried to put it in a time(7) datatype field.
Scott FellDeveloper
CERTIFIED EXPERT
Fellow
Most Valuable Expert 2013
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
Bob SchneiderCo-Owner

Author

Commented:
Scott, that is exactly what I did:
1) I created a new field called FinalTime of datatype time(7)
2) I changed it to time(3) although it wasn't working before that.
3) I used my script to "move" all FnlTime (text) data to the new (time) field after ensuring that the script formatted them correctly.   Got a  "Bad data type." error.  Note that I had trimmed it before putting it in there.
4) I tried to open a view of the data and enter the exact same as what my script generated and it went in just fine.
5) I tried to insert the default value "00:00:00" into each cell via classic asp and I got a  "Bad data type." error.
6) I successfully entered the default value "00:00:00" in enterprise manager.

So I can enter manually with no difficulty but can't do it via my asp script, even though it appears to be generating the exact same data.

Here is the code that i used to just try to insert "0:00:00" into the field.  Again, I can do this manually but the code generates a "Bad data type." error
Set rs = Server.CreateObject("ADODB.Recordset")
sql = "SELECT FinalTime FROM IndResults"
rs.Open sql, conn, 1, 2
Do While Not rs.EOF
    rs(0).Value = "00:00:00"
    rs.Update
    rs.MoveNext
Loop
rs.Close
Set rs = Nothing

Open in new window

Scott FellDeveloper
CERTIFIED EXPERT
Fellow
Most Valuable Expert 2013
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
Bob SchneiderCo-Owner

Author

Commented:
So you are sayiing convert all the string FnlTime data into time FinalTime data in sql server.  The problem is that if I do it all in sql server now I will have to re-write all of my code to view this input as time datatype so that it goes in correctly as I time more races.  That is what I should do but not sure about the time to do it before two large races next week.  I guess I will have to make the time.

Plus it puzzles me that this did not work?  I can't resolve that in my small and limited mind... :)
Scott FellDeveloper
CERTIFIED EXPERT
Fellow
Most Valuable Expert 2013
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
Big MontyWeb Ninja at large
CERTIFIED EXPERT
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
Bob SchneiderCo-Owner

Author

Commented:
I did make a new field: FinalTime with datatype time(3)...tried time(7) first...pretty sure it doesn't matter.

The old (varchar(12)) field is FnlTime.

Then I tried to more the data one item at a time from FnltTime to FinalTime using classic asp and it crapped out on the first item: 1:45:09  I front-loaded a zero with asp code and tried to place that into FinalTime and it said "Bad data type."  If i put it in manually through Enterprise Manager it goes just fine.  I trimmed it in asp but, again, won't populate the field with it.
Bob SchneiderCo-Owner

Author

Commented:
You guys are awesome, and way ahead of me in terms of knowledge and experience, but please read my post above.  I took one "good" data point and it would not let me place that in the field so I can safely assume that it is not a "bad data" issue, correct?  It wouldn't do the first one with classic asp even though I can do the first one manually.
Big MontyWeb Ninja at large
CERTIFIED EXPERT
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
Bob SchneiderCo-Owner

Author

Commented:
I am running sql server 2012 web.  I tried to insert the default value "00:00:00" with this code:
Set rs = Server.CreateObject("ADODB.Recordset")
sql = "SELECT FinalTime FROM IndResults"
rs.Open sql, conn, 1, 2
Do While Not rs.EOF
    rs(0).Value = "00:00:00"
    rs.Update
    rs.MoveNext
Loop
rs.Close
Set rs = Nothing

Open in new window

Bob SchneiderCo-Owner

Author

Commented:
IndResults data table
Web Ninja at large
CERTIFIED EXPERT
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
Scott FellDeveloper
CERTIFIED EXPERT
Fellow
Most Valuable Expert 2013
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
Bob SchneiderCo-Owner

Author

Commented:
I will be glad to do that but first can you tell me why it won't take "00:00:00" when I just try to insert that into the FinalTime field with the code I posted above?  Again, not in any way using the "old" FnlTime field or it's data.
Bob SchneiderCo-Owner

Author

Commented:
BigMonty, that worked except where the race times had decimals (only 500 of the 44,000 didnt).  So what about your code enabled that data to go in there when mine wouldn't even after I formatted it for the time data type?
Big MontyWeb Ninja at large
CERTIFIED EXPERT
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
Bob SchneiderCo-Owner

Author

Commented:
Ok that makes sense.  Final question: so when I time my next race and I want to put the time into both fields if I type the variable I am using for final time I should be ok?  What vb6 datatype do I use?  I still use vb6 but, unlike asp, that allows me to type variables.  Again, for now, all I want the time datatype for is for sorting.  Ultimately (maybe within a couple of months) I will switch over to that completely but in the interim I need to process data.
Big MontyWeb Ninja at large
CERTIFIED EXPERT
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
Scott FellDeveloper
CERTIFIED EXPERT
Fellow
Most Valuable Expert 2013
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
Bob SchneiderCo-Owner

Author

Commented:
I don't know much about triggers but you guys ROCK as usual.  This will speed my site up dramatically!  Thanks so much!!!
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.