June 29, 2012 at 10:17 am
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!!
July 1, 2012 at 9:22 pm
Since valuation date has changed on the B record for counterparty, I don't see how you can consider it to be part of the same group on deal ID, contract month, bs (and valuation date).
However if you want to ignore this, and group only by deal ID, contract month and bs, you may be able to do something like this:
CREATE TABLE #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
)
INSERT INTO #TEMP3
SELECT '6652560','201204','PURCHASE','49425','X','GAS','A','2012-03-29 00:00:00.000'
UNION ALL SELECT '6691651','201204','PURCHASE','49425','X','GAS','A','2012-03-29 00:00:00.000'
UNION ALL SELECT '6691651','201204','PURCHASE','49425','X','GAS','B','2012-03-30 00:00:00.000'
UNION ALL SELECT '5423835','201204','PURCHASE','150609','X','GAS','A','2012-03-29 00:00:00.000'
UNION ALL SELECT '5423835','201205','PURCHASE','150609','X','GAS','A','2012-03-29 00:00:00.000'
;WITH CTE AS (
SELECT [Deal ID], [Contract Month], BS, [PORTFOLIO ID], [TRADE TYPE]
,[TRADE TYPE CODE], Counterparty, [Valuation date]
,rn=ROW_NUMBER() OVER (PARTITION BY [Deal ID], [Contract Month], BS ORDER BY [Valuation date])
FROM #TEMP3)
SELECT [Deal ID], [Contract Month], BS, [PORTFOLIO ID], [TRADE TYPE]
,CASE WHEN c1.[TRADE TYPE CODE] <> x.[TRADE TYPE CODE] THEN x.[TRADE TYPE CODE]
WHEN c1.Counterparty <> x.Counterparty THEN x.Counterparty
ELSE NULL END AS [Old Value]
,CASE WHEN c1.[TRADE TYPE CODE] <> x.[TRADE TYPE CODE] THEN c1.[TRADE TYPE CODE]
WHEN c1.Counterparty <> x.Counterparty THEN c1.Counterparty
ELSE NULL END AS [Old Value]
,[Valuation date]
FROM CTE c1
CROSS APPLY (SELECT [TRADE TYPE CODE], Counterparty
FROM CTE c2 WHERE c2.rn=1 AND c1.[Deal ID] = c2.[Deal ID] AND
c1.[Contract Month] = c2.[Contract Month] AND c1.BS = c1.BS) x
WHERE rn>1
DROP TABLE #TEMP3
Result row is:
Deal IDContract MonthBSPORTFOLIO IDTRADE TYPEOld ValueOld ValueValuation date
6691651201204PURCHASE49425XAB2012-03-30 00:00:00.000
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
July 1, 2012 at 10:34 pm
Excellent solution..Thanks a lot mate..I really wholeheartedly appreciate that *clap*
#respect
July 1, 2012 at 10:53 pm
Your welcome. That's actually the first time I've had a chance to try a correlated subquery inside a CROSS APPLY!
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply