Need to return single row per ID across multiple tables

I'm getting better with this SQL stuff, but this one has defeated me. I have six tables to pull data from and need a single row for each employee. I can get the single row from the tables individually, but when I roll them in with the rest of the tables I'm back to multiple records again. The result I need is based on the MAX JobClassStartDate in JobInfo. A correct query will return only:

Smith, John Q | 349 | 2115.38 | PROD | Prod. Mgr. | 3629 | 1.1

Sample data:
CREATE TABLE dbo.Employees(
	EmpID int NOT NULL,
	CompID varchar(5) NOT NULL,
	BadgeNum varchar(20) NULL,
	StatusID varchar(10) NOT NULL,
	CONSTRAINT pk_Employees PRIMARY KEY (EmpID, CompID));

CREATE TABLE dbo.People(
	EmpID int NOT NULL PRIMARY KEY,
	FirstName varchar(40) NOT NULL,
	LastName varchar(40) NOT NULL,
	MiddleName varchar(40) NULL);

CREATE TABLE dbo.JobInfo(
	EmpID int NOT NULL,
	CompID varchar(5) NOT NULL,
	JobClassStartDate smalldatetime NOT NULL,
	JobClassID varchar(10) NOT NULL,
	WorkCompID varchar(10) NULL,
	CONSTRAINT pk_JobInfo PRIMARY KEY (EmpID, CompID, JobClassStartDate));

CREATE TABLE dbo.PayInfo(
	EmpID int NOT NULL,
	CompID varchar(5) NOT NULL,
	PayChangeDate smalldatetime NOT NULL,
	TypeID varchar(10) NOT NULL,
	HourlyRate money NULL,
	SalaryRate money NULL,
	CONSTRAINT pk_PayInfo PRIMARY KEY (EmpID, CompID, PayChangeDate));

CREATE TABLE dbo.JobClasses(
	CompID varchar(5) NOT NULL,
	JobClassID varchar(10) NOT NULL,
	JobClassDesc varchar(50) NOT NULL,
	EEOClassID varchar(10) NULL,
	CONSTRAINT pk_JobClasses PRIMARY KEY (CompID, JobClassID));

CREATE TABLE dbo.CompanyInfo(
	EmpID int NOT NULL,
	CompID varchar(5) NOT NULL,
	Level3ID varchar(10) NULL,
	CONSTRAINT pk_CompanyInfo PRIMARY KEY (EmpID, CompID));

INSERT INTO dbo.Employees(EmpID, CompID, BadgeNum, StatusID)
VALUES (21, 'FWM', '349', 'A');

INSERT INTO dbo.People(EmpID, FirstName, LastName, MiddleName)
VALUES (21, 'John', 'Smith', 'Q');

INSERT INTO dbo.JobInfo(EmpID, CompID, JobClassStartDate, JobClassID, WorkCompID)
VALUES(21, 'FWM', '20120101', 'Scheduling', '8810'),
(21, 'FWM', '20140225', 'ProdFC', '3629'),
(21, 'FWM', '20140519', 'Prod. Mgr', '3629'),
(21, 'FWM', '20141110', 'Prod. Mgr', '3629');

INSERT INTO dbo.PayInfo(EmpID, CompID, PayChangeDate, TypeID, HourlyRate, SalaryRate)
VALUES(21, 'FWM', '20120201', 'H', 16.00, 1280.00),
(21, 'FWM', '20120430', 'H', 18.00, 1440.00),
(21, 'FWM', '20121003', 'S', 26.44, 2115.38);

INSERT INTO dbo.JobClasses(CompID, JobClassID, JobClassDesc, EEOClassID)
VALUES('FWM', 'Prod. Mgr', 'Production Manager', '1.1'),
('FWM', 'ProdFC', 'Production Flow Coordinator', '8'),
('FWM', 'Scheduling', 'Scheduling', '6');

INSERT INTO dbo.CompanyInfo(EmpID, CompID, Level3ID)
VALUES(21, 'FWM', 'PROD')

Open in new window


This is the base query, which returns six rows. :
SELECT p.LastName + ', ' + p.FirstName + ' ' + ISNULL(p.MiddleName, '') AS Name,
       e.BadgeNum,
       epi.TypeID,
       CASE
           WHEN epi.TypeID = 'H'
           THEN MAX(epi.HourlyRate)
           WHEN epi.TypeID = 'S'
           THEN MAX(epi.SalaryRate)
       END AS PayRate,
       oi.Level3ID AS Dept,
       cj.JobClassID,
       ISNULL(cj.WorkCompID, '') AS WorkersCompID,
       mj.EEOClassID
FROM Employees AS e
     INNER JOIN People AS p ON p.EmpID = e.EmpID
     INNER JOIN JobInfo AS cj ON cj.EmpID = e.EmpID
                                      AND cj.CompID = e.CompID
     INNER JOIN PayInfo AS epi ON epi.EmpID = cj.EmpID
     INNER JOIN JobClasses AS mj ON mj.CompID = cj.CompID
                                      AND mj.JobClassID = cj.JobClassID
     INNER JOIN CompanyInfo AS oi ON oi.EmpID = cj.EmpID
                                               AND oi.CompID = e.CompID
WHERE( e.StatusID = 'A' )
GROUP BY p.LastName,
         p.FirstName,
         p.MiddleName,
         e.BadgeNum,
         epi.TypeID,
         cj.JobClassID,
         cj.WorkCompID,
         mj.EEOClassID,
         oi.Level3ID
ORDER BY p.LastName;

Open in new window

BAMAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Vitor MontalvãoMSSQL Senior EngineerCommented:
You can add a filter to return only the MAX(JobClassStartDate) for that employee but will return two records:
- one for Hourly Payrate and another one for Salary Payrate.
SELECT p.LastName + ', ' + p.FirstName + ' ' + ISNULL(p.MiddleName, '') AS Name,
       e.BadgeNum,
       epi.TypeID,
       CASE
           WHEN epi.TypeID = 'H' THEN MAX(epi.HourlyRate)
           WHEN epi.TypeID = 'S' THEN MAX(epi.SalaryRate)
       END AS PayRate,
       oi.Level3ID AS Dept,
       cj.JobClassID,
       ISNULL(cj.WorkCompID, '') AS WorkersCompID,
       mj.EEOClassID
FROM Employees AS e
     INNER JOIN People AS p ON p.EmpID = e.EmpID
     INNER JOIN JobInfo AS cj ON cj.EmpID = e.EmpID AND cj.CompID = e.CompID
     INNER JOIN PayInfo AS epi ON epi.EmpID = cj.EmpID
     INNER JOIN JobClasses AS mj ON mj.CompID = cj.CompID AND mj.JobClassID = cj.JobClassID
     INNER JOIN CompanyInfo AS oi ON oi.EmpID = cj.EmpID AND oi.CompID = e.CompID
WHERE e.StatusID = 'A'
AND cj.JobClassStartDate = (SELECT MAX(JobClassStartDate) 
				FROM JobInfo ji
				WHERE ji.EmpID=cj.EmpID)
GROUP BY p.LastName,
         p.FirstName,
         p.MiddleName,
         e.BadgeNum,
         epi.TypeID,
         cj.JobClassID,
         cj.WorkCompID,
         mj.EEOClassID,
         oi.Level3ID
ORDER BY p.LastName;

Open in new window

BAMAuthor Commented:
That's the problem. I don't want the hourly. In this case he was promoted from an hourly to a salary position, so I only want his current pay rate. The reason I have the CASE is because they want to see the hourly rate for hourly employees but the pay period rate for salaried. These are stored in separate fields in the table. I could just take the hourly rate for salaried individuals and multiply it by 80, but I still have to check each employee to see if they're hourly or salaried. I haven't been able to get a single row to return for employees who have changed from hourly to salary or for those who have had a change in their EEO Class due to a new job assignment.
Vitor MontalvãoMSSQL Senior EngineerCommented:
So, this don't work for you?
SELECT p.LastName + ', ' + p.FirstName + ' ' + ISNULL(p.MiddleName, '') AS Name,
       e.BadgeNum,
       epi.TypeID,
       MAX(epi.SalaryRate) AS PayRate,
       oi.Level3ID AS Dept,
       cj.JobClassID,
       ISNULL(cj.WorkCompID, '') AS WorkersCompID,
       mj.EEOClassID
FROM Employees AS e
     INNER JOIN People AS p ON p.EmpID = e.EmpID
     INNER JOIN JobInfo AS cj ON cj.EmpID = e.EmpID AND cj.CompID = e.CompID
     INNER JOIN PayInfo AS epi ON epi.EmpID = cj.EmpID
     INNER JOIN JobClasses AS mj ON mj.CompID = cj.CompID AND mj.JobClassID = cj.JobClassID
     INNER JOIN CompanyInfo AS oi ON oi.EmpID = cj.EmpID AND oi.CompID = e.CompID
WHERE e.StatusID = 'A'
    AND epi.TypeID = 'S' 
    AND cj.JobClassStartDate = (SELECT MAX(JobClassStartDate) 
			FROM JobInfo ji
			WHERE ji.EmpID=cj.EmpID)
GROUP BY p.LastName,
         p.FirstName,
         p.MiddleName,
         e.BadgeNum,
         epi.TypeID,
         cj.JobClassID,
         cj.WorkCompID,
         mj.EEOClassID,
         oi.Level3ID
ORDER BY p.LastName;

Open in new window

Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

BAMAuthor Commented:
Vitor,

I apologize for not making my problem more clear. I included only one employee who had been promoted from an hourly position to a salaried position, but I need to see all employees. That's why I had the CASE because I need to return the hourly rate for hourly employees and the pay period salary for salaried employees. I also found that I should have been pulling the WorkCompID from JobClasses, not JobInfo. What I need is to see only the information for each employee's current JobClassID and pay rate. I have added three additional employees so my original query (with the WorkCompID change) returns more than one row for two employees.

I get six rows for John Smith because he changed from hourly to salary and changed his JobClassID and EEOClassID. I only want:
Smith, John Q | 349 | 2115.38 | PROD | Prod. Mgr. | 3629 | 1.1

Mary Martin maintained the same hourly rate and EEOClassID, but her JobClassID changed so I'm getting two records for her instead of only the latest one, which is CNCMill:
Martin, Mary | 682 | 18.50 | CNC| CNCLathe | 3629 | 6
Martin, Mary | 682 | 18.50 | CNC| CNCMill | 3629 | 6

CREATE TABLE dbo.Employees(
	EmpID int NOT NULL,
	CompID varchar(5) NOT NULL,
	BadgeNum varchar(20) NULL,
	StatusID varchar(10) NOT NULL,
	CONSTRAINT pk_Employees PRIMARY KEY (EmpID, CompID))

CREATE TABLE dbo.People(
	EmpID int NOT NULL PRIMARY KEY,
	FirstName varchar(40) NOT NULL,
	LastName varchar(40) NOT NULL,
	MiddleName varchar(40) NULL)

CREATE TABLE dbo.JobInfo(
	EmpID int NOT NULL,
	CompID varchar(5) NOT NULL,
	JobClassStartDate smalldatetime NOT NULL,
	JobClassID varchar(10) NOT NULL
	CONSTRAINT pk_JobInfo PRIMARY KEY (EmpID, CompID, JobClassStartDate))

CREATE TABLE dbo.PayInfo(
	EmpID int NOT NULL,
	CompID varchar(5) NOT NULL,
	PayChangeDate smalldatetime NOT NULL,
	TypeID varchar(10) NOT NULL,
	HourlyRate money NULL,
	SalaryRate money NULL,
	CONSTRAINT pk_PayInfo PRIMARY KEY (EmpID, CompID, PayChangeDate))

CREATE TABLE dbo.JobClasses(
	CompID varchar(5) NOT NULL,
	JobClassID varchar(10) NOT NULL,
	JobClassDesc varchar(50) NOT NULL,
	EEOClassID varchar(10) NULL,
	WorkCompID varchar(10) NULL
	CONSTRAINT pk_JobClasses PRIMARY KEY (CompID, JobClassID))

CREATE TABLE dbo.CompanyInfo(
	EmpID int NOT NULL,
	CompID varchar(5) NOT NULL,
	Level3ID varchar(10) NULL,
	CONSTRAINT pk_CompanyInfo PRIMARY KEY (EmpID, CompID))

INSERT INTO dbo.Employees(EmpID, CompID, BadgeNum, StatusID)
VALUES (21, 'FWM', '349', 'A'),
(106, 'FWM', '682', 'A'),
(194, 'FWM', '838', 'A'),
(320, 'FWM', '899', 'A')

INSERT INTO dbo.People(EmpID, FirstName, LastName, MiddleName)
VALUES (21, 'John', 'Smith', 'Q'),
(106, 'Mary', 'Martin', ''),
(194, 'Bob', 'Jones', ''),
(320, 'Jane', 'Doe', 'J')

INSERT INTO dbo.JobInfo(EmpID, CompID, JobClassStartDate, JobClassID)
VALUES(21, 'FWM', '20120101', 'Scheduling'),
(21, 'FWM', '20140225', 'ProdFC'),
(21, 'FWM', '20140519', 'Prod. Mgr'),
(21, 'FWM', '20141110', 'Prod. Mgr'),
(106, 'FWM', '20120101', 'CNCLathe'),
(106, 'FWM', '20120319', 'CNCMill'),
(106, 'FWM', '20120820', 'CNCMill'),
(106, 'FWM', '20140625', 'CNCMill'),
(194, 'FWM', '20120403', 'CNCLathe'),
(194, 'FWM', '20120814', 'CNCLathe'),
(194, 'FWM', '20120910', 'CNCLathe'),
(320, 'FWM', '20130225', 'Prog Mgr'),
(320, 'FWM', '20130913', 'CRBCA')

INSERT INTO dbo.PayInfo(EmpID, CompID, PayChangeDate, TypeID, HourlyRate, SalaryRate)
VALUES(21, 'FWM', '20120201', 'H', 16.00, 1280.00),
(21, 'FWM', '20120430', 'H', 18.00, 1440.00),
(21, 'FWM', '20121003', 'S', 26.44, 2115.38),
(106, 'FWM', '20120101', 'H', 16.00, 1200.00),
(106, 'FWM', '20120430', 'H', 16.50, 1320.00),
(106, 'FWM', '20120806', 'H', 18.50, 1480.00),
(194, 'FWM', '20120403', 'H', 20.00, 1600.00),
(194, 'FWM', '20120530', 'H', 22.00, 1760.00),
(320, 'FWM', '20130225', 'S', 18.27, 1461.54)

INSERT INTO dbo.JobClasses(CompID, JobClassID, JobClassDesc, EEOClassID, WorkCompID)
VALUES ('FWM', 'CNCLathe', 'CNC LATHE MACHINIST', '6', '3629'),
('FWM', 'CNCMill', 'CNC MILL MACHINIST', '6', '3629'),
('FWM', 'CRB-CA', 'Contract Review Board - Contracts Administrator', '5', '8810'),
('FWM', 'Prod. Mgr', 'Production Manager', '1.1', '3629'),
('FWM', 'ProdFC', 'Production Flow Coordinator', '8', '3629'),
('FWM', 'Prog Mgr', 'Program Manager', '1.1', '3629'),
('FWM', 'Scheduling', 'Scheduling', '6', '3629')

INSERT INTO dbo.CompanyInfo(EmpID, CompID, Level3ID)
VALUES(21, 'FWM', 'PROD'),
(106, 'FWM', 'CNC'),
(194, 'FWM', 'CNCLA'),
(320, 'FWM', 'OFFICE')

Open in new window

Vitor MontalvãoMSSQL Senior EngineerCommented:
Something that I don't understand is how to know when the employee has been promoted.
For example if you run the following query you should get the last star date in a job class for each employee:
SELECT j.EmpID, MAX(j.JobClassStartDate) LastStartDate
FROM JobInfo J
GROUP BY j.EmpID

Open in new window


But then when you run this one, that should return all payments after the last promotion, it returns zero records. Can you confirm that your data is correct?
SELECT *
FROM PayInfo P 
WHERE p.PayChangeDate > (SELECT MAX(j.JobClassStartDate)
						FROM JobInfo j
						WHERE j.EmpID=p.EmpID AND j.CompID=p.CompID)

Open in new window

BAMAuthor Commented:
The data is correct. The problem is that a JobClass change does not always mean there was also a pay change. Employee 320 started a new job on 9-13-2013, but remained at the same salary, so the pay change date remained at 2-25-2013.
Vitor MontalvãoMSSQL Senior EngineerCommented:
Ok, I understood.
I used a Common Table Expression (CTE) to solve your problem:
;WITH CTE_LastPayChangeDate AS
	(SELECT	EmpID, MAX(PayChangeDate) AS LastPayChangeDate
	FROM PayInfo
	GROUP BY EmpID)
SELECT p.LastName + ', ' + p.FirstName + ' ' + ISNULL(p.MiddleName, '') AS Name,
       e.BadgeNum,
       epi.TypeID,
       CASE
           WHEN epi.TypeID = 'H' THEN epi.HourlyRate
           WHEN epi.TypeID = 'S' THEN epi.SalaryRate
       END AS PayRate,
       oi.Level3ID AS Dept,
       cj.JobClassID,
       ISNULL(cj.CompID, '') AS WorkersCompID,
       mj.EEOClassID
FROM Employees AS e
     INNER JOIN People AS p ON p.EmpID = e.EmpID
     INNER JOIN JobInfo AS cj ON cj.EmpID = e.EmpID AND cj.CompID = e.CompID
     INNER JOIN PayInfo AS epi 
	INNER JOIN CTE_LastPayChangeDate AS pcd ON epi.EmpID=pcd.EmpID AND epi.PayChangeDate=pcd.LastPayChangeDate
     ON epi.EmpID = cj.EmpID
     INNER JOIN JobClasses AS mj ON mj.CompID = cj.CompID AND mj.JobClassID = cj.JobClassID
     INNER JOIN CompanyInfo AS oi ON oi.EmpID = cj.EmpID AND oi.CompID = e.CompID
WHERE e.StatusID = 'A'
    AND cj.JobClassStartDate = (SELECT MAX(JobClassStartDate) 
			FROM JobInfo ji
			WHERE ji.EmpID=cj.EmpID)
ORDER BY p.LastName;

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
BAMAuthor Commented:
Vitor,

This is very close to what I'm after. I tried CTE and thought it was probably the best way to go, but I couldn't get mine to work and yours is almost there. I had to change this line:
ISNULL(cj.CompID, '') AS WorkersCompID,

Open in new window

to this:
ISNULL(mj.WorkCompID, '') AS WorkersCompID,

Open in new window

because it was returning the Company ID instead of the Worker's Comp ID, but that didn't impact the way the query works. The problem now is that one of the four employees is not showing up. The only difference I see between her and the others is that when her Job Class changed, both her WorkersCompID and her EEOClassIDs changed where with the others only one (or none) changed. I think I can figure out how to fix this, but I'm going to leave this open for another day or two in case I find that I can't fix it and will need more help.

Thanks much for your efforts so far.
Vitor MontalvãoMSSQL Senior EngineerCommented:
Which employee isn't returned by the query?
BAMAuthor Commented:
The query returns:
Name            BadgeNum  TypeID  PayRate  Dept    JobClassID  WorkersCompID  EEOClassID
Jones, Bob      838       H       22.00    CNCLA   CNCLathe    3629           6
Martin, Mary    682       H       18.50    CNC     CNCMill     3629           6
Smith, John Q   349       S       2115.38  PROD    Prod. Mgr   3629           1.1

Missing is:
Name            BadgeNum  TypeID  PayRate  Dept    JobClassID  WorkersCompID  EEOClassID
Doe, Jane J     899       S       1461.54  OFFICE  CRBCA       8810           5

Open in new window


Jane's previous ClassID was Prog Mgr. Her WCID was 3629 and her EEOID was 1.1. All three of these changed when she started a new job. Her salary did not change.
Vitor MontalvãoMSSQL Senior EngineerCommented:
That's because your data have a typo.
Check for JobClassID in JobClasses table: CRB-CA

And the JobClassID in JobInfo; CRBCA

So, when you join both tables that record doesn't match:
INNER JOIN JobClasses AS mj ON mj.CompID = cj.CompID AND mj.JobClassID = cj.JobClassID
BAMAuthor Commented:
Duh! Sorry about the typo. With that fixed, this worked perfectly on my sample database and also on the real one with many more records. Thanks much for your help on this.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Databases

From novice to tech pro — start learning today.