Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

QA returning inconsistent records

Posted on 2004-08-27
6
Medium Priority
?
322 Views
Last Modified: 2012-06-27
Here's perhaps the most bizarre thing I've seen from SQL Server in a long time.  I have two tables, TiMaster (1.2million recs) and Staff (300 recs) and an application that gets inconsistent results.  So I ran the sql statements directly in Query Analyzer and found differing results, from time to time.

Is there something wrong with my statement syntax? The only thing I can think of is the "T.STAFF IN (..." part of the Where clause might not handle strings the way I expect.  I've used it consistently with a set of integers, but maybe 'AS' would be treated as found in a list containing 'ASX'?

Here are the statements:

Select
      S.NAME, T.STAFF, T.MATTER_ID, SUM(T.HOURS) AS ATTYHOURS, T.TEAM,
      SUM(0) AS ASSTHOURS, SUM(T.ORIG_AMT) AS ATTYAMOUNT,
      SUM(0) AS ASSTAMOUNT, T.MATTER_ID AS MATTERID, S.NAME
From
      TIMASTER T, STAFF S
Where
      (T.IS_ATTY<>0) AND
      (T.ACCT_NO IN (16914462)) AND
      (T.STAFF IN ('ASB', 'BLP', 'VT')) AND
      ((T.EFF_DATE >= '2003-07-01') AND (T.EFF_DATE < '2004-07-01')) AND
      (T.STAFF = S.STAFF_ID)
Group By
      S.NAME, T.STAFF, T.MATTER_ID, T.TEAM , S.NAME
ORDER BY
      S.NAME

This one returns consistently a set of records for user 'VT' which total 1,151 hours.  But when I add more users, with everything else identical, the number of records returned is FEWER (sometimes)!  They usually, but not always, return 296 hours.  Another bizarre twist here is that if I keep the number of STAFF items in the list the same, but shorten the date range, the correct number of hours are returned.

Here's the second one:

Select
      S.NAME, T.STAFF, T.MATTER_ID, SUM(T.HOURS) AS ATTYHOURS, T.TEAM,
      SUM(0) AS ASSTHOURS, SUM(T.ORIG_AMT) AS ATTYAMOUNT,
      SUM(0) AS ASSTAMOUNT, T.MATTER_ID AS MATTERID, S.NAME
From
      TIMASTER T, STAFF S
Where
      (T.IS_ATTY<>0) AND
      (T.ACCT_NO IN (16914462)) AND
      (T.STAFF IN ('ASB', 'BLP', 'CKC', 'DEM', 'DIF', 'DJS', 'EEM',
            'JAP', 'JRO', 'LGT', 'LMM', 'LWP', 'MAL',
            'MWE', 'NTD', 'SRM', 'VT', 'WAJ', 'WDS', 'WS')) AND
      ((T.EFF_DATE >= '2003-07-01') AND (T.EFF_DATE < '2004-07-01')) AND
      (T.STAFF = S.STAFF_ID)
Group By
      S.NAME, T.STAFF, T.MATTER_ID, T.TEAM , S.NAME
ORDER BY
      S.NAME

Any information that might give me clues would be GREATLY appreciated.
0
Comment
Question by:bjones8888
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
6 Comments
 
LVL 12

Expert Comment

by:kselvia
ID: 11915448
You might have some corrupt indexes. Run

DBCC DBREINDEX('TIMASTER')
DBCC DBREINDEX('STAFF')

and see if that makes any difference.
0
 

Author Comment

by:bjones8888
ID: 11916421
That sounded like the perfect solution.  Perfectly explains the differing results, and also explains why I could import all that data into a new database and NOT see different results.  However, after the reindex, I still get the same problem.  

I'm not giving up on the idea of corrupted indices, though.  Unfortunately, I've got about 80 users in the system for another 5-6 hours, but after that I'm planning to do a complete rebuild of the tables and indices.

Would the index rebuild be invalidated / delayed if there are clients using the tables?  The response from QA indicated that the rebuild was complete, but didn't mention anything about errors.
0
 
LVL 12

Expert Comment

by:kselvia
ID: 11916745
It would have run immediatly.  When you get the users off the system you might also try

DBCC CHECKTABLE ('TIMASTER')
DBCC CHECKTABLE ('STAFF')

What are the datatypes of T.HOURS and T.ORIG_AMT?
0
 

Author Comment

by:bjones8888
ID: 11943764
Sorry for the long delay here.  I tried all of the above, including DBCC CHECKDB.  None of that had any effect.  The indices and tables reported no errors.

However, here's what worked.  In the Where clause, when I changed "(T.STAFF IN ('ASB', ..." to "(S.STAFF_ID IN ('ASB', ...", it worked perfectly.  I'm clueless as to why, though.  I'm still willing to award the points to whomever can tell me why.  I thought specifying the staff criteria on the TiMaster table would be more appropriate (that's the detail of all time records).  The STAFF table is, obviously, one record per staff member.  You'll see one of the criteria in the Where clause is (T.STAFF = S.STAFF_ID).

Two questions:  1. Why would my change make such a difference?  and, 2. Is there a difference in how that type of statement was implemented in different versions of SQL Server?  (Remember, my copy of their data worked every time.  I'm on SQL 2000 and my client is probably on SQL Server 7.0.)
0
 
LVL 1

Accepted Solution

by:
Computer101 earned 0 total points
ID: 12195585
PAQed, with points refunded (250)

Computer101
E-E Admin
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

722 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question