mickeyziggyk
Technical User
Hello All
I work with a HR db that has no end dates built into its design. I only have effective dates that represent the start of a change to an employee's profile.
I want to see all records/changes about every employee from the date Jan 1st 2008.
But some employees have had no changes to their profile since 2007, which means that if I do a simple select * between 01/01/2008 and now(), this would miss that particular population.
Therefore I want to know everything between the first update closest to Jan 1st 2008 to the current date.
My SQL has got as far as :
SELECT *
FROM HR
WHERE
[eff date] between
(select max([eff date]) from HR
where[eff date] <#01/01/2008#)
and now();
But, I know that this is not the whole picture and that I think I should query the relation on itself. I simply do not know how to grab the closest record to Jan 1st 2008 and then all subsequent records after that.
Each employee is uniquely identified by an employee number(empl_id).
I am baffled, if someone can help, I will gladly listen and learn.
Thanks
I work with a HR db that has no end dates built into its design. I only have effective dates that represent the start of a change to an employee's profile.
I want to see all records/changes about every employee from the date Jan 1st 2008.
But some employees have had no changes to their profile since 2007, which means that if I do a simple select * between 01/01/2008 and now(), this would miss that particular population.
Therefore I want to know everything between the first update closest to Jan 1st 2008 to the current date.
My SQL has got as far as :
SELECT *
FROM HR
WHERE
[eff date] between
(select max([eff date]) from HR
where[eff date] <#01/01/2008#)
and now();
But, I know that this is not the whole picture and that I think I should query the relation on itself. I simply do not know how to grab the closest record to Jan 1st 2008 and then all subsequent records after that.
Each employee is uniquely identified by an employee number(empl_id).
I am baffled, if someone can help, I will gladly listen and learn.
Thanks