[Date Prev][Date Next][Thread Prev][Thread Next]
[Author Index] [Date Index] [Thread Index]
[SQR-USERS Info] [SQRUG Home Page]

RE: [sqr-users] SQR Report

I suggest you try to stay close to the original queries.  Your SQR program
has a main selection that identifies employees in the proper paygroup and in
the departments accessible to DPALL.  Then you select PS_TL_RPTD_ELPTIME for
each employee.  Then you select PS_TL_PAYABLE_TIME for each employee and
TRC.  That's going to be slow.

Is DPALL defined as "all departments?"  If so, take it out of your
selections entirely and save the join of PS_FAST_PERSGL_VW1.

Keep the PS_JOB join to PS_TL_RPTD_ELPTIME and to PS_TL_PAYABLE_TIME.  That
will reduce the number of SQL executes and probably speed you up more than
the join to PS_JOB will slow you down.  You don't care about individual
employees, only the total for each TRC.

The current row of PS_JOB should be as of PAY_END_DT, not as of SYSDATE.
You want the employees who were in that PAYGROUP during that pay period -
not the employees who are in the paygroup when you run the report.

Don't call the payable time selection from the elapsed time selection.  It's
unlikely, but what if there is time in a TRC in payable time but not in
elapsed time?  You'd want to catch that type of error.

I think you have the right approach to use arrays.  You could create a
load-lookup to map your complete list of TRCs to the index of your array.
If you're using Oracle, the return value is ROWNUM.  I don't know what you'd
do in other databases:

load-lookup name=trc_index
table='(select T.TRC, ROWNUM from PS_TL_TRC_TBL T where T.EFFDT = (select
max(EFFDT) from PS_TL_TRC_TBL where TRC = T.TRC) order by T.TRC)'
key=T.TRC
return_value=ROWNUM
rows=100

 
-----Original Message-----
From: radhika reddy [mailto:radhika_05@hotmail.com]
Sent: Thursday, March 24, 2005 5:08 PM
To: sqr-users@sqrug.org
Subject: [sqr-users] SQR Report


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

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