|
| |
|
|
dzirkelb1
Posts: 1315
Joined: 10/5/2004
From: Cedar Rapids, Iowa
Status: offline
 |
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
Posts: 1315
Joined: 10/5/2004
From: Cedar Rapids, Iowa
Status: offline
|
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
Posts: 1315
Joined: 10/5/2004
From: Cedar Rapids, Iowa
Status: offline
|
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
Posts: 6381
Joined: 6/21/2002
From: West Point Utah USA
Status: offline
|
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
_____________________________
Duane
Some people are like Slinkies . . . Not really good for anything . . . . . But they still bring a smile to your face when you push them down a flight of stairs.
| |
|
|
|
dzirkelb1
Posts: 1315
Joined: 10/5/2004
From: Cedar Rapids, Iowa
Status: offline
|
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
Posts: 6381
Joined: 6/21/2002
From: West Point Utah USA
Status: offline
|
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]
_____________________________
Duane
Some people are like Slinkies . . . Not really good for anything . . . . . But they still bring a smile to your face when you push them down a flight of stairs.
| |
|
|
|
dzirkelb1
Posts: 1315
Joined: 10/5/2004
From: Cedar Rapids, Iowa
Status: offline
|
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
Posts: 1315
Joined: 10/5/2004
From: Cedar Rapids, Iowa
Status: offline
|
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
Posts: 1315
Joined: 10/5/2004
From: Cedar Rapids, Iowa
Status: offline
|
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
Posts: 6381
Joined: 6/21/2002
From: West Point Utah USA
Status: offline
|
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.
_____________________________
Duane
Some people are like Slinkies . . . Not really good for anything . . . . . But they still bring a smile to your face when you push them down a flight of stairs.
| |
|
|
|
dzirkelb1
Posts: 1315
Joined: 10/5/2004
From: Cedar Rapids, Iowa
Status: offline
|
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
Posts: 6381
Joined: 6/21/2002
From: West Point Utah USA
Status: offline
|
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?
_____________________________
Duane
Some people are like Slinkies . . . Not really good for anything . . . . . But they still bring a smile to your face when you push them down a flight of stairs.
| |
|
|
|
dzirkelb1
Posts: 1315
Joined: 10/5/2004
From: Cedar Rapids, Iowa
Status: offline
|
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
Posts: 6381
Joined: 6/21/2002
From: West Point Utah USA
Status: offline
|
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
_____________________________
Duane
Some people are like Slinkies . . . Not really good for anything . . . . . But they still bring a smile to your face when you push them down a flight of stairs.
| |
|
|
|
dzirkelb1
Posts: 1315
Joined: 10/5/2004
From: Cedar Rapids, Iowa
Status: offline
|
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
Posts: 1315
Joined: 10/5/2004
From: Cedar Rapids, Iowa
Status: offline
|
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
Posts: 6381
Joined: 6/21/2002
From: West Point Utah USA
Status: offline
|
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.
_____________________________
Duane
Some people are like Slinkies . . . Not really good for anything . . . . . But they still bring a smile to your face when you push them down a flight of stairs.
| |
|
|
|
dzirkelb1
Posts: 1315
Joined: 10/5/2004
From: Cedar Rapids, Iowa
Status: offline
|
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
Posts: 6381
Joined: 6/21/2002
From: West Point Utah USA
Status: offline
|
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.
_____________________________
Duane
Some people are like Slinkies . . . Not really good for anything . . . . . But they still bring a smile to your face when you push them down a flight of stairs.
| |
|
|
|
dzirkelb1
Posts: 1315
Joined: 10/5/2004
From: Cedar Rapids, Iowa
Status: offline
|
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
Posts: 1315
Joined: 10/5/2004
From: Cedar Rapids, Iowa
Status: offline
|
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
Posts: 1315
Joined: 10/5/2004
From: Cedar Rapids, Iowa
Status: offline
|
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
Posts: 6381
Joined: 6/21/2002
From: West Point Utah USA
Status: offline
|
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.
_____________________________
Duane
Some people are like Slinkies . . . Not really good for anything . . . . . But they still bring a smile to your face when you push them down a flight of stairs.
| |
|
|
|
dzirkelb1
Posts: 1315
Joined: 10/5/2004
From: Cedar Rapids, Iowa
Status: offline
|
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
Posts: 6381
Joined: 6/21/2002
From: West Point Utah USA
Status: offline
|
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
_____________________________
Duane
Some people are like Slinkies . . . Not really good for anything . . . . . But they still bring a smile to your face when you push them down a flight of stairs.
| |
|
|
|
dzirkelb1
Posts: 1315
Joined: 10/5/2004
From: Cedar Rapids, Iowa
Status: offline
|
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
Posts: 1315
Joined: 10/5/2004
From: Cedar Rapids, Iowa
Status: offline
|
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
Posts: 6381
Joined: 6/21/2002
From: West Point Utah USA
Status: offline
|
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.
_____________________________
Duane
Some people are like Slinkies . . . Not really good for anything . . . . . But they still bring a smile to your face when you push them down a flight of stairs.
| |
|
|
|
dzirkelb1
Posts: 1315
Joined: 10/5/2004
From: Cedar Rapids, Iowa
Status: offline
|
RE: Help with sql statement - 4/19/2005 11:39:40
what is q1?
| |
|
|
|
dzirkelb1
Posts: 1315
Joined: 10/5/2004
From: Cedar Rapids, Iowa
Status: offline
|
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 !!
| |
|
 New Messages |
 No New Messages |
 Hot Topic w/ New Messages |
 Hot Topic w/o New Messages |
 Locked w/ New Messages |
 Locked w/o New Messages |
|
Post New Thread
Reply to Message
Post New Poll
Submit Vote
Delete My Own Post
Delete My Own Thread
Rate Posts
|
|
|
Printable Version 
