partioning data + updates - 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)

partioning data + updates

Author

Topic

sauce1979
Starting Member

47 Posts

Posted - 2011-12-13 : 17:54:38

I need to create a query that can partition a data set and the do some updates to the partitions and finally produce an output.

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 query needs to pull out all the rows(transactions) per package which have a real portfolio and then for those lines update the broker field with correct/real broker for that package. The way to 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
--drop table #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 'PortfolioCO' UNION ALL
SELECT 'PortfolioCO2' UNION ALL
SELECT 'PortfolioCO' UNION ALL
SELECT 'PortfolioCO' UNION ALL
SELECT 'InvestmentCO1' UNION ALL
SELECT 'InvestmentCO2'

With this data the output should be:

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
[/code]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-13 : 23:29:03

[code]

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)
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

sauce1979
Starting Member

47 Posts

Posted - 2011-12-14 : 01:18:29

Thanks! would never have thought of that!!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-14 : 03:36:51

wc

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources