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!

 

[bperry]

Hi there,
maybe we can start with this one and try to work it to a point where it is useful to you.
-----------------------------------
This works quite well with my test data. It identifies single gaps (ie 21 - 23) very well. But gaps with more than one missing number are still reported as one gap. (ie 21 - 25 is one gap)

Everywhere here, change the work 'gaps' to the actual name of your table. (I think 3 spots)

You can see where I've begun to deal with the range issue, where I have hard-coded 'between 10 and 20'. Change those two numbers to something suitable.

I have ignored the issue of CMTrxType=3 for now because I'm not quite clear on what it means and how to deal with it. For instance, if we find number 50 missing, how do we know if that's a type we are interested in (ie if it is missing, then we can't tell??)

Try this if you like, and if you want to then let us know how you got along with it.
----------------------------------
select (g1.CMTrxNum + 1) as 'Gap!'
from gaps g1
Left JOIN gaps g2
on g1.CMTrxNum + 1 = g2.CMTrxNum
where
g1.CMTrxNum between 10 and 20
and
g2.CMTrxNum IS NULL
and
g1.CMTrxNum <> (Select MAX(CMTrxNum) from gaps)
order by g1.CMTrxNum
------------------------------------

bperry

 

[stef315]

Thank you for the response. Here's what happened.

I got an error converting to integer because the numbers are actually strings. If I restrict first to CMTrxType=3, then I will just be looking at the existing checks in the table and looking for gaps in there. Most of the time checks will be integers. Very rarely someone will put a letter after the number or something. In that case, I'd like to ignore those.

Since the range is further down in the query, it's looking at all the numbers and the first ones tend to be screwy ones. If I do the trx type restriction first, that should help.

Do you think it would be good to do a view with the restrictions (trx type, non-numeric characters)? Then, run the query on the view? After all this is done, I need to do an update on the main table with these values and fill-in values for the rest of the record.

 

[bperry]

Well, I guess a view might be interesting. Let us try this.
-----------------
Create View OurView
AS
Select
CAST(CMTrxNum as Integer) as CMTrxNum
from YourTable
where CMTrxType=3 and ISNUMERIC(CMTrxNum)
------------------
select (g1.CMTrxNum + 1) as 'Gap!'
from OurView g1
Left JOIN OurView g2
on g1.CMTrxNum + 1 = g2.CMTrxNum
where
g1.CMTrxNum between 10 and 20
and
g2.CMTrxNum IS NULL
and
g1.CMTrxNum <> (Select MAX(CMTrxNum) from OurView)
order by g1.CMTrxNum
--------------------

 

[stef315]

Okay, I'm stupid. I'm getting &quot;Line 6: Incorrect syntax near ')'.&quot; and I can't find the error. This is when creating the view.

 

[bperry]

Please add = 1 to the end
---------------------------------
Create View OurView
AS
Select
CAST(CMTrxNum as Integer) as CMTrxNum
from YourTable
where CMTrxType=3 and ISNUMERIC(CMTrxNum) = 1
--------------------------------

also, Is CMTrxType an integer? if not then
.....CMTrxType='3'

 

[tlbroadbent]

The following stored procedure returns all missing values in the table or within a specified range. The stored procedure in the FAQ returns next available number in the range.

Create Proc uspMissingCheckNumbers
@start varchar(8)='', @end varchar(8)='99999999'
As

Select @end=cast(cast(@end as int)-1 As varchar(8))

Select Missing=cast(a.CMTrxNum As Int)+1
From tblCMTrx a
Left Join tblCMTrx b
On cast(a.CMTrxNum As Int)+1=cast(b.CMTrxNum AS Int)
Where a.CMTrxType=3
And a.CMTrxNum Between @start And @end
And b.CMTrxType Is Null
Go

Execute the SP to find missing numbers in a range.

Exec uspMissingCheckNumbers '10500', '10504' Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains &quot;Suggestions for Getting Quick and Appropriate Answers&quot; to your questions.

 

[bperry]

Okay, this is a very tricky query to get just right. Several issues besides gaps: numbers in string format, letters in the number column, various Types, etc. With my test data, uspMissingCheckNumbers behaves quite strangely when passed various combinations. (I can provide test results if desired.) Also, I didn't like the view approach I was beginning to take.

I now propose a completely different approach to building the SP. Instead of a self-join, my new SP generates a derived range of numbers, which are left-joined to tblCMTrx to see if the right-hand table has NULLS or not.

This SP appears to work quite well on my test data, and seems to overcome the problems I found with uspMissingCheckNumbers. However, there is this hitch: it will only scan a range of 100 numbers at a time. This range can be made bigger if needed, but based on your earlier comments I thought 100 was quite reasonable.

Execute the SP like this:
exec uspMissingNumbers 540,565

Notice no quotes around the numbers.

Like I said, it seems to work with all my test data. Hopefully it will with yours too.
---------------------------------
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 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]

