December 5, 2011 at 9:24 am
I am currently developing some reports based on fact data in or trading datawarehouse. One of those reports is a data provision which provides summary data in cross tab format. The stroed procedure calls a another stored procedure which loads base data into a table variable. The data in this data variable is then used as base data which is summarised to produce the output. I am using a series of sum case statements to create a multi aggregate corss tab
The procedure accepts parameters, one of which is a date parameter. The Fact table contains data from jan 2010 to Ocotber 2011. If the date parameter is between jan 2010 and apr 2010 the query retrieves the output in about 10 seconds. If I use any date above that it takes anything from a 1min 30 sec to 2min 30secs.
I have included the sql of the procedure below along with the table definitions and execution plan.
stroed procedure
-- ===========================================================================================================
-- Author: Eseosa Omoregie
-- Create date: 20111123
-- Last modified:
-- Description: Returns trade Aggregate data for Equities
-- ===========================================================================================================
-- Use Examples:
/*
EXEC [prv].[sp_TradeAggregate_Equity] '', 'ALL','ALL','ALL'
EXEC [prv].[sp_TradeAggregate_Equity] 'Broker','E','ALL','ALL','ALL','201110'
EXEC [prv].[sp_TradeAggregate_Equity] 'Broker','E','ALL','ALL','ALL','201101'
EXEC [prv].[sp_TradeAggregate_Equity] 'Portfolio','E','ALL','ALL','ALL','201109'
EXEC [prv].[sp_TradeAggregate_Equity] 'E','ABG SUNDAL COLLIER','ALL','ALL','201110'
EXEC [prv].[sp_TradeAggregate_Equity] 'E','ABG SUNDAL COLLIER','Common Stock','ALL','201110'
EXEC [prv].[sp_TradeAggregate_Equity] 'E','ABN-AMRO','ALL','ALL','201110'
EXEC [prv].[sp_TradeAggregate_Equity] 'E','SG COWEN SECURITIES','ALL','ALL','201110'
*/
ALTER PROCEDURE [prv].[sp_TradeAggregate_Equity]
@ReportView varchar(12) = 'Order'
,@TradeDataType char(8) = ''-- Default current date
,@Broker varchar(120) = 'ALL'
,@InstrumentType varchar(120) = 'ALL'-- Default all
,@TraderRegion char(3) = 'ALL'-- Default all
,@ToMonth char(8) = '' -- default all
AS
--- FOR PREVENTION OF PARAMETER SNIFFING
DECLARE @MyReportView varchar(12);
DECLARE @MyTradeDataType char(8);-- Default current date
DECLARE @MyBroker varchar(120);
DECLARE @MyInstrumentType varchar(120); -- Default all
DECLARE @MyTraderRegion char(3); -- Default all
DECLARE @MyToMonth char(8);
-- ===========================================================================
-- (1) Check Parameters and set defaults
-- ===========================================================================
IF @TradeDataType NOT IN ('E','C,','D') SET @TradeDataType = 'E'
IF @Broker = '' SET @Broker = 'ALL';
IF @InstrumentType= '' SET @InstrumentType = 'ALL';
IF @TraderRegion = '' SET @TraderRegion = 'ALL';
IF @ToMonth = ''
SET @ToMonth = convert(char(8),getdate(),112);--Pertains to default
ELSE
SET @ToMonth = --Pertains scenario where date is passed as parameter
CASE
WHEN @ToMonth between 175301 and 999912 and
@ToMonth%100 between 1 and 12
THEN convert(char(8),dateadd(mm,(((@ToMonth/100)-1900)*12)+(@ToMonth%100),-1),112)
END
-- ===========================================================================
-- (2) Load Base Data into table variable to be aggregated
-- ===========================================================================
DECLARE @TradeDetail TABLE (
DataSourceName varchar(120)
,CorporateRegion char(3)
,Country char(3)
,TradeDateKey int
,TradeOrderNumber varchar(50)
,TradeTransactionNumber varchar(50)
,TransactionType varchar(20)
,ISIN char(12)
,IssueCountry char(3)
,UPI varchar(20)
,Portfolio varchar(120)
,Boutique varchar(120)
,Strategy varchar(120)
,Broker varchar(120)
,ParentBrokerCode varchar(120)
,AssetClassCode char(1)
,AssetClassName varchar(120)
,InstrumentType varchar(120)
,PortfolioManager_Number varchar(18)
,PortfolioManager_Name varchar(120)
,Trader_Number varchar(18)
,Trader_Name varchar(120)
,Trader_Region varchar(100)
,DebtCategory varchar(120)
,InstrumentAttributes varchar(120)
,OECD_MemberFlag char(1)
,CreditGrade varchar(120)
,GrossAmount_EUR numeric(18,6)
,Commission_EUR numeric(18,6)
,GrossAmount_USD numeric(18,6)
,Commission_USD numeric(18,6)
,Quantity numeric(18,6)
)
INSERT INTO @TradeDetail
EXEC [prv].[sp_TradeDetail] '20100101', @ToMonth,'ALL','ALL','ALL',@Broker,@TradeDataType,@InstrumentType
-- ====================================================================================
-- (4) Prepare Cross Tab dependent on the Reportview variable i.e Portfolio or Broker
-- ====================================================================================
IF @ReportView = 'Broker'
SELECT DataSourceName,
CorporateRegion,
Country,
Broker,
ParentBrokerCode AS BrokerID,
Trader_Number AS TraderID,
Trader_Region AS TraderRegion,
AssetClassCode,
AssetClassName,
InstrumentType,
TransactionType,
coalesce(SUM(PreviousYear1_GrossAmount_EUR_Dec),0) AS PreviousYear1_GrossAmount_EUR_Dec,
coalesce(MAX(GrossAmount_EUR_Jan),0) AS GrossAmount_EUR_Jan,
coalesce(MAX(GrossAmount_EUR_Feb),0)AS GrossAmount_EUR_Feb,
coalesce(MAX(GrossAmount_EUR_Mar),0) AS GrossAmount_EUR_Mar,
coalesce(MAX(GrossAmount_EUR_Apr),0) AS GrossAmount_EUR_Apr,
coalesce(MAX(GrossAmount_EUR_May),0) AS GrossAmount_EUR_May,
coalesce(MAX(GrossAmount_EUR_Jun),0) AS GrossAmount_EUR_Jun,
coalesce(MAX(GrossAmount_EUR_Jul),0) AS GrossAmount_EUR_Jul,
coalesce(MAX(GrossAmount_EUR_Aug),0) AS GrossAmount_EUR_Aug,
coalesce(MAX(GrossAmount_EUR_Sep),0) AS GrossAmount_EUR_Sep,
coalesce(MAX(GrossAmount_EUR_Oct),0) AS GrossAmount_EUR_Oct,
coalesce(MAX(GrossAmount_EUR_Nov),0) AS GrossAmount_EUR_Nov,
coalesce(MAX(GrossAmount_EUR_Dec),0) AS GrossAmount_EUR_Dec,
coalesce(SUM(GrossAmount_EUR_YTD),0) AS GrossAmount_EUR_YTD,
coalesce(SUM(GrossAmount_EUR_YTD),0)/MONTH(@ToMonth)* 12 AS GrossAmount_EUR_ANN,
coalesce(SUM(PreviousYear1_Commission_EUR_Dec),0) AS PreviousYear1_Commission_EUR_Dec,
coalesce(MAX(Commission_EUR_Jan),0) AS Commission_EUR_Jan,
coalesce(MAX(Commission_EUR_Feb),0) AS Commission_EUR_Feb,
coalesce(MAX(Commission_EUR_Mar),0) AS Commission_EUR_Mar,
coalesce(MAX(Commission_EUR_Apr),0) AS Commission_EUR_Apr,
coalesce(MAX(Commission_EUR_May),0) AS Commission_EUR_May,
coalesce(MAX(Commission_EUR_Jun),0) AS Commission_EUR_Jun,
coalesce(MAX(Commission_EUR_Jul),0) AS Commission_EUR_Jul,
coalesce(MAX(Commission_EUR_Aug),0) AS Commission_EUR_Aug,
coalesce(MAX(Commission_EUR_Sep),0) AS Commission_EUR_Sep,
coalesce(MAX(Commission_EUR_Oct),0) AS Commission_EUR_Oct,
coalesce(MAX(Commission_EUR_Nov),0) AS Commission_EUR_Nov,
coalesce(MAX(Commission_EUR_Dec),0) AS Commission_EUR_Dec,
coalesce(SUM(Commission_EUR_YTD),0) AS Commission_EUR_YTD,
coalesce(SUM(Commission_EUR_YTD),0)/MONTH(@ToMonth)* 12 AS Commission_EUR_ANN,
coalesce(SUM(OrdersJan),0) AS OrdersJan,
coalesce(SUM(OrdersFeb),0) AS OrdersFeb,
coalesce(SUM(OrdersMar),0) AS OrdersMar,
coalesce(SUM(OrdersApr),0) AS OrdersApr,
coalesce(SUM(OrdersMay),0) AS OrdersMay,
coalesce(SUM(OrdersJun),0) AS OrdersJun,
coalesce(SUM(OrdersJul),0) AS OrdersJul,
coalesce(SUM(OrdersAug),0) AS OrdersAug,
coalesce(SUM(OrdersSep),0) AS OrdersSep,
coalesce(SUM(OrdersOct),0) AS OrdersOct,
coalesce(SUM(OrdersNov),0) AS OrdersNov,
coalesce(SUM(OrdersDec),0) AS OrdersDec,
coalesce(SUM(Orders_EUR_YTD),0) AS Orders_EUR_YTD,
SUM(Orders_EUR_YTD /MONTH(@ToMonth)* 12) AS Orders_EUR_ANN,
coalesce(MAX(PreviousYear1Turnover),0) AS PreviousYear1Turnover,
coalesce(SUM(PreviousYear1Commission),0) AS PreviousYear1Commission,
coalesce(SUM(PreviousYear1Orders),0) AS PreviousYear1Orders,
coalesce(SUM(PreviousYear2Turnover),0) AS PreviousYear2Turnover,
coalesce(SUM(PreviousYear2Commission),0) AS PreviousYear2Commission,
coalesce(SUM(PreviousYear2Orders),0) AS PreviousYear2Orders,
coalesce(SUM(PreviousYear3Turnover),0) AS PreviousYear3Turnover,
coalesce(SUM(PreviousYear3Commission),0) AS PreviousYear3Commission,
coalesce(SUM(PreviousYear3Orders),0) AS PreviousYear3Orders
FROM
(SELECTDataSourceName,
CorporateRegion,
Country,
Broker,
ParentBrokerCode,
Trader_Number,
Trader_Region,
AssetClassCode,
AssetClassName,
InstrumentType,
TransactionType,
SUM(CASE WHEN MONTH(CONVERT(smalldatetime, CONVERT(char(8), TradeDateKey)) ) = 1 AND YEAR(CONVERT(smalldatetime, CONVERT(char(8), TradeDateKey)) ) = YEAR(@ToMonth)THEN GrossAmount_EUR END) AS GrossAmount_EUR_Jan,
SUM(CASE WHEN MONTH(CONVERT(smalldatetime, CONVERT(char(8), TradeDateKey)) ) = 2 AND YEAR(CONVERT(smalldatetime, CONVERT(char(8), TradeDateKey)) ) = YEAR(@ToMonth)THEN GrossAmount_EUR END) AS GrossAmount_EUR_Feb,
SUM(CASE WHEN MONTH(CONVERT(smalldatetime, CONVERT(char(8), TradeDateKey)) ) = 3 AND YEAR(CONVERT(smalldatetime, CONVERT(char(8), TradeDateKey)) ) = YEAR(@ToMonth)THEN GrossAmount_EUR END) AS GrossAmount_EUR_Mar,
SUM(CASE WHEN MONTH(CONVERT(smalldatetime, CONVERT(char(8), TradeDateKey)) ) = 4 AND YEAR(CONVERT(smalldatetime, CONVERT(char(8), TradeDateKey)) ) = YEAR(@ToMonth)THEN GrossAmount_EUR END) AS GrossAmount_EUR_Apr,
SUM(CASE WHEN MONTH(CONVERT(smalldatetime, CONVERT(char(8), TradeDateKey)) ) = 5 AND YEAR(CONVERT(smalldatetime, CONVERT(char(8), TradeDateKey)) ) = YEAR(@ToMonth)THEN GrossAmount_EUR END) AS GrossAmount_EUR_May,
SUM(CASE WHEN MONTH(CONVERT(smalldatetime, CONVERT(char(8), TradeDateKey)) ) = 6 AND YEAR(CONVERT(smalldatetime, CONVERT(char(8), TradeDateKey)) ) = YEAR(@ToMonth)THEN GrossAmount_EUR END) AS GrossAmount_EUR_Jun,
SUM(CASE WHEN MONTH(CONVERT(smalldatetime, CONVERT(char(8), TradeDateKey)) ) = 7 AND YEAR(CONVERT(smalldatetime, CONVERT(char(8), TradeDateKey)) ) = YEAR(@ToMonth)THEN GrossAmount_EUR END) AS GrossAmount_EUR_Jul,
SUM(CASE WHEN MONTH(CONVERT(smalldatetime, CONVERT(char(8), TradeDateKey)) ) = 8 AND YEAR(CONVERT(smalldatetime, CONVERT(char(8), TradeDateKey)) ) = YEAR(@ToMonth)THEN GrossAmount_EUR END) AS GrossAmount_EUR_Aug,
SUM(CASE WHEN MONTH(CONVERT(smalldatetime, CONVERT(char(8), TradeDateKey)) ) = 9 AND YEAR(CONVERT(smalldatetime, CONVERT(char(8), TradeDateKey)) ) = YEAR(@ToMonth)THEN GrossAmount_EUR END) AS GrossAmount_EUR_Sep,
SUM(CASE WHEN MONTH(CONVERT(smalldatetime, CONVERT(char(8), TradeDateKey)) ) = 10 AND YEAR(CONVERT(smalldatetime, CONVERT(char(8), TradeDateKey)) ) = YEAR(@ToMonth)THEN GrossAmount_EUR END) AS GrossAmount_EUR_Oct,
SUM(CASE WHEN MONTH(CONVERT(smalldatetime, CONVERT(char(8), TradeDateKey)) ) = 11 AND YEAR(CONVERT(smalldatetime, CONVERT(char(8), TradeDateKey)) ) = YEAR(@ToMonth)THEN GrossAmount_EUR END) AS GrossAmount_EUR_Nov,
SUM(CASE WHEN MONTH(CONVERT(smalldatetime, CONVERT(char(8), TradeDateKey)) ) = 12 AND YEAR(CONVERT(smalldatetime, CONVERT(char(8), TradeDateKey)) ) = YEAR(@ToMonth)THEN GrossAmount_EUR END) AS GrossAmount_EUR_Dec,
SUM(CASE WHEN CONVERT(smalldatetime, CONVERT(char(8), TradeDateKey)) BETWEEN DATEADD(DAY,0,DATEADD(YEAR,DATEDIFF(YEAR,0,@ToMonth),0)) AND @ToMonth THEN GrossAmount_EUR END)AS GrossAmount_EUR_YTD,
SUM(CASE WHEN CONVERT(smalldatetime, CONVERT(char(8), TradeDateKey))
BETWEEN DATEADD(MONTH,-1,DATEADD(YEAR,DATEDIFF(YEAR,0,@ToMonth),0)) AND DATEADD(DAY,-1,DATEADD(YEAR,DATEDIFF(YEAR,0,@ToMonth),0)) THEN GrossAmount_EUR END) PreviousYear1_GrossAmount_EUR_Dec,
SUM(CASE WHEN MONTH(CONVERT(smalldatetime, CONVERT(char(8), TradeDateKey)) ) = 1 AND YEAR(CONVERT(smalldatetime, CONVERT(char(8), TradeDateKey)) ) = YEAR(@ToMonth)THEN Commission_EUR END) AS Commission_EUR_Jan,
SUM(CASE WHEN MONTH(CONVERT(smalldatetime, CONVERT(char(8), TradeDateKey)) ) = 2 AND YEAR(CONVERT(smalldatetime, CONVERT(char(8), TradeDateKey)) ) = YEAR(@ToMonth)THEN Commission_EUR END) AS Commission_EUR_Feb,
SUM(CASE WHEN MONTH(CONVERT(smalldatetime, CONVERT(char(8), TradeDateKey)) ) = 3 AND YEAR(CONVERT(smalldatetime, CONVERT(char(8), TradeDateKey)) ) = YEAR(@ToMonth)THEN Commission_EUR END) AS Commission_EUR_Mar,
SUM(CASE WHEN MONTH(CONVERT(smalldatetime, CONVERT(char(8), TradeDateKey)) ) = 4 AND YEAR(CONVERT(smalldatetime, CONVERT(char(8), TradeDateKey)) ) = YEAR(@ToMonth)THEN Commission_EUR END) AS Commission_EUR_Apr,
SUM(CASE WHEN MONTH(CONVERT(smalldatetime, CONVERT(char(8), TradeDateKey)) ) = 5 AND YEAR(CONVERT(smalldatetime, CONVERT(char(8), TradeDateKey)) ) = YEAR(@ToMonth)THEN Commission_EUR END) AS Commission_EUR_May,
SUM(CASE WHEN MONTH(CONVERT(smalldatetime, CONVERT(char(8), TradeDateKey)) ) = 6 AND YEAR(CONVERT(smalldatetime, CONVERT(char(8), TradeDateKey)) ) = YEAR(@ToMonth)THEN Commission_EUR END) AS Commission_EUR_Jun,
SUM(CASE WHEN MONTH(CONVERT(smalldatetime, CONVERT(char(8), TradeDateKey)) ) = 7 AND YEAR(CONVERT(smalldatetime, CONVERT(char(8), TradeDateKey)) ) = YEAR(@ToMonth)THEN Commission_EUR END) AS Commission_EUR_Jul,
SUM(CASE WHEN MONTH(CONVERT(smalldatetime, CONVERT(char(8), TradeDateKey)) ) = 8 AND YEAR(CONVERT(smalldatetime, CONVERT(char(8), TradeDateKey)) ) = YEAR(@ToMonth)THEN Commission_EUR END) AS Commission_EUR_Aug,
SUM(CASE WHEN MONTH(CONVERT(smalldatetime, CONVERT(char(8), TradeDateKey)) ) = 9 AND YEAR(CONVERT(smalldatetime, CONVERT(char(8), TradeDateKey)) ) = YEAR(@ToMonth)THEN Commission_EUR END) AS Commission_EUR_Sep,
SUM(CASE WHEN MONTH(CONVERT(smalldatetime, CONVERT(char(8), TradeDateKey)) ) = 10 AND YEAR(CONVERT(smalldatetime, CONVERT(char(8), TradeDateKey)) ) = YEAR(@ToMonth)THEN Commission_EUR END) AS Commission_EUR_Oct,
SUM(CASE WHEN MONTH(CONVERT(smalldatetime, CONVERT(char(8), TradeDateKey)) ) = 11 AND YEAR(CONVERT(smalldatetime, CONVERT(char(8), TradeDateKey)) ) = YEAR(@ToMonth)THEN Commission_EUR END) AS Commission_EUR_Nov,
SUM(CASE WHEN MONTH(CONVERT(smalldatetime, CONVERT(char(8), TradeDateKey)) ) = 12 THEN Commission_EUR END) AS Commission_EUR_Dec,
SUM(CASE WHEN CONVERT(smalldatetime, CONVERT(char(8), TradeDateKey)) BETWEEN DATEADD(DAY,0,DATEADD(YEAR,DATEDIFF(YEAR,0,@ToMonth),0)) AND @ToMonth THEN Commission_EUR END)AS Commission_EUR_YTD,
SUM(CASE WHEN CONVERT(smalldatetime, CONVERT(char(8), TradeDateKey))
BETWEEN DATEADD(MONTH,-1,DATEADD(YEAR,DATEDIFF(YEAR,0,@ToMonth),0)) AND DATEADD(DAY,-1,DATEADD(YEAR,DATEDIFF(YEAR,0,@ToMonth),0)) THEN Commission_EUR END) PreviousYear1_Commission_EUR_Dec,
COUNT(DISTINCT CASE WHEN MONTH(CONVERT(smalldatetime, CONVERT(char(8), TradeDateKey)) ) = 1 AND YEAR(CONVERT(smalldatetime, CONVERT(char(8), TradeDateKey)) ) = YEAR(@ToMonth)THEN TradeOrderNumber END) AS OrdersJan,
COUNT(DISTINCT CASE WHEN MONTH(CONVERT(smalldatetime, CONVERT(char(8), TradeDateKey)) ) = 2 AND YEAR(CONVERT(smalldatetime, CONVERT(char(8), TradeDateKey)) ) = YEAR(@ToMonth)THEN TradeOrderNumber END) AS OrdersFeb,
COUNT(DISTINCT CASE WHEN MONTH(CONVERT(smalldatetime, CONVERT(char(8), TradeDateKey)) ) = 3 AND YEAR(CONVERT(smalldatetime, CONVERT(char(8), TradeDateKey)) ) = YEAR(@ToMonth)THEN TradeOrderNumber END) AS OrdersMar,
COUNT(DISTINCT CASE WHEN MONTH(CONVERT(smalldatetime, CONVERT(char(8), TradeDateKey)) ) = 4 AND YEAR(CONVERT(smalldatetime, CONVERT(char(8), TradeDateKey)) ) = YEAR(@ToMonth)THEN TradeOrderNumber END) AS OrdersApr,
COUNT(DISTINCT CASE WHEN MONTH(CONVERT(smalldatetime, CONVERT(char(8), TradeDateKey)) ) = 5 AND YEAR(CONVERT(smalldatetime, CONVERT(char(8), TradeDateKey)) ) = YEAR(@ToMonth)THEN TradeOrderNumber END) AS OrdersMay,
COUNT(DISTINCT CASE WHEN MONTH(CONVERT(smalldatetime, CONVERT(char(8), TradeDateKey)) ) = 6 AND YEAR(CONVERT(smalldatetime, CONVERT(char(8), TradeDateKey)) ) = YEAR(@ToMonth)THEN TradeOrderNumber END) AS OrdersJun,
COUNT(DISTINCT CASE WHEN MONTH(CONVERT(smalldatetime, CONVERT(char(8), TradeDateKey)) ) = 7 AND YEAR(CONVERT(smalldatetime, CONVERT(char(8), TradeDateKey)) ) = YEAR(@ToMonth)THEN TradeOrderNumber END) AS OrdersJul,
COUNT(DISTINCT CASE WHEN MONTH(CONVERT(smalldatetime, CONVERT(char(8), TradeDateKey)) ) = 8 AND YEAR(CONVERT(smalldatetime, CONVERT(char(8), TradeDateKey)) ) = YEAR(@ToMonth)THEN TradeOrderNumber END) AS OrdersAug,
COUNT(DISTINCT CASE WHEN MONTH(CONVERT(smalldatetime, CONVERT(char(8), TradeDateKey)) ) = 9 AND YEAR(CONVERT(smalldatetime, CONVERT(char(8), TradeDateKey)) ) = YEAR(@ToMonth)THEN TradeOrderNumber END) AS OrdersSep,
COUNT(DISTINCT CASE WHEN MONTH(CONVERT(smalldatetime, CONVERT(char(8), TradeDateKey)) ) = 10 AND YEAR(CONVERT(smalldatetime, CONVERT(char(8), TradeDateKey)) ) = YEAR(@ToMonth)THEN TradeOrderNumber END) AS OrdersOct,
COUNT(DISTINCT CASE WHEN MONTH(CONVERT(smalldatetime, CONVERT(char(8), TradeDateKey)) ) = 11 AND YEAR(CONVERT(smalldatetime, CONVERT(char(8), TradeDateKey)) ) = YEAR(@ToMonth)THEN TradeOrderNumber END) AS OrdersNov,
COUNT(DISTINCT CASE WHEN MONTH(CONVERT(smalldatetime, CONVERT(char(8), TradeDateKey)) ) = 12 AND YEAR(CONVERT(smalldatetime, CONVERT(char(8), TradeDateKey)) ) = YEAR(@ToMonth)THEN TradeOrderNumber END) AS OrdersDec,
CAST(COUNT(DISTINCT CASE WHEN CONVERT(smalldatetime, CONVERT(char(8), TradeDateKey)) BETWEEN DATEADD(DAY,0,DATEADD(YEAR,DATEDIFF(YEAR,0,@ToMonth),0)) AND @ToMonth THEN TradeOrderNumber END)AS FLOAT ) AS OrderS_EUR_YTD,
COUNT(DISTINCT CASE WHEN CONVERT(smalldatetime, CONVERT(char(8), TradeDateKey))
BETWEEN DATEADD(MONTH,-1,DATEADD(YEAR,DATEDIFF(YEAR,0,@ToMonth),0)) AND DATEADD(DAY,-1,DATEADD(YEAR,DATEDIFF(YEAR,0,@ToMonth),0)) THEN TradeOrderNumber END) PreviousYear1_Orders_EUR_Dec,
SUM(CASE WHEN MONTH(CONVERT(smalldatetime, CONVERT(char(8), TradeDateKey)) ) >= MONTH(DATEADD(MM,-12,@ToMonth))THEN GrossAmount_EUR END) AS PreviousYear1Turnover,
SUM(CASE WHEN MONTH(CONVERT(smalldatetime, CONVERT(char(8), TradeDateKey)) ) >= MONTH(DATEADD(MM,-12,@ToMonth))THEN Commission_EUR END)AS PreviousYear1Commission,
COUNT(DISTINCT CASE WHEN MONTH(CONVERT(smalldatetime, CONVERT(char(8), TradeDateKey)) ) >= MONTH(DATEADD(MM,-12,@ToMonth))THEN TradeOrderNumber END) AS PreviousYear1Orders,
SUM(CASE WHEN CONVERT(smalldatetime, CONVERT(char(8), TradeDateKey)) BETWEEN DATEADD(MM,-12,@ToMonth) AND DATEADD(MM,-24,@ToMonth) THEN GrossAmount_EUR END) AS PreviousYear2Turnover,
SUM(CASE WHEN CONVERT(smalldatetime, CONVERT(char(8), TradeDateKey)) BETWEEN DATEADD(MM,-12,@ToMonth) AND DATEADD(MM,-24,@ToMonth) THEN Commission_EUR END) AS PreviousYear2Commission,
COUNT(DISTINCT CASE WHEN CONVERT(smalldatetime, CONVERT(char(8), TradeDateKey)) BETWEEN DATEADD(MM,-12,@ToMonth) AND DATEADD(MM,-24,@ToMonth) THEN TradeOrderNumber END) AS PreviousYear2Orders,
SUM(CASE WHEN CONVERT(smalldatetime, CONVERT(char(8), TradeDateKey)) BETWEEN DATEADD(MM,-24,@ToMonth) AND DATEADD(MM,-36,@ToMonth) THEN GrossAmount_EUR END) AS PreviousYear3Turnover,
SUM(CASE WHEN CONVERT(smalldatetime, CONVERT(char(8), TradeDateKey)) BETWEEN DATEADD(MM,-24,@ToMonth) AND DATEADD(MM,-36,@ToMonth) THEN Commission_EUR END) AS PreviousYear3Commission,
COUNT(DISTINCT CASE WHEN CONVERT(smalldatetime, CONVERT(char(8), TradeDateKey)) BETWEEN DATEADD(MM,-24,@ToMonth) AND DATEADD(MM,-36,@ToMonth) THEN TradeOrderNumber END) AS PreviousYear3Orders,
SUM(CASE WHEN CONVERT(smalldatetime, CONVERT(char(8), TradeDateKey)) BETWEEN DATEADD(MM,-36,@ToMonth) AND DATEADD(MM,-48,@ToMonth) THEN GrossAmount_EUR END) AS PreviousYear4Turnover,
SUM(CASE WHEN CONVERT(smalldatetime, CONVERT(char(8), TradeDateKey)) BETWEEN DATEADD(MM,-36,@ToMonth) AND DATEADD(MM,-48,@ToMonth) THEN Commission_EUR END) AS PreviousYear4Commission,
COUNT(DISTINCT CASE WHEN CONVERT(smalldatetime, CONVERT(char(8), TradeDateKey)) BETWEEN DATEADD(MM,-36,@ToMonth) AND DATEADD(MM,-48,@ToMonth) THEN TradeOrderNumber END) AS PreviousYear4Orders
FROM @TradeDetail
GROUP BY DataSourceName,CorporateRegion,Country,Broker, ParentBrokerCode,Trader_Number,Trader_Region,AssetClassCode, AssetClassName,InstrumentType,TransactionType
) rawData
GROUP BY DataSourceName,CorporateRegion,Country,Broker,ParentBrokerCode,Trader_Number,Trader_Region,AssetClassCode,AssetClassName,InstrumentType,TransactionType
ELSE
SELECTDataSourceName,
CorporateRegion,
Country,
Broker,
ParentBrokerCode AS BrokerID,
UPI AS PortfolioID,
Portfolio,
Boutique,
Trader_Number AS TraderID,
Trader_Region AS TraderRegion,
AssetClassCode,
AssetClassName,
InstrumentType,
TransactionType,
coalesce(SUM(PreviousYear1_GrossAmount_EUR_Dec),0) AS PreviousYear1_GrossAmount_EUR_Dec,
coalesce(MAX(GrossAmount_EUR_Jan),0) AS GrossAmount_EUR_Jan,
coalesce(MAX(GrossAmount_EUR_Feb),0)AS GrossAmount_EUR_Feb,
coalesce(MAX(GrossAmount_EUR_Mar),0) AS GrossAmount_EUR_Mar,
coalesce(MAX(GrossAmount_EUR_Apr),0) AS GrossAmount_EUR_Apr,
coalesce(MAX(GrossAmount_EUR_May),0) AS GrossAmount_EUR_May,
coalesce(MAX(GrossAmount_EUR_Jun),0) AS GrossAmount_EUR_Jun,
coalesce(MAX(GrossAmount_EUR_Jul),0) AS GrossAmount_EUR_Jul,
coalesce(MAX(GrossAmount_EUR_Aug),0) AS GrossAmount_EUR_Aug,
coalesce(MAX(GrossAmount_EUR_Sep),0) AS GrossAmount_EUR_Sep,
coalesce(MAX(GrossAmount_EUR_Oct),0) AS GrossAmount_EUR_Oct,
coalesce(MAX(GrossAmount_EUR_Nov),0) AS GrossAmount_EUR_Nov,
coalesce(MAX(GrossAmount_EUR_Dec),0) AS GrossAmount_EUR_Dec,
coalesce(SUM(GrossAmount_EUR_YTD),0) AS GrossAmount_EUR_YTD,
coalesce(SUM(GrossAmount_EUR_YTD),0)/MONTH(@ToMonth)* 12 AS GrossAmount_EUR_ANN,
coalesce(SUM(PreviousYear1_Commission_EUR_Dec),0) AS PreviousYear1_Commission_EUR_Dec,
coalesce(MAX(Commission_EUR_Jan),0) AS Commission_EUR_Jan,
coalesce(MAX(Commission_EUR_Feb),0) AS Commission_EUR_Feb,
coalesce(MAX(Commission_EUR_Mar),0) AS Commission_EUR_Mar,
coalesce(MAX(Commission_EUR_Apr),0) AS Commission_EUR_Apr,
coalesce(MAX(Commission_EUR_May),0) AS Commission_EUR_May,
coalesce(MAX(Commission_EUR_Jun),0) AS Commission_EUR_Jun,
coalesce(MAX(Commission_EUR_Jul),0) AS Commission_EUR_Jul,
coalesce(MAX(Commission_EUR_Aug),0) AS Commission_EUR_Aug,
coalesce(MAX(Commission_EUR_Sep),0) AS Commission_EUR_Sep,
coalesce(MAX(Commission_EUR_Oct),0) AS Commission_EUR_Oct,
coalesce(MAX(Commission_EUR_Nov),0) AS Commission_EUR_Nov,
coalesce(MAX(Commission_EUR_Dec),0) AS Commission_EUR_Dec,
coalesce(SUM(Commission_EUR_YTD),0) AS Commission_EUR_YTD,
coalesce(SUM(Commission_EUR_YTD),0)/MONTH(@ToMonth)* 12 AS Commission_EUR_ANN,
coalesce(SUM(AllocationJan),0) AS AllocationJan,
coalesce(SUM(AllocationFeb),0) AS AllocationFeb,
coalesce(SUM(AllocationMar),0) AS AllocationMar,
coalesce(SUM(AllocationApr),0) AS AllocationApr,
coalesce(SUM(AllocationMay),0) AS AllocationMay,
coalesce(SUM(AllocationJun),0) AS AllocationJun,
coalesce(SUM(AllocationJul),0) AS AllocationJul,
coalesce(SUM(AllocationAug),0) AS AllocationAug,
coalesce(SUM(AllocationSep),0) AS AllocationSep,
coalesce(SUM(AllocationOct),0) AS AllocationOct,
coalesce(SUM(AllocationNov),0) AS AllocationNov,
coalesce(SUM(AllocationDec),0) AS AllocationDec,
coalesce(SUM(Allocation_EUR_YTD),0) AS Allocation_EUR_YTD,
coalesce(SUM(Allocation_EUR_YTD),0)/MONTH(@ToMonth)* 12 AS Allocation_EUR_ANN,
coalesce(SUM(PreviousYear1Turnover),0) AS PreviousYear1Turnover,
coalesce(SUM(PreviousYear1Commission),0) AS PreviousYear1Commission,
coalesce(SUM(PreviousYear1Allocation),0) AS PreviousYear1Allocation,
coalesce(SUM(PreviousYear2Turnover),0) AS PreviousYear2Turnover,
coalesce(SUM(PreviousYear2Commission),0) AS PreviousYear2Commission,
coalesce(SUM(PreviousYear2Allocation),0) AS PreviousYear2Allocation,
coalesce(SUM(PreviousYear3Turnover),0) AS PreviousYear3Turnover,
coalesce(SUM(PreviousYear3Commission),0) AS PreviousYear3Commission,
coalesce(SUM(PreviousYear3Allocation),0) AS PreviousYear3Allocation
FROM
(SELECT DataSourceName,
CorporateRegion,
Country,
Broker,
ParentBrokerCode,
UPI,
Portfolio,
Boutique,
Trader_Number,
Trader_Region,
AssetClassCode,
AssetClassName,
InstrumentType,
TransactionType,
SUM(CASE WHEN MONTH(CONVERT(smalldatetime, CONVERT(char(8), TradeDateKey)) ) = 1 AND YEAR(CONVERT(smalldatetime, CONVERT(char(8), TradeDateKey)) ) = YEAR(@ToMonth)THEN GrossAmount_EUR END) AS GrossAmount_EUR_Jan,
SUM(CASE WHEN MONTH(CONVERT(smalldatetime, CONVERT(char(8), TradeDateKey)) ) = 2 AND YEAR(CONVERT(smalldatetime, CONVERT(char(8), TradeDateKey)) ) = YEAR(@ToMonth)THEN GrossAmount_EUR END) AS GrossAmount_EUR_Feb,
SUM(CASE WHEN MONTH(CONVERT(smalldatetime, CONVERT(char(8), TradeDateKey)) ) = 3 AND YEAR(CONVERT(smalldatetime, CONVERT(char(8), TradeDateKey)) ) = YEAR(@ToMonth)THEN GrossAmount_EUR END) AS GrossAmount_EUR_Mar,
SUM(CASE WHEN MONTH(CONVERT(smalldatetime, CONVERT(char(8), TradeDateKey)) ) = 4 AND YEAR(CONVERT(smalldatetime, CONVERT(char(8), TradeDateKey)) ) = YEAR(@ToMonth)THEN GrossAmount_EUR END) AS GrossAmount_EUR_Apr,
SUM(CASE WHEN MONTH(CONVERT(smalldatetime, CONVERT(char(8), TradeDateKey)) ) = 5 AND YEAR(CONVERT(smalldatetime, CONVERT(char(8), TradeDateKey)) ) = YEAR(@ToMonth)THEN GrossAmount_EUR END) AS GrossAmount_EUR_May,
SUM(CASE WHEN MONTH(CONVERT(smalldatetime, CONVERT(char(8), TradeDateKey)) ) = 6 AND YEAR(CONVERT(smalldatetime, CONVERT(char(8), TradeDateKey)) ) = YEAR(@ToMonth)THEN GrossAmount_EUR END) AS GrossAmount_EUR_Jun,
SUM(CASE WHEN MONTH(CONVERT(smalldatetime, CONVERT(char(8), TradeDateKey)) ) = 7 AND YEAR(CONVERT(smalldatetime, CONVERT(char(8), TradeDateKey)) ) = YEAR(@ToMonth)THEN GrossAmount_EUR END) AS GrossAmount_EUR_Jul,
SUM(CASE WHEN MONTH(CONVERT(smalldatetime, CONVERT(char(8), TradeDateKey)) ) = 8 AND YEAR(CONVERT(smalldatetime, CONVERT(char(8), TradeDateKey)) ) = YEAR(@ToMonth)THEN GrossAmount_EUR END) AS GrossAmount_EUR_Aug,
SUM(CASE WHEN MONTH(CONVERT(smalldatetime, CONVERT(char(8), TradeDateKey)) ) = 9 AND YEAR(CONVERT(smalldatetime, CONVERT(char(8), TradeDateKey)) ) = YEAR(@ToMonth)THEN GrossAmount_EUR END) AS GrossAmount_EUR_Sep,
SUM(CASE WHEN MONTH(CONVERT(smalldatetime, CONVERT(char(8), TradeDateKey)) ) = 10 AND YEAR(CONVERT(smalldatetime, CONVERT(char(8), TradeDateKey)) ) = YEAR(@ToMonth)THEN GrossAmount_EUR END) AS GrossAmount_EUR_Oct,
SUM(CASE WHEN MONTH(CONVERT(smalldatetime, CONVERT(char(8), TradeDateKey)) ) = 11 AND YEAR(CONVERT(smalldatetime, CONVERT(char(8), TradeDateKey)) ) = YEAR(@ToMonth)THEN GrossAmount_EUR END) AS GrossAmount_EUR_Nov,
SUM(CASE WHEN MONTH(CONVERT(smalldatetime, CONVERT(char(8), TradeDateKey)) ) = 12 AND YEAR(CONVERT(smalldatetime, CONVERT(char(8), TradeDateKey)) ) = YEAR(@ToMonth)THEN GrossAmount_EUR END) AS GrossAmount_EUR_Dec,
SUM(CASE WHEN CONVERT(smalldatetime, CONVERT(char(8), TradeDateKey)) BETWEEN DATEADD(DAY,0,DATEADD(YEAR,DATEDIFF(YEAR,0,@ToMonth),0)) AND @ToMonth THEN GrossAmount_EUR END)AS GrossAmount_EUR_YTD,
SUM(CASE WHEN CONVERT(smalldatetime, CONVERT(char(8), TradeDateKey))
BETWEEN DATEADD(MONTH,-1,DATEADD(YEAR,DATEDIFF(YEAR,0,@ToMonth),0)) AND DATEADD(DAY,-1,DATEADD(YEAR,DATEDIFF(YEAR,0,@ToMonth),0)) THEN GrossAmount_EUR END) PreviousYear1_GrossAmount_EUR_Dec,
SUM(CASE WHEN MONTH(CONVERT(smalldatetime, CONVERT(char(8), TradeDateKey)) ) = 1 AND YEAR(CONVERT(smalldatetime, CONVERT(char(8), TradeDateKey)) ) = YEAR(@ToMonth)THEN Commission_EUR END) AS Commission_EUR_Jan,
SUM(CASE WHEN MONTH(CONVERT(smalldatetime, CONVERT(char(8), TradeDateKey)) ) = 2 AND YEAR(CONVERT(smalldatetime, CONVERT(char(8), TradeDateKey)) ) = YEAR(@ToMonth)THEN Commission_EUR END) AS Commission_EUR_Feb,
SUM(CASE WHEN MONTH(CONVERT(smalldatetime, CONVERT(char(8), TradeDateKey)) ) = 3 AND YEAR(CONVERT(smalldatetime, CONVERT(char(8), TradeDateKey)) ) = YEAR(@ToMonth)THEN Commission_EUR END) AS Commission_EUR_Mar,
SUM(CASE WHEN MONTH(CONVERT(smalldatetime, CONVERT(char(8), TradeDateKey)) ) = 4 AND YEAR(CONVERT(smalldatetime, CONVERT(char(8), TradeDateKey)) ) = YEAR(@ToMonth)THEN Commission_EUR END) AS Commission_EUR_Apr,
SUM(CASE WHEN MONTH(CONVERT(smalldatetime, CONVERT(char(8), TradeDateKey)) ) = 5 AND YEAR(CONVERT(smalldatetime, CONVERT(char(8), TradeDateKey)) ) = YEAR(@ToMonth)THEN Commission_EUR END) AS Commission_EUR_May,
SUM(CASE WHEN MONTH(CONVERT(smalldatetime, CONVERT(char(8), TradeDateKey)) ) = 6 AND YEAR(CONVERT(smalldatetime, CONVERT(char(8), TradeDateKey)) ) = YEAR(@ToMonth)THEN Commission_EUR END) AS Commission_EUR_Jun,
SUM(CASE WHEN MONTH(CONVERT(smalldatetime, CONVERT(char(8), TradeDateKey)) ) = 7 AND YEAR(CONVERT(smalldatetime, CONVERT(char(8), TradeDateKey)) ) = YEAR(@ToMonth)THEN Commission_EUR END) AS Commission_EUR_Jul,
SUM(CASE WHEN MONTH(CONVERT(smalldatetime, CONVERT(char(8), TradeDateKey)) ) = 8 AND YEAR(CONVERT(smalldatetime, CONVERT(char(8), TradeDateKey)) ) = YEAR(@ToMonth)THEN Commission_EUR END) AS Commission_EUR_Aug,
SUM(CASE WHEN MONTH(CONVERT(smalldatetime, CONVERT(char(8), TradeDateKey)) ) = 9 AND YEAR(CONVERT(smalldatetime, CONVERT(char(8), TradeDateKey)) ) = YEAR(@ToMonth)THEN Commission_EUR END) AS Commission_EUR_Sep,
SUM(CASE WHEN MONTH(CONVERT(smalldatetime, CONVERT(char(8), TradeDateKey)) ) = 10 AND YEAR(CONVERT(smalldatetime, CONVERT(char(8), TradeDateKey)) ) = YEAR(@ToMonth)THEN Commission_EUR END) AS Commission_EUR_Oct,
SUM(CASE WHEN MONTH(CONVERT(smalldatetime, CONVERT(char(8), TradeDateKey)) ) = 11 AND YEAR(CONVERT(smalldatetime, CONVERT(char(8), TradeDateKey)) ) = YEAR(@ToMonth)THEN Commission_EUR END) AS Commission_EUR_Nov,
SUM(CASE WHEN MONTH(CONVERT(smalldatetime, CONVERT(char(8), TradeDateKey)) ) = 12 AND YEAR(CONVERT(smalldatetime, CONVERT(char(8), TradeDateKey)) ) = YEAR(@ToMonth)THEN Commission_EUR END) AS Commission_EUR_Dec,
SUM(CASE WHEN CONVERT(smalldatetime, CONVERT(char(8), TradeDateKey)) BETWEEN DATEADD(DAY,0,DATEADD(YEAR,DATEDIFF(YEAR,0,@ToMonth),0)) AND @ToMonth THEN Commission_EUR END)AS Commission_EUR_YTD,
SUM(CASE WHEN CONVERT(smalldatetime, CONVERT(char(8), TradeDateKey))
BETWEEN DATEADD(MONTH,-1,DATEADD(YEAR,DATEDIFF(YEAR,0,@ToMonth),0)) AND DATEADD(DAY,-1,DATEADD(YEAR,DATEDIFF(YEAR,0,@ToMonth),0)) THEN Commission_EUR END) PreviousYear1_Commission_EUR_Dec,
COUNT(DISTINCT CASE WHEN MONTH(CONVERT(smalldatetime, CONVERT(char(8), TradeDateKey)) ) = 1 AND YEAR(CONVERT(smalldatetime, CONVERT(char(8), TradeDateKey)) ) = YEAR(@ToMonth)THEN TradeOrderNumber END) AS AllocationJan,
COUNT(DISTINCT CASE WHEN MONTH(CONVERT(smalldatetime, CONVERT(char(8), TradeDateKey)) ) = 2 AND YEAR(CONVERT(smalldatetime, CONVERT(char(8), TradeDateKey)) ) = YEAR(@ToMonth)THEN TradeOrderNumber END) AS AllocationFeb,
COUNT(DISTINCT CASE WHEN MONTH(CONVERT(smalldatetime, CONVERT(char(8), TradeDateKey)) ) = 3 AND YEAR(CONVERT(smalldatetime, CONVERT(char(8), TradeDateKey)) ) = YEAR(@ToMonth)THEN TradeOrderNumber END) AS AllocationMar,
COUNT(DISTINCT CASE WHEN MONTH(CONVERT(smalldatetime, CONVERT(char(8), TradeDateKey)) ) = 4 AND YEAR(CONVERT(smalldatetime, CONVERT(char(8), TradeDateKey)) ) = YEAR(@ToMonth)THEN TradeOrderNumber END) AS AllocationApr,
COUNT(DISTINCT CASE WHEN MONTH(CONVERT(smalldatetime, CONVERT(char(8), TradeDateKey)) ) = 5 AND YEAR(CONVERT(smalldatetime, CONVERT(char(8), TradeDateKey)) ) = YEAR(@ToMonth)THEN TradeOrderNumber END) AS AllocationMay,
COUNT(DISTINCT CASE WHEN MONTH(CONVERT(smalldatetime, CONVERT(char(8), TradeDateKey)) ) = 6 AND YEAR(CONVERT(smalldatetime, CONVERT(char(8), TradeDateKey)) ) = YEAR(@ToMonth)THEN TradeOrderNumber END) AS AllocationJun,
COUNT(DISTINCT CASE WHEN MONTH(CONVERT(smalldatetime, CONVERT(char(8), TradeDateKey)) ) = 7 AND YEAR(CONVERT(smalldatetime, CONVERT(char(8), TradeDateKey)) ) = YEAR(@ToMonth)THEN TradeOrderNumber END) AS AllocationJul,
COUNT(DISTINCT CASE WHEN MONTH(CONVERT(smalldatetime, CONVERT(char(8), TradeDateKey)) ) = 8 AND YEAR(CONVERT(smalldatetime, CONVERT(char(8), TradeDateKey)) ) = YEAR(@ToMonth)THEN TradeOrderNumber END) AS AllocationAug,
COUNT(DISTINCT CASE WHEN MONTH(CONVERT(smalldatetime, CONVERT(char(8), TradeDateKey)) ) = 9 AND YEAR(CONVERT(smalldatetime, CONVERT(char(8), TradeDateKey)) ) = YEAR(@ToMonth)THEN TradeOrderNumber END) AS AllocationSep,
COUNT(DISTINCT CASE WHEN MONTH(CONVERT(smalldatetime, CONVERT(char(8), TradeDateKey)) ) = 10 AND YEAR(CONVERT(smalldatetime, CONVERT(char(8), TradeDateKey)) ) = YEAR(@ToMonth)THEN TradeOrderNumber END) AS AllocationOct,
COUNT(DISTINCT CASE WHEN MONTH(CONVERT(smalldatetime, CONVERT(char(8), TradeDateKey)) ) = 11 AND YEAR(CONVERT(smalldatetime, CONVERT(char(8), TradeDateKey)) ) = YEAR(@ToMonth)THEN TradeOrderNumber END) AS AllocationNov,
COUNT(DISTINCT CASE WHEN MONTH(CONVERT(smalldatetime, CONVERT(char(8), TradeDateKey)) ) = 12 AND YEAR(CONVERT(smalldatetime, CONVERT(char(8), TradeDateKey)) ) = YEAR(@ToMonth)THEN TradeOrderNumber END) AS AllocationDec,
CAST(COUNT(DISTINCT CASE WHEN CONVERT(smalldatetime, CONVERT(char(8), TradeDateKey)) BETWEEN DATEADD(DAY,0,DATEADD(YEAR,DATEDIFF(YEAR,0,@ToMonth),0)) AND @ToMonth THEN TradeOrderNumber END)AS FLOAT) AS Allocation_EUR_YTD,
COUNT(DISTINCT CASE WHEN CONVERT(smalldatetime, CONVERT(char(8), TradeDateKey))
BETWEEN DATEADD(MONTH,-1,DATEADD(YEAR,DATEDIFF(YEAR,0,@ToMonth),0)) AND DATEADD(DAY,-1,DATEADD(YEAR,DATEDIFF(YEAR,0,@ToMonth),0)) THEN TradeOrderNumber END) PreviousYear1_Orders_EUR_Dec,
SUM(CASE WHEN MONTH(CONVERT(smalldatetime, CONVERT(char(8), TradeDateKey)) ) >= MONTH(DATEADD(MM,-12,@ToMonth))THEN GrossAmount_EUR END) AS PreviousYear1Turnover,
SUM(CASE WHEN MONTH(CONVERT(smalldatetime, CONVERT(char(8), TradeDateKey)) ) >= MONTH(DATEADD(MM,-12,@ToMonth))THEN Commission_EUR END)AS PreviousYear1Commission,
COUNT(DISTINCT CASE WHEN MONTH(CONVERT(smalldatetime, CONVERT(char(8), TradeDateKey)) ) >= MONTH(DATEADD(MM,-12,@ToMonth))THEN TradeOrderNumber END) AS PreviousYear1Allocation,
SUM(CASE WHEN CONVERT(smalldatetime, CONVERT(char(8), TradeDateKey)) BETWEEN DATEADD(MM,-12,@ToMonth) AND DATEADD(MM,-24,@ToMonth) THEN GrossAmount_EUR END) AS PreviousYear2Turnover,
SUM(CASE WHEN CONVERT(smalldatetime, CONVERT(char(8), TradeDateKey)) BETWEEN DATEADD(MM,-12,@ToMonth) AND DATEADD(MM,-24,@ToMonth) THEN Commission_EUR END) AS PreviousYear2Commission,
COUNT(DISTINCT CASE WHEN CONVERT(smalldatetime, CONVERT(char(8), TradeDateKey)) BETWEEN DATEADD(MM,-12,@ToMonth) AND DATEADD(MM,-24,@ToMonth) THEN TradeOrderNumber END) AS PreviousYear2Allocation,
SUM(CASE WHEN CONVERT(smalldatetime, CONVERT(char(8), TradeDateKey)) BETWEEN DATEADD(MM,-24,@ToMonth) AND DATEADD(MM,-36,@ToMonth) THEN GrossAmount_EUR END) AS PreviousYear3Turnover,
SUM(CASE WHEN CONVERT(smalldatetime, CONVERT(char(8), TradeDateKey)) BETWEEN DATEADD(MM,-24,@ToMonth) AND DATEADD(MM,-36,@ToMonth) THEN Commission_EUR END) AS PreviousYear3Commission,
COUNT(DISTINCT CASE WHEN CONVERT(smalldatetime, CONVERT(char(8), TradeDateKey)) BETWEEN DATEADD(MM,-24,@ToMonth) AND DATEADD(MM,-36,@ToMonth) THEN TradeOrderNumber END) AS PreviousYear3Allocation,
SUM(CASE WHEN CONVERT(smalldatetime, CONVERT(char(8), TradeDateKey)) BETWEEN DATEADD(MM,-36,@ToMonth) AND DATEADD(MM,-48,@ToMonth) THEN GrossAmount_EUR END) AS PreviousYear4Turnover,
SUM(CASE WHEN CONVERT(smalldatetime, CONVERT(char(8), TradeDateKey)) BETWEEN DATEADD(MM,-36,@ToMonth) AND DATEADD(MM,-48,@ToMonth) THEN Commission_EUR END) AS PreviousYear4Commission,
COUNT(DISTINCT CASE WHEN CONVERT(smalldatetime, CONVERT(char(8), TradeDateKey)) BETWEEN DATEADD(MM,-36,@ToMonth) AND DATEADD(MM,-48,@ToMonth) THEN TradeOrderNumber END) AS PreviousYear4Allocation
FROM @TradeDetail
GROUP BY DataSourceName,CorporateRegion,Country,Broker, ParentBrokerCode,UPI,Portfolio,Boutique,Trader_Number,Trader_Region,AssetClassCode, AssetClassName,InstrumentType,TransactionType
) rawData
GROUP BY DataSourceName,CorporateRegion,Country,Broker,ParentBrokerCode,UPI,Portfolio,Boutique,Trader_Number,Trader_Region,AssetClassCode,AssetClassName,InstrumentType,TransactionType
RETURN
the called stored procedure
ALTER PROCEDURE [prv].[sp_TradeDetail]
@PeriodStart char(8) = ''-- Default current date
,@PeriodEnd char(8) = ''-- Default current data
,@CorporateRegion char(3) = 'ALL'-- Default all
,@Country char(3) = 'ALL'-- Default all
,@Team varchar(120) = 'ALL'-- Default all
,@Broker varchar(120) = 'ALL'-- Default all
,@AssetClassCode char(1) = '-'-- Default all
,@InstrumentType varchar(120) = 'ALL'-- Default all
,@Debug int = 0-- For debugging
AS
SET NOCOUNT ON
-- ===========================================================================
-- (1) Check Parameters and set defaults
-- ===========================================================================
IF @PeriodStart = '' SET @PeriodStart = convert(char(8),year(getdate()));
IF @PeriodEnd = '' SET @PeriodEnd = convert(char(8),year(getdate()));
IF @CorporateRegion = '' SET @CorporateRegion = 'ALL';
IF @Country = '' SET @Country = 'ALL';
IF @Team = '' SET @Team = 'ALL';
IF @Team = 'EMPTY' SET @Team = '';-- special case to capture unmapped boutiques
IF @Broker = '' SET @Broker = 'ALL';
IF @AssetClassCode= '' SET @AssetClassCode = '-';
IF @InstrumentType= '' SET @InstrumentType = 'ALL';
-- ===========================================================================
-- (2) Load the base data
-- ===========================================================================
SELECTS.DataSourceName AS DataSourceName
,R.CorporateRegionCode AS CorporateRegion
,N.DataSourceCountry AS Country
,N.TradeDateKey
,N.TradeOrderNumber
,N.TradeTransactionNumber
,N.TransactionType
,I.ISIN
,CASE WHEN len(ltrim(rtrim(I.IssueCountry))) = 0 THEN (SELECT TOP 1 CC.CountryCode FROM ref.Map_CountryCurrency CC JOIN ref.Country C ON CC.CountryCode = C.CountryCode WHERE CurrencyCode = N.TradeCurrency ORDER BY OECD_MemberFlag DESC) ELSE I.IssueCountry END AS IssueCountry
,P.UPI
,P.OfficialName AS Portfolio
,P.BoutiqueA AS Boutique
,P.StrategyA AS Strategy
,B.OrganizationName AS Broker
,B.OrganizationCode AS ParentBrokerCode
,T.AssetClassCode AS AssetClassCode
,T.AssetClassName AS AssetClassName
,T.InstrumentType
,EM.EmployeeNumber AS PortfolioManager_Number
,EM.EmployeeName AS PortfolioManager_Name
,ET.EmployeeNumber AS Trader_Number
,ET.EmployeeName AS Trader_Name
,ET.CorporateRegion AS Trader_Region
--,I.InstrumentCategory
,CASE
-- FI only
WHEN T.AssetClassName != 'Fixed Income' THEN ''-- Only do this for FI
-- But also ignore
WHEN T.InstrumentType IN ('Future','Mutual Fund (Open-end)','Preferred','IRS','CDS','CLN','SWAP','SWAPTION') THEN ''
-- Supras
WHEN T.InstrumentType = 'Supra National Bond' THEN 'SupraNational'
-- Agencies
WHEN T.InstrumentType IN ('Quasi-Government Bond','Quasi-Government','Quasi-Sovereign') THEN 'Agency'
-- Munies
WHEN T.InstrumentType = 'Sub-Sovereign' THEN 'Municipal'
-- Govies
WHEN T.InstrumentType IN ('Sovereign','Government Bond') THEN 'Sovereign'
-- ABS
WHEN T.InstrumentType = 'Asset-backed' THEN 'ABS'
-- FI via Bloomberg using ISIN, if BBG not available, get from strategy
WHEN (I.InstrumentCategory = 'Credit Quality Unknown' OR len(I.InstrumentCategory) = 0) AND P.StrategyA LIKE '%High%Yield%' THEN 'High Yield'
WHEN (I.InstrumentCategory = 'Credit Quality Unknown' OR len(I.InstrumentCategory) = 0) AND (P.StrategyA LIKE '%Investment%Grade%' OR P.StrategyA LIKE '%AAA%') THEN 'Investment Grade'
WHEN len(I.InstrumentCategory) > 0 THEN I.InstrumentCategory-- If captured using bloomberg, keep it
-- EM Debt
--WHEN I.OECD_MemberFlag = 'N' AND T.InstrumentType IN ('Fixed Income','Bond','Convertible','Corporate','MEDIUM TERM NOTE') THEN 'EM Debt'
-- Credits (note that at this stage AC = 'FI', no Govies, and OECD is 'Y')
--WHEN I.OECD_MemberFlag IN ('Y','X',' ') THEN 'Credit Quality Unknown'
ELSE 'Credit Quality Unknown'
END AS DebtCategory
,N.InstrumentAttributes
,CASE WHEN len(ltrim(rtrim(I.IssueCountry))) = 0 THEN (SELECT TOP 1 C.OECD_MemberFlag FROM ref.Map_CountryCurrency CC JOIN ref.Country C ON CC.CountryCode = C.CountryCode WHERE CurrencyCode = N.TradeCurrency ORDER BY OECD_MemberFlag DESC) ELSE I.OECD_MemberFlag END AS OECD_MemberFlag
,CASE WHEN T.AssetClassName = 'Fixed Income' THEN I.CreditGrade ELSE 'N/A' END AS CreditGrade
,GrossAmount_EUR
,Commission_EUR
,GrossAmount_EUR * FX.ExchangeRate AS GrossAmount_USD
,Commission_EUR * FX.ExchangeRate AS Commission_USD
,Quantity AS Quantity
FROM dbo.FactTradeOrderAllocation N
JOIN dbo.DimDate D ON N.TradeDateKey = D.DateKey
JOIN dbo.DimPortfolio P ON N.PortfolioKey = P.PortfolioKey
JOIN dbo.DimInstrumentType T ON N.InstrumentTypeKey = T.InstrumentTypeKey
JOIN dbo.DimInstrument I ON N.InstrumentKey = I.InstrumentKey
JOIN dbo.DimOrganization B ON N.BrokerKey = B.OrganizationKey
JOIN dbo.DimDataSource S ON N.DataSourceKey = S.DataSourceKey
JOIN dbo.DimEmployee EM ON N.ManagerKey = EM.EmployeeKey
JOIN dbo.DimEmployee ET ON N.TraderKey = ET.EmployeeKey
JOIN ref.Map_CountryRegion R ON N.DataSourceCountry = R.CountryCode
JOIN dbo.FactExchangeRate FX ON N.TradeDateKey BETWEEN FX.QuoteFromDateKey AND FX.QuoteEndDateKey AND FX.QuoteCurrency = 'USD' AND FX.BaseCurrency = 'EUR'
WHERE N.TradeDateKey BETWEEN @PeriodStart AND @PeriodEnd
AND(CASE WHEN @CorporateRegion = 'ALL' THEN R.CorporateRegionCode ELSE @CorporateRegion END) = R.CorporateRegionCode
AND (CASE WHEN @Country = 'ALL' THEN N.DataSourceCountry ELSE @Country END) = N.DataSourceCountry
AND (CASE WHEN @Team = 'ALL' THEN P.BoutiqueA ELSE @Team END) = P.BoutiqueA
AND (CASE WHEN @Broker = 'ALL' THEN B.OrganizationName ELSE @Broker END) = B.OrganizationName
AND (CASE WHEN @AssetClassCode = '-' THEN T.AssetClassCode ELSE @AssetClassCode END) = T.AssetClassCode
AND (CASE WHEN @InstrumentType = 'ALL' THEN T.InstrumentType ELSE @InstrumentType END) = T.InstrumentType
ORDER BY N.TradeDateKey
,N.TradeOrderNumber
,N.TradeTransactionNumber
RETURN
The table definitions
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[FactTradeOrderAllocation](
[DataSourceKey] [int] NOT NULL,
[TradeDateKey] [int] NOT NULL,
[SettlementDateKey] [int] NOT NULL,
[PortfolioKey] [int] NOT NULL,
[BrokerKey] [int] NOT NULL,
[InstrumentTypeKey] [int] NOT NULL,
[InstrumentKey] [int] NOT NULL,
[TraderKey] [int] NOT NULL,
[ManagerKey] [int] NOT NULL,
[ExchangeKey] [int] NOT NULL,
[AuditKey] [int] NOT NULL,
[InstrumentAttributes] [varchar](120) NOT NULL,
[TradeOrderNumber] [varchar](50) NOT NULL,
[TradeTransactionNumber] [varchar](50) NOT NULL,
[TradeCurrency] [char](3) NOT NULL,
[QuoteCurrency] [char](3) NOT NULL,
[TransactionType] [varchar](20) NOT NULL,
[TransactionSide] [varchar](20) NOT NULL,
[Quantity] [numeric](18, 6) NOT NULL,
[MarketPrice_EUR] [numeric](18, 6) NOT NULL,
[GrossAmount_EUR] [numeric](22, 6) NOT NULL,
[Commission_EUR] [numeric](18, 6) NOT NULL,
[Fees_EUR] [numeric](18, 6) NOT NULL,
[NetAmount_EUR] [numeric](22, 6) NOT NULL,
[DataSourceCountry] [char](3) NOT NULL,
[AssetClassCode] [char](1) NOT NULL,
[OTC] [char](1) NOT NULL,
CONSTRAINT [PK_dbo.FactTradeorderAllocation] PRIMARY KEY CLUSTERED
(
[DataSourceKey] ASC,
[TradeDateKey] ASC,
[SettlementDateKey] ASC,
[PortfolioKey] ASC,
[BrokerKey] ASC,
[InstrumentTypeKey] ASC,
[InstrumentKey] ASC,
[TraderKey] ASC,
[ManagerKey] ASC,
[InstrumentAttributes] ASC,
[TradeOrderNumber] ASC,
[TransactionType] ASC,
[TradeTransactionNumber] ASC,
[TradeCurrency] ASC,
[QuoteCurrency] ASC,
[DataSourceCountry] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [Facts]
) ON [Facts]
GO
SET ANSI_PADDING OFF
GO
USE [GEAR]
GO
/****** Object: Table [dbo].[DimDate] Script Date: 12/05/2011 16:57:38 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[DimDate](
[DateKey] [int] NOT NULL,
[Date] [smalldatetime] NOT NULL,
[SerialDate] [decimal](12, 6) NOT NULL,
[DayOfWeek] [tinyint] NOT NULL,
[DayOfMonth] [tinyint] NOT NULL,
[DayOfYear] [smallint] NOT NULL,
[DayName] [varchar](9) NOT NULL,
[DayShortName] [varchar](3) NOT NULL,
[WeekdayFlag] [char](1) NOT NULL,
[WeekOfYear] [tinyint] NOT NULL,
[WeekBeginDate] [smalldatetime] NOT NULL,
[WeekBeginDateKey] [int] NOT NULL,
[MonthOfYear] [tinyint] NOT NULL,
[MonthName] [varchar](9) NOT NULL,
[MonthShortName] [varchar](3) NOT NULL,
[Quarter] [tinyint] NOT NULL,
[Year] [smallint] NOT NULL,
[YearMonth] [int] NOT NULL,
[EndOfMonthFlag] [char](1) NOT NULL,
CONSTRAINT [PK_dbo.DimDate] PRIMARY KEY CLUSTERED
(
[DateKey] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [Dimensions]
) ON [Dimensions]
GO
SET ANSI_PADDING OFF
GO
USE [GEAR]
GO
/****** Object: Table [dbo].[DimEmployee] Script Date: 12/05/2011 16:57:47 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[DimEmployee](
[EmployeeKey] [int] IDENTITY(1,1) NOT NULL,
[CorporateRegion] [char](3) NOT NULL,
[Domicile] [char](3) NOT NULL,
[OrganizationName] [nvarchar](120) NOT NULL,
[DepartmentName] [nvarchar](120) NOT NULL,
[FinanceCostCenterCode] [varchar](50) NOT NULL,
[FinanceCostCenterName] [nvarchar](120) NOT NULL,
[MDVS_CostCenterCode] [varchar](50) NOT NULL,
[MDVS_CostCenterName] [nvarchar](120) NOT NULL,
[EmployeeNumber] [varchar](18) NOT NULL,
[EmployeeCorporateKey] [varchar](18) NOT NULL,
[EmployeeName] [nvarchar](120) NOT NULL,
[Gender] [char](1) NOT NULL,
[MangerCorporateKey] [varchar](18) NOT NULL,
[ManagerName] [nvarchar](120) NOT NULL,
[CIO_CorporateKey] [varchar](18) NOT NULL,
[CIO_Name] [nvarchar](120) NOT NULL,
[JobFamilyDescription] [nvarchar](120) NOT NULL,
[JobDescription] [nvarchar](120) NOT NULL,
[HireDateKey] [int] NOT NULL,
[ExperienceDateKey] [int] NOT NULL,
[BirthDateKey] [int] NOT NULL,
[ExitDateKey] [int] NOT NULL,
[JobGrade] [int] NOT NULL,
[JobCoverage] [nvarchar](250) NOT NULL,
[Education] [nvarchar](250) NOT NULL,
[Biography] [nvarchar](4000) NOT NULL,
[CFA_Flag] [char](1) NOT NULL,
[MBA_Flag] [char](1) NOT NULL,
[PHD_Flag] [char](1) NOT NULL,
[CAIA_Flag] [char](1) NOT NULL,
[Notes] [varchar](4000) NOT NULL,
[DataSourceKey] [int] NOT NULL,
[InferredMember] [bit] NOT NULL,
[CurrentFlag] [char](1) NOT NULL,
[EffectiveFromDate] [datetime] NOT NULL,
[EffectiveToDate] [datetime] NULL,
[AddDateKey] [int] NULL,
[LastChangeDateKey] [int] NOT NULL,
[LastChangeReason] [varchar](200) NOT NULL,
[AuditKey] [int] NOT NULL,
CONSTRAINT [PK_dbo.DimEmployee] PRIMARY KEY CLUSTERED
(
[EmployeeKey] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [Dimensions]
) ON [Dimensions]
GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[DimEmployee] ADD CONSTRAINT [DF_DimEmployee_Gender] DEFAULT ('U') FOR [Gender]
GO
ALTER TABLE [dbo].[DimEmployee] ADD DEFAULT ((0)) FOR [HireDateKey]
GO
ALTER TABLE [dbo].[DimEmployee] ADD DEFAULT ((0)) FOR [ExperienceDateKey]
GO
ALTER TABLE [dbo].[DimEmployee] ADD DEFAULT ((0)) FOR [BirthDateKey]
GO
ALTER TABLE [dbo].[DimEmployee] ADD DEFAULT ((99991231)) FOR [ExitDateKey]
GO
ALTER TABLE [dbo].[DimEmployee] ADD CONSTRAINT [DF_DimEmployee_JobGrade] DEFAULT ((0)) FOR [JobGrade]
GO
ALTER TABLE [dbo].[DimEmployee] ADD CONSTRAINT [DF_DimEmployee_CFA_Flag] DEFAULT ('U') FOR [CFA_Flag]
GO
ALTER TABLE [dbo].[DimEmployee] ADD CONSTRAINT [DF_DimEmployee_MBA_Flag] DEFAULT ('U') FOR [MBA_Flag]
GO
ALTER TABLE [dbo].[DimEmployee] ADD CONSTRAINT [DF_DimEmployee_PHD_Flag] DEFAULT ('U') FOR [PHD_Flag]
GO
ALTER TABLE [dbo].[DimEmployee] ADD CONSTRAINT [DF_DimEmployee_CAIA_Flag] DEFAULT ('U') FOR [CAIA_Flag]
GO
ALTER TABLE [dbo].[DimEmployee] ADD CONSTRAINT [DF_DimEmployee_InferredMember] DEFAULT ((0)) FOR [InferredMember]
GO
ALTER TABLE [dbo].[DimEmployee] ADD CONSTRAINT [DF_DimEmployee_CurrentFlag] DEFAULT ('Y') FOR [CurrentFlag]
GO
ALTER TABLE [dbo].[DimEmployee] ADD CONSTRAINT [DF_DimEmployee_ISO_AddDate] DEFAULT (CONVERT([int],getdate(),(112))) FOR [AddDateKey]
GO
USE [GEAR]
GO
/****** Object: Table [dbo].[DimInstrument] Script Date: 12/05/2011 16:58:23 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[DimInstrument](
[InstrumentKey] [int] IDENTITY(1,1) NOT NULL,
[UII] [char](12) NOT NULL,
[Sedol] [char](7) NOT NULL,
[ISIN] [char](12) NOT NULL,
[Cusip] [char](9) NOT NULL,
[Ticker] [varchar](60) NOT NULL,
[InstrumentName] [varchar](120) NOT NULL,
[IssuerCode] [varchar](60) NOT NULL,
[IssuerName] [varchar](120) NOT NULL,
[Industry] [varchar](20) NOT NULL,
[IssueCountry] [char](3) NOT NULL,
[IssueDate] [char](8) NOT NULL,
[AnnouncementDate] [char](8) NOT NULL,
[BloombergMarketSector] [varchar](20) NOT NULL,
[BloombergSecurityType] [varchar](20) NOT NULL,
[BloombergSecuritySubType] [varchar](20) NOT NULL,
[BloombergCollateralType] [varchar](20) NOT NULL,
[InstrumentType] [varchar](120) NOT NULL,
[InstrumentCategory] [varchar](120) NOT NULL,
[AssetClassCode] [char](1) NOT NULL,
[AssetClassName] [varchar](120) NOT NULL,
[AC_Description] [varchar](250) NOT NULL,
[CFI_Code] [char](6) NOT NULL,
[CFI_Name] [varchar](120) NOT NULL,
[CFI_Description] [varchar](250) NOT NULL,
[DerivativeFlag] [char](1) NOT NULL,
[AlternativeFlag] [char](1) NOT NULL,
[OECD_MemberFlag] [char](1) NOT NULL,
[EmergingMarketFlag] [char](1) NOT NULL,
[DevelopedMarketFlag] [char](1) NOT NULL,
[CreditGrade] [varchar](60) NOT NULL,
[CreditRatingMoody] [varchar](12) NOT NULL,
[CreditRatingSP] [varchar](12) NOT NULL,
[CreditRatingFitch] [varchar](12) NOT NULL,
[DataSourceKey] [int] NOT NULL,
[InferredMember] [bit] NOT NULL,
[CurrentFlag] [char](1) NOT NULL,
[EffectiveFromDate] [datetime] NOT NULL,
[EffectiveToDate] [datetime] NULL,
[AddDateKey] [int] NULL,
[LastChangeDateKey] [int] NOT NULL,
[LastChangeReason] [varchar](200) NOT NULL,
[AuditKey] [int] NOT NULL,
CONSTRAINT [PK_dboDimInstrument] PRIMARY KEY CLUSTERED
(
[InstrumentKey] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [Dimensions]
) ON [Dimensions]
GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[DimInstrument] ADD CONSTRAINT [DF_DimInstrument_InferredMember] DEFAULT ((0)) FOR [InferredMember]
GO
ALTER TABLE [dbo].[DimInstrument] ADD CONSTRAINT [DF_DimInstrument_CurrentFlag] DEFAULT ('Y') FOR [CurrentFlag]
GO
ALTER TABLE [dbo].[DimInstrument] ADD CONSTRAINT [DF_DimInstrument_ISO_AddDate] DEFAULT (CONVERT([int],getdate(),(112))) FOR [AddDateKey]
GO
USE [GEAR]
GO
/****** Object: Table [dbo].[DimInstrumentType] Script Date: 12/05/2011 16:58:37 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[DimInstrumentType](
[InstrumentTypeKey] [int] IDENTITY(1,1) NOT NULL,
[AssetClassCode] [char](1) NOT NULL,
[AssetClassName] [varchar](120) NOT NULL,
[AC_Description] [varchar](250) NOT NULL,
[InstrumentType] [varchar](120) NOT NULL,
[CFI_Code] [char](6) NOT NULL,
[CFI_Name] [varchar](120) NOT NULL,
[CFI_Description] [varchar](250) NOT NULL,
[DerivativeFlag] [char](1) NOT NULL,
[AlternativeFlag] [char](1) NULL,
[DataSourceKey] [int] NOT NULL,
[InferredMember] [bit] NOT NULL,
[CurrentFlag] [char](1) NOT NULL,
[EffectiveFromDate] [datetime] NOT NULL,
[EffectiveToDate] [datetime] NULL,
[AddDateKey] [int] NULL,
[LastChangeDateKey] [int] NOT NULL,
[LastChangeReason] [varchar](200) NOT NULL,
[AuditKey] [int] NOT NULL,
CONSTRAINT [PK_dboDimInstrumentType] PRIMARY KEY CLUSTERED
(
[InstrumentTypeKey] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [Dimensions]
) ON [Dimensions]
GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[DimInstrumentType] ADD CONSTRAINT [DF_DimInstrumentType_InferredMember] DEFAULT ((0)) FOR [InferredMember]
GO
ALTER TABLE [dbo].[DimInstrumentType] ADD CONSTRAINT [DF_DimInstrumentType_CurrentFlag] DEFAULT ('Y') FOR [CurrentFlag]
GO
ALTER TABLE [dbo].[DimInstrumentType] ADD CONSTRAINT [DF_DimInstrumentType_ISO_AddDate] DEFAULT (CONVERT([int],getdate(),(112))) FOR [AddDateKey]
GO
USE [GEAR]
GO
/****** Object: Table [dbo].[DimOrganization] Script Date: 12/05/2011 16:58:48 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[DimOrganization](
[OrganizationKey] [int] IDENTITY(1,1) NOT NULL,
[OrganizationCode] [varchar](50) NOT NULL,
[OrganizationName] [nvarchar](120) NOT NULL,
[CorporateRegion] [char](3) NOT NULL,
[Domicile] [char](3) NOT NULL,
[UCI] [varchar](12) NOT NULL,
[BIC] [varchar](11) NOT NULL,
[Notes] [varchar](500) NOT NULL,
[DataSourceKey] [int] NOT NULL,
[InferredMember] [bit] NOT NULL,
[CurrentFlag] [char](1) NOT NULL,
[EffectiveFromDate] [datetime] NOT NULL,
[EffectiveToDate] [datetime] NULL,
[AddDateKey] [int] NULL,
[LastChangeDateKey] [int] NOT NULL,
[LastChangeReason] [varchar](200) NOT NULL,
[AuditKey] [int] NOT NULL,
CONSTRAINT [PK_dboDimOrganization] PRIMARY KEY CLUSTERED
(
[OrganizationKey] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [Dimensions]
) ON [Dimensions]
GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[DimOrganization] ADD CONSTRAINT [DF_DimOrganization_InferredMember] DEFAULT ((0)) FOR [InferredMember]
GO
ALTER TABLE [dbo].[DimOrganization] ADD CONSTRAINT [DF_DimOrganization_CurrentFlag] DEFAULT ('Y') FOR [CurrentFlag]
GO
ALTER TABLE [dbo].[DimOrganization] ADD CONSTRAINT [DF_DimOrganization_ISO_AddDate] DEFAULT (CONVERT([int],getdate(),(112))) FOR [AddDateKey]
GO
USE [GEAR]
GO
/****** Object: Table [stg].[DimPortfolio] Script Date: 12/05/2011 16:59:05 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [stg].[DimPortfolio](
[UPI] [varchar](20) NOT NULL,
[OfficialName] [varchar](120) NOT NULL,
[ISIN] [char](12) NOT NULL,
[MF_Code] [varchar](20) NOT NULL,
[SC_Code] [varchar](20) NOT NULL,
[BBH_Code] [varchar](20) NOT NULL,
[BNP_Code] [varchar](20) NOT NULL,
[DEX_Code] [varchar](20) NOT NULL,
[CR_Code] [varchar](20) NOT NULL,
[FK_Code] [varchar](50) NOT NULL,
[FKFX_Code] [varchar](50) NOT NULL,
[FKPF_Code] [varchar](50) NOT NULL,
[FKCS_Code] [varchar](50) NOT NULL,
[PA_Code] [varchar](20) NOT NULL,
[PME_Code] [varchar](20) NOT NULL,
[UBS_Code] [varchar](20) NOT NULL,
[Umbrella] [varchar](120) NOT NULL,
[UmbrellaType] [varchar](120) NOT NULL,
[PortfolioType] [varchar](15) NOT NULL,
[PortfolioSubType] [varchar](30) NOT NULL,
[ProprietaryFlag] [char](1) NOT NULL,
[Domicile] [char](3) NOT NULL,
[PortfolioCurrency] [char](3) NOT NULL,
[InceptionDate] [int] NOT NULL,
[LiquidationDate] [int] NOT NULL,
[LiquidationReason] [varchar](120) NOT NULL,
[MultiTeamFlag] [char](1) NOT NULL,
[SecuritiesLendingFlag] [char](1) NOT NULL,
[LMS_Flag] [char](1) NOT NULL,
[SustainableFlag] [char](1) NOT NULL,
[PoolingFlag] [char](1) NOT NULL,
[FundOfFundFlag] [char](1) NOT NULL,
[ActivelyManagedFlag] [char](1) NOT NULL,
[OfficialBenchmark] [varchar](120) NOT NULL,
[ReferenceBenchmark] [varchar](120) NOT NULL,
[ReferenceShareClass] [varchar](20) NOT NULL,
[Notes] [varchar](500) NOT NULL,
[LoadDate] [datetime] NOT NULL,
[BoutiqueA] [varchar](120) NULL,
[InvestmentTeamA] [varchar](120) NULL,
[AssetClassificationA] [varchar](50) NOT NULL,
[StrategyA] [varchar](120) NOT NULL,
[GeographicFocusA] [varchar](120) NOT NULL,
[SectorA] [varchar](120) NOT NULL,
[TargetAlphaA] [varchar](60) NOT NULL,
[TargetTrackingErrorA] [varchar](60) NOT NULL,
[TargetInfoRatioA] [varchar](60) NOT NULL,
[ReportedBenchmarkA] [varchar](250) NOT NULL,
[ReportedManagerNamesA] [varchar](250) NOT NULL,
[ManagerCorporateKey1A] [varchar](6) NOT NULL,
[ManagerCorporateKey2A] [varchar](6) NOT NULL,
[ManagerCorporateKey3A] [varchar](6) NOT NULL,
[ManagerCorporateKey4A] [varchar](6) NOT NULL,
[ManagerCorporateKey5A] [varchar](6) NOT NULL,
[ReportedPortfolioSpecialistNameA] [varchar](250) NOT NULL,
[PortfolioSpecialistCorporateKey1A] [varchar](6) NOT NULL,
[PortfolioSpecialistCorporateKey2A] [varchar](6) NOT NULL,
[PortfolioSpecialistCorporateKey3A] [varchar](6) NOT NULL,
[PortfolioSpecialistCorporateKey4A] [varchar](6) NOT NULL,
[PortfolioSpecialistCorporateKey5A] [varchar](6) NOT NULL,
[AllocationPercentA] [int] NOT NULL,
[ManagedFlagA] [char](1) NOT NULL,
[TeamManagedFlagA] [char](1) NOT NULL,
[AdvisoryFlagA] [char](1) NOT NULL,
[CommentA] [varchar](500) NULL,
[BoutiqueB] [varchar](120) NULL,
[InvestmentTeamB] [varchar](120) NULL,
[AssetClassificationB] [varchar](50) NOT NULL,
[StrategyB] [varchar](120) NOT NULL,
[GeographicFocusB] [varchar](120) NOT NULL,
[SectorB] [varchar](120) NOT NULL,
[TargetAlphaB] [varchar](60) NOT NULL,
[TargetTrackingErrorB] [varchar](60) NOT NULL,
[TargetInfoRatioB] [varchar](60) NOT NULL,
[ReportedBenchmarkB] [varchar](250) NOT NULL,
[ReportedManagerNamesB] [varchar](250) NOT NULL,
[ManagerCorporateKey1B] [varchar](6) NOT NULL,
[ManagerCorporateKey2B] [varchar](6) NOT NULL,
[ManagerCorporateKey3B] [varchar](6) NOT NULL,
[ManagerCorporateKey4B] [varchar](6) NOT NULL,
[ManagerCorporateKey5B] [varchar](6) NOT NULL,
[ReportedPortfolioSpecialistNameB] [varchar](250) NOT NULL,
[PortfolioSpecialistCorporateKey1B] [varchar](6) NOT NULL,
[PortfolioSpecialistCorporateKey2B] [varchar](6) NOT NULL,
[PortfolioSpecialistCorporateKey3B] [varchar](6) NOT NULL,
[PortfolioSpecialistCorporateKey4B] [varchar](6) NOT NULL,
[PortfolioSpecialistCorporateKey5B] [varchar](6) NOT NULL,
[AllocationPercentB] [int] NOT NULL,
[ManagedFlagB] [char](1) NOT NULL,
[TeamManagedFlagB] [char](1) NOT NULL,
[AdvisoryFlagB] [char](1) NOT NULL,
[CommentB] [varchar](500) NULL,
[BoutiqueC] [varchar](120) NULL,
[InvestmentTeamC] [varchar](120) NULL,
[AssetClassificationC] [varchar](50) NOT NULL,
[StrategyC] [varchar](120) NOT NULL,
[GeographicFocusC] [varchar](120) NOT NULL,
[SectorC] [varchar](120) NOT NULL,
[TargetAlphaC] [varchar](60) NOT NULL,
[TargetTrackingErrorC] [varchar](60) NOT NULL,
[TargetInfoRatioC] [varchar](60) NOT NULL,
[ReportedBenchmarkC] [varchar](250) NOT NULL,
[ReportedManagerNamesC] [varchar](250) NOT NULL,
[ManagerCorporateKey1C] [varchar](6) NOT NULL,
[ManagerCorporateKey2C] [varchar](6) NOT NULL,
[ManagerCorporateKey3C] [varchar](6) NOT NULL,
[ManagerCorporateKey4C] [varchar](6) NOT NULL,
[ManagerCorporateKey5C] [varchar](6) NOT NULL,
[ReportedPortfolioSpecialistNameC] [varchar](250) NOT NULL,
[PortfolioSpecialistCorporateKey1C] [varchar](6) NOT NULL,
[PortfolioSpecialistCorporateKey2C] [varchar](6) NOT NULL,
[PortfolioSpecialistCorporateKey3C] [varchar](6) NOT NULL,
[PortfolioSpecialistCorporateKey4C] [varchar](6) NOT NULL,
[PortfolioSpecialistCorporateKey5C] [varchar](6) NOT NULL,
[AllocationPercentC] [int] NOT NULL,
[ManagedFlagC] [char](1) NOT NULL,
[TeamManagedFlagC] [char](1) NOT NULL,
[AdvisoryFlagC] [char](1) NOT NULL,
[CommentC] [varchar](500) NULL
) ON [Stage]
GO
SET ANSI_PADDING OFF
GO
The fact table contains about 405000 rows of data. The procedure which loads the table variable takes about 50 seconds if I run it separaetly with the largest window of data i.e. 20100101 and 20111031. I was thinking perhaps it was the way I create the cross tab. However I was unsure of any other way to produce the specified output.
Any ideas?
December 5, 2011 at 9:47 am
Change from @tbl to #tbl. Post back on how much that helps.
December 5, 2011 at 10:00 am
Maybe you can benefit from using a PIVOT operator instead of the case list in the procedure [prv].[sp_TradeAggregate_Equity]?
Also consider creating a separate column in the [FactTradeOrderAllocation] table where you can store the month value, instead of using the "MONTH(CONVERT(smalldatetime, CONVERT(char(8), TradeDateKey)) )" conversion. Besides being slightly faster, it will also make your code more readable and easier to maintain.
_____________________________________________________
Do not go past the mark you aimed for, but learn when to stop.
You can find me on LinkedIn.
I support The Programmer's Bill of Rights.
MCITP, MCDBA, MCSD
December 5, 2011 at 10:08 am
I used a temp table but the query took exactly the same time.
I am not really sure on how I to create PIVOT with multiple aggregates. Could you give me some pointers.
December 5, 2011 at 10:10 am
eseosaoregie (12/5/2011)
I used a temp table but the query took exactly the same time.I am not really sure on how I to create PIVOT with multiple aggregates. Could you give me some pointers.
You can play with it if you want but last time I did I didn't see any gain. This situation might be different.
Can you post the new actual plan with the #tbl?
December 5, 2011 at 10:20 am
Here is the new execution plan with the temp table.
December 5, 2011 at 10:28 am
I was hoping the stats would help the plan but they don't.
I don't see any low hanging fruits here. It takes time because there's a lot of work to do.
It's not like there's a magic "go fast" switch for max.
Assuming you really need those column then I don't see anything you can do here. Sum is not really expansive, it's the max that needs to run the sort + group by that's killing you.
December 5, 2011 at 10:43 am
I was just wondering if there was another way of getting the same output with a different query. I have tried to look up multi aggregate pivots on google but haven't managed to find anything
December 5, 2011 at 10:45 am
eseosaoregie (12/5/2011)
I was just wondering if there was another way of getting the same output with a different query. I have tried to look up multi aggregate pivots on google but haven't managed to find anything
If there's 1 way, I've never heard of it. That being said DW and SSAS are not my strong suits.
December 5, 2011 at 10:49 am
eseosaoregie (12/5/2011)
I was just wondering if there was another way of getting the same output with a different query. I have tried to look up multi aggregate pivots on google but haven't managed to find anything
Is there a problem with letting SSRS do this? That way, you let the report do the aggregating and just have the SQL select the data?
Jared
Jared
CE - Microsoft
December 5, 2011 at 10:54 am
p-nut (12/5/2011)
eseosaoregie (12/5/2011)
I was just wondering if there was another way of getting the same output with a different query. I have tried to look up multi aggregate pivots on google but haven't managed to find anythingIs there a problem with letting SSRS do this? That way, you let the report do the aggregating and just have the SQL select the data?
Jared
That's a heck of a lot of data to process client side in SSRS. I'd do the test but I wouldn't expect a big gain, if any. Tho it sends to workload to a different machine. That alone could be a benefit even if the total time doesn't change.
December 5, 2011 at 11:01 am
At the moment the SSRS and SSAS are not available. I agree that would be the better option but at the moment it is a no no. That will come later in q4. For now I have to produce the queries that summarise the data.
December 5, 2011 at 11:06 am
eseosaoregie (12/5/2011)
At the moment the SSRS and SSAS are not available. I agree that would be the better option but at the moment it is a no no. That will come later in q4. For now I have to produce the queries that summarise the data.
Aside from pre-aggregate in some sort of pre-reporting table I have no idea how to speed this up.
<calls in help>
December 5, 2011 at 11:11 am
Being that this is a data warehouse, why not build an aggregate table and run your query against that?
Jared
Jared
CE - Microsoft
December 5, 2011 at 11:31 am
Hm, nice problem setup, and an interesting scenario. I've never seen a difference in 1 row estimation vs. accurate 227000 row estimation NOT make a difference...
Is the 405000 base rows evenly devided between the months, roughly? In case I need to build a local test case.
The first thing that comes to mind here is every aggregation is being done independently. Add that to a number of enforced sorts which are costing you a ton of processing in memory for the secondary merge joins and you're getting hosed up. I'd need to spend some time monkey'ing around with it and grokking your code and base schema. Can you provide a single record chain for the primary tables so I can get a better idea of how the data distributes and associates when being multipled in joins?
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
Viewing 15 posts - 1 through 15 (of 41 total)
You must be logged in to reply to this topic. Login to reply