Find delta in the same table - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Find delta in the same table

Author

Topic

rockstar283
Yak Posting Veteran

96 Posts

Posted - 2012-06-29 : 12:16:07

CREATE TABLE [dbo].[TEMP3](
[Deal ID ] [nvarchar](255) NULL,
[Contract Month] [nvarchar](255) NULL,

[BS] [nvarchar](255) NULL,

[PORTFOLIO ID] [nvarchar](255) NULL,
[TRADE TYPE] [nvarchar](255) NULL,
[TRADE TYPE CODE] [nvarchar](255) NULL,
[Counterparty] [nvarchar](255) NULL,
[Valuation date] datetime
) ON [PRIMARY]
and has records like below:

SELECT '6652560','201204','PURCHASE','49425','X','GAS','A','2012-03-29 00:00:00.000'
SELECT '6691651','201204','PURCHASE','49425','X','GAS','A','2012-03-29 00:00:00.000'
SELECT '6691651','201204','PURCHASE','49425','X','GAS','B','2012-03-30 00:00:00.000'
SELECT '5423835','201204','PURCHASE','150609','X','GAS','A','2012-03-29 00:00:00.000'
SELECT '5423835','201205','PURCHASE','150609','X','GAS','A','2012-03-29 00:00:00.000'

Now I want to go through all these records and Identify the records where portfolio id,counterparty,trade type or trade type code are different for the combination of deal id, contract month,BS and valuation date..

so the result of the above data will be.. (AS COUNTERPARTY has changed) (result will be in a new table)

deal_id contract_month
BS Valuation_date
delta_column old_value
new_value

6691651 201204 PURCHASE 2012-03-30 00:00:00.000
Counterparty A
B

I think this can be achieved using MERGE..but still trying to explore other options..

Can somebody please help me with this..thank you!!

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-06-29 : 13:29:05

If you are looking for combinations of deal id, contract month,BS and valuation date, would 6691651 show up in your results - the valuation dates for the two rows are different for the two rows in your sample data.

In the query below, I may not have the rules correctly - but in the first PARTITION BY clause, you should include all the columns that you want to consider to be unique and in the second PARTITION by clause you should include all those columns and the columns that if different would be flagged.

SELECT * FROM 
(
SELECT
	*,
	COUNT(*) OVER (PARTITION BY [Deal ID ],[Contract Month],[BS])
	- 	COUNT(*) OVER (PARTITION BY [Deal ID ],[Contract Month],[BS],
		[PORTFOLIO ID],[Counterparty],[TRADE TYPE], [Counterparty]) AS N

FROM
	TEMP3
) s
WHERE N > 0

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources