table - 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)

table

Author

Topic

jewel
Starting Member

18 Posts

Posted - 2004-08-17 : 21:07:17

If I have a table like this: table is called Call_Subject

CallSubjectUnique 3481
CallSubjectType 3
CallSubject1 Building Services
CallSubject2 Help
CallSubject3 Copier
MinCharge 0
ChargeRate 0
MaterialCharge 0
TTHrs <NULL>
TTMins <NULL>
DefaultPriority <NULL>
ScriptComments
InfoComments
SLTUnique1 21
SLTUnique2 <NULL>
SupportGroupUnique 66
IthelpUserUnique <NULL>
Timestamp <Binary>

other tables access this information as a lookup table - eg main table that accesses is a call_table

This is a huge database, so is there anything you can tell me about how to find out if the subject has been accessed or used.

I tried using the Timestamp field eg <>1 or 0 but didn't seem to give the correct results.

I know this is a longshot without knowing the setup but worth a try
thanks
jewel

timmy
Master Smack Fu Yak Hacker

1242 Posts

Posted - 2004-08-17 : 21:27:41

What do you mean used?
You can't get information on when a particular record was last accessed

jewel
Starting Member

18 Posts

Posted - 2004-08-17 : 21:49:21

thanks Tim - ok maybe I need to explain a little more

we have over 3500 service/category/items = field 1/2/3

I was asked to find out how many of these have never been used.

so we log a call - it then uses the call_subject table as a lookup for these three fields and logs the call. (which is why I thought the binary field might have been a good choice)

so other than exporting 3500 entries to excel and then trying an export of all the ones with a call against it - I didn't know who else to approach it

thanks

timmy
Master Smack Fu Yak Hacker

1242 Posts

Posted - 2004-08-18 : 02:01:53

So, if it uses the table as a lookup then presumably the ID of the call_subject in the call_table table?
If so, all you need to do is run a query joining these two:

SELECT S.* 
FROM call_subject S LEFT JOIN  call_table T ON S.CallSubjectID = T.CallSubjectID
WHERE T.CallSubjectID IS NULL

This will list all call_subject records that are not referenced by the call_table table.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources