I am trying to report on changes made to the asset's change history based on the user history fields.

Within the Resourceownerhistory table, I see that once a change is made the previous record has an endtime entered. A new row is then created with the new user/department details.

Typically id expect the closed record (row with the endtime just entered) to be set as inactive on the Resourceownerhistory table and be put into a totally separate history table. (some tables ive seen would delete that row from the resourceownerhistory table once its inserted into the history table meaning there would be no need for the inactive tag)

As all records are on the same table, I am finding it very difficult to get my sql to work. (I have not posted any here as nothing I did seemed to work)

The desired output would be

Resource Name

New User Name

Previous User

Previous User Department

Date of Change

We use MS SQL Server 2008

Build 8011

Any help would be appreciated.

Mike Palmer