Find Employees how are in perticular Title any time during the year

Question

Find Employees how are in perticular Title any time during the year

rakkpra

Old Hand

Points: 304
More actions
February 1, 2011 at 12:47 pm

#231449

Hi
I have a Table with all the details of Employee along with the Title.
I want to know the list of employees with Title as App Dev II anytime during the Year.
For Example i was App Dev I untill Nov and my title Changed to App Dev II in Nov. i want my self in the List.Any Idea's about it?
Also i have other request that want to Get salary of Employee as of 01/01/2011 .
Help appreciated Need help ASAP
Thanks

Viewing 6 posts - 1 through 5 (of 5 total)

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

Lowell SSC Guru Points: 323518 More actions · Answer 1

do you have a table that does an audit or history for when someone changes their title?

so if i got promoted to "Developer 3" , is that stored in a related table to your main table you described, or is the main table updated directly with no audit?

if there is no audit table, there is no way to get the "differences" ...there's nothing to compare it to.

SQL does not keep a history of data changes like that , unless you have an auditing trigger, or CDC(sql 2008) or C2 auditing, or your own custom trace running.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

rakkpra Old Hand Points: 304 More actions · Answer 2

Hi Lowell

We have a History table but like Emp is my current table and EmpHistory is our History table .

Emp Table only have current table and EmpHistory has the dates when the title is changed and when it is effective from

Lowell SSC Guru Points: 323518 More actions · Answer 3

rakkpra (2/1/2011)
Hi Lowell
We have a History table but like Emp is my current table and EmpHistory is our History table .
Emp Table only have current table and EmpHistory has the dates when the title is changed and when it is effective from

without the definitions of the tables, i can only guess....remember I'm not in your cubicle looking over your shoulder, you have to show us what you've got.

as a wild guess it's a simple join between the main table and hte history. it's going to be something like this:

select *

from myEmployees

left outer join myEmployeesHistory

On myEmployees.ID = myEmployeesHistory.ID

Where myEmployeesHistory.DateOfChange BETWEEN '20100701 00:00:00.000' AND '20101231 00:00:00.000'

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

ChazMan SSCrazy Points: 2259 More actions · Answer 4

we can't really give you the SQL to use as we don't have the table structures, however assuming that EmpID is the key in your EmpTable and that EmpHistory has a foreign Key of EmpID. The following should come close:

select E.EmpFirstName, E.EmpLastName, E.Title, H.ChangeDate, H.PreviousTitle

from Emp E

join EmpHistory H on E.EmpID = H.EmpID

where E.Title = 'App Dev II' or

(H.Title = 'App Dev II' and H.ChangeDate >= '01/01/2011')

--------------------------------------------------------------------------
When you realize you've dug yourself into a hole....Step 1...stop digging.

ChazMan SSCrazy Points: 2259 More actions · Answer 5

Lowell's is better than mine as it takes the possibility of not having a history record into account. Heh heh.

--------------------------------------------------------------------------
When you realize you've dug yourself into a hole....Step 1...stop digging.