January 3, 2012 at 5:12 am
I need update adapt a query that I use to present trade transactions. This is based on post i posted a few weeks back:
http://qa.sqlservercentral.com/Forums/Topic1221241-392-1.aspx
In my data set are rows that pertain to individual trade transactions. A package is made up of several transactions. Each transaction contains a broker, portfolio, commission and gross amount. Within each transaction there can be placeholder
brokers and place older portfolios. i.e.values that are used as dummies to fill fields in the source entry system. For each package there can only be on real broker
My current query pulls out all the rows(transactions) per package which have a real
portfolio and then for those same lines updates the broker field with correct/real broker for that package.
I determine if a broker or portfolio is real is to check if it exists in the portfolio and broker table. I have provided some sample data for clarity.
CREATE TABLE #TradeDetail
(
Package varchar(12)
, [Broker] varchar(50)
, Portfolio VARCHAR(20)
, GrossAmount_EUR numeric(18,6)
, Commission_EUR numeric(18,6)
)
CREATE TABLE #Broker
(
ID INT IDENTITY (1,1)
, [Broker] varchar(50)
)
CREATE TABLE #Portfolio
(
ID INT IDENTITY (1,1)
, [Portfolio] varchar(50)
)
INSERT INTO #TradeDetail
SELECT '123456','Good_Broker','Investment','120','130' UNION ALL
SELECT '123456','Bad_Broker1','Investment_false','100','130' UNION ALL
SELECT '123456','Bad_Broker2','InvestCo','120','100' UNION ALL
SELECT '123456','Bad_Broker3','Investment_false','100','130' UNION ALL
SELECT '123456','Bad_Broker4','Investment_Cash','100','150' UNION ALL
SELECT '123456','Bad_Broker5','PortfolioCO','150','100' UNION ALL
SELECT '789110','Good_Broker2','InvestmentCO2','150','100' UNION ALL
SELECT '789110','Bad_Broker6','Investment_false','150','100' UNION ALL
SELECT '789110','Bad_Broker7','PortfolioCO2','150','100' UNION ALL
SELECT '789110','Bad_Broker8','Investment_Cash','150','100' UNION ALL
SELECT '789110','Bad_Broker9','PortfolioCO','150','100' UNION ALL
SELECT '453211','Good_Broker3','InvestCo','150','100' UNION ALL
SELECT '453211','Bad_Broker10','InvestmentCO1','150','100' UNION ALL
SELECT '453211','Bad_Broker11','Investment_false2','150','143' UNION ALL
SELECT '453211','Bad_Broker12','InvestmentCO2','105','200' UNION ALL
SELECT '453211','Bad_Broker13','Investment_false','100','200' UNION ALL
SELECT '453211','Bad_Broker14','Investment','100','220'
INSERT INTO #Broker
SELECT 'Good_Broker' UNION ALL
SELECT 'Good_Broker2' UNION ALL
SELECT 'Good_Broker3'
INSERT INTO #Portfolio
SELECT 'Investment' UNION ALL
SELECT 'InvestCo' UNION ALL
SELECT 'PortfolioCO1' UNION ALL
SELECT 'PortfolioCO2' UNION ALL
SELECT 'PortfolioCO3' UNION ALL
SELECT 'PortfolioCO' UNION ALL
SELECT 'InvestmentCO1' UNION ALL
SELECT 'InvestmentCO2'
Initially my output needed to be as follows
Package Broker Portfolio GrossAmount_EUR Commission_EUR
123456 Good_Broker Investment 120 130
123456 Good_Broker InvestCo 120 100
123456 Good_Broker PortfolioCO 150 100
789110 Good_Broker2 InvestmentCO2 150 100
789110 Good_Broker2 PortfolioCO2 150 100
789110 Good_Broker2 PortfolioCO 150 100
453211 Good_Broker3 InvestCo 150 100
453211 Good_Broker3 InvestmentCO1 150 100
453211 Good_Broker3 InvestmentCO2 105 200
453211 Good_Broker3 Investment 100 220
I achieved this with the following query
SELECT
td.Package
,MAX(B.[Broker]) OVER (PARTITION BY td.Package) AS [Broker]
,td.Portfolio
,td.GrossAmount_EUR
,td.Commission_EUR
FROM #TradeDetail td
LEFT JOIN #Broker b
ON B.[Broker] = td.[Broker]
WHERE EXISTS(SELECT 1 FROM #Portfolio WHERE Portfolio=td.Portfolio)
However I need to amend the query to produce columns that show the GrossAmount_EUR and Commission_EUR that was in the row containing the real Broker. These columns should be called: RealGrossAmount_EUR and RealCommission_EUR
This has to be done per partition i.e per package. Thus the new output would look like:
Package Broker Portfolio GrossAmount_EUR Commission_EUR RealGrossAmount_EUR RealCommission_EUR
123456 Good_Broker Investment 120 130 120 130
123456 Good_Broker InvestCo 120 100 120 130
123456 Good_Broker PortfolioCO 150 100 120 130
789110 Good_Broker2 InvestmentCO2 150 100 150 100
789110 Good_Broker2 PortfolioCO2 150 100 150 100
789110 Good_Broker2 PortfolioCO 150 100 150 100
453211 Good_Broker3 InvestCo 150 100 150 100
453211 Good_Broker3 InvestmentCO1 150 100 150 100
453211 Good_Broker3 InvestmentCO2 105 200 150 100
453211 Good_Broker3 Investment 100 220 150 100
any ideas? I know the solution is straight but I just cant think of it at the moment
January 3, 2012 at 7:09 am
After a lunch break a managaed to get the necessary output with the following query
SELECT
td.Package
,MAX(B.[Broker]) OVER (PARTITION BY td.Package) AS [Broker]
,td.Portfolio
,td.GrossAmount_EUR
,td.Commission_EUR
,A.GrossAmount_EUR
,A.Commission_EUR
FROM #TradeDetail td
LEFT JOIN #Broker b
ON B.[Broker] = td.[Broker]
LEFT JOIN (
SELECT T2.Package,T2.GrossAmount_EUR, T2.Commission_EUR FROM
#TradeDetail T2 LEFT JOIN #Broker b2
ON B2.[Broker] = T2.[Broker]
WHERE B2.[Broker] IS NOT NULL) A ON A.Package = td.Package
WHERE EXISTS(SELECT 1 FROM #Portfolio WHERE Portfolio=td.Portfolio)
I knew it was straight forward!!
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply