SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 SQLdeveloper question (possibly) with MAX(value)
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Shego
Starting Member

USA
7 Posts

Posted - 05/22/2013 :  19:43:21  Show Profile  Reply with Quote
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, salary
WHERE person_pidm=job_pidm
AND job_sgrp_code=salary_sgrp_code
GROUP BY person_name,person_pidm,job_description,job_sgrp_code

This 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

India
52325 Posts

Posted - 05/23/2013 :  00:25:28  Show Profile  Reply with Quote
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 MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

Transact Charlie
Flowing Fount of Yak Knowledge

United Kingdom
3451 Posts

Posted - 05/23/2013 :  04:38:32  Show Profile  Visit Transact Charlie's Homepage  Reply with Quote
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 Charlie
Msg 3903.. The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION.
http://nosqlsolution.blogspot.co.uk/

Edited by - Transact Charlie on 05/23/2013 04:44:48
Go to Top of Page

Shego
Starting Member

USA
7 Posts

Posted - 05/23/2013 :  13:28:07  Show Profile  Reply with Quote
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....maxSal
JohnSmith.....11111.....cook.......88888....12000.....038000
JaneSmith.....11112.....shepherd 44444....10000.....020000
JoeySmith.....11113.....pirate.....77777.....45000....120000
JackSmith.....11114.....driver.....66666.....17000.....027000


but what I'm getting is this:
name............pidm......job..........sgrp......minSal....maxSal
JohnSmith.....11111.....cook.......88888....10000.....030000
JaneSmith.....11112.....shepherd.44444....10000.....030000
JoeySmith.....11113.....pirate.....77777....10000.....030000
JackSmith.....11114.....driver.....66666.....10000.....030000

Edit: Further, if I ask for the grade and step, they also reflect as the same, no matter what the sgrp_code is.

Edited by - Shego on 05/23/2013 13:38:20
Go to Top of Page

MuMu88
Aged Yak Warrior

547 Posts

Posted - 05/23/2013 :  14:14:01  Show Profile  Reply with Quote
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.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 05/24/2013 :  00:29:44  Show Profile  Reply with Quote
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 format

http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

Shego
Starting Member

USA
7 Posts

Posted - 05/24/2013 :  14:11:45  Show Profile  Reply with Quote
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';
Go to Top of Page

Shego
Starting Member

USA
7 Posts

Posted - 05/24/2013 :  14:13:14  Show Profile  Reply with Quote
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.

Edited by - Shego on 05/24/2013 14:27:47
Go to Top of Page

Shego
Starting Member

USA
7 Posts

Posted - 05/24/2013 :  14:14:24  Show Profile  Reply with Quote
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.
 
Go to Top of Page

Shego
Starting Member

USA
7 Posts

Posted - 05/24/2013 :  14:22:51  Show Profile  Reply with Quote
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.

Edited by - Shego on 05/24/2013 14:29:32
Go to Top of Page

Shego
Starting Member

USA
7 Posts

Posted - 05/24/2013 :  14:25:26  Show Profile  Reply with Quote
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_amount
from spriden,nbrjobs,ntrsala,ntrpcls,nbbposn

where spriden_pidm=nbrjobs_pidm
and nbrjobs_sgrp_code=ntrsala_sgrp_code
and ntrpcls_code=nbbposn_pcls_code
and nbbposn_posn=nbrjobs_posn
and ntrpcls_table=nbbposn_table
and ntrpcls_grade=nbbposn_grade
and ntrpcls_step=nbbposn_step
and nbbposn_table=ntrsala_table
and nbbposn_grade=ntrsala_grade
and nbbposn_step=ntrsala_step

group by spriden_id,nbrjobs_desc,nbbposn_grade,nbbposn_step,ntrsala_amount
order by spriden_id


I've tried combining the tables through inner joins, but this 1:1 connection format actually works better.
Go to Top of Page

MIK_2008
Flowing Fount of Yak Knowledge

Pakistan
1054 Posts

Posted - 05/24/2013 :  14:57:43  Show Profile  Reply with Quote
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_name
job: job_pidm, job_description, job_sgrp_code
salary: salary_sgrp_code, salary_amount, salary_amount

Provide 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 Smith
SELECT 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 clause


Provide the result of the above three queries in consumable format. And then also suggest the desired ouput you think it should have.

Cheers
MIK
Go to Top of Page

bitsmed
Constraint Violating Yak Guru

422 Posts

Posted - 05/24/2013 :  21:31:46  Show Profile  Reply with Quote
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 this

select 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.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.4 seconds. Powered By: Snitz Forums 2000