We have an SQL server and keep records of paper numbers used. Our system pulls from a table of available numbers.

The fields are: LOW, HIGH, NEXT, CLOSED.

LOW = the low number in the paper batch.
HIGH = the high number in the paper batch.
NEXT = the next number. Auto-increments when we print.
CLOSED = Batch is closed when NEXT equals HIGH. No more paper avail.

We want to be able to check if there are any duplicates when adding a new row. If we have a LOW of 10 and HIGH of 50 with an ENDING of 30, and we want to add a new row, what SQL statement can be written to check if there are no numbers BETWEEN 10 and 50 for LOW or HIGH? There are no physical rows to show these numbers.

I know how to check if 10 or 50 exists, but i need to know how to get every number in between and check them.

Thanks,
Eric

Sample data and desired results, please.

Data:

LOCATION BEG# END# NEXT# OPEN?
CENTRAL 23515518 23515525 23515525 N
CENTRAL 23515620 23515635 23515635 N
CENTRAL 23515670 23515676 23515676 N
CENTRAL 23515714 23515716 23515716 N
CENTRAL 23515849 23515853 23515853 N
CENTRAL 23516034 23516046 23516046 N
CENTRAL 23516289 23516294 23516294 N
CENTRAL 23516555 23516562 23516562 N
CENTRAL 23516646 23516650 23516651 N

Output from query: I just want to know if a row exists. So, something like

DIM DUPLICATE
DUPLICATE = (Select ROWID from TABLE where BEG# "is already used")

Then I would say if DUPLICATE <> 0 then msgbox "You have entered number which has already been used"

The part where I am stuck is the "is already used" in the select statement.

SELECT Beg FROM Table As 't' WHERE Beg = (SELECT Beg FROM Table WHERE Beg = t.Beg)

HHmm, that doesn't look right... Someone correct me please

You're mixing terminology. So my first question is whether LOW = Begin and HIGH = End? Also, you are using "BEG#" in multiple contexts.

I believe you just want to do regular interval overlap tests, right? The following query assumes that "start" is the begin of the interval that should not overlap with any other interval in the table, and "stop" is the end of that interval. If you are only interested in a single value, you have a point interval, i.e. start = stop. The assumption is that we have always start <= stop and begin <= end.
If the query returns a value largen than 0, you have an overlap. Alternatively, you can use "SELECT *" or whatever.

Note: there is no ROWID in your table and standardized SQL doesn't define such a constructor. That's why you can't use this in general - only on your specific DBMS.

For point intervals, you could actually write this as well: