Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Chriss Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Search results for query: *

  1. rougex

    Fixed Width Field - EE. LAST_NAME

    Hello I am using Reportsmith / SQL based and creating a new Interface Report. I have reviewed Charles' power point for suggestions but still having a problem. I am trying to PAD EE.LAST_NAME to be 18 characters in length - left justified. Can anyone be of assistance?
  2. rougex

    Max EFFDT when pay Action = LOA ?

    Charles - I was actually able to get the data I was needing by using the statement below in my selection criteria. Thanks for your power point by the way! Mighty Helpful (SELECT MAX (B.REVIEW_DT) FROM PS_EMPLOYEE_REVIEW B WHERE REVIEW.EMPLID=B.EMPLID AND B.REVIEW_DT <=...
  3. rougex

    Max EFFDT when pay Action = LOA ?

    Hi there I am trying to find the MAX EFFDT from PS_EMPLOYEES (alias EE) where ACTION = LOA. It appears that we have PAY and XFR actions when an employee is on Leave, so pulling the effdt date of the table is not getting me the correct information. Any help to guide me would be appreciated!
  4. rougex

    Need Assistance - Review Date rpt with a twist

    I have the Effective Date option correct on the Job Table, however no matter what date I put in there the NEXT REVIEW DATE always picks up the MAX, or duplicates depending on the formula (or no formula) that I enter into the selection crieria. I am thinking it may be because the...
  5. rougex

    Need Assistance - Review Date rpt with a twist

    Hi there . . . SQL Servier - ADP Enterprise I am creating a Next Review Date report for our company scorecard and need to look at historical records. Tables: PS_PERSONAL_DATA (ALIAS EE), PS_JOB (ALIAS JOB), AND PS_EMPLOYEE_REVIEW (ALIAS REVIEW) I am looking for the REVIEW_DT and...
  6. rougex

    Help Calculating Eligibility Date -

    CASE WHEN DATEPART(D,DATEADD(DD,90,PS_EMPLOYEES.HIRE_DT)) = 1 THEN DATEADD(DD,90,PS_EMPLOYEES.HIRE_DT) ELSE DATEADD(DD,(1-DATEPART(D,(DATEADD(MM,1,DATEADD(DD,90,PS_EMPLOYEES.HIRE_DT))))),(DATEADD(MM,1,DATEADD(DD,90,PS_EMPLOYEES.HIRE_DT)))) END Finally got it to work !! Just in...
  7. rougex

    Help Calculating Eligibility Date -

    Thank you for the information! I know NADA about Oracle so I can do some playing around in the Derived Field box to see if I can get it to work.
  8. rougex

    Help Calculating Eligibility Date -

    Greetings Using SQL platform I am trying to calculate the eligibility date for Health Benefits based on an employees Hire Date. I have tried many derivations of Date Functions and even gone through a few threads here, but it is not working properly Eligibility Rules Eligible the first of the...
  9. rougex

    Help using Date Functions to caluclate new date!

    Thank you George I am however trying to find the first of the month FOLLOWING 90 days of employment, so in your example I would be looking for 10/1/2007?????? Thank you thank you
  10. rougex

    Help using Date Functions to caluclate new date!

    Greetings I am attempting to find the 1st of the month following 90 days of an employees hire date (EE.HIRE_DT) So far I have come up with: DATEPART(MM,(DATEADD(MM,4,DATEADD(DD,-(DATEPART(DD,EE.HIRE_DT)-1),EE.HIRE_DT))) ) However... this isnt working in all cases. For example, if the hire...
  11. rougex

    Subquery using EXIST error

    Hi there I am using Reportsmith, which is a tool that writes SQL based on table selections, unfortunately it is not a very dynamic tool and doesnt allow for a lot of direct SQL entry. I Have one major Select below and I am trying to add the second select statement as a subquery but it is...
  12. rougex

    Attaching Dependents to Multiple Benefit Plans

    Hi all I am using Reportsmith 3.1 SQL based to extract from ADP Enterprise version 3F I am attemtping to attach active dependents to individual Benefit Plans (multiple in one report) but having difficulty due to multple outer joins. Has anyone cracked this case, and if so would I be able to...
  13. rougex

    Inner Memeber of Outer Join Clause ERROR

    Hello! Using Reportsmith I am creating Benefit Statements for our organization. I need an outer join on the 'EE' table when linked to the remaining tables in order to return all employees regardless if they have opted for benefits or not. When trying to add their dependents, I can add the...
  14. rougex

    Control Hours Report

    In your select criteria, you will need to write a MAX statement. I am making two assumptions in the following, that your table alias is CNTL_HRS, and that your key field is EMPL ID FollOw this: Data Field CNTL_HRS.AL_PROCESS_EFFDT is equal to formula: (SELECT MAX (AL_PROCESS_EFFDT) FROM...
  15. rougex

    Benefit Statements - revisited/suggestions

    HI~ Enterprise ADP / Reportsmith 3.1 - SQL Based Viewed some older posts with regards to Benefit Statements. I have been building our unique statement with 'mild' success and was looking for a couple tips. One thread read to use Ded Calc, I assume with a MAX Check Date selection. What do you...
  16. rougex

    Receiving a 'varchar' error

    woo hoo!!!!!!!! So simple, yet not. Thank you.
  17. rougex

    creating a derived field using case

    CASE WHEN (INJURY.RESTRICTD_WORKDAYS) > 0 THEN '1' ELSE ' ' END Through trial and error I was able to work through it with the above.
  18. rougex

    Receiving a 'varchar' error

    Hi all! I am writing a CASE statement in the Reportsmith software to 'rename' or 'update' field values...see below CASE (LOC.LOCATION) WHEN 'C07' THEN 'NRG' ELSE 0 END it is returning the following: Syntax error converting varchar value 'NRG' to a column of data type int. What does this mean...
  19. rougex

    creating a derived field using case

    HELP! - SQL - Running Reportsmith 3.1 (linking to ADP Enterprise) to create a Compliance report for Workers Comp Reporting. I am trying to translate data to do the following When '0' then ' ' (nothing) - or it can be '0' when greater than '0' then '1' I cant get it to accept any comparison...

Part and Inventory Search

Back
Top