Brain Blockage with Query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Brain Blockage with Query

Author

Topic

MuadDBA

628 Posts

Posted - 2004-02-27 : 17:00:49

I need help. I guess that's obvious to anyone who talks to me, but I need it now for a particular query.

I have a (vendor designed) database for tracking where physical loan files are located, as well as providing labelling and other information about the files.

There is a table which contains the current position of each of my file folders. There are 2 file folders per loan, a credit folder, and a closing folder. Folders are designated by "F" + loan number + "CR"(for credit folders) or "CL" for closing folders.

So now I have a user group which wants to know:

What loans have a credit folder(and its location) which is in [location set] and also have a closing folder (and its location) that is not in [location]

My table structure looks like this:
TRACKINGSTATUS TABLE:
Trackedtable (identifies if the item is a folder, document, or box) varchar(25)
Trackedtableid (this is the F + loanno + CR/CL) varchar(25)
LocationsId varchar(10)
(and a bunch of other irrelevant columns)

My problem is that the location criteria is so vast, I know I am going to have to do a tablescan to get this info. I want to avoid doing *2* of them if possible, one to find the credit folders, and one to find the closings. however, on this friday evening, I just can't seem to get my head around the problem.

Any help would be most appreciated.

MichaelP
Jedi Yak

2489 Posts

Posted - 2004-02-27 : 17:53:59

Got some sample data?

Michael

<Yoda>Use the Search page you must. Find the answer you will.</Yoda>

MuadDBA

628 Posts

Posted - 2004-02-27 : 17:57:40

Folders F0000276448CL P002943
Folders F0000276448CR P000172
Folders F0000731200CR P000167
Folders F0001704451CL P001693
Folders F0001704451CR P001693
Folders F0001962892CR P000167
Folders F0002626152CL P000347
Folders F0002626152CR P003658
Folders F0002628252CL P000347
Folders F0002628252CR P000347
Folders F0002629461CL P000172
Folders F0003043762CL P000172
Folders F0003043762CR P000172
Folders F0003043789CL P001643
Folders F0003043789CR P001643
Folders F0003331318CL P000172
Folders F0003331318CR P000347
Folders F0003715400CL P000347
Folders F0003715400CR P001719
Folders F0003855899CL P000347

AjarnMark
SQL Slashing Gunting Master

3246 Posts

Posted - 2004-02-27 : 20:20:48

What about putting the location criteria into a temp table and then joining it to the first table using additional terms in the JOIN clause like I discuss in my article at http://www.sqlteam.com/item.asp?ItemID=11122

I'm thinking something like
SELECT *
FROM #MyTempTable mt
LEFT JOIN TrackingStatus ts on mt.LocationsID = ts.LocationsID AND ts.TrackedTable = 'Folders' AND (RIGHT(ts.TrackedTableID, 2) = 'CL' OR RIGHT(ts.TrackedTableID,2) = 'CR')

Or something like that...

--------------------------------------------------------------
Find more words of wisdom at [url]http://weblogs.sqlteam.com/markc[/url]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources