Sign in to follow this  

ASP Date Sorting

This topic is 4592 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
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
Quote:
Original post by chbrules
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.


Unbelievably ugly, and totally inefficient. If there is more then one tradeshow on the report and you are thus repeating this code, you should be sent to SQL jail. You have caused a ton of records to be pulled from the database that you aren't even going to use.

As I've tried to explain to you all along, the correct method, taught on the first day, is this:


dater.Open "SELECT Min(Setup_Date) AS StartDate, Max(Setup_Date) AS EndDate FROM TradeshowTimes WHERE Tradeshow_ID = '" & rs("Tradeshow_ID") & "' GROUP BY Tradeshow_ID ", sConn, 1, 2, 0

Response.Write Replace( dater("StartDate"), "/" & year(dater("Setup_Date")), "" ) & "<font size='2'> - </font>"

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

Share this post


Link to post
Share on other sites
I think you really need to take a step back if you intend to continue working on SQL. You believe you know enough about SQL, yet your posting shows you don't even understand the absolute basics. To be generous I would wager your SQL experience before this has been on MySQL (since you mentioned PHP), which would have given you a very bad impression of how databases work (sort of like learning to program in a language with no loops or functions and only a goto - when given a C compiler you would proceed to write absolutely terrible C code because you don't understand how it works).

While you can certainly buy a book, you could also get a free crash course in the absolute basics at a site like W3Schools. While it's not even going to cover the capabilities of Access, it should give you a better understanding of SQL. You should also look into the crash course in ADO.

Share this post


Link to post
Share on other sites
Sorry if I've offended the SQL gods. >_>

The company makes me do this ASP/SQL crap which I could care less for, I'm a C++ programmer. Way to use your resources eh? I'm too young w/ not enough experience and not a degree yet to change jobs, so I'm stuck here for now while in school.

Anyway, thank you for explaining all this, I will have to get more in-depth w/ SQL queries now since I know this. Do you recommend any good sources for learning about such?

I have one more dilema with this project though, how do I check for records that aren't there? Say I call a tradeshow_ID that doesn't have times recorded for it yet, how do I check to see if the record doesn't exist from the query results? I try an if statement against <> "" or NULL, but it keeps getting errors. I'm going to kill myself. -.-

Share this post


Link to post
Share on other sites
While you can do it in SQL, for your situation (and frankly most, since it is getting into very advanced conditional SQL) you can do it in ASP code. ASP is not really a language, instead it's a platform for connecting a language to IIS. By default VBScript is the language you are using (with ASP providing a few objects like "Response" and "Request") so if you are stuck you can always look up the VBScript reference (if you are interested, you can also use javascript out the box, or install 3rd party language DLLs to allow you to write ASP pages in languages like Python or Perl).

With VBScript you just need to use IsNull(), a function that returns True or False.

Also, in case you didn't know, when you get a recordset, the "EOF" property (dater.eof) will return true if there are no records (you also use EOF to determine when you've reached the end while browsing with MoveNext)

Share this post


Link to post
Share on other sites
Well I was trying as you said, the values come out just right until I reach a query that doesn't exist. Say I call tradeshow_ID 123 or something, and it didn't have an entry for times, with this code I use:

set dater = Server.CreateObject("ADODB.RecordSet")	
dater.Open "SELECT Min(Setup_Date) AS StartDate, Max(Setup_Date) AS EndDate FROM TradeshowTimes WHERE Tradeshow_ID = '" & rs("Tradeshow_ID") & "' GROUP BY Tradeshow_ID ", sConn, 1, 2, 0

If isnull(dater("StartDate")) then
Response.Write ""
Else
Response.Write Replace( dater("StartDate"), "/" & year(dater("StartDate")), "" ) & " - "

Response.Write Replace( dater("EndDate"), "/" & year(dater("EndDate")), "" )
End If


With this error:

error '80020009' 
Exception occurred.

/marketing/tradeshow/tradeshows.asp, line 326


Which is this line:

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


Which I assume is because my isnull() didn't catch the query not existing or something?

Share this post


Link to post
Share on other sites

This topic is 4592 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.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

Sign in to follow this