|
|
Posted by Erland Sommarskog on 07/06/07 21:58
(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/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
Navigation:
[Reply to this message]
|