December 19, 2011 at 7:27 am
I have data which contains trade transactions from a trading system. There are 2 scenarios found within the data.
One scenario is that 3 rows can represent a broker passing cash to an Investment vehicle. Then an Investment vehicle passing the money to a Portfolio
The other is 1 row representing broker going directly to Portfolio hence 1 line. In both cases the 3 rows or 1 row represent a trade package. However in some cases the package number is not available.
I am able to work out which rows belong to a particular package by looking at the allocation number, broker and portfolio fields.
If a row contains a valid broker and a valid portfolio then that is a package, the 1 row scenario. The valid portfolios and valid brokers are determined if they are contained in the portfolio and broker table
For the 1 row scenario a unique package number should be added to the row.
For the 3 row scenario I use the following criteria
-The allocationID within a a package are sequential.
-The first row always contains a valid broker
-The third row always contain a valid portfolio
Once packages are determined I need to add a package id number to the package. For the 3 row scenario I need to convert the 3 rows into 1 row containing the valid portfolio, valid broker plus the grossAmounts and NetAmounts
from the original first line from the package. I have provided some data to further explain the situation.
CREATE TABLE #TradeDetail
(
AllocationID int IDENTITY(1,1)
, 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 '','Good_Broker','Investment_false','120','130' UNION ALL
SELECT '','Bad_Broker1','Investment_false','-120','-130' UNION ALL
SELECT '','Bad_Broker2','InvestCo','120','130' UNION ALL
SELECT '','Good_Broker2','Investment_false','100','150' UNION ALL
SELECT '','Bad_Broker4','Investment_false','-100','-150' UNION ALL
SELECT '','Bad_Broker5','PortfolioCO','100','150' UNION ALL
SELECT '','Good_Broker3','Investment_false','250','100' UNION ALL
SELECT '','Bad_Broker6','Investment_false','-250','-100' UNION ALL
SELECT '','Bad_Broker7','PortfolioCO2','250','100' UNION ALL
SELECT '','Good_Broker3','Investment_false','350','100' UNION ALL
SELECT '','Bad_Broker9','Investment_false','-350','-100' UNION ALL
SELECT '','Bad_Broker9','InvestCo','350','100' UNION ALL
SELECT '','Good_Broker3','Investment_false2','150','100' UNION ALL
SELECT '','Bad_Broker11','Investment_false2','-150','-100' UNION ALL
SELECT '','Bad_Broker12','InvestmentCO2','150','100' UNION ALL
SELECT '','Good_Broker3','InvestmentCO2','100','200' UNION ALL
SELECT '','Good_Broker3','InvestCo','400','300'
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 the above data the output should be as follows
AllocationID Package Broker Portfolio GrossAmount_EUR Commission_EUR
__________________________________________________________________________________________________
1UniquepackageID Good_Broker InvestCo120.000000130.000000
4UniquepackageID Good_Broker2 PortfolioCO100.000000150.000000
7UniquepackageID Good_Broker3 PortfolioCO2250.000000100.000000
10UniquepackageID Good_Broker3 InvestCo350.000000100.000000
13UniquepackageID Good_Broker3 InvestmentCO2150.000000100.000000
16UniquepackageID Good_Broker3 InvestmentCO2 100.000000200.000000
17UniquepackageID Good_Broker3 InvestCo 400.000000300.000000
December 19, 2011 at 10:22 pm
;with
q1 as (select *
,case when exists(select * from #Broker b where b.Broker=td.Broker) then 1 else 0 end StartT
,case when exists(select * from #Portfolio p where p.Portfolio=td.Portfolio) then 1 else 0 end EndT
from #TradeDetail td
)
,q2 as (select *
,(select MIN(AllocationID) from q1 q where q.AllocationID>=q1.AllocationID and q.EndT=1) TN
from q1
)
,q3 as (select MIN(AllocationID) MinID,MAX(AllocationID) MaxID
from q2
group by TN having MAX(AllocationID)-MIN(AllocationID)=COUNT(*)-1
)
select a.AllocationID,a.Broker,b.Portfolio,b.GrossAmount_EUR,b.Commission_EUR
from q3 join q1 a on q3.MinID=a.AllocationID
join q1 b on q3.MaxID=b.AllocationID
or so
;with
q1 as (select *
,case when exists(select * from #Broker b where b.Broker=td.Broker) then 1 else 0 end StartT
,case when exists(select * from #Portfolio p where p.Portfolio=td.Portfolio) then 1 else 0 end EndT
from @TradeDetail td
)
,q2 as (select *
,(select MIN(AllocationID) from q1 q where q.AllocationID>=q1.AllocationID and q.EndT=1) TN
from q1 where q1.StartT=1
)
select q2.AllocationID,q2.Broker,a.Portfolio,a.GrossAmount_EUR,a.Commission_EUR
from q2 join q1 a on a.AllocationID=q2.TN
December 20, 2011 at 1:51 am
many thanks for that. Have been staring at the screen all day.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply