March 25, 2013 at 5:01 pm
I'm a bit new to SQL, and I'm attempting my first real project. Currently, my setup is: SQL Server 2008 R2 with a linked SQL Server 2000.
A couple points that I'm having issues comprehending:
I'm attempting to make a hierarchy from a SQL Server that houses are workforce software and place it into my SQL Server 2008 R2 to be utilized in another application. I want to grab all active employees (ENDDATE IS NULL) with the most recent ACTIONDATE of each active EMPLOYEEID.
The query I'm attempting to run is:
SELECT DISTINCT employee.EMPLOYEEID, employee.FIRSTNAME, employee.LASTNAME, employee.STARTDATE, employee.EMAILADDRESS, org.ORGANIZATIONID, job.JOBTITLEID
FROM linkedServer.linkedDB.dbo.AUEMPLOYEE as employee
RIGHT JOIN linkedServer.linkedDB.dbo.AUEMPLOYEEORGANIZATION as org
ON employee.employeeid = org.employeeid
RIGHT JOIN linkedServer.linkedDB.dbo.AUEMPLOYEEJOBTITLE as job
ON employee.employeeid = job.employeeid
WHERE employee.ENDDATE IS NULL
March 25, 2013 at 10:45 pm
Try something like this
SELECT employee.EMPLOYEEID, employee.FIRSTNAME, employee.LASTNAME, employee.STARTDATE, employee.EMAILADDRESS, org.ORGANIZATIONID, job.JOBTITLEID
FROM dbo.AUEMPLOYEEJOBTITLE as job
CROSS APPLY
(SELECT TOP 1 emp.employeeid, emp.FIRSTNAME, emp.LASTNAME, emp.STARTDATE, emp.EMAILADDRESS, emp.ENDDATE
FROM dbo.AUEMPLOYEE emp
INNER JOIN dbo.AUEMPLOYEEJOBTITLE empjob
ON emp.EMPLOYEEID = empjob.employeeid
ORDER BY ACTIONDATE) employee
RIGHT JOIN dbo.AUEMPLOYEEORGANIZATION as org
ON employee.employeeid = org.employeeid
WHERE employee.ENDDATE IS NULL
March 25, 2013 at 10:47 pm
Also check that you're using an account that is db_owner on the linked server db. There's a known issue when querying across linked servers and statistics permissions. Post the execution plan if you need it to be faster.
March 25, 2013 at 11:28 pm
Try this query it will work
SELECT temp.EMPLOYEEID, temp.FIRSTNAME, temp.LASTNAME, temp.STARTDATE, temp.EMAILADDRESS, temp.ORGANIZATIONID, temp.JOBTITLEID
(
SELECT row_number() OVER (PARTITION BY employee.EMPLOYEEID ORDER BY ACTIONDATE) as 'RowNumber',
employee.EMPLOYEEID, employee.FIRSTNAME, employee.LASTNAME, employee.STARTDATE, employee.EMAILADDRESS, org.ORGANIZATIONID, job.JOBTITLEID
FROM linkedServer.linkedDB.dbo.AUEMPLOYEE as employee
RIGHT JOIN linkedServer.linkedDB.dbo.AUEMPLOYEEORGANIZATION as org
ON employee.employeeid = org.employeeid
RIGHT JOIN linkedServer.linkedDB.dbo.AUEMPLOYEEJOBTITLE as job
ON employee.employeeid = job.employeeid
WHERE employee.ENDDATE IS NULL
GROUP BY employee.EMPLOYEEID, employee.FIRSTNAME, employee.LASTNAME, employee.STARTDATE, employee.EMAILADDRESS, org.ORGANIZATIONID, job.JOBTITLEID,ACTIONDATE
) temp
WHERE RowNumber<=1
ORDER BY temp.EMPLOYEEID
Regards,
Arjun
March 26, 2013 at 2:18 am
foxxo (3/25/2013)
Try something like this
SELECT employee.EMPLOYEEID, employee.FIRSTNAME, employee.LASTNAME, employee.STARTDATE, employee.EMAILADDRESS, org.ORGANIZATIONID, job.JOBTITLEIDFROM dbo.AUEMPLOYEEJOBTITLE as job
CROSS APPLY
(SELECT TOP 1 emp.employeeid, emp.FIRSTNAME, emp.LASTNAME, emp.STARTDATE, emp.EMAILADDRESS, emp.ENDDATE
FROM dbo.AUEMPLOYEE emp
INNER JOIN dbo.AUEMPLOYEEJOBTITLE empjob
ON emp.EMPLOYEEID = empjob.employeeid
ORDER BY ACTIONDATE) employee
RIGHT JOIN dbo.AUEMPLOYEEORGANIZATION as org
ON employee.employeeid = org.employeeid
WHERE employee.ENDDATE IS NULL
Thank you Foxxo! That actually ran really quick too. I have one more question, if you don't mind. Since there are multiple records with the same employeeid (basically changes made are transactional which causes multiple, I would like to only include the latest actiondate of each employeeid in the table. Is this possible? This is to ensure that I do not have duplicate records for employees, I'm probably going to set this up to run as a task to run nightly to update the hierarchy.
The permissions are correct for the linked connections. 🙂
@writearjun53 - I'm getting an incorrect syntax error from that code after the second SELECT
Msg 156, Level 15, State 1, Line 3
Incorrect syntax near the keyword 'SELECT'.
Msg 102, Level 15, State 1, Line 12
Incorrect syntax near ')'.
Thank you!
March 26, 2013 at 2:27 am
please try now
SELECT temp.EMPLOYEEID, temp.FIRSTNAME, temp.LASTNAME, temp.STARTDATE, temp.EMAILADDRESS, temp.ORGANIZATIONID, temp.JOBTITLEID From
(
SELECT row_number() OVER (PARTITION BY employee.EMPLOYEEID ORDER BY ACTIONDATE) as 'RowNumber',
employee.EMPLOYEEID, employee.FIRSTNAME, employee.LASTNAME, employee.STARTDATE, employee.EMAILADDRESS, org.ORGANIZATIONID, job.JOBTITLEID
FROM linkedServer.linkedDB.dbo.AUEMPLOYEE as employee
RIGHT JOIN linkedServer.linkedDB.dbo.AUEMPLOYEEORGANIZATION as org
ON employee.employeeid = org.employeeid
RIGHT JOIN linkedServer.linkedDB.dbo.AUEMPLOYEEJOBTITLE as job
ON employee.employeeid = job.employeeid
WHERE employee.ENDDATE IS NULL
GROUP BY employee.EMPLOYEEID, employee.FIRSTNAME, employee.LASTNAME, employee.STARTDATE, employee.EMAILADDRESS, org.ORGANIZATIONID, job.JOBTITLEID
) temp
WHERE RowNumber<=1
ORDER BY temp.EMPLOYEEID
March 26, 2013 at 2:40 am
Hey,
Thanks for the reply.
I'm receiving the follow error:
Msg 8120, Level 16, State 1, Line 3
Column 'linkedServer.linkedDB.dbo.AUEMPLOYEE.ACTIONDATE' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Msg 8120, Level 16, State 1, Line 3
Column 'linkedServer.linkedDB.dbo.AUEMPLOYEE.ACTIONDATE' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
I apologize, it doesn't appear I mentioned it, but ACTIONDATE is actually used in all tables by the software as a transaction record to show when the change was made.
March 26, 2013 at 2:42 am
;WITH Employees AS (
SELECT TOP 1
employeeid,
FIRSTNAME,
LASTNAME,
EMAILADDRESS,
STARTDATE
FROM linkedServer.linkedDB.dbo.AUEMPLOYEE
WHERE employee.ENDDATE IS NULL -- still active
ORDER BY ACTIONDATE DESC -- most recent
)
SELECT
e.*,
org.ORGANIZATIONID,
job.JOBTITLEID
FROM Employees e
LEFT JOIN linkedServer.linkedDB.dbo.AUEMPLOYEEJOBTITLE as job
ON e.EMPLOYEEID = job.employeeid
LEFT JOIN linkedServer.linkedDB.dbo.AUEMPLOYEEORGANIZATION as org
ON e.employeeid = org.employeeid
Check the actual execution plan. SQL Server does a surprisingly good job of figuring out when a query can be run remotely in its entirety but it doesn't always work. When it doesn't, you can often force it using OPENQUERY.
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
March 26, 2013 at 2:58 am
ChrisM@Work (3/26/2013)
;WITH Employees AS (SELECT TOP 1
employeeid,
FIRSTNAME,
LASTNAME,
EMAILADDRESS,
STARTDATE
FROM linkedServer.linkedDB.dbo.AUEMPLOYEE
WHERE employee.ENDDATE IS NULL -- still active
ORDER BY ACTIONDATE DESC -- most recent
)
SELECT
e.*,
org.ORGANIZATIONID,
job.JOBTITLEID
FROM Employees e
LEFT JOIN linkedServer.linkedDB.dbo.AUEMPLOYEEJOBTITLE as job
ON e.EMPLOYEEID = job.employeeid
LEFT JOIN linkedServer.linkedDB.dbo.AUEMPLOYEEORGANIZATION as org
ON e.employeeid = org.employeeid
Check the actual execution plan. SQL Server does a surprisingly good job of figuring out when a query can be run remotely in its entirety but it doesn't always work. When it doesn't, you can often force it using OPENQUERY.
Thank you Chris! So close! This is only pulling the records for one employeeid.
March 26, 2013 at 3:01 am
D'Oh!
;WITH Employees AS (
SELECT rn = ROW_NUMBER() OVER(PARTITION BY employeeid ORDER BY ACTIONDATE DESC),
employeeid,
FIRSTNAME,
LASTNAME,
EMAILADDRESS,
STARTDATE
FROM linkedServer.linkedDB.dbo.AUEMPLOYEE
WHERE employee.ENDDATE IS NULL -- still active
)
SELECT
e.*,
org.ORGANIZATIONID,
job.JOBTITLEID
FROM Employees e
LEFT JOIN linkedServer.linkedDB.dbo.AUEMPLOYEEJOBTITLE as job
ON e.EMPLOYEEID = job.employeeid
LEFT JOIN linkedServer.linkedDB.dbo.AUEMPLOYEEORGANIZATION as org
ON e.employeeid = org.employeeid
WHERE e.rn = 1
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
March 26, 2013 at 3:23 am
This is really odd, Chris. When I run that code, it runs quick and easy enough, however for some reason, it is duplicating ACTIONORDERS*
*NOTE - I changed ACTIONDATE to ACTIONORDER as I had appear to have missed this column as the key for ACTIONDATES, as ACTIONORDERS are unique.
;WITH Employees AS (
SELECT rn = ROW_NUMBER() OVER(PARTITION BY employeeid ORDER BY ACTIONORDER DESC),
employeeid,
FIRSTNAME,
LASTNAME,
EMAILADDRESS,
STARTDATE,
ACTIONORDER
FROM linkedServer.linkedDB.dbo.AUEMPLOYEE as employee
WHERE employee.ENDDATE IS NULL -- still active
)
SELECT
e.*,
org.ORGANIZATIONID,
job.JOBTITLEID
FROM Employees e
LEFT JOIN linkedServer.linkedDB.dbo.AUEMPLOYEEJOBTITLE as job
ON e.EMPLOYEEID = job.employeeid
LEFT JOIN linkedServer.linkedDB.dbo.AUEMPLOYEEORGANIZATION as org
ON e.employeeid = org.employeeid
WHERE e.rn = 1
Results in:
rnemployeeidFIRSTNAMELASTNAMEEMAILADDRESSSTARTDATEACTIONORDERORGANIZATIONIDJOBTITLEID
11TestTester1NULL8/15/2007 5:00270-3002NULL
11TestTester1NULL8/15/2007 5:00270-3002NULL
12TestTester2NULL7/11/2006 5:0063307-3002NULL
12TestTester2NULL7/11/2006 5:0063307-3002NULL
13TestTester3NULL4/11/2006 5:0086963-3002NULL
13TestTester3NULL4/11/2006 5:0086963-3002NULL
14TestTester4NULL12/7/2005 6:0014380-3002NULL
14TestTester4NULL12/7/2005 6:0014380-3002NULL
Clearly picture of the results. http://i.imgur.com/z8tXFvJ.png
And so... Any ideas?
March 26, 2013 at 3:43 am
Run this, post the first ten or so rows returned:
SELECT rn = ROW_NUMBER() OVER(PARTITION BY employeeid ORDER BY ACTIONORDER DESC),
employeeid,
FIRSTNAME,
LASTNAME,
EMAILADDRESS,
STARTDATE,
ACTIONORDER
FROM linkedServer.linkedDB.dbo.AUEMPLOYEE as employee
WHERE employee.ENDDATE IS NULL -- still active
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
March 26, 2013 at 3:47 am
March 26, 2013 at 4:04 am
tmac25 (3/26/2013)
This is really odd, Chris. ...And so... Any ideas?
Yes - one or both of the other two tables has multiple rows per employee. Can you check both tables to find out?
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
March 26, 2013 at 4:12 am
Hello Chris!
I apologize, I attempted to point this out in the original post, I see where I could have been more clear.
The software that is being utilized all has ACTIONDATES/ACTIONORDER in all the tables I'm using that signify a change to a user. Essentially, and this is where I knew it was going to get really complicated, which is why I posted, I'm sure there has got to be a easier way to do this, I find the most recent (ACTIONORDER) active (NOT NULL) employeeid from auemployee.
I would then cross reference that to AUJOB and AUORG to get the most recent change (ACTIONORDER) in both of those tables for that employeeid and join them into a hierarchy table.
Thank you!
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply
