Help with sql statement

All Forums >> [Web Development] >> ASP and Database

Message

dzirkelb1 -> Help with sql statement (4/12/2005 13:37:49)
I am having trouble on thinking of how to accomplish this...I'll try my best to explain. Basically, what happens is a shopper will call in to our office and ask for what we call "shop dates". What we do is ensure the individual store was shopped at least 7 days ago, if it was, then its clear to shop...if, however, it was shopped 3 days ago, then it isn't clear to shop and we tell them when it will be able to shop. So, now I'll list the applicable fields and describe them quick: PK ID- this is simply the autonumber - unique id...each shop has a specefic number. This increases and I think will be usable. Period/Month- This shows the frequency of shops we do a month for a specefic store. Example...if it says Apr, then we do a single shop in April. If it says Mar, then a single in March. If it says Apr 1st, then we do multiple shops a month...most commonly there will be an Apr 2nd for the same store in the same month, and sometimes an Apr 3rd and Apr 4th. RT1- This is what we call the route number...it is basically a numer we assign to a shopper for their current route. In its data is basically each store they need to do. So, shopper calls in for route 10864, and we then know what to look for. EvalDate- This is the date field for the shop...it shows what date it was shopped. KFC ID- the KFC store id TB ID- the Taco Bell store id So, what I'm sort of thinking about doing is like this: Subtract the current date from the most recent evaldate per Store ID and if it is greater than 7 days, then type clear or something...if it is less, then display the date it will be clear. All this is seperated by a simple where clause of rt1. I think I would need to pull all of the store id's out of the route, then pull the max eval date from those store id's, and subtract 7 days from that. Is this possible in one statement or would I have to create multiple? Currently this will be access based and will be implemented to the web in a couple weeks...so, I can do all the work in access for now (I can figure out the asp code after the sql is working) Any ideas on where to start? :)

dzirkelb1 -> RE: Help with sql statement (4/12/2005 16:49:57)
I currently have this to build off of (to start) SELECT [MASTER ROUTE TABLE 1998].[KFC ID], (SELECT MAX(EvalDate) FROM [MASTER ROUTE TABLE 1998]) AS MaxEvalDate FROM [MASTER ROUTE TABLE 1998] WHERE ((([MASTER ROUTE TABLE 1998].RT1)=10864)); This shows the KFC ID's for the specefic route correctly. In the MaxEvalDate field it has the max eval date for my entire database.

dzirkelb1 -> RE: Help with sql statement (4/12/2005 17:02:29)
Ok, now I have some of it working: SELECT [KFC ID], MAX(EvalDate) AS MaxEvalDate FROM [MASTER ROUTE TABLE 1998] GROUP BY [KFC ID] This displays every KFC ID with its max eval date next to it. Now, I wish to display only the KFC ID's on route 10864...so, I normally do this: SELECT [KFC ID], MAX(EvalDate) AS MaxEvalDate FROM [MASTER ROUTE TABLE 1998] WHERE (rt1=10864) GROUP BY [KFC ID] However, that pulls the current route up first, then pulls the max eval date for the store id's on that route...I wish to show the max eval date of the kfc id's, and then display only those involved on rt 10864...so, its backwards (when I put in the where clause my display is blank fields because none of the shops are completed yet)... So, I am thinking I need to create a query that pulls the kfc id's from the route, then inner join that query to get the max eval date, somehow....

BeTheBall -> RE: Help with sql statement (4/12/2005 17:13:24)
Perhaps using HAVING in place of WHERE? SELECT [KFC ID], MAX(EvalDate) AS MaxEvalDate FROM [MASTER ROUTE TABLE 1998] GROUP BY [KFC ID] HAVING rt1=10864

dzirkelb1 -> RE: Help with sql statement (4/12/2005 17:20:55)
Well, the following works so far like intended: SELECT [MASTER ROUTE TABLE 1998].[KFC ID], Max([MASTER ROUTE TABLE 1998].EvalDate) AS MaxEvalDate FROM [MASTER ROUTE TABLE 1998] INNER JOIN Query6 ON [MASTER ROUTE TABLE 1998].[KFC ID] = Query6.[KFC ID] GROUP BY [MASTER ROUTE TABLE 1998].[KFC ID]; However, I wish to not have seperate queries made and join them if possible. Also, when i tried having, I got the following error: you tried to execute a query that does not include the specified expression 'rt1=10864' as part of an aggregate function. Also, I will have another field in here that will display the max eval date, it is called TB ID. When I place TB ID on my query6, it displays correctly (that query simply displays the kfc id's and tb id's for specefic route)...however, when I try to join KFC ID and TB ID, then no results show...if I pull one of the joins away, then it works correctly....here is the code with both joins: SELECT [MASTER ROUTE TABLE 1998].[KFC ID], Max([MASTER ROUTE TABLE 1998].EvalDate) AS MaxEvalDate, [MASTER ROUTE TABLE 1998].[TB ID] FROM [MASTER ROUTE TABLE 1998] INNER JOIN Query6 ON (Query6.[TB ID] = [MASTER ROUTE TABLE 1998].[TB ID]) AND ([MASTER ROUTE TABLE 1998].[KFC ID] = Query6.[KFC ID]) GROUP BY [MASTER ROUTE TABLE 1998].[KFC ID], [MASTER ROUTE TABLE 1998].[TB ID]; Any ideas?

BeTheBall -> RE: Help with sql statement (4/12/2005 19:21:41)
I am still not all that clear on what you are trying to do. It's often hard to comprehend what's going on in another person's db. Not sure this will get you any closer, but you can nest your SQL statements. For example: SELECT [KFC ID], Max(EvalDate) AS MaxEvalDate, [TB ID] FROM (SELECT * FROM [MASTER ROUTE TABLE 1998] WHERE rt1=10864) GROUP BY [KFC ID], [TB ID]

dzirkelb1 -> RE: Help with sql statement (4/13/2005 10:10:00)
That technically works for what its trying to do. I believe that is pulling the store id's from the specefic route, and then writing the max eval date for those store id's FROM that route. I wish to pull the max store ID's from the store id's first, and THEN display only those in the route....I'll try to ellaborate more. Lets say route 10800 has 2 stores on it, id 777 and id 888 for Period Apr 1st. Those stores were shopped on 4/1 and 4/2. Now, if I used the query above on that route, it would work perfectly; however, there is no reason to use it on that route because it is already done. Now, lets look at route 10801. It also has stores 777 and 888 for Period Apr 2nd, but they weren't shopped yet. If I did the query above, it would show me nothing for the last shop date on the stores as it would pull the route up, then display the stores, then pull the max id's for those stores IN that route and not for the database as a whole. I guess the query needs to run in a certain order to work correctly. It needs to pull the store id's from the whole database, find the max evaldate on all of those stores, and then it needs to narrow down what to display by what route it is. Does that make a little more sense?

dzirkelb1 -> RE: Help with sql statement (4/13/2005 10:28:07)

I have it sort of working in MS Access right now using 3 differnt queries, but that definately is not what I want to do....here is what is showing:

qryShopDatesSelectRoute

SELECT [MASTER ROUTE TABLE 1998].[KFC ID], [MASTER ROUTE TABLE 1998].RT1, [MASTER ROUTE TABLE 1998].[TB ID]
FROM [MASTER ROUTE TABLE 1998]
WHERE ((([MASTER ROUTE TABLE 1998].RT1)=10864));

qryShopDatesKFC

SELECT [MASTER ROUTE TABLE 1998].[KFC ID], Max([MASTER ROUTE TABLE 1998].EvalDate) AS MaxEvalDate
FROM [MASTER ROUTE TABLE 1998] INNER JOIN qryShopDatesSelectRoute ON [MASTER ROUTE TABLE 1998].[KFC ID] = qryShopDatesSelectRoute.[KFC ID]
GROUP BY [MASTER ROUTE TABLE 1998].[KFC ID];

qryShopDatesTB

SELECT Max([MASTER ROUTE TABLE 1998].EvalDate) AS MaxEvalDate, [MASTER ROUTE TABLE 1998].[TB ID]
FROM [MASTER ROUTE TABLE 1998] INNER JOIN qryShopDatesSelectRoute ON [MASTER ROUTE TABLE 1998].[TB ID] = qryShopDatesSelectRoute.[TB ID]
GROUP BY [MASTER ROUTE TABLE 1998].[TB ID];

as you'll see, the TB and KFC ID queries are the same minus the fact they are seperate queries and the TB ID is replaced with KFC ID. I can't get them to view together without the results I want. I hope that helps ya as opposed to confuses you more !! :)

dzirkelb1 -> RE: Help with sql statement (4/14/2005 10:45:12)
Actually, I think it will work if I pull the store ID's from the specefic route number, and then find the max eval date of those store ID's for the entire database and not just for that route...so, I use route to find the store id's I want. Then I use another statement to find th max evaldate of those id's...is this possible in one statement at all?

BeTheBall -> RE: Help with sql statement (4/14/2005 11:32:05)
I'm lost. Is all the information in one table or are you dealing with multiple tables? I think I get the idea that a store id may appear multiple times in the db with different route numbers, right? Maybe you could give us a sample of what a handful of records would lool like and then show which ones you would like your SQL to pull out.

dzirkelb1 -> RE: Help with sql statement (4/14/2005 11:59:18)
Yup, one table is all that is needed on this one...I'll try to get some sample data for ya: KFC ID D181004 Ok, www.msultd.com/newroute.htm That link shows the new route, with the store id's, route number, eval date, Period Month, and the rest is pretty much not needed. Notice that there isn't any evaldate's in the evaldate column...thats because they haven't been shopped yet. This shopper (rep 251) would call in and ask if his dates are clear. What he is asking is if each of his store id's have been shopped longer than 7 days ago. Currently, what we do is have two queries display next to each other and count in our heads if 7 days have gone yet or not...then we tell him if its clear or not. now, look at www.msultd.com/oldroute.htm The same KFC ID was shopped on this route (10794) on 4/1/2005. Notice also, that this KFC ID is a APR 1st shop and the new route was APR 2nd. That is how we differentiate the amount of shops per month we give...its really not relavant, however, as the max evaldate of the store is what we go off of. I hope this helps a little bit...in this case the query would pull the MaxEvalDate of KFC ID D181004 as 4/1/2005...this needs to happen for all the id's on the current route...thanks a bunch :)

BeTheBall -> RE: Help with sql statement (4/14/2005 12:34:23)
Sorry to keep answering questions with more questions, but . . . So no store can be shopped twice in seven days regardless of what the route number is? Correct?

dzirkelb1 -> RE: Help with sql statement (4/14/2005 12:37:02)
Exactly! The route number is just used as what to display on the query really...say a route has 8 stores on it. I'd want this query to show those 8 stores and dates instead of all 30k.

BeTheBall -> RE: Help with sql statement (4/14/2005 13:16:14)
Does this get the desired result? SELECT [MASTER ROUTE TABLE 1998].[KFC ID], [MASTER ROUTE TABLE 1998].[TB ID], Max([MASTER ROUTE TABLE 1998].EvalDate) AS MaxEvalDate, RT1 FROM [MASTER ROUTE TABLE 1998] GROUP BY [MASTER ROUTE TABLE 1998].[KFC ID], [MASTER ROUTE TABLE 1998].[TB ID], RT1 HAVING RT1 = 10549

dzirkelb1 -> RE: Help with sql statement (4/14/2005 13:27:06)
Unfortunately, no. It works correctly, but not quite what I'm looking for yet...but its very close. It does work correctly for a route that is completed...in this case, it displays the max evaldate of 1/31/05 for all the stores (it happens to be the same for them all, I tested on other routes, it works correctly); however, that is not the max eval date for the entire table...it is only the max evaldate for that route. In essence, since the same store id pretty much isn't ever on the same route, this only displays the evaldate for each store for that route. Example. When I put in route 10873 (shown on the pic), it gives me no MaxEvalDates...when, in fact, for example store id D181004 should give a max eval date of 4/1/2005 (as shown on the oldroute.htm) I think on thd database I sent you I forgot to put the queries that work for 1/2 of each one...plug them in with the sql code listed above and it will show you the desired outcome.

dzirkelb1 -> RE: Help with sql statement (4/14/2005 13:47:45)

The above statements gives, for example, KFC ID k33333 a max eval date of 9 different dates. It is giving it the max eval date of whatever route it was in.

If I take out any of the rt1 in the sql statement, then it outputs correctly...the max evaldate of a k33333 is displayed once as it only has one true MaxEvalDate. so, I'm thinking I could build off of that...here is the sql for the above mentioned:

SELECT DISTINCT [MASTER ROUTE TABLE 1998].[KFC ID], [MASTER ROUTE TABLE 1998].[TB ID], Max([MASTER ROUTE TABLE 1998].EvalDate) AS MaxEvalDate
FROM [MASTER ROUTE TABLE 1998] 
GROUP BY [MASTER ROUTE TABLE 1998].[KFC ID], [MASTER ROUTE TABLE 1998].[TB ID]

Now, if I somehow tell the query to only display the specefic stores I wish...this does work:

SELECT DISTINCT [MASTER ROUTE TABLE 1998].[KFC ID], [MASTER ROUTE TABLE 1998].[TB ID], Max([MASTER ROUTE TABLE 1998].EvalDate) AS MaxEvalDate
FROM [MASTER ROUTE TABLE 1998] 
GROUP BY [MASTER ROUTE TABLE 1998].[KFC ID], [MASTER ROUTE TABLE 1998].[TB ID] HAVING [KFC ID]='J33333'

I need the KFC ID to eqal all of the KFC ID's in route 10873...somehow.

I just tried this:

SELECT DISTINCT [MASTER ROUTE TABLE 1998].[KFC ID], [MASTER ROUTE TABLE 1998].[TB ID], Max([MASTER ROUTE TABLE 1998].EvalDate) AS MaxEvalDate
FROM [MASTER ROUTE TABLE 1998] 
GROUP BY [MASTER ROUTE TABLE 1998].[KFC ID], [MASTER ROUTE TABLE 1998].[TB ID] HAVING ([KFC ID]=(SELECT [KFC ID] FROM [MASTER ROUTE TABLE 1998] WHERE (rt1=10873)))

and it gave an error saying at most one output can be given by this query

BeTheBall -> RE: Help with sql statement (4/14/2005 14:13:22)
I think the biggest obstacle here is the fact that there is not simply a store ID. If you were able to somehow merge the TB ID and KFC ID fields into one ID field, I think you could overcome the problem quite easily. Absent that, I don't see how it can be done.

dzirkelb1 -> RE: Help with sql statement (4/14/2005 14:32:56)
Well, the seq collumn sort of merges them...it gives a unique number to that route for each store (sort of an order for them to go in). I think with the most recent portion of code I posted above that works should be something to build on as it works perfectly for each store SELECT MASTER ROUTE TABLE 1998].[KFC ID], [MASTER ROUTE TABLE 1998].[TB ID], Max([MASTER ROUTE TABLE 1998].EvalDate) AS MaxEvalDate FROM [MASTER ROUTE TABLE 1998] GROUP BY [MASTER ROUTE TABLE 1998].[KFC ID], [MASTER ROUTE TABLE 1998].[TB ID] HAVING [KFC ID]='SomeVariable' Could I put it into a loop somehow in asp? If it comes down to possible in asp, but not in straight sql, then I'll do it in asp as it will be there in the future anyways.

BeTheBall -> RE: Help with sql statement (4/14/2005 14:52:48)
<last gasp effort> How about this: SELECT [MASTER ROUTE TABLE 1998].[KFC ID], [MASTER ROUTE TABLE 1998].[TB ID], Max([MASTER ROUTE TABLE 1998].EvalDate) AS MaxEvalDate FROM [MASTER ROUTE TABLE 1998] WHERE [MASTER ROUTE TABLE 1998].[KFC ID] IN (SELECT [KFC ID] FROM [MASTER ROUTE TABLE 1998] WHERE RT1 = 10873) OR [MASTER ROUTE TABLE 1998].[TB ID] IN (SELECT [TB ID] FROM [MASTER ROUTE TABLE 1998] WHERE RT1 = 10873) GROUP BY [MASTER ROUTE TABLE 1998].[KFC ID], [MASTER ROUTE TABLE 1998].[TB ID] </last gasp effort> By the way, using DISTINCT doesn't seem to change anything.

dzirkelb1 -> RE: Help with sql statement (4/14/2005 15:11:26)

ha ha!! that works perfectly!!!

I need to compact the database, and it will run slick as a whistle :)

<% 
myDSN ="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:/apps/msu_database/secure all location data.mdb"
myStoreIDs = "SELECT [MASTER ROUTE TABLE 1998].[KFC ID], [MASTER ROUTE TABLE 1998].[TB ID], Max([MASTER ROUTE TABLE 1998].EvalDate) AS MaxEvalDate FROM [MASTER ROUTE TABLE 1998] WHERE [MASTER ROUTE TABLE 1998].[KFC ID] IN (SELECT [KFC ID] FROM [MASTER ROUTE TABLE 1998] WHERE RT1 = 10873) OR [MASTER ROUTE TABLE 1998].[TB ID] IN (SELECT [TB ID] FROM [MASTER ROUTE TABLE 1998] WHERE RT1 = 10873) GROUP BY [MASTER ROUTE TABLE 1998].[KFC ID], [MASTER ROUTE TABLE 1998].[TB ID]"


set conntemp=server.createobject("adodb.connection") 
conntemp.open myDSN 
set rsTempStoreIDs=conntemp.execute(myStoreIDs)

on error resume next
IF rstempStoreIDs.eof THEN 
response.write ("ERROR - Problem with using: " & myStoreIDs ) 
rstempStoreIDs.close 
set rstempStoreIDs=nothing 
conntemp.close 
set conntemp=nothing 
ELSE 
arrayStoreIDs=rstempStoreIDs.getrows 
END IF


Response.write("<table border=1>" & VbCrLf)
Response.write("<tr>")
For Each F In rstempStoreIDs.Fields
Response.write("<th>" & F.Name & "</th>" & VbCrLf) 
Next 
Response.write("</tr>")
FOR r = 0 TO ubound(arrayStoreIDs,2) 
Response.write("<tr>" & VbCrLf) 
FOR c = 0 TO ubound(arrayStoreIDs,1) 
Response.write("<td>" & arrayStoreIDs(c,r) & "</td>" & VbCrLf) 
NEXT 
Response.write("</tr>" & VbCrLf) 
NEXT 
Response.write("</table>" & VbCrLf) 

rstempStoreIDs.close 
set rstempStoreIDs=nothing 
conntemp.close 
set conntemp=nothing 
%>

that puts it online for now, i'll play around with the rest for subtracting 7 days from each one and displaying the rest of the output. Thanks a ton for all of your help Dwayne, what a fun one huh!! :)

