Assistance with this query

Question

Assistance with this query

Viewing 5 posts - 16 through 20 (of 20 total)

You must be logged in to reply to this topic. Login to reply

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 1

You will need to apply similar logic to all tables which have duplicate rows per employeeid. I'd guess that you need the most recent rows containing org.ORGANIZATIONID and job.JOBTITLEID. Can you figure this out or would you like some help?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

tmac25 SSC Enthusiast Points: 184 More actions · Answer 2

ChrisM@Work (3/26/2013)
You will need to apply similar logic to all tables which have duplicate rows per employeeid. I'd guess that you need the most recent rows containing org.ORGANIZATIONID and job.JOBTITLEID. Can you figure this out or would you like some help?

Hey Chris, sorry for the delayed response, it was 3 in the morning. 🙂

Your help has been invaluable. I will take it from here now I have a good idea of what logic I should follow to differentiate the records.

Thanks!

tmac25 SSC Enthusiast Points: 184 More actions · Answer 3

Hey Chris (or anyone!),

Looks like I am have jumped the gun on my previous statement, as I'm having a bit of trouble with my new modified queries weeding out duplicate records polluting my results.

Just to give a quick rundown: I have 3 tables (EMPLOYEEAM, AUEMPLOYEEORGANIZATION, AUEMPLOYEE), the first one houses all my active employees and the unique key is personid. Both "AU" tables are transactions that show the latest update to the employee via their employeeid (AUEMPLOYEE also houses a personid column to have a relationship with EMPLOYEEAM) and the same goes for the AUEMPLOYEORGANIZATION (Does NOT have a personid, only employeeid).

Basically what I need to do is:

1.) Get personid from EMPLOYEEAM

2.) Get latest employeeid via most recent ID using EMPLOYEEAM.personid = AUEMPLOYEE.personid

3.) Get latest organizationid via most recent ID using AUEMPLOYEE.employeeid = AUORGANIZATION.employeeid

My results: http://i.imgur.com/48PCkDE.png (as you can see, all 3 columns have repeating data.

My code is as follows:

;WITH Employees AS (

SELECT DISTINCT

personid

FROM linkedServer.linkedDB.dbo.EMPLOYEEAM as employee

WHERE employee.ENDTIME IS NULL -- still active

)

SELECT DISTINCT

e.*,

emp.employeeid,

org.organizationid

FROM Employees e

LEFT OUTER JOIN (

SELECT DISTINCT

personid,

employeeid

FROM linkedServer.linkedDB.dbo.AUEMPLOYEE) as emp

ON e.PERSONID = emp.personid

LEFT OUTER JOIN (

SELECT DISTINCT

employeeid,

organizationid

FROM linkedServer.linkedDB.dbo.AUEMPLOYEEORGANIZATION) as org

ON emp.employeeid = org.employeeid

ORDER BY emp.employeeid DESC

tmac25 SSC Enthusiast Points: 184 More actions · Answer 4

tmac25

SSC Enthusiast

Points: 184

March 29, 2013 at 8:20 pm

#1602227

Bump, still working at this. 🙂

Thanks!

Jeff Moden SSC Guru Points: 1004641 More actions · Answer 5

tmac25 (3/29/2013)
Bump, still working at this. 🙂
Thanks!

"Bump" indeed. Instead of going back and forth, over and over, post some data and tables for us to use so we can do it right the first time. Don't assume that you know how to do that correctly. See the article at the first link in my signature line below for how to help us help you the best.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)