| Author |
 Topic  |
|
|
rockstar283
Yak Posting Veteran
73 Posts |
 Posted - 06/29/2012 : 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
Flowing Fount of Yak Knowledge
5152 Posts |
Posted - 06/29/2012 : 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
|
 |
|
| |
Topic |
|
|
|
Find delta in the same table
