|
| |
|
|
dzirkelb1
Posts: 1315
Joined: 10/5/2004
From: Cedar Rapids, Iowa
Status: offline
 |
RE: Help with sql statement - 4/19/2005 11:54:10
currently it displays a seq1 value, but it is incorrect as the seq number doesn't match up with the correct store id on the route...it matches with store ID on a previous route, but not for the current route.
|
|
|
|
BeTheBall
Posts: 6381
Joined: 6/21/2002
From: West Point Utah USA
Status: offline
|
RE: Help with sql statement - 4/19/2005 13:17:49
I think this one works, but sadly, it won't speed things up any:
SELECT Q1.[KFC ID], Q1.[TB ID], Q1.MaxEvalDate, Q2.SEQ1 FROM (SELECT SEQ1, [KFC ID], [TB ID] FROM [MASTER ROUTE TABLE 1998] WHERE RT1=10873) AS Q2 INNER JOIN (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 ON Q1.[KFC ID]=Q2.[KFC ID] OR Q1.[TB ID]=Q2.[TB ID]
Q1 and Q2 are random names assigned to the two subqueries that are created.
I really wonder if normalizing your db would speed it up, but that would require a lot of work on your part. Normalizing would entail breaking up your one big table into smaller ones. Most of your current columns would become their own table with just the column info and an autonumber ID field. EvalDates would include only the date and the ID of the store evaluated. You then use joins to bring needed information together. I also think you would benefit from a table that gave each store a unique ID instead of a set of TB IDs and KFC IDs. You could simply take the current ID and precede it with at T or K.
Just my $.02
_____________________________
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 13:28:24
Yup, that statement works correctly as advertised...thanks!!!
ya, not sure what I can do with the slowness, I can't chagne id's at all as some have a kfc id and a tb id...why? who the heck knows :)
I do have a table that has all the info for id's in a specefic table...I am not sure if they have a specefic ID in that table or not.
I am going to compact the database this week as it really needs also, and clean up all of the junk in it...its sooo full of junk, and its been a project I wanted to do once I started here.
again, thanks for all of your help!! I'll see what I can do :)
| |
|
 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 
