help with join query

I am writing a query to find missing numbers. The query works, but is very very slow due to the number of records it is currently pulling.

I want to limit the records to only search where "records.event_year = 2007" but when I try to insert that after the "from dbo.records" it gives me an error.

Where exactly do I need to add this and is there anything else I can do to make the query run faster? The event_year, state_file_number, and isactive are all indexed.

================================================== =======
ALTER procedure [Migrate].[Chk_Missing_SFN]

@.beg as int,
@.end as int

AS
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
select BeforeSkip+1 as gapStart, NextValue-1 as gapEnd from (
select
a.state_file_number as BeforeSkip,
min(b.state_file_number) as NextValue
from dbo.records A join dbo.records b
on a.state_file_number < b.state_file_number

where a.state_file_number between @.beg and @.end and a.isactive = 'T' and b.isactive = 'T'

group by a.state_file_number
having min(b.state_file_number) > a.state_file_number + 1
) Xwhat error are you getting?

I imagine that the "A.event_year = 2007" needs to go after the "on A.state_file_number < b.state_file_number"|||Remember that this snippet creates a million row test set before you judge speed.-- ptp 20071130 See http://www.dbforums.com/showthread.php?t=1624988

CREATE TABLE patp (
patpId INT NOT NULL
CONSTRAINT XPKpatp
PRIMARY KEY CLUSTERED (patpId)
)

INSERT INTO patp (
patpID
) SELECT 0 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4
UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9

INSERT INTO patp (
patpID
) SELECT d0.patpID + d1.patpId + d2.patpId + d3.patpId + d4.patpId + d5.patpId
FROM patp AS d0
CROSS JOIN (SELECT 10 * patpId AS patpID FROM patp) AS d1
CROSS JOIN (SELECT 100 * patpId AS patpID FROM patp) AS d2
CROSS JOIN (SELECT 1000 * patpId AS patpID FROM patp) AS d3
CROSS JOIN (SELECT 10000 * patpId AS patpID FROM patp) AS d4
CROSS JOIN (SELECT 100000 * patpId AS patpID FROM patp) AS d5
WHERE 0 < d1.patpId + d2.patpId + d3.patpId + d4.patpId + d5.patpId
ORDER BY 1

DECLARE @.i INT
SET @.i = 1

WHILE @.i < (SELECT Max(patpID) FROM patp)
BEGIN
DELETE FROM patp WHERE patpID = @.i
SET @.i = 2 * @.i
END

SELECT a.patpID AS block_begin
, (SELECT Min(b.patpID)
FROM patp AS b
WHERE a.patpID <= b.patpID
AND NOT EXISTS (SELECT *
FROM patp AS c
WHERE c.patpID = 1 + b.patpID)) AS block_end
FROM patp AS a
WHERE NOT EXISTS (SELECT *
FROM patp AS b
WHERE b.patpId = a.patpID - 1)

DROP TABLE patp-PatP|||Thank you very much. That worked as needed.