Jump to content
  • Advertisement
Sign in to follow this  
chbrules

ASP Date Sorting

This topic is 4961 days old which is more than the 365 day threshold we allow for new replies. Please post a new topic.

If you intended to correct an error in the post then please contact us.

Recommended Posts

I'm currently writing a report form and I need a little help doing this efficiently as possible. I have a list of dates, say maybe 3-10 for each entry in this report, I need to extract the first date in the series and the last date to tell the length of time the entry will last for. The dates are not in order either. Is there a function in ASP to sift through a set of date results from a DB source like this?

Share this post


Link to post
Share on other sites
Advertisement
Sure, if you're selecting them from a database you can just use an order by clause:

SELECT * FROM report ORDER BY date ASC;

Share this post


Link to post
Share on other sites
Probably a bit of a nooby question, but I'm pretty new to ASP, I'm migrating from PHP/C++/VB background, so it's not too bad. How do I know the size of the array returned so I can access the last element?

Share this post


Link to post
Share on other sites
This kind of processing should always be done on the database side, trying to do it on the script side is just plain bad design; your script code should be glue code, not processing code. If you find you are writing a loop to search for something or calculate something in the result set you are probably doing things wrong. What you are doing is the equivalent of "Mr Parker, I need the file on the Johnson case, would you please bring me the entire filling cabinet so I can get it".

For example:

SELECT
DATEDIFF(d, MIN(DateField), MAX(DateField)) AS TimeSpan
FROM TheTable

Will return the number of days between the first entry and the last entry, in days. Refer to the documentation for the identifier to use in place of "d" if you want the time difference in another format (hours, seconds, years, quarters, etc)

Share this post


Link to post
Share on other sites
That's not what I want, I want the dates in the database, the beginning date and the last date from the database, not the days between. I have multiple records for multiple entries. It's like a 2 level hierarchy.

Share this post


Link to post
Share on other sites
Your description is incomplete. Please provide some sort of example (such as some table layouts and an example of the results) so that I can actually understand what you want. As I do not know your level of understanding I have no way of knowing if the problem has some complexity you are not mentioning, or if you simply do not know enough basic SQL. From your second description it sounds like a simple use of MIN/MAX and GROUP BY to return a set of records, each record having the min and max date values of a related table.

Share this post


Link to post
Share on other sites
I'm dealing with tradeshows for this company, they have mutliple days each can go on for with different times each day. So the table has a Tradeshow_ID column which groups the dates, then there is a Start_Date column, and 2 more with the start and end times which I just need to read once I sort by the tradeshow and start and end dates.

Since there is only one date column, I need to get the list of dates for each tradeshow and determine which is the first and which is the last dates of the show. Then I take that data and print it to the page for the people to use in their reports.

I understand the sort by SQL and I'm pretty good w/ SQL. We're using MS SQL/ASP.NET here. I just need to know, if I did a SORT BY Tradeshow_ID, then how would I tell how large the returned array size is so I can grab the latest date? Is there another way to get the first and last elements of that array? That's all I need. I don't now what that min/max function is as I'm not an ASP guru or anything. I wish Microsoft did things more like C. =/

Share this post


Link to post
Share on other sites
Nevermind, I just figured it out myself:

set dater = Server.CreateObject("ADODB.RecordSet")
dater.Open "SELECT * FROM TradeshowTimes WHERE Tradeshow_ID = '" & rs("Tradeshow_ID") & "' ORDER BY Tradeshow_ID ASC", sConn, 1, 2, 0

Response.Write Replace( dater("Setup_Date"), "/" & year(dater("Setup_Date")), "" ) & " - "

dater.MoveLast

Response.Write Replace( dater("Setup_Date"), "/" & year(dater("Setup_Date")), "" )


I hope that's not too sloppy. I get quirk at blank fields though, I'll have to do error checking too on it.

Share this post


Link to post
Share on other sites
Quote:
Original post by chbrules
I'm dealing with tradeshows for this company, they have mutliple days each can go on for with different times each day. So the table has a Tradeshow_ID column which groups the dates, then there is a Start_Date column, and 2 more with the start and end times which I just need to read once I sort by the tradeshow and start and end dates.

Since there is only one date column, I need to get the list of dates for each tradeshow and determine which is the first and which is the last dates of the show. Then I take that data and print it to the page for the people to use in their reports.

I understand the sort by SQL and I'm pretty good w/ SQL. We're using MS SQL/ASP.NET here. I just need to know, if I did a SORT BY Tradeshow_ID, then how would I tell how large the returned array size is so I can grab the latest date? Is there another way to get the first and last elements of that array? That's all I need. I don't now what that min/max function is as I'm not an ASP guru or anything. I wish Microsoft did things more like C. =/


Where to begin?

MIN/MAX have nothing at all to do with ASP. They are SQL keywords - Microsoft had absolutely nothing to do with them, they date back to 1976 in the original SEQUEL research paper and have been in SQL from the very beginning (which was at IBM). Any SQL book you find will use MIN/MAX as early examples as they are the most common aggregate functions in the entire SQL language.

SQL is not going to work like C. SQL is the exact opposite of C - C is a procedural language, you tell the computer the steps you want it to take and the computer has not idea what the outcome will be; SQL is a functional language, you tell the computer what you want the outcome to be, and it determines the best way to reach it. As long as you try to think in C terms you will never succeed.

I assume you are using ADO in ASP (though all other methods work in a similar way). There is no "array" that records are stored in. Imagine you had a 20GB database, and you did a query that returned all of the records in that database. Would you expect a huge array to be constructed to hold all that? The records are streamed from the server as needed. Please learn about cursor and lock types (you will again utterly fail if you don't learn about these), then optionally browse the ADO documentation about page sizing and caching. The ADODB.RecordSet object is NOT an array. In theory you could use MoveLast, but this is counterproductive, as you are again asking for the filing cabinet when you only need one file.


Based on your still vague description I'm guessing your database looks something like this:

TradeShows(TradeShowID, Name)
   1, 'Chicago Show'
   2, 'NewYork Show'

TradeShowDays(TradeShowID, ShowDate, StartTime, EndTime)
   1, 9/17/2005, 9AM, 5PM
   1, 9/18/2005, 12PM, 5PM
   1, 9/19/2005, 9AM, 9PM
   2, 9/18/2005, 9AM, 5PM
   2, 9/19/2005, 9AM, 5PM
   2, 9/20/2005, 9AM, 5PM

And the result you want is this:

'Chicago Show', 9/17/2005, 9/19/2005
'NewYork Show', 9/18/2005, 9/20/2005

To get this you would run the following query:

SELECT Name, Min(ShowDate) AS ShowStartDate, Max(ShowDate) AS ShowEndDate
FROM TradeShowDays INNER JOIN TradeShows ON TradeShowDays.TradeShowID = TradeShows.TradeShowID
GROUP BY TradeShowDays.TradeShowID, Name



Share this post


Link to post
Share on other sites
Sign in to follow this  

  • Advertisement
×

Important Information

By using GameDev.net, you agree to our community Guidelines, Terms of Use, and Privacy Policy.

GameDev.net is your game development community. Create an account for your GameDev Portfolio and participate in the largest developer community in the games industry.

Sign me up!