Solved

# Modifying Data In Query To Sort

Posted on 2014-11-19
Medium Priority
159 Views
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)
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
``````
0
Question by:Bob Schneider
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• 25
• 16
• 10
• +1

LVL 24

Expert Comment

ID: 40452137
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)
``````

gives 51030, the number of seconds in 14 hours, 10 minutes and 30 seconds.
0

LVL 53

Assisted Solution

Scott Fell,  EE MVE earned 1120 total points
ID: 40452322
Bubble sorting in vbs is going to be slow for large number of rows.

You are better off converting the text data in your sql to time or datetime, then add the sorting in your sql statement instead of bubble sorting.  I think you will see the speed of that portion of your process speed up.

If you want to be able to sort from one thing to other without a page refresh, then instead of sorting with serverside code, let javascript do this on the client.

I started using http://momentjs.com/ earlier this year. I wish I found it sooner.

Also, I seem to remember you were using datatables http://www.datatables.net/ for displaying your data.  If you set the column as time, that will sort very quickly and easily  https://datatables.net/plug-ins/sorting/

I would just avoid this type of sorting in vbs.
0

LVL 33

Assisted Solution

Big Monty earned 880 total points
ID: 40452593
agree with scott, don't use a bubble sort in your code, use the database to sort everything. if you use this query a lot, stick it into a view or stored procedure where you can pass in an order by parameter, that way you can order it by anything going forward
0

Author Comment

ID: 40452917
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!!!!
0

LVL 33

Assisted Solution

Big Monty earned 880 total points
ID: 40452954
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?

you can. all  a stored procedure is is a way of executing sql, whether it's just one line or multiple lines.

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

they CAN be faster, depending on what you're trying to do. In this case, it would be a lot faster than doing a bubble sort

regarding point #1 that you brought up, how many records are in the table? roughly how many places in your site query that table? I ask because this may be a good time to normalize the data, and convert the column over to a datetime. if you don't have tons of places in your code where you would need to change the sql to work with a datetime field, it's something worth considering, and I'm sure you could get lots of help here on EE.

if converting the field to datetime is an option, then you probably wouldn't even need to use a view or stored procedure
0

LVL 53

Assisted Solution

Scott Fell,  EE MVE earned 1120 total points
ID: 40453012
Right now you have
``````		sql = "SELECT ParticipantID, FnlTime, ChipTime FROM IndResults WHERE RaceID = " & lRaceID & " ORDER BY EventPl"
``````
And you can still use
``````		sql = "SELECT ParticipantID, FnlTime, ChipTime FROM IndResults WHERE RaceID = " & lRaceID & " ORDER BY cast(FnlTime as datetime)"
``````
Notice you are still using fnlTime in it's raw form for output but the order by is casting that as datetime.

A SP will not necessarily be faster then the example above.  It will be faster than trying to sort server side using vbscript.

A SP may be easier to package to do multiple things at once however.   I use saved stored procedures for things like creating contact records where I can send some data to a classic asp function that accepts some input, then calls a SP, inserts one table, then based on the new record id, inserts a related record to a 2nd table, then sends out some data I need for the function.    That means my asp code is one simple function and I can easily reuse that SP for multiple pages without reinventing the wheel.

If you do need to resort data on the page, I like the idea of allowing javascript/jquery to do the work with data already on the page and not having to go back to the server.  You might wan to think about datatables.

Some alternatives
https://github.com/jiren/StreamTable.js/
http://www.trirand.com/blog/
http://flexigrid.info/

I would also look at http://www.telerik.com/kendo-ui or  http://wijmo.com/ both of these will need a paid license though for the grid.  Much of Kendo is now open source but the grid is not.
0

Author Comment

ID: 40453148
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)
0

LVL 53

Assisted Solution

Scott Fell,  EE MVE earned 1120 total points
ID: 40453166
Sounds like you have it all together.    Did you do any testing of perhaps some old results to see how much faster the pages are loading so far?
0

LVL 53

Expert Comment

ID: 40453168
Make sure that FnlTime is in a datetime format before casting it.
0

Author Comment

ID: 40453180
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.
0

LVL 33

Assisted Solution

Big Monty earned 880 total points
ID: 40453182
if you want to just get something up and running before optimizing everything, try this out:

sql = "SELECT ParticipantID, FnlTime, ChipTime FROM IndResults WHERE RaceID = " & lRaceID & " ORDER BY cast( FnlTime as datetime)"
0

LVL 53

Assisted Solution

Scott Fell,  EE MVE earned 1120 total points
ID: 40453190
I don't know if it is best practice but prior to sql 2008, I used to dummy my date for datetime with 1900-01-01.   With sql server 2012, you don't have to use datetime, you can use just time.  But you could also use the actual race date as the date along with the time to make a correct datetime format.

``````time = "00:12:34.567"

end function
``````
0

Author Comment

ID: 40453191
tried it...got an error that it resulted in an out-of-range value
0

LVL 53

Assisted Solution

Scott Fell,  EE MVE earned 1120 total points
ID: 40453192
>ORDER BY cast( FnlTime as datetime)"

or if it is just time value

ORDER BY cast( FnlTime as time)"
0

LVL 33

Assisted Solution

Big Monty earned 880 total points
ID: 40453193
if CASTing isnt working, you can try the convert function:

sql = "SELECT ParticipantID, FnlTime, ChipTime FROM IndResults WHERE RaceID = " & lRaceID & " ORDER BY convert( datetime, FnlTime"
0

Author Comment

ID: 40453194
Scott, but that removes the option of sorting from within the db correct?
0

LVL 53

Assisted Solution

Scott Fell,  EE MVE earned 1120 total points
ID: 40453252
I'm not sure which you are talking about.

if you cast as date time, you have to start with text that is in datetime format.   I have always just hardcoded the date portion in.

But you can just cast as time instead of datetime.  I think that may be why you received an error.  Try changing to ORDER BY cast( FnlTime as time) if FnlTime only has time and no date.
0

Author Comment

ID: 40453751
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.
0

LVL 33

Assisted Solution

Big Monty earned 880 total points
ID: 40453775
did you try using the convert function like I suggested before?
0

Author Comment

ID: 40453792
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)
0

Author Comment

ID: 40453797
Also did it with convert(time, FnlTime)
0

Author Comment

ID: 40453801
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."
0

LVL 53

Assisted Solution

Scott Fell,  EE MVE earned 1120 total points
ID: 40453856
Make sure you have good data.

This works http://sqlfiddle.com/#!6/0a3b8/2
``````-- Now you can see how it works in 2012!
CREATE TABLE IndResults
(
id int identity primary key,
FnlTime varchar(20),

);

INSERT INTO IndResults
(FnlTime)
VALUES
('00:12:34.402'),
('00:12:36.763'),
('00:12:31.017'),
('00:12:34.568');

SELECT FnlTime FROM IndResults ORDER BY CAST(FnlTime as Time)
``````
0

Author Comment

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

Author Comment

ID: 40453950
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.
0

LVL 53

Assisted Solution

Scott Fell,  EE MVE earned 1120 total points
ID: 40453993
It looks like it is the format of the data.    This does not work
``````INSERT INTO IndResults
(FnlTime)
VALUES
('00:12:34.402'),
('00:12:36.763'),
('00:12:31.017'),
('9:59.14'),
('9:50:17.60'),
('58:07.984'),
('52:41'),
('53:00'),
('1:00:00.37'),

('00:12:34.568');
``````
This works
``````INSERT INTO IndResults
(FnlTime)
VALUES
('00:12:34.402'),
('00:12:36.763'),
('00:12:31.017'),
('09:50:17.600'),
('00:58:07.984'),
('00:52:41.000'),
('00:53:00.000'),
('01:00:00.370'),
('00:12:34.568');
``````

It looks like you have to have it in hrs:mins:sec.fraction zero padded.  http://www.tech-recipes.com/rx/30469/sql-server-how-to-left-pad-a-number-with-zeros/
0

LVL 53

Expert Comment

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

Author Comment

ID: 40454075
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?
0

Author Comment

ID: 40454081
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.
0

LVL 53

Assisted Solution

Scott Fell,  EE MVE earned 1120 total points
ID: 40454132
You only have to do this for the WHERE clause.  Your output is in your SELECT and that is not changed.

You can do the padding in your sql statement using replicate http://msdn.microsoft.com/en-us/library/ms174383.aspx

However, this is not as straight forward for you because you are dealing with uneven characters to the right of the decimal.  Also do you have to account for minutes and seconds needing leading zeros?
0

Author Comment

ID: 40454144
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!  :)
0

Author Comment

ID: 40454198
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.
0

LVL 53

Assisted Solution

Scott Fell,  EE MVE earned 1120 total points
ID: 40454285
It works if the data starts in the correct format.

- Data field not blank
- 2 digits in each of hours, minutes, seconds
- The fraction does not have to in place.
- Characters making up hours are between 00 and 24 inclusive
- Characters making up minutes and seconds are between 00 and 60 inclusive

Knowing what you know now, I would say it would be a good idea to make this a time field and not a text field.

Might be a good idea to make a new time field.  Create code to format the old data to the new time field.  Then rename the current field to something else and rename the new field to fnlTime.

Once you do that, anywhere you are inserting or updating data or using a parameterized query you will have to change your asp code.
0

Author Comment

ID: 40454974
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
``````
0

LVL 53

Assisted Solution

Scott Fell,  EE MVE earned 1120 total points
ID: 40455303
I think you will be fine if you do this all in sql server rather than outputting to asp/vb then inputing.  I just did a quick experiment.

Created a table similar to what you have with varchar text time
``````CREATE TABLE [dbo].[tblTest](
[OldTime] [nvarchar](10) NULL,
[newTime] [time](7) NULL
) ON [PRIMARY]
``````
Added values to the OldTime as below
0:1:15
01:20:18
3:1:5

Then ran this query in manager studio
``````UPDATE [dbo].[tblTest]
SET [newTime] = [OldTime]
``````
And the data came out like
00:01:15
01:20:18
03:01:05

The next experiment is I simply changed the field type of OldTime to time(7) and the data came out just as the update did.

Making sure you have a back up of course, I would do all of this in sql server and not bother with outputting to an asp page, then updating via asp.
0

Author Comment

ID: 40455639
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... :)
0

LVL 53

Assisted Solution

Scott Fell,  EE MVE earned 1120 total points
ID: 40455678
That's why I suggested making a new field.

It is very possible with 44K records that there is some bad data somewhere that needs more cleaning.
0

LVL 33

Assisted Solution

Big Monty earned 880 total points
ID: 40455691
for what it's worth, the script below worked without error. if you just substitute your table name, it'll work, assuming you don't have any bad data (which I'm starting to suspect is the case.

``````CREATE TABLE [dbo].[#tblTest](
[OldTime] [nvarchar](10) NULL,
[newTime] [datetime] NULL
) ON [PRIMARY];

insert into #tblTest (oldTime) values( '2:14pm' );

update #tblTest set newTime = CONVERT(DATETIME, oldtime, 0) ;
``````

if this doesn't work, I think it's safe to say you have bad data somewhere in your table. to get around this, you could write an asp script (or even a sql script if you know how) to extract each oldTime, check to make sure it's a valid time, and then insert it into the newTime field. if it isn't a valid time value, then mark it as such and deal with it manually afterwards. if you're on a time crunch, this may be the quickest way to go
0

Author Comment

ID: 40455698
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.
0

Author Comment

ID: 40455705
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.
0

LVL 33

Assisted Solution

Big Monty earned 880 total points
ID: 40455706
what version of sql server are you running?

can you post the code you're using to update the field?
0

Author Comment

ID: 40455720
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
``````
0

Author Comment

ID: 40455725
0

LVL 33

Accepted Solution

Big Monty earned 880 total points
ID: 40455740
i would think something like this should work when trying to update the table via asp code:

``````on error resume next

sql = "SELECT id, FnlTime FROM IndResults"
set rs = conn.Execute( sql )

do while not rs.EOF
id = rs(0)
raceTime = rs(1)

if isDate( raceTime ) then
sql = "update IndResults set FinalTime = '" & raceTime & "' where id = " & id
conn.Execute( sql )

if Err.Number <> 0 then
errorIDs = errorIDs & ","
end if
else
errorIDs = errorIDs & ","
end if

rs.MoveNext
loop

if errorIDs <> "" then Response.Write "The following IDs had errors:<br/>" & errorIDs

Response.Write "<br><br>Done"
``````

just change the ID column to whatever the name of your ID field is
0

LVL 53

Assisted Solution

Scott Fell,  EE MVE earned 1120 total points
ID: 40455744
>safely assume that it is not a "bad data" issue, correct?

I think the fact that you have a text field means you are open to bad data.  There could be something invisible so to speak you are not seeing.

Try creating a new table as I did above
``````CREATE TABLE [dbo].[tblTest](
[OldTime] [nvarchar](10) NULL,
[newTime] [time](7) NULL
) ON [PRIMARY]
``````
Then in add some typical data as I did using single digit time formats.  Just a few rows.  Then give it a try.  If that works, that means there is some bad data someplace,  You could have a row of data with blank (not null) or in some other format that it can't determine.  Or some hidden character.

I would try this first to rule out it is not something with some setting.
0

Author Comment

ID: 40455749
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.
0

Author Comment

ID: 40455776
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?
0

LVL 33

Assisted Solution

Big Monty earned 880 total points
ID: 40455786
updating via a recordset object directly can be tricky, i've never had good luck with it. it may be due to a cursor issue, or the driver that your connection string is using. I ALWAYS use a sql approach, it's a lot more reliable I've found.

looks like the decimal was throwing off the data, you can modify the code i gave you to check for a decimal and then deal with it however you want to. once that's done, you should be able to use your sort
0

Author Comment

ID: 40455811
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.
0

LVL 33

Assisted Solution

Big Monty earned 880 total points
ID: 40455838
you *should* be ok, yes. Without seeing exactly what your code is, I can't say for 100%...

as for the VB6 data type, whatever the equivalent is, I'm not sure, for time is what you want to use.
0

LVL 53

Assisted Solution

Scott Fell,  EE MVE earned 1120 total points
ID: 40455839
I wouldn't rely too much on triggers, but in this temporary case, you could certainly use a trigger for when the original field is created to update the new field.   Triggers are not always the best thing because you can forget about them and that makes debugging hard.

But if you simply format the time correctly with your vb code you should be able to update directly.
0

Author Comment

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

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
###### Suggested Courses
Course of the Month12 days, 13 hours left to enroll