Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author |
Topic |
Shego
Starting Member
7 Posts |
Posted - 2013-05-22 : 19:43:21
|
So, I'm trying to find the lowest and highest salary amount possible for each employee in an organization. Salaries are coded by SalaryGroup_code (SGRP_code), grade, step, and amount, and are linkable to jobs by the sgrp_code. Jobs are defined on a different table, and linkable to persons (a third table) through identifying numbers (PIDM). I'm looking to get only one line of data for each person that is defined thusly:person_name, person_pidm, job_description, job_sgrp_code, min(salary_amount), max(salary_amount)The query I've written looks like this:SELECT person_name, person_pidm, job_description, job_sgrp_code, min(salary_amount), max(salary_amount)FROM person, job, salaryWHERE person_pidm=job_pidmAND job_sgrp_code=salary_sgrp_codeGROUP BY person_name,person_pidm,job_description,job_sgrp_codeThis appears to run properly, in that it selects a minimum and maximum value for each person for each row of data. Unfortunately it selects the same min/max salary values for each person for each row of data, and I know for a fact that this is not true. There is no way an intern's max salary is the same as the ceo's max salary..... it's just not logical.So my question is "what have I done wrong" or maybe "what haven't I done that I should have"???? |
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-05-23 : 00:25:28
|
so as per your explanation your query should work properly. You're linking on salary_sgrp_code for getting the salaray value and that should be the way to go as salary should be defined based on group. If you can post some sample data for us we might be able to validate if data is coming as per expectations and if not, suggest an alternative.------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
Transact Charlie
Master Smack Fu Yak Hacker
3451 Posts |
Posted - 2013-05-23 : 04:38:32
|
your joins are probably incorrect. Maybe an implicit cross join.Using ANSI syntax is both advised and more readable. Something like:SELECT p.person_name , p.person_pidm , j.job_description , j.job_sgrp_code , min(s.salary_amount) , max(s.salary_amount)FROM person AS p JOIN job AS j ON j.job_pidm = p.person_pidm JOIN salary AS s ON s.salary_sgrp_code = j.job_sgrp_code AND <JOIN FROM PERSON to SALARY>GROUP BY p.person_name, p.person_pidm, j.job_description, j.job_sgrp_code Note the line in red...So I think right now you are doing an implicit cross join between person and salary.Transact CharlieMsg 3903.. The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION. http://nosqlsolution.blogspot.co.uk/ |
|
|
Shego
Starting Member
7 Posts |
Posted - 2013-05-23 : 13:28:07
|
Yes, I'm using an implicit connection between person and salary. This is because there are no explicit connections between these two tables, the only way (as far as I know) to connect them is through the commonality of the job. I feel almost as if I need to set up a temporary table or two, as each person can have more than one job and each job can belong to more than one person, just as each job can have more than one salary grade, which has more than one step, which determines the actual amount.Normally I'd direct the query to the highest grade and step, but each sgrp_code has it's own set of grades and steps, and there is no consistency. I expect the output to look something like this:name............pidm......job..........sgrp......minSal....maxSalJohnSmith.....11111.....cook.......88888....12000.....038000JaneSmith.....11112.....shepherd 44444....10000.....020000JoeySmith.....11113.....pirate.....77777.....45000....120000JackSmith.....11114.....driver.....66666.....17000.....027000but what I'm getting is this:name............pidm......job..........sgrp......minSal....maxSalJohnSmith.....11111.....cook.......88888....10000.....030000JaneSmith.....11112.....shepherd.44444....10000.....030000JoeySmith.....11113.....pirate.....77777....10000.....030000JackSmith.....11114.....driver.....66666.....10000.....030000Edit: Further, if I ask for the grade and step, they also reflect as the same, no matter what the sgrp_code is. |
|
|
MuMu88
Aged Yak Warrior
549 Posts |
Posted - 2013-05-23 : 14:14:01
|
Can you provide your table definitions and some data in consumable format? The information you provided is good but not enough to solve the problem you are trying to address. |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-05-24 : 00:29:44
|
you explanantion doesnt make sense to anyone seeing unless they either see your tables or have access to your data. Thats why we're repeatedly asking you to post some sample data from tables in below formathttp://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
Shego
Starting Member
7 Posts |
Posted - 2013-05-24 : 14:11:45
|
This is the Person table. I have limited access to the others, but they are stored on my desktop in a different format.---------------------------------------------------------- DDL for Table SPRIDEN-------------------------------------------------------- CREATE TABLE "SATURN"."SPRIDEN" ( "SPRIDEN_PIDM" NUMBER(8,0), "SPRIDEN_ID" VARCHAR2(9 CHAR), "SPRIDEN_LAST_NAME" VARCHAR2(60 CHAR), "SPRIDEN_FIRST_NAME" VARCHAR2(60 CHAR), "SPRIDEN_MI" VARCHAR2(60 CHAR), "SPRIDEN_CHANGE_IND" VARCHAR2(1 CHAR), "SPRIDEN_ENTITY_IND" VARCHAR2(1 CHAR), "SPRIDEN_ACTIVITY_DATE" DATE, "SPRIDEN_USER" VARCHAR2(30 CHAR), "SPRIDEN_ORIGIN" VARCHAR2(30 CHAR), "SPRIDEN_SEARCH_LAST_NAME" VARCHAR2(60 CHAR), "SPRIDEN_SEARCH_FIRST_NAME" VARCHAR2(60 CHAR), "SPRIDEN_SEARCH_MI" VARCHAR2(60 CHAR), "SPRIDEN_SOUNDEX_LAST_NAME" CHAR(4 CHAR), "SPRIDEN_SOUNDEX_FIRST_NAME" CHAR(4 CHAR), "SPRIDEN_NTYP_CODE" VARCHAR2(4 CHAR), "SPRIDEN_CREATE_USER" VARCHAR2(30 CHAR), "SPRIDEN_CREATE_DATE" DATE, "SPRIDEN_DATA_ORIGIN" VARCHAR2(30 CHAR), "SPRIDEN_CREATE_FDMN_CODE" VARCHAR2(30 CHAR), "SPRIDEN_SURNAME_PREFIX" VARCHAR2(60 CHAR) ) SEGMENT CREATION IMMEDIATE PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING STORAGE(INITIAL 81920 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE "DEVELOPMENT" ; COMMENT ON COLUMN "SATURN"."SPRIDEN"."SPRIDEN_PIDM" IS 'Internal identification number of the person.'; COMMENT ON COLUMN "SATURN"."SPRIDEN"."SPRIDEN_ID" IS 'This field defines the identification number used to access person on-line.'; COMMENT ON COLUMN "SATURN"."SPRIDEN"."SPRIDEN_LAST_NAME" IS 'This field defines the last name of person.'; COMMENT ON COLUMN "SATURN"."SPRIDEN"."SPRIDEN_FIRST_NAME" IS 'This field identifies the first name of person.'; COMMENT ON COLUMN "SATURN"."SPRIDEN"."SPRIDEN_MI" IS 'This field identifies the middle name of person.'; COMMENT ON COLUMN "SATURN"."SPRIDEN"."SPRIDEN_CHANGE_IND" IS 'This field identifies whether type of change made to the record was an ID number change or a name change. Valid values: I - ID change, N - name change.'; COMMENT ON COLUMN "SATURN"."SPRIDEN"."SPRIDEN_ENTITY_IND" IS 'This field identifies whether record is person or non-person record. It does not display on the form. Valid values: P - person, C - non-person.'; COMMENT ON COLUMN "SATURN"."SPRIDEN"."SPRIDEN_ACTIVITY_DATE" IS 'This field defines the most current date record is created or changed.'; COMMENT ON COLUMN "SATURN"."SPRIDEN"."SPRIDEN_USER" IS 'USER: The ID for the user that most recently updated the record.'; COMMENT ON COLUMN "SATURN"."SPRIDEN"."SPRIDEN_ORIGIN" IS 'ORIGIN: The name of the Banner Object that was used most recently to update the row in the spriden table.'; COMMENT ON COLUMN "SATURN"."SPRIDEN"."SPRIDEN_SEARCH_LAST_NAME" IS 'The Last Name field with all spaces and punctuation removed and all letters capitalized.'; COMMENT ON COLUMN "SATURN"."SPRIDEN"."SPRIDEN_SEARCH_FIRST_NAME" IS 'The First Name field with all spaces and punctuation removed and all letters capitalized.'; COMMENT ON COLUMN "SATURN"."SPRIDEN"."SPRIDEN_SEARCH_MI" IS 'The MI (Middle Initial) field with all spaces and punctuation removed and all letters capitalized.'; COMMENT ON COLUMN "SATURN"."SPRIDEN"."SPRIDEN_SOUNDEX_LAST_NAME" IS 'The Last Name field in SOUNDEX phonetic format.'; COMMENT ON COLUMN "SATURN"."SPRIDEN"."SPRIDEN_SOUNDEX_FIRST_NAME" IS 'The First Name field in SOUNDEX phonetic format.'; COMMENT ON COLUMN "SATURN"."SPRIDEN"."SPRIDEN_NTYP_CODE" IS 'NAME TYPE CODE: The field is used to store the code that represents the name type associated with a person''s name.'; COMMENT ON COLUMN "SATURN"."SPRIDEN"."SPRIDEN_CREATE_USER" IS 'Record Create User: This field contains Banner User ID which created new record'; COMMENT ON COLUMN "SATURN"."SPRIDEN"."SPRIDEN_CREATE_DATE" IS 'Record Create Date: This field contains date new record created'; COMMENT ON COLUMN "SATURN"."SPRIDEN"."SPRIDEN_DATA_ORIGIN" IS 'DATA SOURCE: Source system that generated the data'; COMMENT ON COLUMN "SATURN"."SPRIDEN"."SPRIDEN_CREATE_FDMN_CODE" IS 'PII DOMAIN: PII Domain of the user who created the spriden row.'; COMMENT ON COLUMN "SATURN"."SPRIDEN"."SPRIDEN_SURNAME_PREFIX" IS 'SURNAME PREFIX: Name tag preceding the last name or surname. (Van, Von, Mac, etc.)'; COMMENT ON TABLE "SATURN"."SPRIDEN" IS 'Person Identification/Name Repeating Table'; |
|
|
Shego
Starting Member
7 Posts |
Posted - 2013-05-24 : 14:13:14
|
This is the Job table. TABLE: NBRJOBS CREATOR: POSNCTL Assignment Repeating Table =================================================================================================================== Column Name Type Width Scale Nulls Column Comments =============================== ========= ===== ===== ======= ===================================================== NBRJOBS_PIDM NUMBER 8 NO PIDM: Internal identification number of the employee. NBRJOBS_POSN VARCHAR2 6 NO POSITION: Position Number. NBRJOBS_SUFF VARCHAR2 2 NO SUFFIX: Suffix of the job. NBRJOBS_EFFECTIVE_DATE DATE 7 NO EFFECTIVE DATE: Date that the Job Assignment record becomes effective. NBRJOBS_STATUS VARCHAR2 1 NO STATUS: Status of the job. Valid values are A = Active, B = Leave without pay with benefits, L = Leave without pay without benefits, F = Leave with full pay and benefits, P = Leave with partial pay and benefits, T = Terminated. NBRJOBS_DESC VARCHAR2 30 YES DESCRIPTION: Description of the job. Defaults from the Position Title if left blank. NBRJOBS_ECLS_CODE VARCHAR2 2 NO EMPLOYEE CLASSIFICATION CODE: The employee class attached to the job, based on the Employee Class entered on the Position Form (NBAPOSN). NBRJOBS_PICT_CODE VARCHAR2 2 NO PAY ID CODE: The Pay ID attached to the job. NBRJOBS_COAS_CODE_TS VARCHAR2 1 YES TIME SHEET CHART OF ACCOUNTS CODE: Valued only if Banner Finance is installed. NBRJOBS_ORGN_CODE_TS VARCHAR2 6 NO TIME SHEET ORGANIZATION: Organization where time sheet is sent for this job. NBRJOBS_SAL_TABLE VARCHAR2 2 YES SALARY TABLE: The Salary Table attached to the job. Defaults from the Salary/Rate Rule Form (NTRSALA) via the Position Form (NBAPOSN). NBRJOBS_SAL_GRADE VARCHAR2 5 YES SALARY GRADE: The Salary Grade attached to the job. Defaults from the Salary/Rate Rule Form (NTRSALA) via the Position Form (NBAPOSN). NBRJOBS_SAL_STEP NUMBER 3 YES SALARY STEP: The Salary Step attached to the job. Defaults from the Salary/Rate Rule Form (NTRSALA) or can be entered online. NBRJOBS_APPT_PCT NUMBER 5 2 NO APPOINTMENT PERCENTAGE: The Appointment percent attached to the Job. Used to establish the percentage of full time appointment for an employee in a position. The value defaults from the position record, but the user may change at the job level. The appointment percent cannot exceed 100. NBRJOBS_FTE NUMBER 6 3 YES FULL TIME EQUIVALENCY: The full time percentage attached to the job. Defaults to 1. NBRJOBS_HRS_DAY NUMBER 6 2 NO HOURS PER DAY: The hours attached to the job that are normally worked in a day. NBRJOBS_HRS_PAY NUMBER 6 2 NO HOURS PER PAY: The hours attached to the job that are normally worked in a pay period. NBRJOBS_SHIFT VARCHAR2 1 NO SHIFT: The shift attached to the job. NBRJOBS_REG_RATE NUMBER 11 6 YES REGULAR RATE: The regular rate attached to the job. If this field is entered or defaulted from the Salary/Rate Rule Form, the Assign Salary is system calculated and vice versa. Date: 18-DEC-2008 Page: 45 =================================================================================================================== Column Name Type Width Scale Nulls Column Comments =============================== ========= ===== ===== ======= ===================================================== NBRJOBS_ASSGN_SALARY NUMBER 11 2 NO ASSIGNMENT SALARY: The salary to be paid each pay period. If the Assignment Salary is entered, the Annual Salary is calculated based on the factor and vice versa. NBRJOBS_FACTOR NUMBER 3 1 NO FACTOR: The number of pays per calendar year. NBRJOBS_ANN_SALARY NUMBER 11 2 NO ANNUAL SALARY: The annual salary attached to the job based on the calendar year. If the Annual Salary is entered, the Assignment Salary is system calculated based the factor and vice versa. NBRJOBS_PER_PAY_SALARY NUMBER 11 2 NO PER PAY SALARY: Used for deferred pay calculations. NBRJOBS_PAYS NUMBER 3 1 NO PAYS: Used for deferred pay calculations. NBRJOBS_PER_PAY_DEFER_AMT NUMBER 11 2 NO PER PAY DEFERRED AMOUNT: The amount deferred each pay period for deferred pay calculation. NBRJOBS_ACTIVITY_DATE DATE 7 NO Date of last activity (insert or update) on the record. NBRJOBS_JCRE_CODE VARCHAR2 5 YES CHANGE REASON CODE: Associates the reason for the change with the job detail record. NBRJOBS_SGRP_CODE VARCHAR2 6 NO SALARY GROUP CODE: The Salary Group Code attached to the job. NBRJOBS_EMPR_CODE VARCHAR2 4 YES EMPLOYER CODE: The Employer Identification code attached to the job. NBRJOBS_LGCD_CODE VARCHAR2 4 YES LONGEVITY CODE: Longevity code attached to the job. NBRJOBS_LOCN_CODE VARCHAR2 6 YES LOCATION CODE: EEO location attached to the job. NBRJOBS_SCHL_CODE VARCHAR2 6 YES SCHOOL CODE: EEO school attached to the job. NBRJOBS_SUPERVISOR_PIDM NUMBER 8 YES SUPERVISOR PIDM: Internal identification number of the supervisor attached to the job. NBRJOBS_SUPERVISOR_POSN VARCHAR2 6 YES SUPERVISOR POSN: The position number of the supervisor attached to the job. NBRJOBS_SUPERVISOR_SUFF VARCHAR2 2 YES SUPERVISOR SUFFIX: Suffix attached to the supervisor's position. NBRJOBS_WKCP_CODE VARCHAR2 4 YES WORKER COMPENSATION CODE: Workers' Compensation Classification Code attached to the job. NBRJOBS_JBLN_CODE VARCHAR2 6 YES JOB LOCATION CODE: Job Location Code attached to the job. NBRJOBS_PERS_CHG_DATE DATE 7 NO PERSONNEL CHANGE DATE: Date the change was actually made as opposed to the effective date which is for payroll purposes. NBRJOBS_PCAT_CODE VARCHAR2 4 YES PREMIUM PAY CODE: Generates premium pay attached to the job. NBRJOBS_DFPR_CODE VARCHAR2 4 YES DEFER PAY CODE: The defer pay schedule attached to the job. Defaults from PTRDFPR. NBRJOBS_ENCUMBRANCE_HRS NUMBER 6 2 YES ENCUMBRANCE HOURS: Hours used to calculate the encumbrance amount for this job in the active fiscal year. NBRJOBS_CONTRACT_NO VARCHAR2 8 YES JOB CONTRACT NUMBER: Job contract number attached to the job. NBRJOBS_STRS_ASSN_CODE VARCHAR2 2 YES STRS ASSIGNMENT CODE: STRS Assignment Code attached to the job. Valid values are STRS mandated. NBRJOBS_STRS_PAY_CODE VARCHAR2 2 YES STRS PAY CODE: STRS Pay Code for the employee attached to the job. The value selected determines how the employee's salary will be reported to STRS administration on the monthly F-496 report. NBRJOBS_PERS_PAY_CODE VARCHAR2 2 YES PERS PAY CODE: PERS Pay Code for the employee attached to the job. The value selected determines how the employee's salary will be reported to PERS administration on the PERS Monthly Contribution Report. Date: 18-DEC-2008 Page: 46 =================================================================================================================== Column Name Type Width Scale Nulls Column Comments =============================== ========= ===== ===== ======= ===================================================== NBRJOBS_TIME_ENTRY_METHOD VARCHAR2 1 NO TIME ENTRY METHOD: Time Entry Method attached to the job. Valid values are P = Payroll, W = Web, D = Departmental, T = Third Party. NBRJOBS_TIME_ENTRY_TYPE VARCHAR2 1 NO TIME ENTRY TYPE: Time Entry Type attached to the job. It represents the structure of how time will be entered over the Web or on the Electronic Approvals of Time Entry Form (PHATIME). Valid values are T = Time Sheet, E = Exception Time Only, N = None. NBRJOBS_TIME_IN_OUT_IND VARCHAR2 1 NO TIME IN AND OUT INDICATOR: Time In/Out Indicator attached to the job. Valiod values are Y = Yes and N = No. If Y, this employee must enter time in and out information on the Web or have it entered by a department administrator on the Electronic Approvals of Time Entry Form (PHATIME). Time In and out must be entered for all earn codes that require hours entry. Time in and out will not be entered for earn codes that require units entry. NBRJOBS_LCAT_CODE VARCHAR2 2 YES LEAVE CATEGORY CODE: A code which identifies a group of leave types. It is used when the Leave by Job Method has been selected for leave processing. NBRJOBS_LEAV_REPT_METHOD VARCHAR2 1 NO LEAVE REPORT ENTRY METHOD: Leave Report Method Attached to the job. Valid values are P = None, W = Leave Report on the Web, D = Departmental Leave Report, T = Third Party Leave Report. NBRJOBS_PICT_CODE_LEAV_REPT VARCHAR2 2 NO LEAVE REPORT PAY ID: The Leave Report Pay ID attached to the job and associated with a leave report or leave request. NBRJOBS_USER_ID VARCHAR2 30 YES USER ID: The Oracle ID of the user who changed the record. NBRJOBS_DATA_ORIGIN VARCHAR2 30 YES DATA ORIGIN: Source system that created or updated the row. |
|
|
Shego
Starting Member
7 Posts |
Posted - 2013-05-24 : 14:14:24
|
This is the Salary table. TABLE: NTRSALA CREATOR: POSNCTL Salary/Rate Rule Table =================================================================================================================== Column Name Type Width Scale Nulls Column Comments =============================== ========= ===== ===== ======= ===================================================== NTRSALA_TABLE VARCHAR2 2 NO SALARY TABLE: The Salary Table being defined. NTRSALA_GRADE VARCHAR2 5 NO SALARY GRADE: The Salary Grade associated with the Salary Table being defined. NTRSALA_STEP NUMBER 3 NO SALARY STEP: The Salary Step associated with the Salary Table and Grade being defined. NTRSALA_AMOUNT NUMBER 13 4 NO ANNUAL SALARY/HOURLY RATE AMOUNT: The annual salary or hourly rate associated with this Table, Grade, and Step. NTRSALA_ACTIVITY_DATE DATE 7 NO Activity Date: Date of Last Modification of the Record; NTRSALA_SGRP_CODE VARCHAR2 6 NO SALARY GROUP CODE: The Group Code associated with a set of Salary Tables and Grades. |
|
|
Shego
Starting Member
7 Posts |
Posted - 2013-05-24 : 14:22:51
|
Then there are these two other tables I've discovered and have been referencing:This one contains position information. TABLE: NBBPOSN CREATOR: POSNCTL Position Base Table =================================================================================================================== Column Name Type Width Scale Nulls Column Comments =============================== ========= ===== ===== ======= ===================================================== NBBPOSN_POSN VARCHAR2 6 NO Position. NBBPOSN_STATUS VARCHAR2 1 NO Status. NBBPOSN_TITLE VARCHAR2 30 NO Title. NBBPOSN_PLOC_CODE VARCHAR2 6 YES Position Location code. NBBPOSN_BEGIN_DATE DATE 7 NO POSITION BEGIN DATE: First date the position is funded to begin. NBBPOSN_END_DATE DATE 7 YES POSITION END DATE: End date of the position. If null, the position is presumed to be a permanent position. NBBPOSN_TYPE VARCHAR2 1 NO Single or pooled position. NBBPOSN_PCLS_CODE VARCHAR2 5 NO POSITION CLASS CODE: The position class the defined position is assigned to. NBBPOSN_ECLS_CODE VARCHAR2 2 NO POSITION EMPLOYEE CLASS CODE: Employee class code defaults from the Position Class Rule Form but can be overriden. NBBPOSN_POSN_REPORTS VARCHAR2 6 YES Position that this position reports to. A superior. NBBPOSN_AUTH_NUMBER VARCHAR2 9 YES POSITION AUTHORIZED NUMBER: The position number that authorized the approval of the defined position. NBBPOSN_TABLE VARCHAR2 2 NO Salary table. NBBPOSN_GRADE VARCHAR2 5 NO SALARY GRADE: Position salary grade defaults from the Position Class Rule Form but can be overriden. NBBPOSN_STEP NUMBER 3 YES Salary step. NBBPOSN_APPT_PCT NUMBER 5 2 NO POSITION APPOINTMENT PERCENT: Appointment percent indicates the percentage of full time the position uses. The salary, leave accrual and budget rolls are modified by this percentage. NBBPOSN_CIPC_CODE VARCHAR2 6 YES COLLEGE INSTRUCTIONAL PROGRAM CODE: Predefined codes that are validated against the Student College Instructional Validation Form. NBBPOSN_ROLL_IND VARCHAR2 1 NO Budget roll indicator. NBBPOSN_COAS_CODE VARCHAR2 1 YES Position Chart of Accounts Code: Valued only if Banner Finance is installed NBBPOSN_ACTIVITY_DATE DATE 7 NO Date of last activity on this record. NBBPOSN_SGRP_CODE VARCHAR2 6 YES Salary Group: The Salary group that the table/grade on position belongs to. NBBPOSN_PGRP_CODE VARCHAR2 4 YES PGRP_CODE: The Position Group code. NBBPOSN_WKSH_CODE VARCHAR2 4 YES WORK SCHEDULE CODE: Determines if a Work Schedule is defaulted when a job is established. NBBPOSN_PREMIUM_ROLL_IND VARCHAR2 1 NO PREMIUM EARNINGS ROLL INDICATOR: Indicator that specifies how to roll budget for premium earnings. Values are (C)urrent Budget Amount, (A)ctual Expenditure for Current Year, (Z)ero, or (N)ot Applicable. NBBPOSN_PFOC_CODE VARCHAR2 3 YES FEDERAL OCCUPATIONAL CODE: Federal Occupational Code. NBBPOSN_PNOC_CODE VARCHAR2 6 YES NATIONAL OCCUPATIONAL CODE: National Occupational Code. Date: 18-DEC-2008 Page: 17 =================================================================================================================== Column Name Type Width Scale Nulls Column Comments =============================== ========= ===== ===== ======= ===================================================== NBBPOSN_DOTT_CODE VARCHAR2 11 YES OCCUPATIONAL TITLE CODE:field specifies the Dictionary of Positions Occupational Titles Code. NBBPOSN_CHANGE_DATE_TIME DATE 7 NO CHANGE DATE TIME: Date and Time this record was changed. NBBPOSN_CALIF_TYPE VARCHAR2 1 YES CALIFORNIA TYPE: California Pension Position Class Type. NBBPOSN_EXEMPT_IND VARCHAR2 1 NO EXEMPTION INDICATOR: Indicates if employees assigned to positions in this Position Class will be exempt or non-exempt. NBBPOSN_JBLN_CODE VARCHAR2 6 YES JOB LOCATION CODE: Code associated with this Position. NBBPOSN_BARG_CODE VARCHAR2 2 YES BARGAINING UNIT CODE: Code associated with this Position. NBBPOSN_PROBATION_UNITS NUMBER 3 YES PROBATION UNITS: Identifies the number of Probationary Units. NBBPOSN_COMMENT VARCHAR2 2000 YES COMMENT: This field stores user comments. NBBPOSN_ACCRUE_SENIORITY_IND VARCHAR2 1 NO ACCRUE SENIORITY INDICATOR: This indicator will denote whether seniority hours are accrued against the position. NBBPOSN_JOBP_CODE VARCHAR2 5 YES JOB PROGRESSION CODE: Job Progression code associated with the position. NBBPOSN_BPRO_CODE VARCHAR2 6 YES BPRO CODE: Budget Profile code. NBBPOSN_BUDGET_TYPE VARCHAR2 1 NO BUDGET TYPE: Expected status of funding sources for position. And this one that defines Position Classes: TABLE: NTRPCLS CREATOR: POSNCTL Position Classification Rule Table =================================================================================================================== Column Name Type Width Scale Nulls Column Comments =============================== ========= ===== ===== ======= ===================================================== NTRPCLS_CODE VARCHAR2 5 NO POSITION CLASS CODE: A code which identifies a Position Class which can then be assigned to a Position. NTRPCLS_DESC VARCHAR2 30 NO POSITION CLASS DESCRIPTION: The description or title for the Position Class. NTRPCLS_ECLS_CODE VARCHAR2 2 NO EMPLOYEE CLASS CODE: A code which identifies the Employee Class that is associated with this Position Class, and will therefore default to the Position Form (NBAPOSN) when the Position Class is entered. NTRPCLS_ESKL_CODE VARCHAR2 2 YES EEO SKILL CODE: The EEO Skill Code as defined by the U.S. Federal Goverment, for use by institutions of higher education, for EEO reporting NTRPCLS_EFUN_CODE VARCHAR2 2 YES EEO FUNCTION CODE: For goverment agencies only. The EEO Function Code as defined by the U.S. Federal Goverment, for use with EEO reporting. NTRPCLS_EXEMPT_IND VARCHAR2 1 NO EXEMPTION INDICATOR: Indicates if employees assigned to positions in this Position Class will be exempt or non-exempt. NTRPCLS_TABLE VARCHAR2 2 NO SALARY TABLE: The Salary Table as defined on the Salary/Rate Rule Form (NTRSALA) that will be associated with positions assigned to this Position Class and will default to the Position Form (NBAPOSN). NTRPCLS_GRADE VARCHAR2 5 YES SALARY GRADE: The Salary Grade, as defined on the Salary/Rate Rule Form (NTRSALA), that is associated with positions in this Position Class and will default to the Position Form (NBAPOSN) when this Position Class is entered NTRPCLS_STEP NUMBER 3 YES SALARY STEP: The Salary Step associated with the Salary Grade as defined on the Salary/Rate Rule Form (NTRSALA) that will be associated with positions assigned to this Position Class. NTRPCLS_ACTIVITY_DATE DATE 7 NO ACTIVITY DATE: Date when the record was inserted or last modified. NTRPCLS_SGRP_CODE VARCHAR2 6 NO SGRP_CODE: The salary group code. NTRPCLS_PGRP_CODE VARCHAR2 4 YES PGRP_CODE: The position group code. NTRPCLS_CHANGE_DATE_TIME DATE 7 NO CHANGE DATE TIME: Date and Time this record was changed. NTRPCLS_CALIF_TYPE VARCHAR2 1 YES CALIFORNIA TYPE: California Pension position class type. NTRPCLS_STRS_FTE NUMBER 6 2 YES STRS FTE: California STRS FTE hours. NTRPCLS_PERS_HOURS NUMBER 6 2 YES PERS HOURS: California PERS Work Schedule hours. NTRPCLS_STRS_BASE_HRS NUMBER 7 2 YES STRS part-time employee base hours NTRPCLS_BARG_CODE VARCHAR2 2 YES BARGAINING UNIT CODE: Code associated with this Position Class. NTRPCLS_PROBATION_UNITS NUMBER 3 YES PROBATION UNITS: Identifies the number of Probationary Units. Date: 18-DEC-2008 Page: 126 =================================================================================================================== Column Name Type Width Scale Nulls Column Comments =============================== ========= ===== ===== ======= ===================================================== NTRPCLS_ACCRUE_SENIORITY_IND VARCHAR2 1 NO ACCRUE SENIORITY INDICATOR:This indicator will denote whether seniority hours are accrued against the position class code. |
|
|
Shego
Starting Member
7 Posts |
Posted - 2013-05-24 : 14:25:26
|
And the script I'm running currently looks like this:select spriden_id , nbrjobs_desc , min(nbbposn_grade),min(nbbposn_step) , min(ntrsala_amount) , max(nbbposn_grade),max(nbbposn_step) , max(ntrsala_amount) , nbbposn_grade, nbbposn_step , ntrsala_amountfrom spriden,nbrjobs,ntrsala,ntrpcls,nbbposnwhere spriden_pidm=nbrjobs_pidmand nbrjobs_sgrp_code=ntrsala_sgrp_codeand ntrpcls_code=nbbposn_pcls_codeand nbbposn_posn=nbrjobs_posnand ntrpcls_table=nbbposn_tableand ntrpcls_grade=nbbposn_gradeand ntrpcls_step=nbbposn_stepand nbbposn_table=ntrsala_tableand nbbposn_grade=ntrsala_gradeand nbbposn_step=ntrsala_stepgroup by spriden_id,nbrjobs_desc,nbbposn_grade,nbbposn_step,ntrsala_amountorder by spriden_id I've tried combining the tables through inner joins, but this 1:1 connection format actually works better. |
|
|
MIK_2008
Master Smack Fu Yak Hacker
1054 Posts |
Posted - 2013-05-24 : 14:57:43
|
Making things even more difficult to find out the desired information we're looking for. Let me make it easier for you .. Provide sample data for the folowing table's fields (for each table seperately) in consumable format. By consumable we means it should be in the form of "insert statments" so that we can quickly create temporary tables and use the insert statments to add the data into it. person: person_pidm, person_namejob: job_pidm, job_description, job_sgrp_codesalary: salary_sgrp_code, salary_amount, salary_amountProvide a full set of sample data for an employee (representing the real data as that of what you have in tables) e.g. Take the example of "JohnSmith", provide all records for this employee in Person table (logically it should be one record) and corresponding all records in Job table (for John Smith) and all records in Salary table (for John Smith). You can get this sample data using the following query SELECT person_pidm, person_name FROM Person WHERE person_pidm=?? and Person_Name='John Smith'SELECT job_pidm, job_description, job_sgrp_code FROM Job WHERE job_pidm=?? -- Value for ?? should be the PIDM of the Person table which belongs to John SmithSELECT salary_sgrp_code, salary_amount, salary_amount FROM Job WHERE salary_sgrp_code IN (??) --value for ?? should be the srgp_code that of the Job table. There could be one/multiple codes, if multiple pass in all the codes comma separated to the IN clauseProvide the result of the above three queries in consumable format. And then also suggest the desired ouput you think it should have.CheersMIK |
|
|
bitsmed
Aged Yak Warrior
545 Posts |
Posted - 2013-05-24 : 21:31:46
|
Looks like the joins might be off. As others have suggested, sample data and expected output would help us point you in the right direction.My best guess would be that you're looking for something like thisselect a.spriden_id ,b.nbrjobs_desc ,f.min_nbbposn_grade ,f.min_nbbposn_step ,f.min_ntrsala_amount ,f.max_nbbposn_grade ,f.max_nbbposn_step ,f.max_ntrsala_amount ,d.nbbposn_grade ,d.nbbposn_step ,c.ntrsala_amount from spriden as a inner join nbrjobs as b on b.nbrjobs_pidm=a.spriden_pidm inner join ntrsala as c on c.ntrsala_table=b.nbrjobs_sal_table and c.ntrsala_grade=b.nbrjobs_sal_grade and c.ntrsala_step=b.nbrjobs_sal_step and c.ntrsala_sgrp_code=b.nbrjobs_sgrp_code inner join nbbposn as d on d.nbbposn_posn=c.nbrjobs_posn and d.nbbposn_table=c.ntrsala_table and d.nbbposn_grade=c.ntrsala_grade and d.nbbposn_step=c.ntrsala_step and d.ntrsala_sgrp_code=c.nbrjobs_sgrp_code inner join ntrpcls as e on e.ntrpcls_code=d.nbbposn_pcls_code and e.ntrpcls_table=d.nbbposn_table and e.ntrpcls_grade=d.nbbposn_grade and e.ntrpcls_step=d.nbbposn_step and e.ntrpcls_sgrp_code=d.nbbposn_sgrp_code and e.ntrpcls_pgrp_code=d.nbbposn_pgrp_code inner join (select a.spriden_id from spriden as a ,min(d.nbbposn_grade) as min_nbbposn_grade ,min(d.nbbposn_step) as min_nbbposn_step ,min(c.ntrsala_amount) as min_ntrsala_amount ,max(d.nbbposn_grade) as max_nbbposn_grade ,max(d.nbbposn_step) as max_nbbposn_step ,max(c.ntrsala_amount) as max_ntrsala_amount inner join nbrjobs as b on b.nbrjobs_pidm=a.spriden_pidm inner join ntrsala as c on c.ntrsala_table=b.nbrjobs_sal_table and c.ntrsala_grade=b.nbrjobs_sal_grade and c.ntrsala_step=b.nbrjobs_sal_step and c.ntrsala_sgrp_code=b.nbrjobs_sgrp_code inner join nbbposn as d on d.nbbposn_posn=c.nbrjobs_posn and d.nbbposn_table=c.ntrsala_table and d.nbbposn_grade=c.ntrsala_grade and d.nbbposn_step=c.ntrsala_step and d.ntrsala_sgrp_code=c.nbrjobs_sgrp_code group by a.spriden_id ) as f on f.spriden_id=a.spriden_id Looking at description of field NBRJOBS_USER_ID, it looks like you're using Oracle db. This forum is for MSSQL db, so my suggestion may not work at all for you. |
|
|
|
|
|
|
|