dzirkelb1 -> RE: Help with sql statement (4/14/2005 15:17:52)
Now, I would like to add two fields to the query...the rt1 field and the seq1 field. All they need to do is be there for display. It will be the exact same as the KFC ID and TB ID...it will only show the sequence numbers for a specefic route and the route will only display whatever route we searched for (in this case, it is 10873. any ideas?

dzirkelb1 -> RE: Help with sql statement (4/18/2005 10:39:12)
Any ideas on how to add other fields for display on this sql statement?

BeTheBall -> RE: Help with sql statement (4/18/2005 11:13:41)
Assuming the user is going to enter the route in some sort of form field, I would just use the Request.Form property to get the route #. I played trying to get the seq1 value, but it broke the query. If I get time, I will take another look.

dzirkelb1 -> RE: Help with sql statement (4/18/2005 11:19:05)
Ya, I will have some sort of form requesting route number, and that shouldn't be much of a problem placing that in the query. Mine also broke on all attempts to add the seq1 value...unsure why. It will eventually be sorted by seq1 value asc, and, is the number we identify with the shopper on what we are talking about as it is in the same order when we print their route for them.

BeTheBall -> RE: Help with sql statement (4/18/2005 17:19:48)
See if the last post in this thread is any help to you. It employs two queries but gets the job done. http://www.access-programmers.co.uk/forums/showthread.php?t=33094

dzirkelb1 -> RE: Help with sql statement (4/19/2005 10:08:05)
I'd rather not have two queries as this will be on an asp page eventually...unless it will work that way on asp? Its always been my experience that running a query from a query in asp breaks on me.

dzirkelb1 -> RE: Help with sql statement (4/19/2005 10:54:12)
I am thinking that this is just going to be too difficult to accomplish, and, the query is awfully slow to run. I have created another thread http://www.frontpagewebmaster.com/m-264320/tm.htm which loops the data from an array of store id's to get the max evaldate; however, having a little trouble with it. Thanks for your help, and if you think of anything on this sql statement, please give ideas as the sql statement doing everything will be easier :)

BeTheBall -> RE: Help with sql statement (4/19/2005 11:34:11)
This comes close but it picked up 4 duplicates, meaning 4 records where the TB ID or KFC ID and the MaxEvalDate were the same. SELECT Q1.[TB ID], Q1.[KFC ID], Q1.MaxEvalDate, [MASTER ROUTE TABLE 1998].SEQ1 FROM (SELECT [MASTER ROUTE TABLE 1998].[KFC ID], [MASTER ROUTE TABLE 1998].[TB ID], Max([MASTER ROUTE TABLE 1998].EvalDate) AS MaxEvalDate FROM [MASTER ROUTE TABLE 1998] WHERE [MASTER ROUTE TABLE 1998].[KFC ID] IN (SELECT [KFC ID] FROM [MASTER ROUTE TABLE 1998] WHERE RT1 = 10873) OR [MASTER ROUTE TABLE 1998].[TB ID] IN (SELECT [TB ID] FROM [MASTER ROUTE TABLE 1998] WHERE RT1 = 10873) GROUP BY [MASTER ROUTE TABLE 1998].[KFC ID], [MASTER ROUTE TABLE 1998].[TB ID]) AS Q1 LEFT JOIN [MASTER ROUTE TABLE 1998] ON (Q1.[TB ID]=[MASTER ROUTE TABLE 1998].[TB ID] OR Q1.[KFC ID]=[MASTER ROUTE TABLE 1998].[KFC ID]) AND Q1.MaxEvalDate=[MASTER ROUTE TABLE 1998].EvalDate However, I think you will make everything much easier if you combine KFC ID and TB ID into one field or at least create a third field for Store ID that gives each store a unique number.

dzirkelb1 -> RE: Help with sql statement (4/19/2005 11:39:40)
what is q1?

dzirkelb1 -> RE: Help with sql statement (4/19/2005 11:43:15)
I have just received numerous complaints about the database being bogged down recently...I have figured it is because of the queries I've been running. I am going to have to abandon this idea of doing it all with one query and go with the looping array. I am going to test this out and see if it works though as I am interested in the query itself...what a fun one !!

Page: [1] 2 next > >>

0.0625