Prev
Next
Forums Lawson Business Intelligence/Reporting/Crystal

Employee Status - to and from

Sort:
You are not authorized to post a reply.
Author
Messages
CHSReportWriter
Basic Member
Posts: 6
Basic Member
2/29/2008 5:23 PM

    I understand that the employee status history is stored in the HRHISTORY table- it is field 20.  Now the part that I'm having trouble with is getting the status to display that is the one they were BEFORE the one I am running the report for.  Here is my SQL so far-

     SELECT "HRHISTORY"."EMPLOYEE", "HRHISTORY"."FLD_NBR", "HRHISTORY"."BEG_DATE", "HRHISTORY"."A_VALUE"
     FROM   "PRODLAW"."HRHISTORY" "HRHISTORY"
     WHERE  "HRHISTORY"."FLD_NBR"=20 AND ("HRHISTORY"."BEG_DATE">={ts '2007-01-01 00:00:00'} AND "HRHISTORY"."BEG_DATE"<{ts '2007-12-31 00:00:01'})

    I am trying to use a subreport to get the correct status to display for the PREVIOUS status.  But maybe a formula would work better within the report.  Anyone have any ideas for me on how to do this?  I was thinking that if I just said, give me the max begin date associated with the employee status that is not equal to the employee status in the Employee Table- but that would not be right for everyone.  (like if they changed status's more than once in the desired date range)

    heather
    New Member
    Posts: 1
    New Member
    3/2/2008 10:00 PM
      I think the best method is to write your SQL in a command. The strategy is to gather the employee's with status changes into a temp table, then gather the maximum date stamp (or effective date) for the hrhistory record (field 20) where the hrhistory record date is less than the employee status change date. Keep in mind that you may have employee's change status multiple times. Therefore, you may need to first gather the employee' with a status change useing the maximum date function (within 2007, or other date range). In our environment, we find that we also have to use the objectID and sequence since there are times that corrections are entered (or effective) on the same date.
      Phil Simon
      Veteran Member
      Posts: 135
      Veteran Member
      3/5/2008 1:30 PM

        If you are storing this to history (via HR10) as most clients do, then you might want to do a max grouping by employee number in Crystal.

        You can then build a formula, as I do, whether the A_VALUE on HRHISTORY does not equal the current value on EMPLOYEE of EMP_STATUS. 

        I agree that a temp table might be best, as HRHISTORY tends to have an obscene number of records and performance may suffer.

        A subreport would be a beast here.  I'd stay away from it.

        Phil Simon http://philsimonsystems.com/ phil@philsimonsystems.com
        Chris Martin
        Veteran Member
        Posts: 277
        Veteran Member
        3/5/2008 2:17 PM
          I would strongly advise against a subreport. I would also go with the temp table/stored procedure for this.
          Ryan
          Basic Member
          Posts: 6
          Basic Member
          3/12/2008 7:34 PM

            Did you get this to work?  I'm working on a change of status report also.  Please let me know.

            You are not authorized to post a reply.