SQL Query To Sort User Records And Update End Date

[geekette33]

Hi,

I have a user table in SQL that stores all users along with information like username, department, team, job title etc... If a user makes an update to their information a new record is created (therefore a user can have more than one record). When the new record is created the previous record is given an end date and active flag is set to 1.

When i run reporting linking this table to other data I need to also have a start date for each record so that I can link the relavant details to the data. E.g if the linked data is pulling a users call volumes in January I want to show the team they worked in in January.

The problem is I don't have the rights to change the original table (it was set up by someone else) So think I may need to first create a temporary table, add an additional column (Start Date), run a query that sorts the records by user, then by record end date and then work out the start date for each record (will always be +1 from the previous end date). Any help or guidance would be so appreciated as I've no idea how to approach

 

[markros]

In SQL Server 2005 and up this problem can be solved easily with self-join, e.g.

;with Sorted as (select *, row_number() over (partition by UserID order by [DateField]) as row from myTable)

select S1.UserName, etc., S1.[DateField] as StartDate, S2.[DateField] as EndDate from Sorted S1 LEFT JOIN Sorted S2
on S1.UserID = S2.UserID and S1.Row = S2.Row - 1

PluralSight Learning Library