Here's a weekend project to play with - My son was home last weekend and
brought Joe Celko's book "SQL Puzzles & Answers" (Morgan Kaufman
Publishers, Inc.(c) 1997) with him. I was paging through it and one of
the problems posed was that of restaurant seating. Given 1000 seats
(big restaurant) write an SQL query that would list the available
seats. The catch was that it had to run on a PDA - space is at a premium.
One of several solutions was a single table (no pun intended),
Restaurant, with a column Seats (INTEGER) whose rows were only the
occupied seat numbers. Two views and a SELECT gave the answer. The view
Firstseat lists the first open seat in each gap of available seats,
Lastseat list the last seat in each gap.
CREATE VIEW Firstseat (Seat) AS SELECT (Seat + 1) FROM Restaurant +
WHERE (Seat + 1) NOT IN (SELECT Seat FROM Restaurant) AND (Seat + 1) <
1001
CREATE VIEW Lastseat (Seat) AS SELECT (Seat - 1) FROM Restaurant +
WHERE (Seat - 1) NOT IN (SELECT Seat FROM Restaurant) AND (Seat - 1) > 0
Use the two views to show blocks of empty seats:
SELECT F1.Seat AS Start, L1.Seat AS Finish, +
((L1.Seat - F1.Seat) + 1) AS Available +
FROM Firstseat F1, Lastseat L1 +
L1.Seat = (SELECT MIN(L2.Seat) FROM Lastseat L2 WHERE F1.Seat <= L2.Seat)
So I thought, OK, seats, check numbers, invoice numbers - they're all
the same: sequentially numbered items where we might want to detect
missing numbers in the series (esp check numbers). I've taken Celko's
solution and adapted it for R:Base (Win 7.6). When you run the code
below, it will ask you for a table and then a column and will post the
starting number, last number and number of missing numbers to a temp table.
This code is for the list - enjoy.
Enhancements might be: CHOOSE from only the integer cols in a table.
Doug
--MissingNbrs.RMD
--Finds missing numbers of sequential integers (invoices, checks, etc)
--It lists the first and last of a group of missing numbers and the count
-- of missing numbers between the first and last.
--User CHOOSES a table and then a column.
CLEAR VAR vColName,vMaxValue,vMinValue,vTblName
SET ERROR MESSAGE 2038 OFF
DROP TABLE NmbrBlocks
SET ERROR MESSAGE 2038 ON
SET VAR +
vCmd TEXT = NULL, +
vColName TEXT, +
vMaxValue INTEGER, +
vMinValue INTEGER, +
vTblName TEXT
CHOOSE vTblName FROM #TABLES +
LINES 13
IF vTblName = '[esc]' OR vTblName IS NULL THEN
GOTO Exit
ENDIF
CHOOSE vColName FROM #COLUMNS IN &vTblName +
LINES 13
IF vColName = '[esc]' OR vColName IS NULL THEN
GOTO Exit
ENDIF
SELECT MIN(&vColName), MAX(&vColName) INTO +
vMinValue, vMaxValue +
FROM &vTblName
--Create view of the first missing number in a gap of missing numbers
SET ERROR MESSAGE 677 OFF
DROP VIEW FirstNumber
SET ERROR MESSAGE 677 ON
SET VAR vCmd = ('CREATE TEMPORARY VIEW FirstNumber (' & .vColName & ')' )
SET VAR vCmd = (.vCmd & ' AS SELECT (' + .vColName + ' + 1) FROM' &
.vTblName)
SET VAR vCmd = (.vCmd & 'WHERE (' + .vColName + ' + 1) NOT IN')
SET VAR vCmd = (.vCmd & '(SELECT ' + .vColName + ' FROM' & .vTblName + ')')
SET VAR vCmd = (.vCmd & 'AND (' + .vColName + '+ 1) < ' & '.vMaxValue' )
&vCmd
SET VAR vCmd = NULL
--Create view of the last number in a gap of missing numbers
SET ERROR MESSAGE 677 OFF
DROP VIEW LastNumber
SET ERROR MESSAGE 677 ON
SET VAR vCmd = ('CREATE TEMPORARY VIEW LastNumber (' & .vColName & ')' )
SET VAR vCmd = (.vCmd & ' AS SELECT (' + .vColName + ' - 1) FROM' &
.vTblName)
SET VAR vCmd = (.vCmd & 'WHERE (' + .vColName + ' - 1) NOT IN')
SET VAR vCmd = (.vCmd & '(SELECT ' + .vColName + ' FROM' & .vTblName + ')')
SET VAR vCmd = (.vCmd & 'AND (' + .vColName + '- 1) > 0 ')
&vCmd
SET VAR vCmd = NULL
--Display 1st & last #s in a gap & number of missing numbers
-- Create temp table to hold results of SELECT for review & sorting
CREATE TEMP TABLE NmbrBlocks +
(StartNmbr INTEGER, EndNmbr INTEGER, NmbrMissing INTEGER)
--Build SELECT command and insert results into table
SET VAR vCmd = ('INSERT INTO NmbrBlocks ')
SET VAR vCmd = +
(.vCmd & 'Select F1.' + .vColName + ' AS Start, L1.' + .vColName + '
AS End,')
SET VAR vCmd = +
(.vCmd & '((L1.' + .vColName + ' - F1.' + .vColName & + ') + 1) AS Free')
SET VAR vCmd = (.vCmd & 'FROM FirstNumber F1, LastNumber L1')
SET VAR vCmd = (.vCmd & 'WHERE L1.' +.vColName & '= (SELECT MIN(L2.' +
.vColName)
SET VAR vCmd = (.vCmd & ') FROM LastNumber L2')
SET VAR vCmd = (.vCmd & 'WHERE F1.' + .vColName & '<= L2.' + .vColName +
')')
&vCmd
BROWSE ALL FROM NmbrBlocks
LABEL Exit
CLEAR VAR vCmd,vColName,vMaxValue,vMinValue,vTblName
RETURN
--- RBASE-L
================================================
TO POST A MESSAGE TO ALL MEMBERS:
Send a plain text email to [email protected]
(Don't use any of these words as your Subject:
INTRO, SUBSCRIBE, UNSUBSCRIBE, SEARCH,
REMOVE, SUSPEND, RESUME, DIGEST, RESEND, HELP)
================================================
TO SEE MESSAGE POSTING GUIDELINES:
Send a plain text email to [email protected]
In the message SUBJECT, put just one word: INTRO
================================================
TO UNSUBSCRIBE:
Send a plain text email to [email protected]
In the message SUBJECT, put just one word: UNSUBSCRIBE
================================================
TO SEARCH ARCHIVES:
Send a plain text email to [email protected]
In the message SUBJECT, put just one word: SEARCH-n
(where n is the number of days). In the message body,
place any
text to search for.
================================================
