Strange results - 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

SQL Server Development (2000)

Strange results

Author

Topic

Lennartb
Starting Member

5 Posts

Posted - 2005-06-24 : 02:07:04

Hello,

I have designed a table in which I compare the delivery of today with the delivery of yesterday. To find out which records are deleted I use the following query:

SELECT a1.PersNr, a1.BoNr, a1.StartDate, a1.Type, a1.Code, a1.EndDate, a1.Urgent, a1.VerpleegAdres, a1.VerpleegPC, a1.VerpleegPlaats, a1.MutatieDatum, a1.ZiektePercentage, 'D' as mutatie
	FROM tblPeopleSoftAbs a1
	LEFT JOIN tblPeopleSoftAbs a2  ON a2.PersNr = a1.PersNr 
--					AND a2.BoNr = a1.BoNr
					AND a2.StartDate = a1.StartDate 
					AND a2.Type = a1.Type
					AND a2.BestandsDatum = '2005-06-21 09:01:34.000'
	WHERE a1.BestandsDatum = '2005-06-21 02:02:34.000'
	AND a2.PersNr IS NULL
	AND a1.StartDate > GETDATE() - 730
	GROUP BY a1.PersNr, a1.BoNr, a1.StartDate, a1.Type, a1.Code, a1.EndDate, a1.Urgent, a1.VerpleegAdres, a1.VerpleegPC, a1.VerpleegPlaats, a1.MutatieDatum, a1.ZiektePercentage

The fieldnames are dutch ;) The resultsets to compare max 100.000 a day.

This worked for about 6 months. Since tuesday this query gives strange results. Unless the data is static (and not manipulated history), my results are different any time! And nevertheless, the results are wrong. When I check the results, the deliveries are the same. For example, when I run it, it returns 50 rows, next time 70, 30 etc.

Well, I solved it by creating a new (empty) table and read back a few days.

Features of the table:
- 39.313.469 Records (i know.. a lot)
- No indexes left, someone told that they were probably broken but that did not help.

Because it is a bussiness critical feature, I really want to know how to prevent this next time. Is there someone who had the same problem?
I have the feeling I reached some limits...

Hope to hear from you!

Lennart

barmalej
Starting Member

40 Posts

Posted - 2005-06-24 : 03:46:23

Hi,
You cannot find which records where deleted by joining same table to itself.
For me the simplest solution is to have simple logic. If something has not been delivered yesterday then BestandsDatum (hope it is delivery date) must be null. Then if it is already delivered then the field must have proper not null value.
Then you write simply:
select ... where BestandsDatum between firstDatetime and secondDatetime.

Naturally good to have proper indexes having StartDate and/or deliveryDate

And also I do not understand what are you trying to group with Group By clause if you want a list of records.

Best wishes

Lennartb
Starting Member

5 Posts

Posted - 2005-06-24 : 04:18:34

barmalej,

Maybe I had to give more information. Every day, the whole set is been delivered. So on monday I get 10000 records, next day 9999 records. I want to have the deleted one. The set is the same everyday with some small changes in it.

The group by is because I don't want any double entries in it. Some entries are the same and I don't want them back in my results.

Lennart

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources