question

priyankavelu avatar image
priyankavelu asked

How do I compare two rows of same deal having two different versions in same table and find non-matching columns?

Table structure is like so: DEAL ID, VERSION ID, QTE, datemodified, systemdate, securities ...... (with many more columns). Inputs: DEALID,VERSIONID,DATEMODIFIED,SYSTEMDATE,QTE,SECURITIES 177 0 13-10-2016 13-10-2016 50 DD 177 1 13-10-2016 13-10-2016 100 DD 177 2 13-10-2016 13-10-2016 100 YY Output: I need to tell which column is changed in each version For deal ID 177 the columns that changed according to SYSTEMDATE are QTE and SECURITIES
sqlversionwindowing-functions
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
GPO avatar image
GPO answered
Some of this syntax won't work in older versions of SQL Server --HUGE ASSUMPTION WARNING: The columns being compared are not nullable? DROP TABLE #deals; SELECT 177 as DEALID ,0 as VERSIONID ,cast('20161013' as date) as DATEMODIFIED ,cast('20161013' as date) as SYSTEMDATE ,50 as QTE ,'DD' as SECURITIES INTO #deals union all SELECT 177 as DEALID ,1 as VERSIONID ,cast('20161013' as date) as DATEMODIFIED ,cast('20161013' as date) as SYSTEMDATE ,100 as QTE ,'DD' as SECURITIES union all SELECT 177 as DEALID ,2 as VERSIONID ,cast('20161013' as date) as DATEMODIFIED ,cast('20161013' as date) as SYSTEMDATE ,100 as QTE ,'YY' as SECURITIES ; --========================================================== WITH current_version_and_next as ( SELECT DEALID,VERSIONID,DATEMODIFIED,SYSTEMDATE,QTE,SECURITIES ,LEAD(QTE) OVER(PARTITION BY DEALID ORDER BY VERSIONID) as Next_QTE ,LEAD(SECURITIES) OVER(PARTITION BY DEALID ORDER BY VERSIONID) as Next_SECURITIES --et cetera, et cetera, et cetera FROM #deals ) SELECT cn.DEALID ,cn.VERSIONID ,iif(cn.QTE cn.Next_QTE ,'QTE changed from ' + cast(cn.QTE as varchar(20)) + ' to ' + cast(cn.Next_QTE as varchar(20)) ,'' ) as QTE_check ,iif(cn.SECURITIES cn.Next_SECURITIES ,'SECURITIES changed from ' + cn.SECURITIES + ' to ' + cn.Next_SECURITIES ,'' ) as SECURITIES_check FROM current_version_and_next cn WHERE (cn.QTE cn.Next_QTE OR cn.SECURITIES cn.Next_SECURITIES --et cetera, et cetera, et cetera ) ;
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.