SQL - Return Last Update Date and Updated By

Guys,

I was told to come up with a solution for the following scenario.

I need to return the "Last Update Date" and "Last Updated By" columns based on the "EmployeeID". The query must search the Parent and all Child tables for the latest entry and return the columns grouped by ID. I'm looking for a solution without using UNION ALL and one that handles the scenario where the latest record can be entered by two different users on the same EmployeeID (Foreign or Primary key) on different tables. E.g. Jim updated the Employee table's EmployeeID 1 today and Paul also updated the Employee_Address table for EmployeeID 1. Based on that EmployeeID 1 has two records with a last update date of today which was entered by different users.

See schema and Union ALL script where I couldn't figure out the Last Updated By part of it.

CREATE TABLE EMPLOYEE
(
  EMPLOYEE_ID INT,
  NAME VARCHAR(100),
  LAST_UPDATED_BY VARCHAR(200),
  LAST_UPDATED_DT DATE
)

CREATE TABLE EMPLOYEE_AWARD
(
  AWARD_ID INT,
  EMPLOYEE_ID INT,
  AWARD_NAME VARCHAR(100),
  LAST_UPDATED_BY VARCHAR(200),
  LAST_UPDATED_DT DATE
)

CREATE TABLE EMPLOYEE_ADDRESS
(
  ADDRESS_ID INT,
  EMPLOYEE_ID INT,
  "ADDRESS" VARCHAR(100),
  LAST_UPDATED_BY VARCHAR(200),
  LAST_UPDATED_DT DATE
)


INSERT INTO EMPLOYEE
      VALUES      (1,'Paul Smith', 'GomesR', '6/22/2015'),
                  (2,'Francis Jones', 'KellyR', '5/20/2013'),
                  (3,'Paul Bo', 'GomesR', '11/09/2015'),
                  (4,'Ed Li', 'TurnerR', '1/19/2012')


INSERT INTO EMPLOYEE_AWARD
      VALUES      (10,1,'Employee of the Month', 'GomesR', '6/22/2015'),
                  (20,3,'Employee of the Month', 'KellyR', '5/20/2013'),
                  (30,1,'Employee of the Quater', 'GomesR', '11/09/2015'),
                  (40,3,'Employee of the Month', 'TurnerR', '1/19/2012'),
                  (50,2,'Employee of the Month', 'KellyR', '11/11/2011')


INSERT INTO EMPLOYEE_ADDRESS

      VALUES      (100,1,'Address 1','GomesR', '6/25/2015'),
                  (200,2,'Address 2','TurnerR', '5/28/2015'),
                  (300,3,'Address 3','TurnerR', '11/20/2015'),
                  (400,4,'Address 4','GomesR', '6/14/2014')
                  


SELECT            D.EMPLOYEE_ID, Max(D.LAST_UPDATED_DT) LAST_UPDATED_DT--, D.LAST_UPDATED_BY
FROM            EMPLOYEE A

INNER JOIN      (      SELECT            EMPLOYEE_ID, LAST_UPDATED_DT, LAST_UPDATED_BY
                        FROM            EMPLOYEE
                        UNION ALL
                        SELECT            EMPLOYEE_ID, LAST_UPDATED_DT, LAST_UPDATED_BY
                        FROM            EMPLOYEE_AWARD
                        UNION ALL
                        SELECT            EMPLOYEE_ID, LAST_UPDATED_DT, LAST_UPDATED_BY
                        FROM            EMPLOYEE_ADDRESS
                        
                        ) AS D

ON A.EMPLOYEE_ID = D.EMPLOYEE_ID
GROUP BY D.EMPLOYEE_ID
King3niAsked:
Who is Participating?
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.

Anoo S PillaiCommented:
Probably you could use windows function ROW_NUMBER in SQL Server for this.
From the query provided, I think you are not that much familiar with SQL. If you are stuck - post back. I could support you.
0
King3niAuthor Commented:
Hey Anoo, Yes. Please provide support.
0
Anoo S PillaiCommented:
To start with - If my understanding is correct , for each employee you have to take the latest record based on LAST_UPDATED_D
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

King3niAuthor Commented:
Yes and who updated the record
0
Anoo S PillaiCommented:
Say for example , for the table EMPLOYEE_AWARD,  the rows under consideration should be

SELECT * FROM 
( SELECT * ,  ROW_NUMBER ()  OVER ( PARTITION BY (EMPLOYEE_ID  ) ORDER BY LAST_UPDATED_DT ) RowNum
  FROM EMPLOYEE_AWARD ) EA_Latest
  WHERE RowNum = 1 

Open in new window

NOTE:- BTW, just noted your comment "I'm looking for a solution without using UNION ALL " - why you choose to NOT use UNION ALL ???
0
King3niAuthor Commented:
I was asked to come up with a solution without using union all.
0
Anoo S PillaiCommented:
Could you please have a look on the following SQL statement

SELECT * 
FROM  ( SELECT	* , 
		ROW_NUMBER ()  OVER ( PARTITION BY (EMPLOYEE_ID  ) ORDER BY LAST_UPDATED_DT ) RowNum
		FROM 
		( SELECT 1 SourceTabl, EMPLOYEE_ID , LAST_UPDATED_BY , LAST_UPDATED_DT  FROM EMPLOYEE
		UNION ALL 
		SELECT 2 SourceTabl, EMPLOYEE_ID , LAST_UPDATED_BY , LAST_UPDATED_DT FROM EMPLOYEE_AWARD
		UNION ALL
		SELECT 3 SourceTabl, EMPLOYEE_ID , LAST_UPDATED_BY , LAST_UPDATED_DT FROM EMPLOYEE_ADDRESS
		) TempTab1
) TempTab2 
WHERE RowNum = 1 
ORDER BY EMPLOYEE_ID

Open in new window

0

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
Anoo S PillaiCommented:
Clarification needed against your comment "I was asked to come up with a solution without using union all." - Do you know why ?
0
King3niAuthor Commented:
ROW_NUMBER ()  OVER ( PARTITION BY (EMPLOYEE_ID  ) ORDER BY LAST_UPDATED_DT DESC ) RowNum


Thanks for your prompt response.
0
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.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.