""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""
FINDING ROWS THAT FALL BETWEEN TWO DATES
""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""
PRODUCT : R:BASE VERSION : 3.1
CATEGORY : PROGRAMMING SUBCATEGORY : TOOLS
""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""
From HB Sloan, 1288 W. 11th St. #117, Tracy, CA 95376. You can reach
HB at 209-836-5713.
Applications frequently need to construct a WHERE clause based on two
dates supplied by the person using the application. For example, you
might want to print a report for rows that fall between two dates, or
browse through rows entered since 6/1/91 or before 1/1/91.
To fill this need, I developed a generic utility that I call
WHEDATE.CMD (listed below and included in 0891MRIM.ZIP) to construct
the WHERE clause. It uses a pop-up screen (WHEDATE.SNP) to collect the
beginning and ending dates. Use the program MAKESNAP.CMD (listed
below) to make WHEDATE.SNP.
When WHEDATE.CMD asks for the beginning and ending dates, you can
enter either date or both dates. If you enter only a beginning date,
the program assumes you want all the dates since that date
(inclusive). If you enter only an ending date, it assumes you want all
the dates up to and including that ending date.
What WHEDATE.CMD Does
"""""""""""""""""""""
WHEDATE.CMD takes a snapshot of the current screen (TMP.SNP), collects
the dates using WHEDATE.SNP as a pop-up screen, and later restores the
screen by displaying TMP.SNP.
WHEDATE.CMD uses the collected dates to construct the WHERE clause,
which it puts into the variable PV_BETW before returning control to
the calling program.
WHEDATE.CMD has a default value feature. Set up default values for
either or both of the two dates by setting PV_DATE1 or PV_DATE2 to a
specific date before running WHEDATE.CMD. WHEDATE.CMD displays */*/*
for any date you leave null. During execution, WHEDATE.CMD uses the
CVAL function to store original environment conditions, which it
restores at the end of the routine.
How to Make WHEDATE.SNP
"""""""""""""""""""""""
WHEDATE.CMD requires a snapshot file named WHEDATE.SNP. To make it,
use CONNECT to open any database. Then set the background color to the
background color of the application, and run this program:
*( MAKESNAP.CMD--Make WHEDATE.SNP,)
*( snapshot file required by WHEDATE.CMD.)
CLS
SET MESSAGES OFF
SET ERROR MESSAGES OFF
CREATE TABLE snapper (snapper TEXT 63)
SET VAR vrow1 = (SFIL(' ',22) + 'Enter Date Range' +
+ SFIL(' ',22) + CHAR(255))
LOAD snapper
.vrow1; ' '; ' '; ' '; ' '
END
CLS
WRITE ' Press [Enter] ' AT 20,30 white ON red
CHOOSE v1 FROM #VALUES FOR snapper FROM snapper AT 10,10
SNAP whedate.snp FROM 10,10 TO 17,76
REMOVE TABLE snapper
MAKESNAP.CMD makes the snapshot file by taking a snapshot of an actual
pop-up box where all the choices are blank lines. You only need to run
it once unless you change the application's background color.
When you run WHEDATE.CMD, you see what appears to be a true pop-up box
because WHEDATE.SNP is based on a real pop-up and WHEDATE.CMD restores
the original screen.
How to Run WHEDATE.CMD
""""""""""""""""""""""
Use the WHEDATE.CMD file included in this MRIM0891.ZIP file and run
MAKESNAP.CMD to create WHEDATE.SNP. Then run WHEDATE.CMD by passing
the following three parameters in the USING clause:
o Screen line number (1 to 19) to display WHEDATE.SNP.
o Screen column number (1 to 14) to display the top left corner of
WHEDATE.SNP.
o Name of the DATE column that you want referenced in the WHERE
clause.
For example, with the CONCOMP database, you might use the following
command to collect and display transaction records falling in a date
range:
RUN whedate.cmd USING 10,10,'transdate'
SELECT * FROM transmaster &pv_betw
Listing of WHEDATE.CMD
""""""""""""""""""""""
*( WHEDATE.CMD--Create and return a variable PV_BETW)
*( that contains a WHERE clause specifying a DATE range.)
*( It displays a snap file named WHEDATE.SNP to collect)
*( beginning and ending dates. Dates left null are shown as */*/*.)
*( Before running, you can set default values by setting PV_DATE1)
*( to a beginning date and PV_DATE2 to an ending date. When)
*( you run WHEDATE.CMD include the following three parameters)
*( in USING clause: ...USING scrnline, scrnpos, datecol)
*( SCRNLINE--screen line number (1 to 19) to display snap file.)
*( SCRNPOS--position (1 to 14) on the line to display snap file.)
*( DATECOL--name of the DATE column for the WHERE clause.)
*( Example: RUN whedate.cmd USING 10,10 'transdate')
*( Then use PV_BETW in a command like this:)
*( Example: SELECT * FROM transmaster &pv_betw)
*( WHEDATE.CMD requires the snap file WHEDATE.SNP and)
*( returns the WHERE clause in the variable PV_BETW. It stores)
*( the original environment and snaps the original screen. Then it)
*( restores the screen and environment at the end of the program.)
SNAP tmp.snp *( Snap the current screen.)
SET ERROR VAR sv_errv
*( Use CVAL to store current environment to restore at the end.)
SET VAR lv_oldmess = (CVAL('MESSAGE')), +
lv_olderr = (CVAL('ERROR')), oldmany = (CVAL('MANY'))
SET VAR vbar TEXT = (CHAR(219)), vbar = (SFIL(.vbar,24)), +
lv_dateT TEXT = '*/*/*', lv_org_row INTEGER = .%1, +
lv_org_col INTEGER = .%2, lv_column TEXT = .%3, +
pv_date1 DATE, pv_date2 DATE, +
pv_betw TEXT = ( 'WHERE' & .lv_column ), +
lv_r3 = (.lv_org_row + 3), lv_r4 = (.lv_org_row + 4), +
lv_c5 = (.lv_org_col + 5), lv_c30 = (.lv_org_col + 30), +
lv_c40 = (.lv_org_col + 40)
SET MESSAGES OFF; SET ERROR MESSAGES OFF
DEBUG SET MESSAGES ON; DEBUG SET ERROR MESSAGES ON
*( Change the MANY setting so you can clear percent variables.)
SET MANY=*
CLEAR VAR %1, %2, %3
SET MANY=%
CLS; DISPLAY whedate.snp AT .lv_org_row .lv_org_col
WRITE ' Enter Beginning Date: ' AT .lv_r3 .lv_c5 white ON gray
WRITE ' ' AT .lv_r3 .lv_c30 *( 10 spaces inside quotes.)
WRITE ' Enter Ending Date: ' AT .lv_r4 .lv_c5 white ON gray
WRITE ' ' AT .lv_r4 .lv_c30 *( 10 spaces inside quotes.)
LABEL date1
WRITE .vbar AT .lv_r3 .lv_c40 gray
FILLIN pv_date1=10 AT .lv_r3 .lv_c30 EDIT
IF sv_errv <> 0 THEN
WRITE ' <--Invalid DATE.' AT .lv_r3 .lv_c40 gray ON black BLINK
PAUSE 2
GOTO date1
ENDIF
SHOW VAR pv_date1=10 AT .lv_r3 .lv_c30
SET VAR lv_last = (LASTKEY(0))
IF lv_last = '[Esc]' OR lv_last = '[Up]' OR lv_last = '[PgUp]' THEN
SET VAR pv_betw = ' '
GOTO goback
ENDIF
IF pv_date1 IS NULL THEN
WRITE .lv_datet AT .lv_r3 .lv_c30
ENDIF
LABEL date2
WRITE .vbar AT .lv_r4 .lv_c40 gray
FILLIN pv_date2=10 AT .lv_r4 .lv_c30 edit
IF sv_errv <> 0 THEN
WRITE ' <--Invalid DATE.' AT .lv_r4 .lv_c40 gray ON black BLINK
PAUSE 2; GOTO date2
ENDIF
SHOW VAR pv_date2=10 AT .lv_r4 .lv_c30
SET VAR lv_last = (LASTKEY(0))
IF lv_last = '[Esc]' OR lv_last = '[Up]' OR lv_last = '[PgUp]' THEN
GOTO date1
ENDIF
IF pv_date2 IS NULL THEN; WRITE .lv_datet AT .lv_r4 .lv_c30
ENDIF
*( Check for both dates null.)
LABEL both_null
IF pv_date1 IS NULL AND pv_date2 IS NULL THEN
SET VAR pv_betw = (.pv_betw & 'IS NOT NULL' ), +
pv_date1 TEXT = .lv_datet, pv_date2 TEXT = .lv_datet
GOTO goback
ENDIF
*( Check for only an ending date.)
IF pv_date1 IS NULL AND pv_date2 IS NOT NULL THEN
SET VAR pv_betw = (.pv_betw & '<=' & CTXT(.pv_date2)), +
pv_date1 TEXT = .lv_datet
GOTO goback
ENDIF
*( Check for only a beginning date.)
IF pv_date1 IS NOT NULL AND pv_date2 IS NULL THEN
SET VAR pv_betw = (.pv_betw & '>=' & CTXT(.pv_date1) ), +
pv_date2 TEXT = .lv_datet
GOTO goback
ENDIF
*( Both dates are present. Check them.)
LABEL no_null
IF pv_date1 = pv_date2 THEN
SET VAR pv_betw = (.pv_betw & '=' & CTXT(.pv_date1))
GOTO goback
ENDIF
*( Check for beginning date bigger than ending date.)
IF pv_date1 > .pv_date2 THEN
PAUSE 1 USING +
'Beginning date is after ending date, so all dates included.'
SET VAR pv_date1 = NULL, pv_date2 = NULL
GOTO both_null
ENDIF
SET VAR pv_betw = (.pv_betw & 'BETW' & CTXT(.pv_date1) +
& 'AND' & CTXT(.pv_date2))
LABEL goback
CLS; DISPLAY tmp.snp *( Redisplay the current screen.)
DEL tmp.snp
SET ERROR MESSAGES .lv_olderr; SET MESSAGES .lv_oldmess
CLEAR VAR lv%, pv_date%, vbar, sv_errv
SET MANY=.oldmany; CLEAR VAR oldmany
RETURN
WHEDATE.CMD's Four Possibilities
""""""""""""""""""""""""""""""""
WHEDATE.CMD handles all four possible combinations. For example, if
the column name passed in the USING clause is TDATE, here are the four
possible WHERE clauses:
o Null beginning & null ending date: WHERE tdate IS NOT NULL.
o 8/1/91 ending date & null beginning date: WHERE tdate <= 8/1/91.
o 7/1/91 beginning date & null ending date: WHERE tdate >= 7/1/91.
o 7/1/91 beginning date & 8/1/91 ending date: WHERE tdate BETWEEN
7/1/91 AND 8/1/91.