tbroadbent:
These are the weird results that I seemed to get with the SP uspMissingCheckNumbers:

Sample Sequence: notice that 4,8,9,& 14 are missing.

1-2-3- -5-6-7- - -10-11-12-13- -15

uspMissingCheckNumbers '1','3' = 14,16 ??

uspMissingCheckNumbers '1','15' = 14 ??

uspMissingCheckNumbers '4','5' = no gaps reported ?

uspMissingCheckNumbers '8','15' = no gaps reported ?

uspMissingCheckNumbers '1','10' = 4,8,14,16 ??

uspMissingCheckNumbers '8','9' = no gaps reported ?

uspMissingCheckNumbers '7','11' = no gaps reported ?

Also, whenever there is a letter in the CMTrxNum column, then uspMissingCheckNumbers will bomb off.

 

[stef315]

Sorry guys, I was busy with other things all day at work. It's just now 5pm here. I'm on Alaska time.

I'll check out all these latest reponses very soon and let you know how it goes. I really appreciate all the help.

My scheduling manager wanted me to give the task to someone else but I didn't want to give it up so I promised I'd make some headway today. She ended up not coming to work today so I got a little relief!

I'll be sure to post back when I've had time to check these things out. Thanks again!

 

[stef315]

Okay. Here's what happened. In tbroadbent's suggestion, I get errors on the data. There are some values with characters and some values like &quot;1000.1&quot; After I take the weird values out of my test table, I get one missing number at times there are numerous.

The last suggestion from bperry, I didn't get any missing numbers. I didn't get any errors though. This field has string values and I noticed there are spaces after the numbers. I guess to fill the max length of the field?

I think it's getting closer though!

 

[bperry]

Okay, there can't be much wrong here. This thing works very well on my test data, so we need to think of what the difference could be.

All that stuff about UNION ALL, etc is just doing one thing: it's generating an ad hoc list of numbers, in memory, for the range that you specify. Then that list is just left-joined to your real table: a match means your number exists, a non-match means it don't. What I like about this approach is that we can guarantee (almost?) that it will work: left-joins are easy and done all the time in sql.

So, let's try to see what the difference could be, between my table (and test data) and yours.

Let's start with something easy. The column in my table is char(10), no nulls, and does contain trailing blanks. Can you confirm what yours is? I will change my table definition to match yours. (To make it easier, can you also mention what the actual name of your table is. (tbroadbent and I have been calling it tblCMTrx, but to avoid confusion I will rename mine to yours.)

Allow me to ask something unnecessary: You are sure that you've run this for a range where there actually is a gap, right? (Seems silly to ask, but I have seen, and done, sillier!)

Is there something you can think of that hasn;t been mentioned yet?

 

[bperry]

Also, what is the column CMTrxTYPE like? Integer? Something else?

In my last post, I asked if you were sure there gaps in your test range. That reminded me of something else: Are you sure that the missing numbers that should be reported are CMTrxType = 3? Because if they are not 3, then they are excluded, right? (i.e. NOT considered to be missing)
-------------------------------------------
Here's a subset of my test data. As I mentioned, this column is char(10); the numbers are left-justified with trailing blanks:
544
545
546

548
549
550

553
--------------
exec uspMissinngNumbers 543,555

= 543,547,551,552
------------------

 

[bperry]

My apologies, I had a cut&paste error.
Here are my correct test results.

Here's a subset of my test data. As I mentioned, this column is char(10); the numbers are left-justified with trailing blanks:
544
545
546

548
549
550

553
--------------
exec uspMissinngNumbers [b[543,555[/b]

= 543,547,551,552,554,555

 

[stef315]

Duh. I did a select and order by CMTrxNum and I wasn't missing those numbers I thought I was. I deleted 3 (including 2 consecutive-10502, 10503, 10509) and it seemed to work. Sorry for that!

Table name is: CM20200
CMTrxNum is: char, 21
CMTrxType is: smallint, 2

Now, I got my missing numbers. I need to take those and update the table with those as the CMTrxNum and static values for the rest of the fields.

You said this is like a temp table, right? Will I have those values available still for my update statement? Or, do I just run that at the same time?

Sorry again for all this stupidity!

 

[bperry]

Yes, I said 'temp table' when trying to describe all that Union stuff. It's building a temporary table in memory of the range of numbers that you asked for. i.e. If you ask for 505-510, it builds a table containing numbers 505, 506, 507, 508, 509, and 510. This table exists only in memory, and only for the duration of the Select statement. Then it disappears.

(Actually, many people will keep a Numbers table of 0-99, or whatever, permanently in the database, because its quite useful in plenty of situations like this.)

Okay. From your comment, it seems that you want to automatically feed the output of this SP (the missing numbers that it finds) into some kind of Insert process that creates new rows back in the CM20200 table. Is that right? I'm not sure how to approach that (or if it is possible) ..... do you want to make some comments about that, describe what you are thinking, etc

bperry

 

[stef315]

Yes, that's what I'm trying to do. So, I find the check numbers that are missing. I am going to assume they were voided checks that were just not entered into the computer (happens all the time). Then I want to insert a record into the table for each missing number (void check). I will insert the missing number as the CMTrxNum and fill in the rest with appropriate values.

Is there a way to actually fill a new table with these values, update the CM20200 (good table) using the values in this new table and the values I pick for the rest of the record, then clear the table?

 

[bperry]

Hmmmm, I guess so. I'm a little reluctant to make a suggestion, as I unable to picture how all this fits together, and how you usually interact with the database.

i.e. How do you usually insert records for these missing voids into the database now? What other columns need to have values assigned (besides CMTrxNum and CMTrxType)?

Do you plan to execute this new stored procedure from Query Analyzer, or is there a client application like VB, ASP etc?

I'm rambling on here, just trying to picture in my mind what you expect to happen when you run this procedure.

bp

 

[stef315]

Usually, the user would enter a bank transaction from within the accounting software and then void that transaction. Just to get the check to show as a voided check. A couple of large clients are just tired of having to enter everything manually.

So, we would run this script from Query Analyzer. I can export the table to excel and email it to you if you'd like to see it. I have a sample (fake) company database that I play with. I will have to update other fields in the table besides just CMTrxNum and CMTrxType but they will pretty much have the same values. Like CMTrxType=3, Voided=1, etc.

 

[bperry]

Okay. Normally we don't like to have users running something like this from QA. However, if that's what you want to do, it's your ticket.

I propose modifying uspMissingNumbers so that we can pass another option, so that we can tell it to just report the missing numbers, or to optionally insert the new records into the table. Something like this:

uspMissingNumbers 551,560 (this would report)

uspMissingNumbers 51,560, Y (this would Insert)

How does that sound? If that's what you want, then you have to send the table layout, indicating the columns, their datatypes, and what you want the default values to be.