HELP!! Please .......

A newbie sql writer, so I hope the question makes sense.....

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.

So a user gets a new title and its active from 6/30/2006 - 7/6/2007,
then their previous title was the same and it was from 5/15/2004 -
6/29/2007, then they either have a break in service OR they have a
different title from 4/1/2003 - 5/14/2004, but then had a stint with
the same as their current title from 6/1/2000 - 3/31/2003.

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 5/15/2004 -
current.

Any ideas on how to set this up?

Thanks for any help.

Bruce

Re: HELP!! Please .......

(brukeste@gmail.com) writes:
> A newbie sql writer, so I hope the question makes sense.....
>
> 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.
>
> So a user gets a new title and its active from 6/30/2006 - 7/6/2007,
> then their previous title was the same and it was from 5/15/2004 -
> 6/29/2007, then they either have a break in service OR they have a
> different title from 4/1/2003 - 5/14/2004, but then had a stint with
> the same as their current title from 6/1/2000 - 3/31/2003.
>
> 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 5/15/2004 -
> current.
>
> Any ideas on how to set this up?

A good idea for this of type question is to include:

o CREATE TABLE statements for the involved table(s).
o INSERT statement with sample data.
o The desired output given the sample.

That helps to clarify what you are asking for, and makes it easy to
copy and paste to develop a tested solution.

And you should always specify which version of SQL Server you are using.

This query has not been tested, and I had to make some guesses on
what columns you may have.

SELECT a.userid, MAX(a.fromdate)
FROM tbl a
LEFT JOIN tbl b ON a.userid = b.userid
AND a.fromdate = dateadd(DAY, 1, b.todate)
WHERE a.title <> b.title OR b.title IS NULL


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downlo ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books .mspx

Re: HELP!! Please .......

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.