chandrakant_gaurav (9/4/2015)

---

IF Object_id('GoldenSecurity') IS NOT NULL DROP TABLE dbo.GoldenSecurity;

IF Object_id('GoldenSecurityRowVersion') IS NOT NULL DROP TABLE dbo.GoldenSecurityRowVersion;

CREATE TABLE dbo.GoldenSecurity (securityID INT, CompanyId INT, Securityname VARCHAR(50), issuedate SMALLDATETIME, currencyID INT)

CREATE TABLE dbo.GoldenSecurityRowVersion (RowversionDate DATETIME,securityID INT, CompanyId INT, Securityname VARCHAR(50), issuedate SMALLDATETIME, currencyID INT)

INSERT INTO dbo.GoldenSecurity(securityID, CompanyId, Securityname, issuedate, currencyID)

SELECT 20, 88, 'Silver Estate', '2015-01-03', 79

UNION SELECT 21, 44, 'Trust Units', '2015-03-12', 79

UNION SELECT 22, 75, 'Partnership Units', '2015-05-24', 79

--SecurityID being the PK.

--There is a change tracking table - GoldenSecurityRowVersion, which stores one row of last value of GoldenSecurity table before update.

--timestamp of the update is inserted in RowversionDate

--There can be change to one or more column, but it will capture one row against each update statement.

INSERT INTO dbo.GoldenSecurityRowVersion (RowversionDate ,securityID , CompanyId , Securityname , issuedate , currencyID )

SELECT dateadd(dd,-1,getdate()), 20, 98, 'Silver Estate', '2015-01-03', 79

UNION SELECT dateadd(dd,-4,getdate()), 21, 108, 'Trust Units', '2015-03-12', 79

UNION SELECT dateadd(dd,-5,getdate()), 22, 88, 'Some other Unit', '2015-05-24', 79

select * from GoldenSecurity order by securityid

select * from GoldenSecurityRowVersion order by securityid

--Requirement is to get an output, when queried for single securityID, gives only the changed columnname (and last value/s if possible), somewhat like this-

--when queried for securityID 20

securityID||CompanyID

20||98

--when queried for securityID 21

securityID||CompanyID

21||108

--when queried for securityID 22

securityID||CompanyID||Securityname

22||88||Some other Unit

I hope it is clear, any help would be much appreciated.