How Can I Find Gaps in Date and Number Sequences?

[stef315]

Hi!

I am searching the MS SQL forum for an answer to a need I have. I came across the FAQ: How Can I Find Gaps in Date and Number Sequences?
faq183-840.

I wonder if this is on the right track or if you someone could at least point me in the right direction? I am not a programmer or database expert. Basically, I'm a CPA who works as a consultant for accounting software running on MS SQL databases.

What I want to do is search a table holding financial transactions and first restrict the type to "check" (CMTrxType=3). I also want the user to be able to change the range (CMTrxNum between '10500' and '10504').

The part I can't figure out is how I determine a missing number from the CMTrxNum range? So, I select checks between 10500 and 10504 and I want to know which numbers are missing (e.g. 10502) so that I can insert a record to add this number with other information. The purpose is to enter voided checks for missing numbers between a certain range.

Can anyone help me with this? Or, maybe tell me what to search for in SQL Books Online or on the forum. Searching on "missing" just isn't cutting it.

I appreciate any help.

Thanks!

 

[stef315]

I don't like users updating in QA either but my boss decided this was worth it.

I can make a new table "MISSING" with just one column called "TrxNum" or something of type bigint. If I update this table with just the missing numbers, I can write an update query with the values from this table and my values. At the end, I will delete from Missing to clear it for the next time. Does this sound possible?

 

[bperry]

Here is what the SP would look like if it were to insert the missing numbers into a table called MISSING. Create table MISSING with a single column, integer datatype. Any existing rows in MISSING will be automatcially deleted before it starts. Changes are in bold
-------------------------------

CREATE PROCEDURE uspMissingNumbers (
@parmStart int,
@parmEnd int
)
AS
-- First do some error checking
if @parmEnd < @parmStart
set @parmEnd = @parmStart
if @parmEnd - @parmStart > 99
set @parmEnd = @parmStart + 99

set NOCOUNT ON
Delete from MISSING
set NOCOUNT off
INSERT INTO MISSING

Select LinkNum as Missing --, CMTrxNum, CMTrxType
from (Select (a0.id + a1.id) as LinkNum
FROM
(
Select 0 + @parmStart as id UNION ALL
Select 1 + @parmStart UNION ALL
Select 2 + @parmStart UNION ALL
Select 3 + @parmStart UNION ALL
Select 4 + @parmStart UNION ALL
Select 5 + @parmStart UNION ALL
Select 6 + @parmStart UNION ALL
Select 7 + @parmStart UNION ALL
Select 8 + @parmStart UNION ALL
Select 9 + @parmStart
) as a0
CROSS JOIN
(
Select 0 as id UNION ALL
Select 10 UNION ALL
Select 20 UNION ALL
Select 30 UNION ALL
Select 40 UNION ALL
Select 50 UNION ALL
Select 60 UNION ALL
Select 70 UNION ALL
Select 80 UNION ALL
Select 90
) as a1
) as n
Left Join tblCMTrx c
ON CAST(n.LinkNum as Varchar(10)) = CMTrxNum
where n.LinkNum <= @parmEnd
and c.CMTrxNum IS NULL

Order By LinkNum
GO
----------------

 

[bperry]

fyi, if you had a NUMBERS table permanently in the database, with 100 rows numbered 0 to 99, then this is what our SP would look like. A lot shorter.
--------------------------------------
CREATE PROCEDURE uspMissingNumbers (
@parmStart int,
@parmEnd int
)
AS
-- First do some error checking
if @parmEnd < @parmStart
set @parmEnd = @parmStart
if @parmEnd - @parmStart > 99
set @parmEnd = @parmStart + 99

Select LinkNum + @parmStart as Missing
from Numbers n
Left Join tblCMTrx c
ON CAST(n.LinkNum + @parmStart as Varchar(10)) = CMTrxNum
where n.LinkNum + @parmStart <= @parmEnd
and c.CMTrxNum IS NULL

Order By LinkNum
GO
---------------------------

 

[stef315]

Wow! You're amazing....tell me it took you 20 years to learn all this?!

The table worked great! I will be out of town for the day so I will continue tonight.