Forum Discussion
Get just one date from multiple rows with the same date
Hi all,
I have a table like the one below, where I have hireDate and terminationDate broken up by individuals, identifiable by their empID. The problem is, a person could have worked in multiple departments as denoted in the departmentCommonName column, in which case they will have multiple rows. This will create multiple rows with the same hireDate and terminationDate for the same person. I am using these dates to calculate attrition, so I want to add in another column which will pull out only one of these pairs of dates for the employee, that way when I count for attrition I only count the person once. So in the data below, we can see that employee 206 has 5 sets of hireDate and terminationDate which are the same, but I want a column which will show this pair only once for employee 206, and then the same for all of the other employees. Any suggestions?
Thank you!
6 Replies
Anonymous may be easier is that in query editor, make copy of your table, remove all unwanted columns, keep emp name, hire and terminate date and remove duplicate rows. At the end you will have unique row for each employee and then it will be much easier to work from there.
parry2k Thanks for your reply! This sounds good, but the problem is that I will need attrition to eventually be broken up by departmentCommonName and empLocation, but if I remove those columns in another table I won't know which people were in which group when I try to add them up. Any suggestions?
Anonymous well if you don't have termination date by department, you cannot achieve that anyhow so not sure how you are planning to do it?
