Avatar of Bob Schneider
Bob Schneider
Flag for United States of America 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

Microsoft SQL ServerASP

Avatar of undefined
Last Comment
Bob Schneider

8/22/2022 - Mon
Phillip Burton

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
Scott Fell

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Bob Schneider

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
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Bob Schneider

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)
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Scott Fell

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

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
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Bob Schneider

ASKER
tried it...got an error that it resulted in an out-of-range value
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Bob Schneider

ASKER
Scott, but that removes the option of sorting from within the db correct?
SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Bob Schneider

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
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Bob Schneider

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)
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
Bob Schneider

ASKER
Also did it with convert(time, FnlTime)
Bob Schneider

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
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Bob Schneider

ASKER
Would it work if the '00:' were not at the front?
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Bob Schneider

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
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Scott Fell

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

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?
Your help has saved me hundreds of hours of internet surfing.
fblack61
Bob Schneider

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
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Bob Schneider

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

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.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Bob Schneider

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
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Bob Schneider

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
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Bob Schneider

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.
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
Bob Schneider

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
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Bob Schneider

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

Bob Schneider

ASKER
IndResults data table
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ASKER CERTIFIED SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Bob Schneider

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.
Bob Schneider

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
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Bob Schneider

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.
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Bob Schneider

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