December 7, 2011 at 10:32 am
I have written a SSRS report and everything works with the exception I am getting multiple values for the same employee name. What I need is everything being the same but with only employee records with the most "effective date".
Here is the original query:
SELECT DISTINCT E.Name
, E.Department
, E.CpnyID AS 'Company'
, J.JobTitle AS 'Job Title'
, J.EffectiveDate
, E. EmployeeId AS 'Empl #'
, E.SocSecNbr AS 'SS#'
, E.Address
, E.City
, E.State
, E.Zip
, E.Phone
, E.DateOfBirth AS 'Date Of Birth'
, E.DateHired AS 'Date Of Hire'
, E.DateTerminated AS 'Date of Term'
, S.SalaryType AS 'Salary Type'
FROM XHR_Employee AS E
JOIN XHR_JobHistory AS J
ON (E.EmployeeId = J.EmpId and E.CpnyID = J.CpnyID)
JOIN XHR_SalaryHist AS S
ON (J.EmpId = S.EmpId and J.CpnyID = S.CpnyID)
WHERE (E.CpnyID = 'KFM')
AND (J.JobTitle IN (@JobTitle))
AND (S.SalaryType IN (@SalaryType))
I have tried this and several other variations to get the most "EffectiveDate" but to no avail..
SELECT DISTINCT E.Name
, E.Department
, E.CpnyID AS 'Company'
, J.JobTitle AS 'Job Title'
, J.EffectiveDate
, E. EmployeeId AS 'Empl #'
, E.SocSecNbr AS 'SS#'
, E.Address
, E.City
, E.State
, E.Zip
, E.Phone
, E.DateOfBirth AS 'Date Of Birth'
, E.DateHired AS 'Date Of Hire'
, E.DateTerminated AS 'Date of Term'
, S.SalaryType AS 'Salary Type'
FROM (Select EmpId, JobTitle, MAX(EffectiveDate) AS EffectiveDate
FROM XHR_JobHistory
Group By EmpId, JobTitle) J
JOIN XHR_Employee AS E
ON (E.EmployeeId = J.EmpId)
JOIN XHR_SalaryHist AS S
ON (J.EmpId = S.EmpId)
WHERE (E.CpnyID = 'KFM')
Here is a small sample of the results:
Employee Name Dept Compny Job Title Effective Date
Employee 1 1 Cpny 1 Carpenter 1 2011-10-08
Employee 1 1 Cpny 1 Carpenter 2 2011-03-18
Any help is greatly appreciated.
December 7, 2011 at 11:40 am
try using a cte and a row_number() ranking function.
Put your query in the cte with that ranking function, then select from the cte where rownum=1
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
December 7, 2011 at 1:04 pm
Exactly what I would do
December 7, 2011 at 1:07 pm
Thanks for the advice.
I have never used a CTE, so I will research their use and hash it out to see if I can get it working that way.
December 7, 2011 at 6:39 pm
If you are looking just to find the max effective date for each person and type of job, just use the max and group by the rest:
SELECT DISTINCT
E.Name
, E.Department
, E.CpnyID AS 'Company'
, J.JobTitle AS 'Job Title'
, MostRecentEffectiveDate=MAX(J.EffectiveDate)
, E. EmployeeId AS 'Empl #'
, E.SocSecNbr AS 'SS#'
, E.Address
, E.City
, E.State
, E.Zip
, E.Phone
, E.DateOfBirth AS 'Date Of Birth'
, E.DateHired AS 'Date Of Hire'
, E.DateTerminated AS 'Date of Term'
, S.SalaryType AS 'Salary Type'
FROM XHR_Employee AS E
JOIN XHR_JobHistory AS J
ON (E.EmployeeId = J.EmpId and E.CpnyID = J.CpnyID)
JOIN XHR_SalaryHist AS S
ON (J.EmpId = S.EmpId and J.CpnyID = S.CpnyID)
WHERE (E.CpnyID = 'KFM')
AND (J.JobTitle IN (@JobTitle))
AND (S.SalaryType IN (@SalaryType))
group by
E.Name
, E.Department
, E.CpnyID
, J.JobTitle
, E. EmployeeId
, E.SocSecNbr
, E.Address
, E.City
, E.State
, E.Zip
, E.Phone
, E.DateOfBirth
, E.DateHired
, E.DateTerminated
, S.SalaryType
December 7, 2011 at 6:45 pm
SQLRNNR (12/7/2011)
try using a cte and a row_number() ranking function.Put your query in the cte with that ranking function, then select from the cte where rownum=1
I would not under most circumstances recommend this method for accessing the main table with. I've found performance to be sub-par unless there are very few rows (five or less, 2 or less by preference) per grouping.
I would recommend either using a MAX() subquery and rejoining on the effective date and employee identifier, or performing a top 1 cross apply subquery and bringing the record out that way.
IE: SELECT a.* FROM tblA as a JOIN ( SELECT bID, MAX(Datefield) AS MaxDate) FROM tblA GROUP BY bID ) AS drv ON a.bID = drv.bID AND a.DateField = drv.MaxDate
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
December 7, 2011 at 8:20 pm
Evil Kraig F (12/7/2011)
SQLRNNR (12/7/2011)
try using a cte and a row_number() ranking function.Put your query in the cte with that ranking function, then select from the cte where rownum=1
I would not under most circumstances recommend this method for accessing the main table with. I've found performance to be sub-par unless there are very few rows (five or less, 2 or less by preference) per grouping.
I would recommend either using a MAX() subquery and rejoining on the effective date and employee identifier, or performing a top 1 cross apply subquery and bringing the record out that way.
IE: SELECT a.* FROM tblA as a JOIN ( SELECT bID, MAX(Datefield) AS MaxDate) FROM tblA GROUP BY bID ) AS drv ON a.bID = drv.bID AND a.DateField = drv.MaxDate
I've seen it go both ways with queries. Sometimes tuning out the max() subquery and using a CTE is better with large result sets and sometimes it works best to use the subquery. At least there are options.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
December 8, 2011 at 7:21 am
Burninator (12/7/2011)
If you are looking just to find the max effective date for each person and type of job, just use the max and group by the rest:
SELECT DISTINCTE.Name
, E.Department
, E.CpnyID AS 'Company'
, J.JobTitle AS 'Job Title'
, MostRecentEffectiveDate=MAX(J.EffectiveDate)
, E. EmployeeId AS 'Empl #'
, E.SocSecNbr AS 'SS#'
, E.Address
, E.City
, E.State
, E.Zip
, E.Phone
, E.DateOfBirth AS 'Date Of Birth'
, E.DateHired AS 'Date Of Hire'
, E.DateTerminated AS 'Date of Term'
, S.SalaryType AS 'Salary Type'
FROM XHR_Employee AS E
JOIN XHR_JobHistory AS J
ON (E.EmployeeId = J.EmpId and E.CpnyID = J.CpnyID)
JOIN XHR_SalaryHist AS S
ON (J.EmpId = S.EmpId and J.CpnyID = S.CpnyID)
WHERE (E.CpnyID = 'KFM')
AND (J.JobTitle IN (@JobTitle))
AND (S.SalaryType IN (@SalaryType))
group by
E.Name
, E.Department
, E.CpnyID
, J.JobTitle
, E. EmployeeId
, E.SocSecNbr
, E.Address
, E.City
, E.State
, E.Zip
, E.Phone
, E.DateOfBirth
, E.DateHired
, E.DateTerminated
, S.SalaryType
I have tried it this way before and I get the same results with all effective dates and not the most recent one.
December 8, 2011 at 7:39 am
Evil Kraig F (12/7/2011)
SQLRNNR (12/7/2011)
try using a cte and a row_number() ranking function.Put your query in the cte with that ranking function, then select from the cte where rownum=1
I would not under most circumstances recommend this method for accessing the main table with. I've found performance to be sub-par unless there are very few rows (five or less, 2 or less by preference) per grouping.
I would recommend either using a MAX() subquery and rejoining on the effective date and employee identifier, or performing a top 1 cross apply subquery and bringing the record out that way.
IE: SELECT a.* FROM tblA as a JOIN ( SELECT bID, MAX(Datefield) AS MaxDate) FROM tblA GROUP BY bID ) AS drv ON a.bID = drv.bID AND a.DateField = drv.MaxDate
Using your advice I create this query but with the same results:
SELECT DISTINCT E.Name
, E.Department
, E.CpnyID AS 'Company'
, E. EmployeeId AS 'Empl #'
, E.SocSecNbr AS 'SS#'
, E.Address
, E.City
, E.State
, E.Zip
, E.Phone
, E.DateOfBirth AS 'Date Of Birth'
, E.DateHired AS 'Date Of Hire'
, E.DateTerminated AS 'Date of Term'
, S.SalaryType AS 'Salary Type'
FROM XHR_Employee AS E
JOIN (Select J.EmpId, J.JobTitle, MAX(J.EffectiveDate) AS EffectiveDate FROM XHR_JobHistory AS J Group BY J.EmpID, J.JobTitle) AS J
ON (E.EmployeeId = J.EmpId)
JOIN XHR_SalaryHist AS S
ON (J.EmpId = S.EmpId)
WHERE (E.CpnyID = 'KFM')
RESULTS:
Name DepartmentCompany JobTitle EffectiveDate
Employee190 KFM DECK HAND 1 2011-10-08
Employee190 KFM DECK HAND 2 2011-03-18
December 8, 2011 at 8:20 am
This CTE/Query seems to be giving me the reults I am looking for. Thank you for your assitance and guidance.
With HR_CTE (seq, EmployeeId, Name, Department, JobTitle, EffectiveDate, SocSecNbr, Address, City, State, Zip, Phone, DateOfBirth, DateHired, DateTerminated, SalaryType, CpnyId)
AS
(Select ROW_NUMBER() OVER(PARTITION BY E.[EmployeeId] ORDER BY J.EffectiveDate DESC) AS seq,
E.EmployeeID, E.Name, E.Department, J.JobTitle, J.EffectiveDate, E.SocSecNbr, E.Address, E.City, E.State, E.Zip, E.Phone, E.DateOfBirth, E.DateHired, E.DateTerminated, S.SalaryType, E.CpnyId
FROM XHR_Employee E
JOIN XHR_JobHistory J
ON E.[EmployeeId] = J.[EmpId]
JOIN XHR_SalaryHist S
ON E.[EmployeeId] = S.[EmpId])
SELECT EmployeeID, Name, Department, JobTitle, EffectiveDate, SocSecNbr, Address, City, State, Zip, Phone, DateOfBirth, DateHired, DateTerminated, SalaryType, CpnyId
FROM HR_CTE
WHERE seq = 1
AND CpnyId = 'KFM'
Order BY EmployeeId
December 8, 2011 at 8:42 am
You are welcome.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply