--CaseStat.rmd
--2/2002 Dave Ebert
--Reports on case levels for a program and date range



--set variables for calculations
SET VAR vtcase1st INTEGER
SET VAR vtcase1sttxt TEXT

SET VAR vtnewcase INTEGER
set var vtnewcasetxt TEXT

SET VAR vtcaseclose INTEGER
set var vtcaseclosetxt TEXT

SET VAR vtcaseend INTEGER
set var vtcaseendtxt text

SET VAR vtnumyouth INTEGER
set var vtnumyouthtxt text

set var vtcase1sttxt = (CTXT(.vtcase1st))
set var vtnewcasetxt = (CTXT(.vtnewcase))
set var vtcaseclosetxt = (CTXT(.vtcaseclose))
set var vtcaseendtxt = (CTXT(.vtcaseend))
set var vtnumyouthtxt = (CTXT(.vtnumyouth))

SET VAR gvprog INTEGER
SET VAR gvdate1 DATE
SET VAR gvdate2 DATE
SET VAR vtind INTEGER
SET VAR vtxt1st TEXT
SET VAR vtxtnew TEXT
SET VAR vtxtclose TEXT
SET VAR vtxtend TEXT
SET VAR vtxtnum TEXT

--Convert calculation variables to text for display
SET VAR vtxt1st = 'cases open 1st of the month = ' + .vtcase1sttxt
SET VAR vtxtnew = 'number of new cases opened = ' + .vtnewcasetxt
SET VAR vtxtclose = 'number of cases closed = ' + .vtcaseclosetxt
SET VAR vtxtend = 'cases open end of the month = ' + .vtcaseendtxt
SET VAR vtxtnum = 'number of youth receiving service = ' + .vtnumyouthtxt


--select program
RUN choprog.rmd

--select date range
RUN chodate.rmd

--populate the variables
--Cases open on the first of the month
--create a view to hold the value and pass to a variable
DROP VIEW vutemp1

CREATE TEMPORARY VIEW vutemp1 (case1st) +
AS SELECT (COUNT(ypid)) +
FROM yp +
WHERE ypid IN +
(SELECT ypid FROM ev WHERE program = .gvprog AND +
(dinact IS NULL OR dinact > .gvdate1))

SELECT case1st INTO vtcase1st INDICATOR vtind1 FROM vutemp1

--number of new cases opened for the specified time period
DROP VIEW vutemp2

CREATE TEMPORARY VIEW vutemp2 (casenum) +
AS SELECT (COUNT(ypid)) +
FROM yp +
WHERE ypid IN +
(SELECT ypid FROM ev WHERE program = .gvprog AND +
(dact BETWEEN .gvdate1 AND .gvdate2))

SELECT casenum INTO vtnewcase INDICATOR vtind1 FROM vutemp2

--number of cases closed for the specified time period
DROP VIEW vutemp3

CREATE TEMPORARY VIEW vutemp3 (casenum) +
AS SELECT (COUNT(ypid)) +
FROM yp +
WHERE ypid IN +
(SELECT ypid FROM ev WHERE program = .gvprog AND +
(dinact BETWEEN .gvdate1 AND .gvdate2))

SELECT casenum INTO vtcaseclose INDICATOR vtind1 FROM vutemp3

--cases open end of the month
DROP VIEW vutemp4

CREATE TEMPORARY VIEW vutemp4 (caseend) +
AS SELECT (COUNT(ypid)) +
FROM yp +
WHERE ypid IN +
(SELECT ypid FROM ev WHERE program = .gvprog AND +
(dinact IS NULL OR dinact > .gvdate2))

SELECT caseend INTO vtcaseend INDICATOR vtind1 FROM vutemp4

--number of youth receiving service
DROP VIEW vutemp5

CREATE TEMPORARY VIEW vutemp5 (caseserv) +
AS SELECT (COUNT(ypid)) +
FROM yp +
WHERE ypid IN +
(SELECT ypid FROM service WHERE costcent = .gvprog AND +
(dserv BETWEEN .gvdate1 AND .gvdate2))

SELECT caseserv INTO vtnumyouth INDICATOR vtind1 FROM vutemp5
--create a text file of results
OUTPUT monstat.txt
SET LINES 0
SET WIDTH 79
WRITE 'Monthly program case stats'
WRITE ' '
WRITE .gvdate1  ' to '  .gvdate2
WRITE '============================================= '
WRITE ' '
WRITE .vtxt1st
WRITE ' '
WRITE .vtxtnew
WRITE ' '
WRITE .vtxtclose
WRITE ' '
WRITE .vtxtend
WRITE ' '
WRITE .vtxtnum
WRITE ' '

SET LINES 20
SET WIDTH 78
OUTPUT SCREEN

LAUNCH monstat.TXT
--DELETE monstat.txt


CLEAR VAR vt*

RETURN


