|
|
Posted by --CELKO-- on 07/08/07 15:55
Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications. It is very hard to debug code when you do not let us
see it.
>> I need to take a users current title and begin stepping through all their title records that they have had since they have been with the organization, until I find a break in the dates of service with their current title. <<
You might want to use the proper ISO-8601 date formats in the
future.
CREATE TABLE EmploymentHistory
(emp_id INTEGER NOT NULL,
job_title CHAR(10) NOT NULL,
start_date DATETIME NOT NULL,
end_date DATETIME, -- null means current
CHECK (start_date < end_date),
PRIMARY KEY (emp_id, start_date),
etc.);
So this would give you the current period with a particular job title:
CREATE VIEW CurrentTitles (emp_id, current_job_title, start_date)
AS
SELECT E1.emp_id, job_title, E1.start_date
FROM EmploymentHistory AS E1
WHERE E1.end_date IS NULL;
We know the end date is CURRENT_TIMESTAMP if we fire or promote him
today, so no need to put it in the view. I am assuming that you want
only currently employed personnel and that an employee has one and
only one job. To get the periods when he held his current title,
use:
SELECT emp_id, job_title, start_date, end_date
FROM EmploymentHistory AS E1, CurrentTitles AS C
WHERE C.current_job_title = E1.job_title
AND E1.emp_id = C.emp_id;
>> So a user gets a new title and its active from 2006-06-30 to 2007-07-06, then their previous title was the same and it was from 2004-05-15 to 2007-06-29, then they either have a break in service OR they have a different title from 2003-04-01 to 2004-05-14, but then had a stint with the same as their current title from 2000-06-01 to 2003-03-31.
Ok, now I would only be interested in stepping back until there was a
break in their current title, so I should end up with a number
(represents days in that title) which would include 2004-05-15 to
current. <<
Then the total number of days spent in the current job title over the
guy's whole career will be something like this:
SELECT emp_id, job_title,
SUM (DATEDIFF(dd, COALESCE (end_date, CURRENT_TIMESTAMP),
start_date)
FROM EmploymentHistory AS E1, CurrentTitles AS C
WHERE C.current_job_title = E1.job_title
AND E1.emp_id = C.emp_id
GROUP BY emp_id, job_title;
A Calendar table will also be handy for using with the (start_date,
end_date) pairs and a BETWEEN predicate.
Navigation:
[Reply to this message]
|