Improve SQR

[Bill Bowers <bowersb@PSPH.PROVIDENCE.ORG>]

     I am attempting to run the following, there are over 200,000 rows in
     the ps_pay_earnings table.  Does anybody out there know how this could
     be improved?

     begin-procedure Get-Reg-Earns

     begin-select

     a.deptid
     rpad(p.union_cd,3,' ')       &punioncd
     to_char(a.pay_end_dt,'YYYY') &RYear
     to_char(a.pay_end_dt,'MM')   &RMonth
     sum(a.reg_hrs)               &RegHrs
     sum(a.reg_earn_hrs)          &RegErnHrs
     sum(a.reg_hrly_earns)        &RegHrlyErns
     sum(a.reg_earns)             &RegErns

       let  #TotRegHrs  = &RegHrs      + &RegErnHrs
       move #TotRegHrs to $TotRegHrs 99999.99

       let  #TotRegErns = &RegHrlyErns + &RegErns
       move #TotRegErns to $TotRegErns 9999999.99


     !  move &OTHrs      to $OTHrs         99999.99
     !  move &OTHrlyErns to $OTHrlyErns  9999999.99

       let $Rcd = &A.DeptId             || $FldDelimiter
       let $Rcd = $Rcd || &RYear        || $FldDelimiter
       let $Rcd = $Rcd || &RMonth       || $FldDelimiter
       let $Rcd = $Rcd || 'REG'         || $FldDelimiter
       let $Rcd = $Rcd || &pUnionCd     || $FldDelimiter
       let $Rcd = $Rcd || $TotRegHrs    || $FldDelimiter
       let $Rcd = $Rcd || $TotRegErns

       !show $Rcd
       write 1 from $Rcd:41
     FROM PS_PAY_EARNINGS A, PS_PERSONNEL p
     where   a.pay_end_dt between $FromDate and $ThruDate
     and     a.emplid = p.emplid
     and     a.empl_rcd# = p.empl_rcd#
     and  P.EFFDT =
             (SELECT MAX(EFFDT) FROM PS_PERSONNEL
             WHERE P.EMPLID = EMPLID
               AND P.EMPL_RCD# = EMPL_RCD#
               AND EFFDT <= a.Pay_End_dt)
         AND P.EFFSEQ =
             (SELECT Max(EFFSEQ) FROM PS_PERSONNEL
             WHERE P.EMPLID = EMPLID
               AND P.EMPL_RCD# = EMPL_RCD#
               AND P.EFFDT = EFFDT)
     group by a.deptid,
              p.union_cd,
              to_char(a.pay_end_dt,'YYYY'),
              to_char(a.pay_end_dt,'MM')
     end-select

     end-procedure Get-Reg-Earns


     Thanks in advance

     Bill Bowers
     St. Peter Hospital
     (360) 493-5437