SQR arrays

Requirement : produce an employee listing by department and
print the following departmental statistical information: the number of active employees,
the number of women, the number of men, and an average salary for the department.

!TEST12A.SQR
!USING ARRAYS IN SQR
#DEFINE STAT_ARRAY_SIZE 100
!***********************
BEGIN-SETUP
!***********************
CREATE-ARRAY NAME=STATISTICS SIZE ={STAT_ARRAY_SIZE}
FIELD=DEPTID:CHAR='999999'
FIELD=ACTIVE_EE:NUMBER
FIELD=WOMEN:NUMBER
FIELD=MEN:NUMBER
FIELD=TOTAL_SALARY:NUMBER
END-SETUP
!********************
BEGIN-HEADING 3
!********************
PRINT 'EMPLOYEE STATISTICS BY THE DEPARTMENT' (1,10)
PRINT 'DEPARTMENT ID' (2,1,)
PRINT 'EMPLOYEE ID' (,+2)
PRINT 'STATUS' (,+2)
PRINT 'SEX' (,+2)
PRINT 'ANNUAL RATE' (,+2)
PRINT ' ' (3,1)
END-HEADING
!********************
BEGIN-PROGRAM
!********************
LET #I=0
DO SELECT_EMPLOYEES
DO PRINT_SUMMARY
END-PROGRAM
!***********************************
BEGIN-PROCEDURE SELECT_EMPLOYEES
!***********************************
BEGIN-SELECT
A.DEPTID (+1,1,13) ON-BREAK
A.EMPLID (,+2,11)
A.EMPL_STATUS (,+2,6)
B.SEX (,+2,3)
A.ANNUAL_RT (,+2,12) EDIT $,$$$,$$$.00
     DO UPDATE_ARRAY
FROM PS_JOB A, PS_PERSONAL_DATA B
WHERE
A.EMPLID=B.EMPLID
AND A.EMPL_STATUS='A'
!AND A.DEPTID IN ('10200','21700')
AND A.EFFDT=(SELECT MAX(EFFDT) FROM PS_JOB
    WHERE EMPLID=A.EMPLID
    AND EMPL_RCD=A.EMPL_RCD
    AND EFFDT<=SUBSTRING(CONVERT(CHAR,GETDATE(),121), 1, 10))
AND A.EFFSEQ=(SELECT MAX(EFFSEQ) FROM PS_JOB
    WHERE EMPLID=A.EMPLID
    AND EMPL_RCD=A.EMPL_RCD
    AND EFFDT = A.EFFDT)
ORDER BY A.DEPTID,A.EMPLID
END-SELECT
END-PROCEDURE
!*******************************
BEGIN-PROCEDURE PRINT_SUMMARY
!*******************************
PRINT 'SUMMARY BY DEPARTMENT' (+2,10)
LET #I=0
WHILE #I <= {STAT_ARRAY_SIZE}
GET $DEPTID #ACTIVE #WOMEN #MEN #TOTAL_SALARY
FROM STATISTICS(#I)
IF $DEPTID='999999'
BREAK
ELSE
IF #ACTIVE <> 0
LET #AVERAGE_SALARY = #TOTAL_SALARY / #ACTIVE
END-IF
PRINT 'DEPARTMENT ' (+1,1)
PRINT $DEPTID (,+1)
PRINT 'NUMBER OF ACTIVE EMPLOYEES = ' (+1,1,33)
PRINT #ACTIVE (,+1) EDIT 9,999,999
PRINT 'NUMBER OF WOMEN = ' (+1,1,33)
PRINT #WOMEN (,+1) EDIT 9,999,999
PRINT 'NUMBER OF MEN = ' (+1,1,33)
PRINT #MEN (,+1) EDIT 9,999,999
PRINT 'AVERAGE SALARY = ' (+1,1,33)
PRINT #AVERAGE_SALARY (,+1) EDIT $$$,$$$,$$$.99
NEXT-LISTING NEED=4 SKIPLINES=1
END-IF
ADD 1 TO #I
END-WHILE
END-PROCEDURE
!*****************************

!*******************************
Begin-Procedure UPDATE_ARRAY
!*******************************
!Find an element in the array with Deptid = Selected Deptid
!If it's not there, create a new element in the array
Let $Found='N'
Let #j=0
While #j < {Stat_Array_Size}
Get $Deptid From Statistics(#j) Deptid
If $Deptid='999999'
Let #i=#j
Put &A.Deptid Into Statistics(#i) Deptid
Let $Found='Y'
Break
Else
If &A.Deptid = $Deptid
Let #i=#j
Let $Found = 'Y'
Break
End-If
End-If
Let #j=#j+1
End-While
If $Found='N'
Display 'ERROR: There are more than {Stat_Array_Size} departments in the array'
Stop
End-If
Array-Add 1 To Statistics(#i) Active_EE
Array-Add &A.Annual_Rt To Statistics(#i) Total_Salary
If &B.Sex='M'
Array-Add 1 to Statistics(#i) Men
Else
Array-Add 1 to Statistics(#i) Women
End-If
End-Procedure
!****************************************************
@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
                                                      OUTPUT
@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
 EMPLOYEE STATISTICS BY THE DEPARTMENT
DEPARTMENT ID  EMPLOYEE ID  STATUS  SEX  ANNUAL RATE


               SFTRN067     A       U      $42,000.00
               SFTRN077     A       U      $42,000.00
               SFTRN087     A       U      $42,000.00
               SFTRN097     A       U      $42,000.00
               SFTRN107     A       U      $42,000.00
               SFTRN117     A       U      $42,000.00
               SFTRN127     A       U      $42,000.00
               SFTRN137     A       U      $42,000.00
               SFTRN147     A       U      $42,000.00
               SFTRN157     A       U      $42,000.00
T006           KUI059       A       M      $30,368.00

         SUMMARY BY DEPARTMENT
DEPARTMENT  10000
NUMBER OF ACTIVE EMPLOYEES =            304
NUMBER OF WOMEN =                       117
NUMBER OF MEN =                         187
AVERAGE SALARY =                   $1,757,008.79