• Search Forums
    Advanced Search Topics Posts
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 Send Private Message
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 Send Private Message
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)