September 4, 2015 at 9:15 am
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.
September 4, 2015 at 1:26 pm
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.
Based upon the information that you've given here, this would be:
DECLARE @SecurityID INTEGER;
SET @SecurityID = 22;
SELECT securityID, CompanyId
FROM dbo.GoldenSecurity
WHERE securityID = @SecurityID;
SELECT securityID, CompanyId, Securityname
FROM dbo.GoldenSecurityRowVersion
WHERE securityID = @SecurityID;
I suspect that something is missing in the requirements. Or that additional test data is needed to better see what the issue is.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
September 7, 2015 at 4:49 am
Apologies for the confusion, the columns that have changed should be selected dynamically. Requirement is to identify the changed columns for any securityID.
September 7, 2015 at 9:57 am
chandrakant_gaurav (9/7/2015)
Apologies for the confusion, the columns that have changed should be selected dynamically. Requirement is to identify the changed columns for any securityID.
Still confused by your requirements... 🙁
Is the user specifying the column that should be selected?
Or does the code need to determine the columns that have been changed?
By your use of "dynamic", I tend to think the first.
By reading between the lines of your 2 posts, I'm wondering if the second is what you actually want.
Do you need this information for a report?
Do you need to see the before and after values?
Edit:
How should it handle a subsequent update? For instance:
INSERT INTO dbo.GoldenSecurity(securityID, CompanyId, Securityname, issuedate, currencyID)
SELECT 20, 88, 'Diamond Estate', '2015-01-03', 79;
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
September 7, 2015 at 10:05 am
The code needs to identify the columns that have changed. This needs to be a part of a stored proc which receives single security ID as parameter and it retrieves the last column/s that were changed and their corresponding values.
Insert is not an issue, update is. For example we run an update statement on dbo.GoldenSecurity like this -
UPDATE dbo.GoldenSecurity SET Securityname = 'Silver Trust' WHERE securityID = 20
THEN a simple update happens in dbo.GoldenSecurity and a single row get inserted into dbo.GoldenSecurityRowVersion like the below insert which is a snapshot of the row before update happens -
INSERT INTO dbo.GoldenSecurityRowVersion (RowversionDate ,securityID , CompanyId , Securityname , issuedate , currencyID )
SELECT 20, 88, 'Silver Estate', '2015-01-03', 79
I hope this clarifies.
September 7, 2015 at 11:02 am
chandrakant_gaurav (9/7/2015)
The code needs to identify the columns that have changed. This needs to be a part of a stored proc which receives single security ID as parameter and it retrieves the last column/s that were changed and their corresponding values.Insert is not an issue, update is. For example we run an update statement on dbo.GoldenSecurity like this -
UPDATE dbo.GoldenSecurity SET Securityname = 'Silver Trust' WHERE securityID = 20
THEN a simple update happens in dbo.GoldenSecurity and a single row get inserted into dbo.GoldenSecurityRowVersion like the below insert which is a snapshot of the row before update happens -
INSERT INTO dbo.GoldenSecurityRowVersion (RowversionDate ,securityID , CompanyId , Securityname , issuedate , currencyID )
SELECT 20, 88, 'Silver Estate', '2015-01-03', 79
I hope this clarifies.
Assuming that you're only looking for changes in these 4 columns:
CompanyId , Securityname , issuedate , currencyID
Then how does this code work for you?
WITH cte AS
(
SELECT GSRV.securityID,
GSRV.CompanyId,
GSRV.Securityname,
GSRV.issuedate,
GSRV.currencyID,
ca.InitialValue,
ca.FinalValue,
ca.ColumnName
FROM dbo.GoldenSecurityRowVersion GSRV
JOIN dbo.GoldenSecurity GS ON GSRV.securityID = GS.securityID
-- UnPivot the columns in pairs that the comparison is to be on using CROSS APPLY VALUES.
-- These all need to be a compatible data type, so convert as necessary.
-- Repeat for any additional columns
-- If any columns are added to the tables, they will have to be added to this query.
CROSS APPLY (VALUES (CONVERT(VARCHAR(1000), GS.CompanyId), CONVERT(VARCHAR(1000), GSRV.CompanyId), 'CompanyId')
,(gs.Securityname, gsrv.Securityname, 'Securityname')
,(CONVERT(VARCHAR(1000), GS.issuedate), CONVERT(VARCHAR(1000), GSRV.issuedate), 'issuedate')
,(CONVERT(VARCHAR(1000), GS.currencyID), CONVERT(VARCHAR(1000), GSRV.currencyID), 'currencyID')
) ca(InitialValue, FinalValue, ColumnName)
WHERE ca.InitialValue <> ca.FinalValue
)
SELECT cte.securityID,
MAX(CASE WHEN cte.ColumnName = 'CompanyId' THEN cte.FinalValue ELSE NULL END) AS CompanyID,
MAX(CASE WHEN cte.ColumnName = 'Securityname' THEN cte.FinalValue ELSE NULL END) AS Securityname,
MAX(CASE WHEN cte.ColumnName = 'issuedate' THEN cte.FinalValue ELSE NULL END) AS issuedate,
MAX(CASE WHEN cte.ColumnName = 'currencyID' THEN cte.FinalValue ELSE NULL END) AS currencyID
-- repeat for any additional columns.
FROM cte
GROUP BY cte.securityID;
What I meant to ask is what if a subsequent row is inserted into dbo.GoldenSecurityRowVersion?
INSERT INTO dbo.GoldenSecurityRowVersion(RowversionDate, securityID, CompanyId, Securityname, issuedate, currencyID)
SELECT GETDATE(), 20, 88, 'Diamond Estate', '2015-01-03', 79;
Does it need to compare to dbo.GoldenSecurity, or to the prior record (for this securityID) in GoldenSecurityRowVersion?
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
September 11, 2015 at 9:20 am
Thanks Wayne, this worked for my scenario. Much appreciated. 🙂
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply