Member Avatar for ehat

My query is below. I need to evaulate each row returned (and provide a "label" - shown with an astrick) based on the values in the 3 date columns and I'm stumped as to how to do it.

Here are my conditions and labels:
Action_Dt = Effdt AND Action_Dt < cutoff_dt- OK*
Action_Dt = Effdt AND Action_Dt > cutoff_dt - Noted*
Action_Dt > cutoff_dt AND Effdt < cutoff_dt - Late*
Action_Dt > cutoff_dt AND Effdt > cutoff_dt - Noted*
Action_Dt < cutoff_dt AND Effdt < cutoff_dt - OK*
Action_Dt < cutoff_dt AND Effdt > cutoff_dt - Data Entry Issue*

select P.NAME, C.CUTOFF_DT, J.ACTION_DT, J.EFFDT, Q.NAME
from PS_PERSONAL_DATA P,
ps_job j,
SYSADM.PS_DEPT_TBL D,
PS_PERSONAL_DATA Q,
SYSADM.PS_PAY_CALENDAR C
WHERE J.EMPLID = P.EMPLID
AND D.DEPTID = J.DEPTID
AND D.PM = Q.EMPLID(+)
AND J.PAYGROUP = C.PAYGROUP
AND J.EFFDT >= C.PAY_BEGIN_DT
AND J.EFFDT <= C.PAY_END_DT
and D.effdt = (select max(D1.effdt) from SYSADM.PS_DEPT_TBL D1
where D.DEPTID = D1.DEPTID)

Can it be done?

  • 2 Contributors
  • 3 Replies
  • 157 Views
  • 1 Day Discussion Span
  • Latest Post Latest Post by ehat

Recommended Answers

All 3 Replies

Member Avatar for debasisdas

can you kindly post your table structure for reference of our experts in the forum.

Member Avatar for ehat

Do you mean a describe of each of the tables in my query?

Member Avatar for ehat

Describes attached. Thanks!

