[sqr-users] SQR Report

["radhika reddy" <radhika_05@hotmail.com>]

Hi,

  i am trying to figure out the best way to write this report. It is from 
the following two queries for which i have to write the SQR report:

select b.paygroup,a.trc,sum(a.tl_quantity)
from ps_tl_rptd_elptime a,ps_job b,ps_fast_persgl_vw1 b1
where b.emplid = b1.emplid
and b1.rowsecclass = 'DPALL'
and a.emplid = b.emplid
and a.empl_rcd = b.empl_rcd
and b.effdt = (select max(b_ed.effdt)
               from ps_job b_ed
                           where b.emplid = b_ed.emplid
                           and b.empl_rcd= b_ed.empl_rcd)
and b.effseq = (select max(b_es.effseq)
                from ps_job b_es
                                where b.emplid = b_es.emplid
                                and b.empl_rcd = b_es.empl_rcd
                                and b.effdt = b_es.effdt)
and b.paygroup = :1                                      !User is prompted 
for paygroup
and a.dur between :2 and :3                    !user is prompter for 
payperiod begin and end dates
group by b.paygroup,a.trc


select b.paygroup,a.trc,sum(a.tl_quantity)
from ps_tl_payable_time  a,ps_job b,ps_fast_persgl_vw1 b1
where b.emplid = b1.emplid
and b1.rowsecclass = 'DPALL'
and a.emplid = b.emplid
and a.empl_rcd = b.empl_rcd
and b.effdt = (select max(b_ed.effdt)
               from ps_job b_ed
                           where b.emplid = b_ed.emplid
                           and b.empl_rcd= b_ed.empl_rcd)
and b.effseq = (select max(b_es.effseq)
                from ps_job b_es
                                where b.emplid = b_es.emplid
                                and b.empl_rcd = b_es.empl_rcd
                                and b.effdt = b_es.effdt)
and b.paygroup = :1                                      !User is prompted 
for paygroup
and a.dur between :2 and :3                    !user is prompter for 
payperiod begin and end dates
group by b.paygroup,a.trc

Output looks like this:-

Paygroup   TRC     Elapsedtime    Payable time       Difference
BWH         HOL        78                  75                      3
BWH         PER        100               100
:
:
:
                            -----              ---------                
-----------
                           178               175                       3


I tried doing it using arrays in the following way

************************************************************************
Begin-Procedure Process-Main
!************************************************************************
#DebugH Show 'Enter Procedure Main'
Begin-Select
Job.emplid
Job.empl_rcd

   Let $Emplid = &Job.emplid
   Let $Emplrcd = &Job.empl_rcd
#Debugh show 'emplid' $emplid
   Do Calculate-Elapsed-Time


from PS_Job Job,PS_FAST_PERSGL_VW1 Fastvw
where Job.emplid = Fastvw.emplid
and Fastvw.Rowsecclass = 'DPALL'
and Job.effdt = (Select Max(b1.effdt)
               from ps_job b1
               where b1.emplid = Job.emplid
               and b1.empl_rcd = Job.empl_rcd
               and b1.effdt <= Sysdate)
and Job.effseq = (Select Max(b2.effseq)
                from ps_job b2
                where b2.emplid = Job.emplid
                and b2.empl_rcd = Job.empl_rcd
                and b2.effdt = Job.effdt)
and Job.paygroup = $Paygroup
order by Job.emplid
End-Select

End-Procedure

!*****************************************
Begin-Procedure Calculate-Elapsed-Time
!*****************************************
#DebugH Show 'Enter Procedure Calculate Elapsed Time'
Begin-Select
Elptime.emplid
Elptime.TRC &TRC
Sum(Elptime.Tl_Quantity) &Tl_qty1

#Debugh show 'emplid' $Emplid 'sumoftime' &Tl_qty1 'TRC' &TRC

   If #i = 0 and Time_qty.TRC(#i) = ''
      Let Time_qty.TRC(#i) = &TRC
   end-If
   If Time_qty.TRC(#i)!=&TRC         !here i am not able to compare with TRC 
codes available in the array except for previous one, which is wrong. can we 
do it using 'FOR'  loop
      Let #i = #i + 1
      If #i>= {Max_TRC}
          display 'Error:There are more than {Max_TRC} Time Reporting Codes'
          stop
      end-If
      Let Time_qty.TRC(#i) = &TRC
   End-If
   Let #j = 0
   Let Time_qty.TM_qty(#i,#j) = Time_qty.TM_qty(#i,#j) + &Tl_qty1
   Do Calculate-Payable-Time        !here the j = 1 which i have to change

from PS_TL_RPTD_ELPTIME Elptime
where Elptime.emplid = $Emplid
and Elptime.empl_rcd = $Emplrcd
and Elptime.dur between $Pay_Begin_dt and $Pay_End_dt
group by Elptime.emplid,Elptime.TRC
order by Elptime.TRC
End-Select

End-Procedure

!*****************************************
Begin-Procedure Calculate-Payable-Time
!*****************************************
#DebugH Show 'Enter Procedure Calculate Payable Time'
Begin-Select
Paytime.emplid
Paytime.TRC
Sum(Paytime.Tl_Quantity) &Tl_qty2

#Debugh show 'emplid' $Emplid 'sumoftime' &Tl_qty2

from PS_TL_PAYABLE_TIME Paytime
where Paytime.emplid = $Emplid
and Paytime.empl_rcd = $Emplrcd
and Paytime.TRC = &TRC
and Paytime.dur between $Pay_Begin_dt and $Pay_End_dt
group by Paytime.emplid,Paytime.TRC
order by Paytime.TRC
End-Select

End-Procedure

Is there is any other idea which i can implement? please let me know.

Thanks.

_________________________________________________________________
Express yourself instantly with MSN Messenger! Download today - it's FREE! 
http://messenger.msn.click-url.com/go/onm00200471ave/direct/01/


_______________________________________________
sqr-users mailing list
sqr-users@sqrug.org
http://www.sqrug.org/mailman/listinfo/sqr-users