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
  • 160 Views
  • 1 Day Discussion Span
  • Latest Post Latest Post by ehat
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.