Table               Key Column                       Type   LengthRequired
PS_PERSONAL_DATA    PK  EMPLID                       Char       11Yes
PS_PERSONAL_DATA    AK  NAME                         Char       40Yes
PS_PERSONAL_DATA        FIRST_NAME                   Char       30Yes
PS_PERSONAL_DATA        MIDDLE_NAME                  Char       30
PS_PERSONAL_DATA        LAST_NAME                    Char       40Yes
PS_PERSONAL_DATA        NAME_SUFFIX                  Char        6
PS_PERSONAL_DATA        NAME_PREFIX                  Char       10
PS_PERSONAL_DATA        FORMAL_TITLE                 Char       30
PS_PERSONAL_DATA        PREFERRED_NAME               Char       40
PS_PERSONAL_DATA        STREET1                      Char       40Yes
PS_PERSONAL_DATA        STREET2                      Char       40
PS_PERSONAL_DATA        STREET3                      Char       40
PS_PERSONAL_DATA        CITY                         Char       30Yes
PS_PERSONAL_DATA    FK  STATE                        Char        2
PS_PERSONAL_DATA        ZIP                          Char       10
PS_PERSONAL_DATA    FK  COUNTRY                      Char        3
PS_PERSONAL_DATA        STREET1_OTHER                Char       40
PS_PERSONAL_DATA        STREET2_OTHER                Char       40
PS_PERSONAL_DATA        STREET3_OTHER                Char       40
PS_PERSONAL_DATA        CITY_OTHER                   Char       30
PS_PERSONAL_DATA    FK  STATE_OTHER                  Char        2
PS_PERSONAL_DATA        ZIP_OTHER                    Char       10
PS_PERSONAL_DATA    FK  COUNTRY_OTHER                Char        3
PS_PERSONAL_DATA        HOME_PHONE                   Char       15
PS_PERSONAL_DATA    AK  SSN                          Char        9
PS_PERSONAL_DATA        SIN                          Char        9
PS_PERSONAL_DATA        NATIONAL_ID                  Char       16
PS_PERSONAL_DATA    FK  NATIONAL_CNTRY_CD            Char        3
PS_PERSONAL_DATA        FORMER_NAME                  Char       40
PS_PERSONAL_DATA    FK  PER_STATUS                   Char        1Yes
PS_PERSONAL_DATA        ORIG_HIRE_DT                 Date       10
PS_PERSONAL_DATA    FK  ETHNIC_GROUP                 Char        1Yes
PS_PERSONAL_DATA    FK  SEX                          Char        1Yes
PS_PERSONAL_DATA        AGE_STATUS                   Char        1Yes
PS_PERSONAL_DATA        US_WORK_ELIGIBILTY           Char        1Yes
PS_PERSONAL_DATA        DISABLED_VET                 Char        1Yes
PS_PERSONAL_DATA        DISABLED                     Char        1Yes
PS_PERSONAL_DATA    FK  PREFERRED_LANGUAGE           Char        1Yes
PS_PERSONAL_DATA    FK  MILITARY_STATUS              Char        1Yes
PS_PERSONAL_DATA        MIL_DISCHARGE_DT             Date       10
PS_PERSONAL_DATA    FK  MAR_STATUS                   Char        1Yes
PS_PERSONAL_DATA        MAR_STATUS_DT                Date       10
PS_PERSONAL_DATA        BIRTHDATE                    Date       10
PS_PERSONAL_DATA        BIRTHPLACE                   Char       30
PS_PERSONAL_DATA        DT_OF_DEATH                  Date       10
PS_PERSONAL_DATA    FK  HIGHEST_EDUC_LVL             Char        1Yes
PS_PERSONAL_DATA    FK  CITIZENSHIP_STATUS           Char        1Yes
PS_PERSONAL_DATA        VISA_NBR                     Char       15
PS_PERSONAL_DATA        VISA_EXPIRE_DT               Date       10
PS_PERSONAL_DATA    FK  VISA_TYPE                    Char        2Yes
PS_PERSONAL_DATA        PASSPORT_NBR                 Char       15
PS_PERSONAL_DATA        PASSPORT_EXPIRE_DT           Date       10
PS_PERSONAL_DATA    FK  PASSPORT_COUNTRY             Char        3
PS_PERSONAL_DATA        CITIZEN_PROOF1               Char       10
PS_PERSONAL_DATA        CITIZEN_PROOF2               Char       10
PS_PERSONAL_DATA    FK  CITIZENSHIP_CNTRY            Char        3
PS_PERSONAL_DATA    FK  CITIZENSHIP_CNTRY2           Char        3
PS_PERSONAL_DATA    FK  CITIZENSHIP_CNTRY3           Char        3
PS_PERSONAL_DATA        CAN_DISABLED                 Char        1Yes
PS_PERSONAL_DATA        CAN_ABORIGINAL               Char        1Yes
PS_PERSONAL_DATA        CAN_VISBL_MINORITY           Char        1Yes
PS_PERSONAL_DATA        SMOKER                       Char        1Yes
PS_PERSONAL_DATA    FK  HIGHLY_COMP_EMPL_P           Char        1Yes
PS_PERSONAL_DATA    FK  HIGHLY_COMP_EMPL_C           Char        1Yes
PS_PERSONAL_DATA        ACCOMMODTN_REQUEST           Char        1Yes
PS_PERSONAL_DATA    FK  DISABILITY_TYPE              Char        2
PS_PERSONAL_DATA        EMAIL_ADDRESS                Char       70
PS_PERSONAL_DATA        EMAIL_ADDRESS2               Char       70
PS_PERSONAL_DATA        COUNTY                       Char       15
PS_PERSONAL_DATA        RETIREE_BILLING              Char        1
PS_PERSONAL_DATA        MEDICARE_DT                  Date       10
PS_PERSONAL_DATA        LOCAL_NATIONAL               Char        1Yes

PS_DEPT_TBL         PK  DEPTID                       Char       10Yes
PS_DEPT_TBL         PK  EFFDT                        Date       10Yes
PS_DEPT_TBL         FK  EFF_STATUS                   Char        1Yes
PS_DEPT_TBL         AK  DESCR                        Char       30
PS_DEPT_TBL             DESCRSHORT                   Char       10
PS_DEPT_TBL         FK  COMPANY                      Char        3
PS_DEPT_TBL         FK  LOCATION                     Char        5
PS_DEPT_TBL         FK  MANAGER_ID                   Char       11
PS_DEPT_TBL             BUDGET_YR_END_DT             Nbr         4
PS_DEPT_TBL         FK  BUDGET_LVL                   Char        1Yes
PS_DEPT_TBL             GL_EXPENSE                   Char       35
PS_DEPT_TBL         FK  EEO4_FUNCTION                Char 0-     2
PS_DEPT_TBL             CAN_IND_SECTOR               Char        3
PS_DEPT_TBL         FK  REPORTS_TO_DEPT              Char       10
PS_DEPT_TBL							PM													 Char       11

PS_JOB              FK  EMPLID                       Char       11Yes
PS_JOB              FK  EMPL_RCD_NBR                 Nbr         3
PS_JOB              PK  EFFDT                        Date       10Yes
PS_JOB              PK  EFFSEQ                       Nbr         1
PS_JOB              FK  DEPTID                       Char       10Yes
PS_JOB              FK  JOBCODE                      Char        6Yes
PS_JOB              FK  POSITION_NBR                 Char        8
PS_JOB              FK  EMPL_STATUS                  Char        1Yes
PS_JOB              FK  ACTION                       Char        3Yes
PS_JOB                  ACTION_DT                    Date       10
PS_JOB              FK  ACTION_REASON                Char        3
PS_JOB              FK  LOCATION                     Char        5Yes
PS_JOB                  JOB_ENTRY_DT                 Date       10
PS_JOB                  DEPT_ENTRY_DT                Date       10
PS_JOB                  POSITION_ENTRY_DT            Date       10
PS_JOB              FK  SHIFT                        Char        1
PS_JOB              FK  REG_TEMP                     Char        1Yes
PS_JOB              FK  FULL_PART_TIME               Char        1Yes
PS_JOB              FK  FLSA_STATUS                  Char        1Yes
PS_JOB              FK  OFFICER_CD                   Char        1Yes
PS_JOB              FK  COMPANY                      Char        3Yes
PS_JOB              FK  PAYGROUP                     Char        3Yes
PS_JOB              FK  EMPL_TYPE                    Char        1Yes
PS_JOB              FK  HOLIDAY_SCHEDULE             Char        4
PS_JOB                  STD_HOURS                    Nbr       2.2Yes
PS_JOB              FK  EEO_CLASS                    Char        1Yes
PS_JOB              FK  EMPL_CLASS                   Char        1
PS_JOB              FK  SAL_ADMIN_PLAN               Char        3
PS_JOB              FK  GRADE                        Char        3
PS_JOB                  GRADE_ENTRY_DT               Date       10
PS_JOB              FK  STEP                         Nbr         2
PS_JOB                  STEP_ENTRY_DT                Date       10
PS_JOB                  GL_PAY_TYPE                  Char        6
PS_JOB              FK  ACCT_CD                      Char       25
PS_JOB              FK  EARNS_DIST_TYPE              Char        1Yes
PS_JOB              FK  COMP_FREQUENCY               Char        1Yes
PS_JOB                  COMPRATE                     Nbr       8.4
PS_JOB                  CHANGE_AMT                   Nbr(+/-   9.4
PS_JOB                  CHANGE_PCT                   Nbr(+/-   4.3
PS_JOB                  ANNUAL_RT                    Nbr       8.2
PS_JOB                  MONTHLY_RT                   Nbr       7.2
PS_JOB                  HOURLY_RT                    Nbr       4.4
PS_JOB                  ANNL_BENEF_BASE_RT           Nbr       8.2
PS_JOB                  SHIFT_RT                     Nbr       3.4
PS_JOB                  SHIFT_FACTOR                 Nbr       1.3
PS_JOB              FK  CURRENCY_CD                  Char        3Yes
PS_JOB                  XFER_ACCUM                   Char        1Yes
PS_JOB                  XFER_DEDUCTIONS              Char        1Yes
PS_JOB                  XFER_SUI_SDI                 Char        1Yes
PS_JOB                  XFER_TAX                     Char        1Yes
PS_JOB              FK  AL_EMPL_STATUS               Char        1
PS_JOB              FK  AL_PAY_FREQUENCY             Char        1
PS_JOB                  AL_STD_HOURS                 Nbr       3.2
PS_JOB                  CLOCK_NBR                    Char        5
PS_JOB                  DATA_CONTROL                 Char        4
PS_JOB              AK  FILE_NBR                     Char 0-     6
PS_JOB              FK  FILE_NBR_STATUS              Char        1
PS_JOB              FK  GROSS_CALC                   Char        1
PS_JOB              FK  HOME_DEPARTMENT              Char        6
PS_JOB              FK  HOME_JOBCOST_NBR             Char       15
PS_JOB
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.