Prev
Next
Forums Human Capital Management Lawson S3 HR/Payroll/Benefits

Crystal - find Emp Status when PRTIME check cut

 1 Replies
 0 Subscribed to this topic
 68 Subscribed to this forum
Sort:
Author
Messages
AngieP
Basic Member
Posts: 11
Basic Member
8/29/2012 4:07 PM
    I am trying to write a Crystal Report from PRTIME for any employee who was part-time when a check was cut.  Basically, I have a group of pay summary groups that I need to total the hours, by check date, and if the employee was at part-time status when the check was cut, give me the record.  I need a way to compare the check date to the employee status/stauts eff date (in HRHISTORY) and if the employee was part-time, give me the total hours for that check on the report. I know the tables I need to look at, but I have no clue where to start and am hoping someone can send me in the right direction.  I'm guessing I'll need to write a sql command, but am clueless.  Any help is appreciated!!!! 
    Dave Curtis
    Veteran Member
    Posts: 136
    Veteran Member
    8/29/2012 8:21 PM
      I put this together real quick and it is not a final product my any means, but it is a basic option you can start with.

      This is a SQL statement that gives you what you are looking for. It is very basic in what it returns but you can add additional data as needed - this was just to show the basic idea.

      There are other ways to put it together, this is just a thought to get you started.

      WITH status_hst as
      (SELECT company
      ,employee
      ,fld_nbr
      ,beg_date as effect_date
      ,LEAD(beg_date,1) OVER (PARTITION BY employee
      ORDER BY employee
      ,beg_date
      ,date_stamp
      ,seq_nbr ) as end_date
      ,date_stamp
      ,seq_nbr
      ,TRIM(a_value) as status
      ,TRIM(LAG(a_value,1) OVER (PARTITION BY employee
      ORDER BY employee
      ,beg_date
      ,date_stamp
      ,seq_nbr)) as previous_status
      FROM hrhistory
      WHERE fld_nbr = 20
      ORDER BY employee
      ,beg_date desc
      ,date_stamp desc
      ,seq_nbr desc),

      pay as
      (SELECT *
      FROM prtime)

      SELECT p.employee
      ,p.tr_date
      ,h.status
      FROM pay p
      ,status_hst h
      WHERE p.employee = h.employee
      AND h.effect_date <= p.tr_date
      AND (h.end_date >=p.tr_date or h.end_date is null)