Start Free Trial

asked on

Modifying Data In Query To Sort

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

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.

SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER

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!!!!

SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER

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)

SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Make sure that FnlTime is in a datetime format before casting it.

ASKER

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.

SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER

tried it...got an error that it resulted in an out-of-range value

SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER

Scott, but that removes the option of sorting from within the db correct?

SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER

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.

SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER

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)

ASKER

Also did it with convert(time, FnlTime)

ASKER

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."

SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER

Would it work if the '00:' were not at the front?

ASKER

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.

SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

.... you don't have to right pad the fraction, just left pad the hours, mins, seconds.

ASKER

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?

ASKER

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.

SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER

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! :)

ASKER

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.

SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER

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

SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER

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... :)

SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER

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.

ASKER

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.

SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER

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

ASKER

User generated image

ASKER CERTIFIED SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER

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.

ASKER

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?

SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER

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.

SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER

I don't know much about triggers but you guys ROCK as usual. This will speed my site up dramatically! Thanks so much!!!