sql - Case Statement returning values in multiple rows, even with Group By statement - Stack Overflow

admin管理员组

文章数量:1317897

I am trying to write a basic query to pull employee dates.

The Employee_Master table has things like Employee number, name, address,etc.

There is an Employee_Dates table that has columns for Employee Number, Date Type, Date, etc. Date types can equal things like "BRTH", "HIRE", "TERM", etc.

The Employee Dates table looks something like this:

Employee_NO	Date_Type	Employee Date
1234	BRTH	01/01/1970
1234	HIRE	01/01/2010
9876	BRTH	01/01/1985
9876	HIRE	01/01/2015

I am trying to write a basic query to pull employee dates.

The Employee_Master table has things like Employee number, name, address,etc.

There is an Employee_Dates table that has columns for Employee Number, Date Type, Date, etc. Date types can equal things like "BRTH", "HIRE", "TERM", etc.

The Employee Dates table looks something like this:

Employee_NO	Date_Type	Employee Date
1234	BRTH	01/01/1970
1234	HIRE	01/01/2010
9876	BRTH	01/01/1985
9876	HIRE	01/01/2015

The Employee_Dates table includes other date types, but I am just interested in Hire and BRTH at this time.

I am trying to pull a query that pulls Employee ID First Name, Last Name, Birth Date, and Hire Date.

I am using a CASE statement to pull the dates from the dates table. When I first did this, it returned everything correctly, except it returned the Birth Date and Hire Dates in separate rows. I tried the group by statement below in an effort to get the data into a single row, however it is still returning data in separate rows.

The results I am getting look something like:

EMPID	LAST	First	Birthdate	Hire
1234	Appleseed	Johnny	01/01/1970
1234	Appleseed	Johnny		01/01/2010

Is a CASE statement the wrong thing to use to pull info from the Employee_Dates table? Am I using the Group By incorrectly?

    SELECT EMPLOYEE_MASTER.EMPLOYEE_NO AS EMPID,
           EMPLOYEE_MASTER.LAST_NAME   AS Last,
           EMPLOYEE_MASTER.FIRST_NAME  AS First,
           CASE
             WHEN HRS.EMPLOYEE_Dates.Date_Type = 'BRTH' THEN HRS.EMPLOYEE_DATES.EMPLOYEE_DATE
           END                         AS BIRTHDATE,
           CASE
             WHEN HRS.EMPLOYEE_Dates.Date_Type = 'HIRE' THEN HRS.EMPLOYEE_DATES.EMPLOYEE_DATE
           END                         AS HIRE
    FROM   EMPLOYEE_MASTER
           INNER JOIN EMPLOYEE_DATES
                   ON EMPLOYEE_MASTER.EMPLOYEE_NO = EMPLOYEE_DATES.EMPLOYEE_NO
    WHERE  HRS.EMPLOYEE_MASTER.EMPLOYMENT_STATUS IN ( 'Y', 'RR', 'LA' )
    GROUP  BY EMPLOYEE_MASTER.EMPLOYEE_NO,
              EMPLOYEE_MASTER.LAST_NAME,
              EMPLOYEE_MASTER.FIRST_NAME,
              CASE
                WHEN HRS.EMPLOYEE_Dates.Date_Type = 'BRTH' THEN HRS.EMPLOYEE_DATES.EMPLOYEE_DATE
              END,
              CASE
                WHEN HRS.EMPLOYEE_Dates.Date_Type = 'HIRE' THEN HRS.EMPLOYEE_DATES.EMPLOYEE_DATE
              END;

    ORDER BY EMPLOYEE_MASTER.LAST_NAME

• sql

Share Improve this question Follow edited Jan 22 at 19:58 marc_s 756k184184 gold badges1.4k1.4k silver badges1.5k1.5k bronze badges asked Jan 22 at 16:42 Patrick McKinneyPatrick McKinney 911 bronze badge

Add a comment  | 

2 Answers 2

Sorted by: Reset to default 0

Your query is almost correct, but you need to use subqueries to flatten the rows, and not GROUP BY:

SELECT EMPLOYEE_MASTER.EMPLOYEE_NO AS EMPID,
       EMPLOYEE_MASTER.LAST_NAME   AS Last,
       EMPLOYEE_MASTER.FIRST_NAME  AS First,
       birth.employee_date         BIRTHDATE,
       hire.employee_date          HIRE,
FROM   EMPLOYEE_MASTER
       LEFT JOIN (SELECT employee_date,
                         employee_no
                  FROM   EMPLOYEE_DATES
                  WHERE  EMPLOYEE_Dates.Date_Type = 'BRTH') birth
              ON EMPLOYEE_MASTER.EMPLOYEE_NO = birth.EMPLOYEE_NO
       LEFT JOIN (SELECT employee_date,
                         employee_no
                  FROM   EMPLOYEE_DATES
                  WHERE  EMPLOYEE_Dates.Date_Type = 'HIRE') hire
              ON EMPLOYEE_MASTER.EMPLOYEE_NO = hire.EMPLOYEE_NO
WHERE  HRS.EMPLOYEE_MASTER.EMPLOYMENT_STATUS IN ( 'Y', 'RR', 'LA' )
ORDER  BY EMPLOYEE_MASTER.LAST_NAME 

I'm not sure which SQL variant you're using, but you may have to tweak this to ensure proper use of the LEFT JOIN.

I think you are on correct path to use Left Join and Group By.

It would have been helpful if you provided some sample data for both the tables.

I created some sample data for EMPLOYEE_MASTER table assuming each employee has one row each and another sample data for EMPLOYEE_DATE based on what you mentioned in the question.

I have used SQL-server as an example as appropriate RDBMS is not tagged in the question.

CASE expression is used based on DATE_TYPE like you used and MAX is used to aggregate one value for each combination of EMPLOYEE_NO, LAST_NAME, FIRST_NAME

Sample Query :

SELECT 
EM.EMPLOYEE_NO AS EMPID,EM.LAST_NAME AS Last, EM.FIRST_NAME AS First,
MAX(CASE WHEN ED.DATE_TYPE = 'BRTH' THEN ED.EMPLOYEE_DATE END) AS BIRTHDATE,
MAX(CASE WHEN ED.DATE_TYPE = 'HIRE' THEN ED.EMPLOYEE_DATE END) AS HIRE
  
FROM EMPLOYEE_MASTER EM
LEFT JOIN EMPLOYEE_DATES ED 
  
ON EM.EMPLOYEE_NO = ED.EMPLOYEE_NO
WHERE  EM.EMPLOYMENT_STATUS IN ('Y', 'RR', 'LA')
GROUP BY  EM.EMPLOYEE_NO, EM.LAST_NAME, EM.FIRST_NAME
ORDER BY  EM.EMPLOYEE_NO;

Demo Fiddle

Output

EMPID	last	first	BIRTHDATE	HIRE
1234	Appleseed	Johnny	1970-01-01	2010-01-01
9876	LastName	FirstName	1985-01-01	2015-01-01

本文标签： sqlCase Statement returning values in multiple rowseven with Group By statementStack Overflow