Join/Where

[earthandfire]

I thought this was going to be straightforward, but I've tried every combination of Join and Where that I can think of and I'm beaten

...
...
...

DECLARE @clno int
DECLARE @chno	varchar(22)
DECLARE @repdt datetime

CREATE TABLE #currentclaims(claimno int)
INSERT INTO #currentclaims SELECT ClaimNo FROM ClaimsList

SET @Counter = ((SELECT Sum(DefaultCount) FROM AccountCodesDefault) + @BusCount) - (SELECT Count(*) FROM ClaimsList)
IF @Counter > 0
BEGIN
	SET @LocalCount = 1
	WHILE @LocalCount <= @Counter
	BEGIN
		SELECT TOP 1
			@clno = t.ClaimNo, @chno = t.ChassisNo, @repdt = t.RepairDate
		FROM
			#tmpTrucks t JOIN ClaimsList c ON
				t.ChassisNo = c.ChassisNo AND
				((t.RepairDate < c.RepairDate - 10) OR (t.RepairDate > c.RepairDate + 10))
		WHERE
			t.ClaimNo NOT IN (SELECT ClaimNo FROM #currentclaims)
			INSERT INTO ClaimsList SELECT * FROM #tmpTrucks WHERE ClaimNo = @clno
			INSERT INTO #currentclaims SELECT @clno
		SET @LocalCount = @LocalCount + 1
	END
END

...
...
...

Table ClaimsList has by this point been populated with unique Chassis Numbers. More records may be required based on the value of @Counter. These additional records will duplicate existing ChassisNumbers but must not have RepairDates within +/- 10 days of an existing record for that ChassisNumber in ClaimsList. It doesn't matter if after this section there are still less than the total required records in ClaimsList (in other word there may not be @Counter records that meet the criteria).

The above code and all variations of Join and Where that I have tried retrieve records irrespective of the RepairDate criteria.

ClaimNo is int
ChassisNo is varchar(22)
RepairDate is datetime

Any help would be greatly appreciated.

 

[earthandfire]

Problem solved. Although I had tried EXISTS and NOT EXISTS earlier, I had obviously got it wrong.


This does the trick:

		SELECT TOP 1
			@clno = t.ClaimNo, @chno = t.ChassisNo, @repdt = t.RepairDate
		FROM
			#tmpTrucks t
		WHERE
			NOT EXISTS 
				(SELECT c.ChassisNo, c.RepairDate 
					FROM ClaimsList c 
					WHERE
						t.ChassisNo = c.ChassisNo AND
						c.RepairDate BETWEEN t.RepairDate - 10 AND t.repairDate + 10)

 

[vongrunt]

Btw. there is TOP clause without ORDER by...

------
[small]<this is sig>
select stuff(stuff(replicate('<P> <B> ', 14), 109, 0, '<.'), 112, 0, '/')
</this is sig>[/small]