December 6, 2011 at 7:53 am
I have included a copy of the execution plan for the stored procedure.
Can someone show me how to do the multiple pivot synatx and I will give that a go.
Regarding the summary table. Is it this portion of the code that should go in a table?
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
If so on what columns would I put indexes? I am a little unclear on this
December 6, 2011 at 8:01 am
Not looking back at your original code, you should have indexes first on the columns that you filter on. Was that a date range I believe? Also, if you will have to join this aggregate, you may want an index on the joining columns if there is not one already. That is a simple start, and you can adjust to covering indexes if you need to increase performance more, but my guess is that it will be fine in this case since all of the work is done before querying this table. I'm still new to identifying ALL of the proper indexes, but this should get you started.
Jared
Jared
CE - Microsoft
December 6, 2011 at 8:55 am
eseosaoregie (12/6/2011)
I have included a copy of the execution plan for the stored procedure.Can someone show me how to do the multiple pivot synatx and I will give that a go.
Regarding the summary table. Is it this portion of the code that should go in a table?
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
If so on what columns would I put indexes? I am a little unclear on this
I thought you said you converted TradeDateKey to smalldatetime? Why all the converts in this code?
December 6, 2011 at 8:59 am
Apologies
CREATE 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
SET NOCOUNT ON
--- 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 smalldatetime
,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
-- ====================================================================================
--SELECT * FROM @TradeDetail
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(TradeDateKey) = 1 AND YEAR(TradeDateKey) = YEAR(@ToMonth)THEN GrossAmount_EUR END) AS GrossAmount_EUR_Jan,
SUM(CASE WHEN MONTH(TradeDateKey) = 2 AND YEAR(TradeDateKey) = YEAR(@ToMonth)THEN GrossAmount_EUR END) AS GrossAmount_EUR_Feb,
SUM(CASE WHEN MONTH(TradeDateKey) = 3 AND YEAR(TradeDateKey) = YEAR(@ToMonth)THEN GrossAmount_EUR END) AS GrossAmount_EUR_Mar,
SUM(CASE WHEN MONTH(TradeDateKey) = 4 AND YEAR(TradeDateKey) = YEAR(@ToMonth)THEN GrossAmount_EUR END) AS GrossAmount_EUR_Apr,
SUM(CASE WHEN MONTH(TradeDateKey) = 5 AND YEAR(TradeDateKey) = YEAR(@ToMonth)THEN GrossAmount_EUR END) AS GrossAmount_EUR_May,
SUM(CASE WHEN MONTH(TradeDateKey) = 6 AND YEAR(TradeDateKey) = YEAR(@ToMonth)THEN GrossAmount_EUR END) AS GrossAmount_EUR_Jun,
SUM(CASE WHEN MONTH(TradeDateKey) = 7 AND YEAR(TradeDateKey) = YEAR(@ToMonth)THEN GrossAmount_EUR END) AS GrossAmount_EUR_Jul,
SUM(CASE WHEN MONTH(TradeDateKey) = 8 AND YEAR(TradeDateKey) = YEAR(@ToMonth)THEN GrossAmount_EUR END) AS GrossAmount_EUR_Aug,
SUM(CASE WHEN MONTH(TradeDateKey) = 9 AND YEAR(TradeDateKey) = YEAR(@ToMonth)THEN GrossAmount_EUR END) AS GrossAmount_EUR_Sep,
SUM(CASE WHEN MONTH(TradeDateKey) = 10 AND YEAR(TradeDateKey) = YEAR(@ToMonth)THEN GrossAmount_EUR END) AS GrossAmount_EUR_Oct,
SUM(CASE WHEN MONTH(TradeDateKey) = 11 AND YEAR(TradeDateKey) = YEAR(@ToMonth)THEN GrossAmount_EUR END) AS GrossAmount_EUR_Nov,
SUM(CASE WHEN MONTH(TradeDateKey) = 12 AND YEAR(TradeDateKey) = YEAR(@ToMonth)THEN GrossAmount_EUR END) AS GrossAmount_EUR_Dec,
SUM(CASE WHEN 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 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(TradeDateKey) = 1 AND YEAR(TradeDateKey) = YEAR(@ToMonth)THEN Commission_EUR END) AS Commission_EUR_Jan,
SUM(CASE WHEN MONTH(TradeDateKey) = 2 AND YEAR(TradeDateKey) = YEAR(@ToMonth)THEN Commission_EUR END) AS Commission_EUR_Feb,
SUM(CASE WHEN MONTH(TradeDateKey) = 3 AND YEAR(TradeDateKey) = YEAR(@ToMonth)THEN Commission_EUR END) AS Commission_EUR_Mar,
SUM(CASE WHEN MONTH(TradeDateKey) = 4 AND YEAR(TradeDateKey) = YEAR(@ToMonth)THEN Commission_EUR END) AS Commission_EUR_Apr,
SUM(CASE WHEN MONTH(TradeDateKey) = 5 AND YEAR(TradeDateKey) = YEAR(@ToMonth)THEN Commission_EUR END) AS Commission_EUR_May,
SUM(CASE WHEN MONTH(TradeDateKey) = 6 AND YEAR(TradeDateKey) = YEAR(@ToMonth)THEN Commission_EUR END) AS Commission_EUR_Jun,
SUM(CASE WHEN MONTH(TradeDateKey) = 7 AND YEAR(TradeDateKey) = YEAR(@ToMonth)THEN Commission_EUR END) AS Commission_EUR_Jul,
SUM(CASE WHEN MONTH(TradeDateKey) = 8 AND YEAR(TradeDateKey) = YEAR(@ToMonth)THEN Commission_EUR END) AS Commission_EUR_Aug,
SUM(CASE WHEN MONTH(TradeDateKey) = 9 AND YEAR(TradeDateKey) = YEAR(@ToMonth)THEN Commission_EUR END) AS Commission_EUR_Sep,
SUM(CASE WHEN MONTH(TradeDateKey) = 10 AND YEAR(TradeDateKey) = YEAR(@ToMonth)THEN Commission_EUR END) AS Commission_EUR_Oct,
SUM(CASE WHEN MONTH(TradeDateKey) = 11 AND YEAR(TradeDateKey) = YEAR(@ToMonth)THEN Commission_EUR END) AS Commission_EUR_Nov,
SUM(CASE WHEN MONTH(TradeDateKey) = 12 AND YEAR(TradeDateKey) = YEAR(@ToMonth)THEN Commission_EUR END) AS Commission_EUR_Dec,
SUM(CASE WHEN 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 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(TradeDateKey) = 1 AND YEAR(TradeDateKey) = YEAR(@ToMonth)THEN TradeOrderNumber END) AS OrdersJan,
COUNT(DISTINCT CASE WHEN MONTH(TradeDateKey) = 2 AND YEAR(TradeDateKey) = YEAR(@ToMonth)THEN TradeOrderNumber END) AS OrdersFeb,
COUNT(DISTINCT CASE WHEN MONTH(TradeDateKey) = 3 AND YEAR(TradeDateKey) = YEAR(@ToMonth)THEN TradeOrderNumber END) AS OrdersMar,
COUNT(DISTINCT CASE WHEN MONTH(TradeDateKey) = 4 AND YEAR(TradeDateKey) = YEAR(@ToMonth)THEN TradeOrderNumber END) AS OrdersApr,
COUNT(DISTINCT CASE WHEN MONTH(TradeDateKey) = 5 AND YEAR(TradeDateKey) = YEAR(@ToMonth)THEN TradeOrderNumber END) AS OrdersMay,
COUNT(DISTINCT CASE WHEN MONTH(TradeDateKey) = 6 AND YEAR(TradeDateKey) = YEAR(@ToMonth)THEN TradeOrderNumber END) AS OrdersJun,
COUNT(DISTINCT CASE WHEN MONTH(TradeDateKey) = 7 AND YEAR(TradeDateKey) = YEAR(@ToMonth)THEN TradeOrderNumber END) AS OrdersJul,
COUNT(DISTINCT CASE WHEN MONTH(TradeDateKey) = 8 AND YEAR(TradeDateKey) = YEAR(@ToMonth)THEN TradeOrderNumber END) AS OrdersAug,
COUNT(DISTINCT CASE WHEN MONTH(TradeDateKey) = 9 AND YEAR(TradeDateKey) = YEAR(@ToMonth)THEN TradeOrderNumber END) AS OrdersSep,
COUNT(DISTINCT CASE WHEN MONTH(TradeDateKey) = 10 AND YEAR( TradeDateKey) = YEAR(@ToMonth)THEN TradeOrderNumber END) AS OrdersOct,
COUNT(DISTINCT CASE WHEN MONTH(TradeDateKey) = 11 AND YEAR(TradeDateKey) = YEAR(@ToMonth)THEN TradeOrderNumber END) AS OrdersNov,
COUNT(DISTINCT CASE WHEN MONTH(TradeDateKey) = 12 AND YEAR(TradeDateKey) = YEAR(@ToMonth)THEN TradeOrderNumber END) AS OrdersDec,
CAST(COUNT(DISTINCT CASE WHEN 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 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(TradeDateKey) >= MONTH(DATEADD(MM,-12,@ToMonth))THEN GrossAmount_EUR END) AS PreviousYear1Turnover,
SUM(CASE WHEN MONTH(TradeDateKey) >= MONTH(DATEADD(MM,-12,@ToMonth))THEN Commission_EUR END)AS PreviousYear1Commission,
COUNT(DISTINCT CASE WHEN MONTH(TradeDateKey) >= MONTH(DATEADD(MM,-12,@ToMonth))THEN TradeOrderNumber END) AS PreviousYear1Orders,
SUM(CASE WHEN TradeDateKey BETWEEN DATEADD(MM,-12,@ToMonth) AND DATEADD(MM,-24,@ToMonth) THEN GrossAmount_EUR END) AS PreviousYear2Turnover,
SUM(CASE WHEN TradeDateKey BETWEEN DATEADD(MM,-12,@ToMonth) AND DATEADD(MM,-24,@ToMonth) THEN Commission_EUR END) AS PreviousYear2Commission,
COUNT(DISTINCT CASE WHEN TradeDateKey BETWEEN DATEADD(MM,-12,@ToMonth) AND DATEADD(MM,-24,@ToMonth) THEN TradeOrderNumber END) AS PreviousYear2Orders,
SUM(CASE WHEN TradeDateKey BETWEEN DATEADD(MM,-24,@ToMonth) AND DATEADD(MM,-36,@ToMonth) THEN GrossAmount_EUR END) AS PreviousYear3Turnover,
SUM(CASE WHEN TradeDateKey BETWEEN DATEADD(MM,-24,@ToMonth) AND DATEADD(MM,-36,@ToMonth) THEN Commission_EUR END) AS PreviousYear3Commission,
COUNT(DISTINCT CASE WHEN TradeDateKey BETWEEN DATEADD(MM,-24,@ToMonth) AND DATEADD(MM,-36,@ToMonth) THEN TradeOrderNumber END) AS PreviousYear3Orders,
SUM(CASE WHEN TradeDateKey BETWEEN DATEADD(MM,-36,@ToMonth) AND DATEADD(MM,-48,@ToMonth) THEN GrossAmount_EUR END) AS PreviousYear4Turnover,
SUM(CASE WHEN TradeDateKey BETWEEN DATEADD(MM,-36,@ToMonth) AND DATEADD(MM,-48,@ToMonth) THEN Commission_EUR END) AS PreviousYear4Commission,
COUNT(DISTINCT CASE WHEN 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(TradeDateKey) = 1 AND YEAR(TradeDateKey) = YEAR(@ToMonth)THEN GrossAmount_EUR END) AS GrossAmount_EUR_Jan,
SUM(CASE WHEN MONTH(TradeDateKey) = 2 AND YEAR(TradeDateKey) = YEAR(@ToMonth)THEN GrossAmount_EUR END) AS GrossAmount_EUR_Feb,
SUM(CASE WHEN MONTH(TradeDateKey) = 3 AND YEAR(TradeDateKey) = YEAR(@ToMonth)THEN GrossAmount_EUR END) AS GrossAmount_EUR_Mar,
SUM(CASE WHEN MONTH(TradeDateKey) = 4 AND YEAR(TradeDateKey) = YEAR(@ToMonth)THEN GrossAmount_EUR END) AS GrossAmount_EUR_Apr,
SUM(CASE WHEN MONTH(TradeDateKey) = 5 AND YEAR(TradeDateKey) = YEAR(@ToMonth)THEN GrossAmount_EUR END) AS GrossAmount_EUR_May,
SUM(CASE WHEN MONTH(TradeDateKey) = 6 AND YEAR(TradeDateKey) = YEAR(@ToMonth)THEN GrossAmount_EUR END) AS GrossAmount_EUR_Jun,
SUM(CASE WHEN MONTH(TradeDateKey) = 7 AND YEAR(TradeDateKey) = YEAR(@ToMonth)THEN GrossAmount_EUR END) AS GrossAmount_EUR_Jul,
SUM(CASE WHEN MONTH(TradeDateKey) = 8 AND YEAR(TradeDateKey) = YEAR(@ToMonth)THEN GrossAmount_EUR END) AS GrossAmount_EUR_Aug,
SUM(CASE WHEN MONTH(TradeDateKey) = 9 AND YEAR(TradeDateKey) = YEAR(@ToMonth)THEN GrossAmount_EUR END) AS GrossAmount_EUR_Sep,
SUM(CASE WHEN MONTH(TradeDateKey) = 10 AND YEAR(TradeDateKey) = YEAR(@ToMonth)THEN GrossAmount_EUR END) AS GrossAmount_EUR_Oct,
SUM(CASE WHEN MONTH(TradeDateKey) = 11 AND YEAR(TradeDateKey) = YEAR(@ToMonth)THEN GrossAmount_EUR END) AS GrossAmount_EUR_Nov,
SUM(CASE WHEN MONTH(TradeDateKey) = 12 AND YEAR(TradeDateKey) = YEAR(@ToMonth)THEN GrossAmount_EUR END) AS GrossAmount_EUR_Dec,
SUM(CASE WHEN 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 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(TradeDateKey) = 1 AND YEAR(TradeDateKey) = YEAR(@ToMonth)THEN Commission_EUR END) AS Commission_EUR_Jan,
SUM(CASE WHEN MONTH(TradeDateKey) = 2 AND YEAR(TradeDateKey) = YEAR(@ToMonth)THEN Commission_EUR END) AS Commission_EUR_Feb,
SUM(CASE WHEN MONTH(TradeDateKey) = 3 AND YEAR(TradeDateKey) = YEAR(@ToMonth)THEN Commission_EUR END) AS Commission_EUR_Mar,
SUM(CASE WHEN MONTH(TradeDateKey) = 4 AND YEAR(TradeDateKey) = YEAR(@ToMonth)THEN Commission_EUR END) AS Commission_EUR_Apr,
SUM(CASE WHEN MONTH(TradeDateKey) = 5 AND YEAR(TradeDateKey) = YEAR(@ToMonth)THEN Commission_EUR END) AS Commission_EUR_May,
SUM(CASE WHEN MONTH(TradeDateKey) = 6 AND YEAR(TradeDateKey) = YEAR(@ToMonth)THEN Commission_EUR END) AS Commission_EUR_Jun,
SUM(CASE WHEN MONTH(TradeDateKey) = 7 AND YEAR(TradeDateKey) = YEAR(@ToMonth)THEN Commission_EUR END) AS Commission_EUR_Jul,
SUM(CASE WHEN MONTH(TradeDateKey) = 8 AND YEAR(TradeDateKey) = YEAR(@ToMonth)THEN Commission_EUR END) AS Commission_EUR_Aug,
SUM(CASE WHEN MONTH(TradeDateKey) = 9 AND YEAR(TradeDateKey) = YEAR(@ToMonth)THEN Commission_EUR END) AS Commission_EUR_Sep,
SUM(CASE WHEN MONTH(TradeDateKey) = 10 AND YEAR(TradeDateKey) = YEAR(@ToMonth)THEN Commission_EUR END) AS Commission_EUR_Oct,
SUM(CASE WHEN MONTH(TradeDateKey) = 11 AND YEAR(TradeDateKey) = YEAR(@ToMonth)THEN Commission_EUR END) AS Commission_EUR_Nov,
SUM(CASE WHEN MONTH(TradeDateKey) = 12 AND YEAR(TradeDateKey) = YEAR(@ToMonth)THEN Commission_EUR END) AS Commission_EUR_Dec,
SUM(CASE WHEN 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 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(TradeDateKey) = 1 AND YEAR(TradeDateKey) = YEAR(@ToMonth)THEN TradeOrderNumber END) AS AllocationJan,
COUNT(DISTINCT CASE WHEN MONTH(TradeDateKey) = 2 AND YEAR(TradeDateKey) = YEAR(@ToMonth)THEN TradeOrderNumber END) AS AllocationFeb,
COUNT(DISTINCT CASE WHEN MONTH(TradeDateKey) = 3 AND YEAR(TradeDateKey) = YEAR(@ToMonth)THEN TradeOrderNumber END) AS AllocationMar,
COUNT(DISTINCT CASE WHEN MONTH(TradeDateKey) = 4 AND YEAR(TradeDateKey) = YEAR(@ToMonth)THEN TradeOrderNumber END) AS AllocationApr,
COUNT(DISTINCT CASE WHEN MONTH(TradeDateKey) = 5 AND YEAR(TradeDateKey) = YEAR(@ToMonth)THEN TradeOrderNumber END) AS AllocationMay,
COUNT(DISTINCT CASE WHEN MONTH(TradeDateKey) = 6 AND YEAR(TradeDateKey) = YEAR(@ToMonth)THEN TradeOrderNumber END) AS AllocationJun,
COUNT(DISTINCT CASE WHEN MONTH(TradeDateKey) = 7 AND YEAR(TradeDateKey) = YEAR(@ToMonth)THEN TradeOrderNumber END) AS AllocationJul,
COUNT(DISTINCT CASE WHEN MONTH(TradeDateKey) = 8 AND YEAR(TradeDateKey) = YEAR(@ToMonth)THEN TradeOrderNumber END) AS AllocationAug,
COUNT(DISTINCT CASE WHEN MONTH(TradeDateKey) = 9 AND YEAR(TradeDateKey) = YEAR(@ToMonth)THEN TradeOrderNumber END) AS AllocationSep,
COUNT(DISTINCT CASE WHEN MONTH(TradeDateKey) = 10 AND YEAR(TradeDateKey) = YEAR(@ToMonth)THEN TradeOrderNumber END) AS AllocationOct,
COUNT(DISTINCT CASE WHEN MONTH(TradeDateKey) = 11 AND YEAR(TradeDateKey) = YEAR(@ToMonth)THEN TradeOrderNumber END) AS AllocationNov,
COUNT(DISTINCT CASE WHEN MONTH(TradeDateKey) = 12 AND YEAR(TradeDateKey)= YEAR(@ToMonth)THEN TradeOrderNumber END) AS AllocationDec,
CAST(COUNT(DISTINCT CASE WHEN 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 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(TradeDateKey) >= MONTH(DATEADD(MM,-12,@ToMonth))THEN GrossAmount_EUR END) AS PreviousYear1Turnover,
SUM(CASE WHEN MONTH(TradeDateKey) >= MONTH(DATEADD(MM,-12,@ToMonth))THEN Commission_EUR END)AS PreviousYear1Commission,
COUNT(DISTINCT CASE WHEN MONTH(TradeDateKey) >= MONTH(DATEADD(MM,-12,@ToMonth))THEN TradeOrderNumber END) AS PreviousYear1Allocation,
SUM(CASE WHEN TradeDateKey BETWEEN DATEADD(MM,-12,@ToMonth) AND DATEADD(MM,-24,@ToMonth) THEN GrossAmount_EUR END) AS PreviousYear2Turnover,
SUM(CASE WHEN TradeDateKey BETWEEN DATEADD(MM,-12,@ToMonth) AND DATEADD(MM,-24,@ToMonth) THEN Commission_EUR END) AS PreviousYear2Commission,
COUNT(DISTINCT CASE WHEN TradeDateKey BETWEEN DATEADD(MM,-12,@ToMonth) AND DATEADD(MM,-24,@ToMonth) THEN TradeOrderNumber END) AS PreviousYear2Allocation,
SUM(CASE WHEN TradeDateKey BETWEEN DATEADD(MM,-24,@ToMonth) AND DATEADD(MM,-36,@ToMonth) THEN GrossAmount_EUR END) AS PreviousYear3Turnover,
SUM(CASE WHEN TradeDateKey BETWEEN DATEADD(MM,-24,@ToMonth) AND DATEADD(MM,-36,@ToMonth) THEN Commission_EUR END) AS PreviousYear3Commission,
COUNT(DISTINCT CASE WHEN TradeDateKey BETWEEN DATEADD(MM,-24,@ToMonth) AND DATEADD(MM,-36,@ToMonth) THEN TradeOrderNumber END) AS PreviousYear3Allocation,
SUM(CASE WHEN TradeDateKey BETWEEN DATEADD(MM,-36,@ToMonth) AND DATEADD(MM,-48,@ToMonth) THEN GrossAmount_EUR END) AS PreviousYear4Turnover,
SUM(CASE WHEN TradeDateKey BETWEEN DATEADD(MM,-36,@ToMonth) AND DATEADD(MM,-48,@ToMonth) THEN Commission_EUR END) AS PreviousYear4Commission,
COUNT(DISTINCT CASE WHEN 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
All the code is blending into one!!
December 6, 2011 at 9:41 am
In the code provided, @MyToMonth is declared but never used. Let's try something.
Give the following a shot, it isn't what I would want in the end, but I want to see if the minor change helps.
CREATE 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
SET NOCOUNT ON
--- 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);
DECLARE @MyToMonth smalldatetime;
-- ===========================================================================
-- (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
set @MyToMonth = @ToMonth;
-- ===========================================================================
-- (2) Load Base Data into table variable to be aggregated
-- ===========================================================================
DECLARE @TradeDetail TABLE (
DataSourceName varchar(120)
, CorporateRegion char(3)
, Country char(3)
, TradeDateKey smalldatetime
, 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
-- ====================================================================================
--SELECT * FROM @TradeDetail
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(@MyToMonth)* 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(@MyToMonth)* 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(@MyToMonth)* 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
(SELECT DataSourceName,
CorporateRegion,
Country,
Broker,
ParentBrokerCode,
Trader_Number,
Trader_Region,
AssetClassCode,
AssetClassName,
InstrumentType,
TransactionType,
SUM(CASE WHEN MONTH(TradeDateKey) = 1 AND YEAR(TradeDateKey) = YEAR(@MyToMonth)THEN GrossAmount_EUR END) AS GrossAmount_EUR_Jan,
SUM(CASE WHEN MONTH(TradeDateKey) = 2 AND YEAR(TradeDateKey) = YEAR(@MyToMonth)THEN GrossAmount_EUR END) AS GrossAmount_EUR_Feb,
SUM(CASE WHEN MONTH(TradeDateKey) = 3 AND YEAR(TradeDateKey) = YEAR(@MyToMonth)THEN GrossAmount_EUR END) AS GrossAmount_EUR_Mar,
SUM(CASE WHEN MONTH(TradeDateKey) = 4 AND YEAR(TradeDateKey) = YEAR(@MyToMonth)THEN GrossAmount_EUR END) AS GrossAmount_EUR_Apr,
SUM(CASE WHEN MONTH(TradeDateKey) = 5 AND YEAR(TradeDateKey) = YEAR(@MyToMonth)THEN GrossAmount_EUR END) AS GrossAmount_EUR_May,
SUM(CASE WHEN MONTH(TradeDateKey) = 6 AND YEAR(TradeDateKey) = YEAR(@MyToMonth)THEN GrossAmount_EUR END) AS GrossAmount_EUR_Jun,
SUM(CASE WHEN MONTH(TradeDateKey) = 7 AND YEAR(TradeDateKey) = YEAR(@MyToMonth)THEN GrossAmount_EUR END) AS GrossAmount_EUR_Jul,
SUM(CASE WHEN MONTH(TradeDateKey) = 8 AND YEAR(TradeDateKey) = YEAR(@MyToMonth)THEN GrossAmount_EUR END) AS GrossAmount_EUR_Aug,
SUM(CASE WHEN MONTH(TradeDateKey) = 9 AND YEAR(TradeDateKey) = YEAR(@MyToMonth)THEN GrossAmount_EUR END) AS GrossAmount_EUR_Sep,
SUM(CASE WHEN MONTH(TradeDateKey) = 10 AND YEAR(TradeDateKey) = YEAR(@MyToMonth)THEN GrossAmount_EUR END) AS GrossAmount_EUR_Oct,
SUM(CASE WHEN MONTH(TradeDateKey) = 11 AND YEAR(TradeDateKey) = YEAR(@MyToMonth)THEN GrossAmount_EUR END) AS GrossAmount_EUR_Nov,
SUM(CASE WHEN MONTH(TradeDateKey) = 12 AND YEAR(TradeDateKey) = YEAR(@MyToMonth)THEN GrossAmount_EUR END) AS GrossAmount_EUR_Dec,
SUM(CASE WHEN TradeDateKey BETWEEN DATEADD(DAY,0,DATEADD(YEAR,DATEDIFF(YEAR,0,@MyToMonth),0)) AND @MyToMonth THEN GrossAmount_EUR END)AS GrossAmount_EUR_YTD,
SUM(CASE WHEN TradeDateKey
BETWEEN DATEADD(MONTH,-1,DATEADD(YEAR,DATEDIFF(YEAR,0,@MyToMonth),0)) AND DATEADD(DAY,-1,DATEADD(YEAR,DATEDIFF(YEAR,0,@MyToMonth),0)) THEN GrossAmount_EUR END) PreviousYear1_GrossAmount_EUR_Dec,
SUM(CASE WHEN MONTH(TradeDateKey) = 1 AND YEAR(TradeDateKey) = YEAR(@MyToMonth)THEN Commission_EUR END) AS Commission_EUR_Jan,
SUM(CASE WHEN MONTH(TradeDateKey) = 2 AND YEAR(TradeDateKey) = YEAR(@MyToMonth)THEN Commission_EUR END) AS Commission_EUR_Feb,
SUM(CASE WHEN MONTH(TradeDateKey) = 3 AND YEAR(TradeDateKey) = YEAR(@MyToMonth)THEN Commission_EUR END) AS Commission_EUR_Mar,
SUM(CASE WHEN MONTH(TradeDateKey) = 4 AND YEAR(TradeDateKey) = YEAR(@MyToMonth)THEN Commission_EUR END) AS Commission_EUR_Apr,
SUM(CASE WHEN MONTH(TradeDateKey) = 5 AND YEAR(TradeDateKey) = YEAR(@MyToMonth)THEN Commission_EUR END) AS Commission_EUR_May,
SUM(CASE WHEN MONTH(TradeDateKey) = 6 AND YEAR(TradeDateKey) = YEAR(@MyToMonth)THEN Commission_EUR END) AS Commission_EUR_Jun,
SUM(CASE WHEN MONTH(TradeDateKey) = 7 AND YEAR(TradeDateKey) = YEAR(@MyToMonth)THEN Commission_EUR END) AS Commission_EUR_Jul,
SUM(CASE WHEN MONTH(TradeDateKey) = 8 AND YEAR(TradeDateKey) = YEAR(@MyToMonth)THEN Commission_EUR END) AS Commission_EUR_Aug,
SUM(CASE WHEN MONTH(TradeDateKey) = 9 AND YEAR(TradeDateKey) = YEAR(@MyToMonth)THEN Commission_EUR END) AS Commission_EUR_Sep,
SUM(CASE WHEN MONTH(TradeDateKey) = 10 AND YEAR(TradeDateKey) = YEAR(@MyToMonth)THEN Commission_EUR END) AS Commission_EUR_Oct,
SUM(CASE WHEN MONTH(TradeDateKey) = 11 AND YEAR(TradeDateKey) = YEAR(@MyToMonth)THEN Commission_EUR END) AS Commission_EUR_Nov,
SUM(CASE WHEN MONTH(TradeDateKey) = 12 AND YEAR(TradeDateKey) = YEAR(@MyToMonth)THEN Commission_EUR END) AS Commission_EUR_Dec,
SUM(CASE WHEN TradeDateKey BETWEEN DATEADD(DAY,0,DATEADD(YEAR,DATEDIFF(YEAR,0,@MyToMonth),0)) AND @MyToMonth THEN Commission_EUR END)AS Commission_EUR_YTD,
SUM(CASE WHEN TradeDateKey
BETWEEN DATEADD(MONTH,-1,DATEADD(YEAR,DATEDIFF(YEAR,0,@MyToMonth),0)) AND DATEADD(DAY,-1,DATEADD(YEAR,DATEDIFF(YEAR,0,@MyToMonth),0)) THEN Commission_EUR END) PreviousYear1_Commission_EUR_Dec,
COUNT(DISTINCT CASE WHEN MONTH(TradeDateKey) = 1 AND YEAR(TradeDateKey) = YEAR(@MyToMonth)THEN TradeOrderNumber END) AS OrdersJan,
COUNT(DISTINCT CASE WHEN MONTH(TradeDateKey) = 2 AND YEAR(TradeDateKey) = YEAR(@MyToMonth)THEN TradeOrderNumber END) AS OrdersFeb,
COUNT(DISTINCT CASE WHEN MONTH(TradeDateKey) = 3 AND YEAR(TradeDateKey) = YEAR(@MyToMonth)THEN TradeOrderNumber END) AS OrdersMar,
COUNT(DISTINCT CASE WHEN MONTH(TradeDateKey) = 4 AND YEAR(TradeDateKey) = YEAR(@MyToMonth)THEN TradeOrderNumber END) AS OrdersApr,
COUNT(DISTINCT CASE WHEN MONTH(TradeDateKey) = 5 AND YEAR(TradeDateKey) = YEAR(@MyToMonth)THEN TradeOrderNumber END) AS OrdersMay,
COUNT(DISTINCT CASE WHEN MONTH(TradeDateKey) = 6 AND YEAR(TradeDateKey) = YEAR(@MyToMonth)THEN TradeOrderNumber END) AS OrdersJun,
COUNT(DISTINCT CASE WHEN MONTH(TradeDateKey) = 7 AND YEAR(TradeDateKey) = YEAR(@MyToMonth)THEN TradeOrderNumber END) AS OrdersJul,
COUNT(DISTINCT CASE WHEN MONTH(TradeDateKey) = 8 AND YEAR(TradeDateKey) = YEAR(@MyToMonth)THEN TradeOrderNumber END) AS OrdersAug,
COUNT(DISTINCT CASE WHEN MONTH(TradeDateKey) = 9 AND YEAR(TradeDateKey) = YEAR(@MyToMonth)THEN TradeOrderNumber END) AS OrdersSep,
COUNT(DISTINCT CASE WHEN MONTH(TradeDateKey) = 10 AND YEAR( TradeDateKey) = YEAR(@MyToMonth)THEN TradeOrderNumber END) AS OrdersOct,
COUNT(DISTINCT CASE WHEN MONTH(TradeDateKey) = 11 AND YEAR(TradeDateKey) = YEAR(@MyToMonth)THEN TradeOrderNumber END) AS OrdersNov,
COUNT(DISTINCT CASE WHEN MONTH(TradeDateKey) = 12 AND YEAR(TradeDateKey) = YEAR(@MyToMonth)THEN TradeOrderNumber END) AS OrdersDec,
CAST(COUNT(DISTINCT CASE WHEN TradeDateKey BETWEEN DATEADD(DAY,0,DATEADD(YEAR,DATEDIFF(YEAR,0,@MyToMonth),0)) AND @MyToMonth THEN TradeOrderNumber END)AS FLOAT ) AS OrderS_EUR_YTD,
COUNT(DISTINCT CASE WHEN TradeDateKey
BETWEEN DATEADD(MONTH,-1,DATEADD(YEAR,DATEDIFF(YEAR,0,@MyToMonth),0)) AND DATEADD(DAY,-1,DATEADD(YEAR,DATEDIFF(YEAR,0,@MyToMonth),0)) THEN TradeOrderNumber END) PreviousYear1_Orders_EUR_Dec,
SUM(CASE WHEN MONTH(TradeDateKey) >= MONTH(DATEADD(MM,-12,@MyToMonth))THEN GrossAmount_EUR END) AS PreviousYear1Turnover,
SUM(CASE WHEN MONTH(TradeDateKey) >= MONTH(DATEADD(MM,-12,@MyToMonth))THEN Commission_EUR END)AS PreviousYear1Commission,
COUNT(DISTINCT CASE WHEN MONTH(TradeDateKey) >= MONTH(DATEADD(MM,-12,@MyToMonth))THEN TradeOrderNumber END) AS PreviousYear1Orders,
SUM(CASE WHEN TradeDateKey BETWEEN DATEADD(MM,-12,@MyToMonth) AND DATEADD(MM,-24,@MyToMonth) THEN GrossAmount_EUR END) AS PreviousYear2Turnover,
SUM(CASE WHEN TradeDateKey BETWEEN DATEADD(MM,-12,@MyToMonth) AND DATEADD(MM,-24,@MyToMonth) THEN Commission_EUR END) AS PreviousYear2Commission,
COUNT(DISTINCT CASE WHEN TradeDateKey BETWEEN DATEADD(MM,-12,@MyToMonth) AND DATEADD(MM,-24,@MyToMonth) THEN TradeOrderNumber END) AS PreviousYear2Orders,
SUM(CASE WHEN TradeDateKey BETWEEN DATEADD(MM,-24,@MyToMonth) AND DATEADD(MM,-36,@MyToMonth) THEN GrossAmount_EUR END) AS PreviousYear3Turnover,
SUM(CASE WHEN TradeDateKey BETWEEN DATEADD(MM,-24,@MyToMonth) AND DATEADD(MM,-36,@MyToMonth) THEN Commission_EUR END) AS PreviousYear3Commission,
COUNT(DISTINCT CASE WHEN TradeDateKey BETWEEN DATEADD(MM,-24,@MyToMonth) AND DATEADD(MM,-36,@MyToMonth) THEN TradeOrderNumber END) AS PreviousYear3Orders,
SUM(CASE WHEN TradeDateKey BETWEEN DATEADD(MM,-36,@MyToMonth) AND DATEADD(MM,-48,@MyToMonth) THEN GrossAmount_EUR END) AS PreviousYear4Turnover,
SUM(CASE WHEN TradeDateKey BETWEEN DATEADD(MM,-36,@MyToMonth) AND DATEADD(MM,-48,@MyToMonth) THEN Commission_EUR END) AS PreviousYear4Commission,
COUNT(DISTINCT CASE WHEN TradeDateKey BETWEEN DATEADD(MM,-36,@MyToMonth) AND DATEADD(MM,-48,@MyToMonth) 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
SELECT DataSourceName,
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(@MyToMonth)* 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(@MyToMonth)* 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(@MyToMonth)* 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(TradeDateKey) = 1 AND YEAR(TradeDateKey) = YEAR(@MyToMonth)THEN GrossAmount_EUR END) AS GrossAmount_EUR_Jan,
SUM(CASE WHEN MONTH(TradeDateKey) = 2 AND YEAR(TradeDateKey) = YEAR(@MyToMonth)THEN GrossAmount_EUR END) AS GrossAmount_EUR_Feb,
SUM(CASE WHEN MONTH(TradeDateKey) = 3 AND YEAR(TradeDateKey) = YEAR(@MyToMonth)THEN GrossAmount_EUR END) AS GrossAmount_EUR_Mar,
SUM(CASE WHEN MONTH(TradeDateKey) = 4 AND YEAR(TradeDateKey) = YEAR(@MyToMonth)THEN GrossAmount_EUR END) AS GrossAmount_EUR_Apr,
SUM(CASE WHEN MONTH(TradeDateKey) = 5 AND YEAR(TradeDateKey) = YEAR(@MyToMonth)THEN GrossAmount_EUR END) AS GrossAmount_EUR_May,
SUM(CASE WHEN MONTH(TradeDateKey) = 6 AND YEAR(TradeDateKey) = YEAR(@MyToMonth)THEN GrossAmount_EUR END) AS GrossAmount_EUR_Jun,
SUM(CASE WHEN MONTH(TradeDateKey) = 7 AND YEAR(TradeDateKey) = YEAR(@MyToMonth)THEN GrossAmount_EUR END) AS GrossAmount_EUR_Jul,
SUM(CASE WHEN MONTH(TradeDateKey) = 8 AND YEAR(TradeDateKey) = YEAR(@MyToMonth)THEN GrossAmount_EUR END) AS GrossAmount_EUR_Aug,
SUM(CASE WHEN MONTH(TradeDateKey) = 9 AND YEAR(TradeDateKey) = YEAR(@MyToMonth)THEN GrossAmount_EUR END) AS GrossAmount_EUR_Sep,
SUM(CASE WHEN MONTH(TradeDateKey) = 10 AND YEAR(TradeDateKey) = YEAR(@MyToMonth)THEN GrossAmount_EUR END) AS GrossAmount_EUR_Oct,
SUM(CASE WHEN MONTH(TradeDateKey) = 11 AND YEAR(TradeDateKey) = YEAR(@MyToMonth)THEN GrossAmount_EUR END) AS GrossAmount_EUR_Nov,
SUM(CASE WHEN MONTH(TradeDateKey) = 12 AND YEAR(TradeDateKey) = YEAR(@MyToMonth)THEN GrossAmount_EUR END) AS GrossAmount_EUR_Dec,
SUM(CASE WHEN TradeDateKey BETWEEN DATEADD(DAY,0,DATEADD(YEAR,DATEDIFF(YEAR,0,@MyToMonth),0)) AND @MyToMonth THEN GrossAmount_EUR END)AS GrossAmount_EUR_YTD,
SUM(CASE WHEN TradeDateKey
BETWEEN DATEADD(MONTH,-1,DATEADD(YEAR,DATEDIFF(YEAR,0,@MyToMonth),0)) AND DATEADD(DAY,-1,DATEADD(YEAR,DATEDIFF(YEAR,0,@MyToMonth),0)) THEN GrossAmount_EUR END) PreviousYear1_GrossAmount_EUR_Dec,
SUM(CASE WHEN MONTH(TradeDateKey) = 1 AND YEAR(TradeDateKey) = YEAR(@MyToMonth)THEN Commission_EUR END) AS Commission_EUR_Jan,
SUM(CASE WHEN MONTH(TradeDateKey) = 2 AND YEAR(TradeDateKey) = YEAR(@MyToMonth)THEN Commission_EUR END) AS Commission_EUR_Feb,
SUM(CASE WHEN MONTH(TradeDateKey) = 3 AND YEAR(TradeDateKey) = YEAR(@MyToMonth)THEN Commission_EUR END) AS Commission_EUR_Mar,
SUM(CASE WHEN MONTH(TradeDateKey) = 4 AND YEAR(TradeDateKey) = YEAR(@MyToMonth)THEN Commission_EUR END) AS Commission_EUR_Apr,
SUM(CASE WHEN MONTH(TradeDateKey) = 5 AND YEAR(TradeDateKey) = YEAR(@MyToMonth)THEN Commission_EUR END) AS Commission_EUR_May,
SUM(CASE WHEN MONTH(TradeDateKey) = 6 AND YEAR(TradeDateKey) = YEAR(@MyToMonth)THEN Commission_EUR END) AS Commission_EUR_Jun,
SUM(CASE WHEN MONTH(TradeDateKey) = 7 AND YEAR(TradeDateKey) = YEAR(@MyToMonth)THEN Commission_EUR END) AS Commission_EUR_Jul,
SUM(CASE WHEN MONTH(TradeDateKey) = 8 AND YEAR(TradeDateKey) = YEAR(@MyToMonth)THEN Commission_EUR END) AS Commission_EUR_Aug,
SUM(CASE WHEN MONTH(TradeDateKey) = 9 AND YEAR(TradeDateKey) = YEAR(@MyToMonth)THEN Commission_EUR END) AS Commission_EUR_Sep,
SUM(CASE WHEN MONTH(TradeDateKey) = 10 AND YEAR(TradeDateKey) = YEAR(@MyToMonth)THEN Commission_EUR END) AS Commission_EUR_Oct,
SUM(CASE WHEN MONTH(TradeDateKey) = 11 AND YEAR(TradeDateKey) = YEAR(@MyToMonth)THEN Commission_EUR END) AS Commission_EUR_Nov,
SUM(CASE WHEN MONTH(TradeDateKey) = 12 AND YEAR(TradeDateKey) = YEAR(@MyToMonth)THEN Commission_EUR END) AS Commission_EUR_Dec,
SUM(CASE WHEN TradeDateKey BETWEEN DATEADD(DAY,0,DATEADD(YEAR,DATEDIFF(YEAR,0,@MyToMonth),0)) AND @MyToMonth THEN Commission_EUR END)AS Commission_EUR_YTD,
SUM(CASE WHEN TradeDateKey
BETWEEN DATEADD(MONTH,-1,DATEADD(YEAR,DATEDIFF(YEAR,0,@MyToMonth),0)) AND DATEADD(DAY,-1,DATEADD(YEAR,DATEDIFF(YEAR,0,@MyToMonth),0)) THEN Commission_EUR END) PreviousYear1_Commission_EUR_Dec,
COUNT(DISTINCT CASE WHEN MONTH(TradeDateKey) = 1 AND YEAR(TradeDateKey) = YEAR(@MyToMonth)THEN TradeOrderNumber END) AS AllocationJan,
COUNT(DISTINCT CASE WHEN MONTH(TradeDateKey) = 2 AND YEAR(TradeDateKey) = YEAR(@MyToMonth)THEN TradeOrderNumber END) AS AllocationFeb,
COUNT(DISTINCT CASE WHEN MONTH(TradeDateKey) = 3 AND YEAR(TradeDateKey) = YEAR(@MyToMonth)THEN TradeOrderNumber END) AS AllocationMar,
COUNT(DISTINCT CASE WHEN MONTH(TradeDateKey) = 4 AND YEAR(TradeDateKey) = YEAR(@MyToMonth)THEN TradeOrderNumber END) AS AllocationApr,
COUNT(DISTINCT CASE WHEN MONTH(TradeDateKey) = 5 AND YEAR(TradeDateKey) = YEAR(@MyToMonth)THEN TradeOrderNumber END) AS AllocationMay,
COUNT(DISTINCT CASE WHEN MONTH(TradeDateKey) = 6 AND YEAR(TradeDateKey) = YEAR(@MyToMonth)THEN TradeOrderNumber END) AS AllocationJun,
COUNT(DISTINCT CASE WHEN MONTH(TradeDateKey) = 7 AND YEAR(TradeDateKey) = YEAR(@MyToMonth)THEN TradeOrderNumber END) AS AllocationJul,
COUNT(DISTINCT CASE WHEN MONTH(TradeDateKey) = 8 AND YEAR(TradeDateKey) = YEAR(@MyToMonth)THEN TradeOrderNumber END) AS AllocationAug,
COUNT(DISTINCT CASE WHEN MONTH(TradeDateKey) = 9 AND YEAR(TradeDateKey) = YEAR(@MyToMonth)THEN TradeOrderNumber END) AS AllocationSep,
COUNT(DISTINCT CASE WHEN MONTH(TradeDateKey) = 10 AND YEAR(TradeDateKey) = YEAR(@MyToMonth)THEN TradeOrderNumber END) AS AllocationOct,
COUNT(DISTINCT CASE WHEN MONTH(TradeDateKey) = 11 AND YEAR(TradeDateKey) = YEAR(@MyToMonth)THEN TradeOrderNumber END) AS AllocationNov,
COUNT(DISTINCT CASE WHEN MONTH(TradeDateKey) = 12 AND YEAR(TradeDateKey)= YEAR(@MyToMonth)THEN TradeOrderNumber END) AS AllocationDec,
CAST(COUNT(DISTINCT CASE WHEN TradeDateKey BETWEEN DATEADD(DAY,0,DATEADD(YEAR,DATEDIFF(YEAR,0,@MyToMonth),0)) AND @MyToMonth THEN TradeOrderNumber END)AS FLOAT) AS Allocation_EUR_YTD,
COUNT(DISTINCT CASE WHEN TradeDateKey
BETWEEN DATEADD(MONTH,-1,DATEADD(YEAR,DATEDIFF(YEAR,0,@MyToMonth),0)) AND DATEADD(DAY,-1,DATEADD(YEAR,DATEDIFF(YEAR,0,@MyToMonth),0)) THEN TradeOrderNumber END) PreviousYear1_Orders_EUR_Dec,
SUM(CASE WHEN MONTH(TradeDateKey) >= MONTH(DATEADD(MM,-12,@MyToMonth))THEN GrossAmount_EUR END) AS PreviousYear1Turnover,
SUM(CASE WHEN MONTH(TradeDateKey) >= MONTH(DATEADD(MM,-12,@MyToMonth))THEN Commission_EUR END)AS PreviousYear1Commission,
COUNT(DISTINCT CASE WHEN MONTH(TradeDateKey) >= MONTH(DATEADD(MM,-12,@MyToMonth))THEN TradeOrderNumber END) AS PreviousYear1Allocation,
SUM(CASE WHEN TradeDateKey BETWEEN DATEADD(MM,-12,@MyToMonth) AND DATEADD(MM,-24,@MyToMonth) THEN GrossAmount_EUR END) AS PreviousYear2Turnover,
SUM(CASE WHEN TradeDateKey BETWEEN DATEADD(MM,-12,@MyToMonth) AND DATEADD(MM,-24,@MyToMonth) THEN Commission_EUR END) AS PreviousYear2Commission,
COUNT(DISTINCT CASE WHEN TradeDateKey BETWEEN DATEADD(MM,-12,@MyToMonth) AND DATEADD(MM,-24,@MyToMonth) THEN TradeOrderNumber END) AS PreviousYear2Allocation,
SUM(CASE WHEN TradeDateKey BETWEEN DATEADD(MM,-24,@MyToMonth) AND DATEADD(MM,-36,@MyToMonth) THEN GrossAmount_EUR END) AS PreviousYear3Turnover,
SUM(CASE WHEN TradeDateKey BETWEEN DATEADD(MM,-24,@MyToMonth) AND DATEADD(MM,-36,@MyToMonth) THEN Commission_EUR END) AS PreviousYear3Commission,
COUNT(DISTINCT CASE WHEN TradeDateKey BETWEEN DATEADD(MM,-24,@MyToMonth) AND DATEADD(MM,-36,@MyToMonth) THEN TradeOrderNumber END) AS PreviousYear3Allocation,
SUM(CASE WHEN TradeDateKey BETWEEN DATEADD(MM,-36,@MyToMonth) AND DATEADD(MM,-48,@MyToMonth) THEN GrossAmount_EUR END) AS PreviousYear4Turnover,
SUM(CASE WHEN TradeDateKey BETWEEN DATEADD(MM,-36,@MyToMonth) AND DATEADD(MM,-48,@MyToMonth) THEN Commission_EUR END) AS PreviousYear4Commission,
COUNT(DISTINCT CASE WHEN TradeDateKey BETWEEN DATEADD(MM,-36,@MyToMonth) AND DATEADD(MM,-48,@MyToMonth) 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
December 6, 2011 at 9:50 am
Hi Lynn,
Query time was exatly the same. The variables were used originally because the query initally suffered from parameter sniffing. However I ran it a few days ago and the speed was fine.
December 6, 2011 at 9:55 am
My concern was all the implicit data conversions. Wanted to see if we eliminated that if it would help. Onward and upward.
December 6, 2011 at 9:57 am
Where is the most current actual execution plan?
Could you repost it?
December 7, 2011 at 2:10 am
got dragged into a meeting yesterday. the latest query execution plan is attached.
I have currently built an aggregation table which is refreshed after our facts and dimensions are processed. Thus the base data is present before the query procedure is run. The calling of tradeDetail is no longer needed. If the query is now run against the aggregation table it takes 1min 17.
However the values are caluclated against the date figure that the user enters. For example PreviousYear1_Commission_EUR_Dec is previous decmeber date based on the date that the user enters. If the user enters 201109 then it should calculate the commision in 201012.
Therefore how can I pre aggregate the data in a way to retreve this information?
December 7, 2011 at 2:50 am
hi eseosaoregie
Going back to the procedure [prv].[sp_TradeDetail], there is a subquery in the select list that returns the top 1 OECD_Memberflag:
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
You said that this procedure alone takes about 2 minutes to complete, so I focused on the select and took a look at the plan you provided. I believe the above subquery is hurting performance. Make sure you have proper indexes on the (foreign key) columns, which are used in that subquery.
What also might help is that you write a table-valued function that takes one inputparameter (TradeCurrency) and returns the same top 1 result, then outer apply it in the from of the main query, somewhat like this:
create function dbo.fnGetOECD_MemberFlag(@TradeCurrency char(3))
returns table
as
return
(
select top 1
C.OECD_MemberFlag
from ref.Map_CountryCurrency CC
join ref.Country C on CC.CountryCode = C.CountryCode
where CurrencyCode = @TradeCurrency
order by OECD_MemberFlag desc
)
and then adjust the select statement in the procedure like this:
SELECT S.DataSourceName AS DataSourceName
...
, CASE WHEN len(ltrim(rtrim(I.IssueCountry))) = 0 THEN oecdMF.OECD_MemberFlag ELSE I.OECD_MemberFlag END AS OECD_MemberFlag
...
FROM dbo.FactTradeOrderAllocation N
...
outer apply dbo.fnGetOECD_MemberFlag( N.TradeCurrency) as oecdMF
WHERE N.TradeDateKey BETWEEN @PeriodStart AND @PeriodEnd
...
I quickly put this together, so I hope my example is clear.
_____________________________________________________
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 7, 2011 at 3:02 am
@Erwin,
I have actually taken the [prv].[sp_TradeDetail] out from the stored proc and added it as part of the processing of the facts and dimension. The output is then loaded into a table. I then run the rest of the query against that table.
My reason for doing this is that we process our facts and dimension once a month. I have 2 days to run the jobs so adding an extra 2mins to the process makes no difference. I will try your suggestion and see how it runs.
My other issue is that I am now trying to pre aggregate the data and have it sitting in a table ready for the corss tab to created from it
December 9, 2011 at 9:55 am
Hey managed to get the query to run much faster. Like 2 or 3 seconds.
Following the ideas of creating summary tables, I basically added an extra ssis package to our scheduled monthly fact and dimesnion table build. The job takes an extra 3 mins to run on top of the usual 3 hours and given it is monthly it is acceptable. After all the fact and dimensions are built I load the summary tables with summarised data from the fact table.
The procedure is as follows
CREATE TABLE #TradeDetail (
DataSourceName varchar(120)
,CorporateRegion char(3)
,Country char(3)
,TradeDateKey smalldatetime
,MONTH int
,YEAR 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(120)
,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', '20121231'
-- ====================================================================================
-- (2) LOAD BASE TRADE ORDERS SUMMARY TABLE
-- ====================================================================================
TRUNCATE TABLE [dbo].[SummaryFactTradeOrders]
INSERT INTO [GEAR].[dbo].[SummaryFactTradeOrders]
([DataSourceName]
,[CorporateRegion]
,[Country]
,[Broker]
,[ParentBrokerCode]
,[Trader_Number]
,[Trader_Region]
,[AssetClassCode]
,[AssetClassName]
,[InstrumentType]
,[TransactionType]
,[Month]
,[Year]
,[GrossAmount_EUR_Jan]
,[GrossAmount_EUR_Feb]
,[GrossAmount_EUR_Mar]
,[GrossAmount_EUR_Apr]
,[GrossAmount_EUR_May]
,[GrossAmount_EUR_Jun]
,[GrossAmount_EUR_Jul]
,[GrossAmount_EUR_Aug]
,[GrossAmount_EUR_Sep]
,[GrossAmount_EUR_Oct]
,[GrossAmount_EUR_Nov]
,[GrossAmount_EUR_Dec]
,[GrossAmount_EUR_YTD]
,[Commission_EUR_Jan]
,[Commission_EUR_Feb]
,[Commission_EUR_Mar]
,[Commission_EUR_Apr]
,[Commission_EUR_May]
,[Commission_EUR_Jun]
,[Commission_EUR_Jul]
,[Commission_EUR_Aug]
,[Commission_EUR_Sep]
,[Commission_EUR_Oct]
,[Commission_EUR_Nov]
,[Commission_EUR_Dec]
,[Commission_EUR_YTD]
,[OrdersJan]
,[OrdersFeb]
,[OrdersMar]
,[OrdersApr]
,[OrdersMay]
,[OrdersJun]
,[OrdersJul]
,[OrdersAug]
,[OrdersSep]
,[OrdersOct]
,[OrdersNov]
,[OrdersDec]
,[OrderS_EUR_YTD])
SELECTDataSourceName,
CorporateRegion,
Country,
Broker,
ParentBrokerCode,
Trader_Number,
Trader_Region,
AssetClassCode,
AssetClassName,
InstrumentType,
TransactionType,
[Month],
[Year],
SUM(CASE WHEN MONTH(TradeDateKey) = 1 THEN GrossAmount_EUR END) ,
SUM(CASE WHEN MONTH(TradeDateKey) = 2 THEN GrossAmount_EUR END) ,
SUM(CASE WHEN MONTH(TradeDateKey) = 3 THEN GrossAmount_EUR END) ,
SUM(CASE WHEN MONTH(TradeDateKey) = 4 THEN GrossAmount_EUR END) ,
SUM(CASE WHEN MONTH(TradeDateKey) = 5 THEN GrossAmount_EUR END) ,
SUM(CASE WHEN MONTH(TradeDateKey) = 6 THEN GrossAmount_EUR END) ,
SUM(CASE WHEN MONTH(TradeDateKey) = 7 THEN GrossAmount_EUR END) ,
SUM(CASE WHEN MONTH(TradeDateKey) = 8 THEN GrossAmount_EUR END) ,
SUM(CASE WHEN MONTH(TradeDateKey) = 9 THEN GrossAmount_EUR END) ,
SUM(CASE WHEN MONTH(TradeDateKey) = 10 THEN GrossAmount_EUR END) ,
SUM(CASE WHEN MONTH(TradeDateKey) = 11 THEN GrossAmount_EUR END) ,
SUM(CASE WHEN MONTH(TradeDateKey) = 12 THEN GrossAmount_EUR END) ,
SUM(GrossAmount_EUR),
SUM(CASE WHEN MONTH(TradeDateKey) = 1 THEN Commission_EUR END) ,
SUM(CASE WHEN MONTH(TradeDateKey) = 2 THEN Commission_EUR END) ,
SUM(CASE WHEN MONTH(TradeDateKey) = 3 THEN Commission_EUR END) ,
SUM(CASE WHEN MONTH(TradeDateKey) = 4 THEN Commission_EUR END) ,
SUM(CASE WHEN MONTH(TradeDateKey) = 5 THEN Commission_EUR END) ,
SUM(CASE WHEN MONTH(TradeDateKey) = 6 THEN Commission_EUR END) ,
SUM(CASE WHEN MONTH(TradeDateKey) = 7 THEN Commission_EUR END) ,
SUM(CASE WHEN MONTH(TradeDateKey) = 8 THEN Commission_EUR END) ,
SUM(CASE WHEN MONTH(TradeDateKey) = 9 THEN Commission_EUR END) ,
SUM(CASE WHEN MONTH(TradeDateKey) = 10 THEN Commission_EUR END) ,
SUM(CASE WHEN MONTH(TradeDateKey) = 11 THEN Commission_EUR END) ,
SUM(CASE WHEN MONTH(TradeDateKey) = 12 THEN Commission_EUR END) ,
SUM(Commission_EUR),
COUNT(DISTINCT CASE WHEN MONTH(TradeDateKey) = 1 THEN TradeOrderNumber END) ,
COUNT(DISTINCT CASE WHEN MONTH(TradeDateKey) = 2 THEN TradeOrderNumber END) ,
COUNT(DISTINCT CASE WHEN MONTH(TradeDateKey) = 3 THEN TradeOrderNumber END) ,
COUNT(DISTINCT CASE WHEN MONTH(TradeDateKey) = 4 THEN TradeOrderNumber END) ,
COUNT(DISTINCT CASE WHEN MONTH(TradeDateKey) = 5 THEN TradeOrderNumber END) ,
COUNT(DISTINCT CASE WHEN MONTH(TradeDateKey) = 6 THEN TradeOrderNumber END) ,
COUNT(DISTINCT CASE WHEN MONTH(TradeDateKey) = 7 THEN TradeOrderNumber END) ,
COUNT(DISTINCT CASE WHEN MONTH(TradeDateKey) = 8 THEN TradeOrderNumber END) ,
COUNT(DISTINCT CASE WHEN MONTH(TradeDateKey) = 9 THEN TradeOrderNumber END) ,
COUNT(DISTINCT CASE WHEN MONTH(TradeDateKey) = 10 THEN TradeOrderNumber END),
COUNT(DISTINCT CASE WHEN MONTH(TradeDateKey) = 11 THEN TradeOrderNumber END) ,
COUNT(DISTINCT CASE WHEN MONTH(TradeDateKey) = 12 THEN TradeOrderNumber END) ,
CAST(COUNT(DISTINCT TradeOrderNumber)AS FLOAT)
FROM #TradeDetail
GROUP BY DataSourceName,CorporateRegion,Country,Broker, ParentBrokerCode,Trader_Number,Trader_Region,AssetClassCode, AssetClassName,InstrumentType,TransactionType,[MONTH], [YEAR]
-- ====================================================================================
-- (3) LOAD BASE TRADE ALLOCATIONS SUMMARY TABLE
-- ====================================================================================
TRUNCATE TABLE [dbo].[SummaryFactTradeAllocations]
INSERT INTO [GEAR].[dbo].[SummaryFactTradeAllocations]
([DataSourceName]
,[CorporateRegion]
,[Country]
,[Broker]
,[ParentBrokerCode]
,[Trader_Number]
,[Trader_Region]
,[UPI]
,[Portfolio]
,[Boutique]
,[AssetClassCode]
,[AssetClassName]
,[InstrumentType]
,[TransactionType]
,[Month]
,[Year]
,[GrossAmount_EUR_Jan]
,[GrossAmount_EUR_Feb]
,[GrossAmount_EUR_Mar]
,[GrossAmount_EUR_Apr]
,[GrossAmount_EUR_May]
,[GrossAmount_EUR_Jun]
,[GrossAmount_EUR_Jul]
,[GrossAmount_EUR_Aug]
,[GrossAmount_EUR_Sep]
,[GrossAmount_EUR_Oct]
,[GrossAmount_EUR_Nov]
,[GrossAmount_EUR_Dec]
,[GrossAmount_EUR_YTD]
,[Commission_EUR_Jan]
,[Commission_EUR_Feb]
,[Commission_EUR_Mar]
,[Commission_EUR_Apr]
,[Commission_EUR_May]
,[Commission_EUR_Jun]
,[Commission_EUR_Jul]
,[Commission_EUR_Aug]
,[Commission_EUR_Sep]
,[Commission_EUR_Oct]
,[Commission_EUR_Nov]
,[Commission_EUR_Dec]
,[Commission_EUR_YTD]
,[AllocationsJan]
,[AllocationsFeb]
,[AllocationsMar]
,[AllocationsApr]
,[AllocationsMay]
,[AllocationsJun]
,[AllocationsJul]
,[AllocationsAug]
,[AllocationsSep]
,[AllocationsOct]
,[AllocationsNov]
,[AllocationsDec]
,[Allocations_EUR_YTD])
SELECTDataSourceName,
CorporateRegion,
Country,
Broker,
ParentBrokerCode,
Trader_Number,
Trader_Region,
UPI,
Portfolio,
Boutique,
AssetClassCode,
AssetClassName,
InstrumentType,
TransactionType,
[Month],
[Year],
SUM(CASE WHEN MONTH(TradeDateKey) = 1 THEN GrossAmount_EUR END) ,
SUM(CASE WHEN MONTH(TradeDateKey) = 2 THEN GrossAmount_EUR END) ,
SUM(CASE WHEN MONTH(TradeDateKey) = 3 THEN GrossAmount_EUR END) ,
SUM(CASE WHEN MONTH(TradeDateKey) = 4 THEN GrossAmount_EUR END) ,
SUM(CASE WHEN MONTH(TradeDateKey) = 5 THEN GrossAmount_EUR END) ,
SUM(CASE WHEN MONTH(TradeDateKey) = 6 THEN GrossAmount_EUR END) ,
SUM(CASE WHEN MONTH(TradeDateKey) = 7 THEN GrossAmount_EUR END) ,
SUM(CASE WHEN MONTH(TradeDateKey) = 8 THEN GrossAmount_EUR END) ,
SUM(CASE WHEN MONTH(TradeDateKey) = 9 THEN GrossAmount_EUR END) ,
SUM(CASE WHEN MONTH(TradeDateKey) = 10 THEN GrossAmount_EUR END) ,
SUM(CASE WHEN MONTH(TradeDateKey) = 11 THEN GrossAmount_EUR END) ,
SUM(CASE WHEN MONTH(TradeDateKey) = 12 THEN GrossAmount_EUR END) ,
SUM(GrossAmount_EUR),
SUM(CASE WHEN MONTH(TradeDateKey) = 1 THEN Commission_EUR END) ,
SUM(CASE WHEN MONTH(TradeDateKey) = 2 THEN Commission_EUR END) ,
SUM(CASE WHEN MONTH(TradeDateKey) = 3 THEN Commission_EUR END) ,
SUM(CASE WHEN MONTH(TradeDateKey) = 4 THEN Commission_EUR END) ,
SUM(CASE WHEN MONTH(TradeDateKey) = 5 THEN Commission_EUR END) ,
SUM(CASE WHEN MONTH(TradeDateKey) = 6 THEN Commission_EUR END) ,
SUM(CASE WHEN MONTH(TradeDateKey) = 7 THEN Commission_EUR END) ,
SUM(CASE WHEN MONTH(TradeDateKey) = 8 THEN Commission_EUR END) ,
SUM(CASE WHEN MONTH(TradeDateKey) = 9 THEN Commission_EUR END) ,
SUM(CASE WHEN MONTH(TradeDateKey) = 10 THEN Commission_EUR END) ,
SUM(CASE WHEN MONTH(TradeDateKey) = 11 THEN Commission_EUR END) ,
SUM(CASE WHEN MONTH(TradeDateKey) = 12 THEN Commission_EUR END) ,
SUM(Commission_EUR),
COUNT(DISTINCT CASE WHEN MONTH(TradeDateKey) = 1 THEN TradeOrderNumber END) ,
COUNT(DISTINCT CASE WHEN MONTH(TradeDateKey) = 2 THEN TradeOrderNumber END) ,
COUNT(DISTINCT CASE WHEN MONTH(TradeDateKey) = 3 THEN TradeOrderNumber END) ,
COUNT(DISTINCT CASE WHEN MONTH(TradeDateKey) = 4 THEN TradeOrderNumber END) ,
COUNT(DISTINCT CASE WHEN MONTH(TradeDateKey) = 5 THEN TradeOrderNumber END) ,
COUNT(DISTINCT CASE WHEN MONTH(TradeDateKey) = 6 THEN TradeOrderNumber END) ,
COUNT(DISTINCT CASE WHEN MONTH(TradeDateKey) = 7 THEN TradeOrderNumber END) ,
COUNT(DISTINCT CASE WHEN MONTH(TradeDateKey) = 8 THEN TradeOrderNumber END) ,
COUNT(DISTINCT CASE WHEN MONTH(TradeDateKey) = 9 THEN TradeOrderNumber END) ,
COUNT(DISTINCT CASE WHEN MONTH(TradeDateKey) = 10 THEN TradeOrderNumber END),
COUNT(DISTINCT CASE WHEN MONTH(TradeDateKey) = 11 THEN TradeOrderNumber END) ,
COUNT(DISTINCT CASE WHEN MONTH(TradeDateKey) = 12 THEN TradeOrderNumber END) ,
CAST(COUNT(DISTINCT TradeOrderNumber)AS FLOAT)
FROM #TradeDetail
GROUP BY DataSourceName,CorporateRegion,Country,Broker, ParentBrokerCode,UPI,Portfolio,Boutique,Trader_Number,Trader_Region,AssetClassCode, AssetClassName,InstrumentType,TransactionType,[MONTH], [YEAR]
I created columns of year and date based on the the date TradeDateKey to aid the calcualtions of previous year gross and commission etc. The summary tables have indexes on the Trader_Region, AssetClassCode, InstrumentType columns.
The user macro then runs the following stored procedure
CREATE 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
SET NOCOUNT ON
-- ===========================================================================
-- (1) Check Parameters and set defaults
-- ===========================================================================
IF @TradeDataType <> 'E' SET @TradeDataType = 'E'
IF @ReportView NOT IN ('Broker','Portfolio') SET @ReportView = 'Broker';
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) CrossTab Based on report view parameter
-- ===========================================================================
IF @ReportView = 'Broker'
SELECTDataSourceName
,CorporateRegion
,Country
,[Broker]
, ParentBrokerCode
,Trader_Number
, Trader_Region
,AssetClassCode
,AssetClassName
,InstrumentType
,TransactionType
,coalesce(SUM(CASE WHEN [YEAR] = YEAR(@ToMonth) -1 AND [MONTH] = 12 THEN GrossAmount_EUR_Dec END),0) AS PreviousYear1_GrossAmount_EUR_Dec
,coalesce(SUM(CASE WHEN [YEAR] = YEAR(@ToMonth) AND [MONTH] = 1 THEN GrossAmount_EUR_Jan END),0) AS GrossAmount_EUR_Jan
, coalesce(SUM(CASE WHEN [YEAR] = YEAR(@ToMonth) AND [MONTH] = 2 THEN GrossAmount_EUR_Feb END),0)AS GrossAmount_EUR_Feb
, coalesce(SUM(CASE WHEN [YEAR] = YEAR(@ToMonth) AND [MONTH] = 3 THEN GrossAmount_EUR_Mar END),0) AS GrossAmount_EUR_Mar
,coalesce(SUM(CASE WHEN [YEAR] = YEAR(@ToMonth) AND [MONTH] = 4 THEN GrossAmount_EUR_Apr END),0) AS GrossAmount_EUR_Apr
,coalesce(SUM(CASE WHEN [YEAR] = YEAR(@ToMonth) AND [MONTH] = 5 THEN GrossAmount_EUR_May END),0) AS GrossAmount_EUR_May
, coalesce(SUM(CASE WHEN [YEAR] = YEAR(@ToMonth) AND [MONTH] = 6 THEN GrossAmount_EUR_Jun END),0) AS GrossAmount_EUR_Jun
, coalesce(SUM(CASE WHEN [YEAR] = YEAR(@ToMonth) AND [MONTH] = 7 THEN GrossAmount_EUR_Jul END),0) AS GrossAmount_EUR_Jul
, coalesce(SUM(CASE WHEN [YEAR] = YEAR(@ToMonth) AND [MONTH] = 8 THEN GrossAmount_EUR_Aug END),0) AS GrossAmount_EUR_Aug
, coalesce(SUM(CASE WHEN [YEAR] = YEAR(@ToMonth) AND [MONTH] = 9 THEN GrossAmount_EUR_Sep END),0) AS GrossAmount_EUR_Sep
, coalesce(SUM(CASE WHEN [YEAR] = YEAR(@ToMonth) AND [MONTH] = 10 THEN GrossAmount_EUR_Oct END),0) AS GrossAmount_EUR_Oct
, coalesce(SUM(CASE WHEN [YEAR] = YEAR(@ToMonth) AND [MONTH] = 11 THEN GrossAmount_EUR_Nov END),0) AS GrossAmount_EUR_Nov
,coalesce(SUM(CASE WHEN [YEAR] = YEAR(@ToMonth) AND [MONTH] = 12 THEN GrossAmount_EUR_Dec END),0) AS GrossAmount_EUR_Dec
, coalesce(SUM(CASE WHEN [YEAR] = YEAR(@ToMonth) THEN GrossAmount_EUR_YTD END),0) AS GrossAmount_EUR_YTD
, coalesce(SUM(CASE WHEN [YEAR] = YEAR(@ToMonth) THEN GrossAmount_EUR_YTD END),0)/MONTH(@ToMonth) * 12 AS GrossAmount_EUR_ANN
,coalesce(SUM(CASE WHEN [YEAR] = YEAR(@ToMonth) - 1 AND [MONTH] = 12 THEN Commission_EUR_Dec END),0) AS PreviousYear1_Commission_EUR_Dec
, coalesce(SUM(CASE WHEN [YEAR] = YEAR(@ToMonth) AND [MONTH] = 1 THEN Commission_EUR_Jan END),0) AS Commission_EUR_Jan
, coalesce(SUM(CASE WHEN [YEAR] = YEAR(@ToMonth) AND [MONTH] = 2 THEN Commission_EUR_Feb END),0) AS Commission_EUR_Feb
, coalesce(SUM(CASE WHEN [YEAR] = YEAR(@ToMonth) AND [MONTH] = 3 THEN Commission_EUR_Mar END),0) AS Commission_EUR_Mar
, coalesce(SUM(CASE WHEN [YEAR] = YEAR(@ToMonth) AND [MONTH] = 4 THEN Commission_EUR_Apr END),0) AS Commission_EUR_Apr
, coalesce(SUM(CASE WHEN [YEAR] = YEAR(@ToMonth) AND [MONTH] = 5 THEN Commission_EUR_May END),0) AS Commission_EUR_May
, coalesce(SUM(CASE WHEN [YEAR] = YEAR(@ToMonth) AND [MONTH] = 6 THEN Commission_EUR_Jun END),0) AS Commission_EUR_Jun
, coalesce(SUM(CASE WHEN [YEAR] = YEAR(@ToMonth) AND [MONTH] = 7 THEN Commission_EUR_Jul END),0) AS Commission_EUR_Jul
, coalesce(SUM(CASE WHEN [YEAR] = YEAR(@ToMonth) AND [MONTH] = 8 THEN Commission_EUR_Aug END),0) AS Commission_EUR_Aug
, coalesce(SUM(CASE WHEN [YEAR] = YEAR(@ToMonth) AND [MONTH] = 9 THEN Commission_EUR_Sep END),0) AS Commission_EUR_Sep
, coalesce(SUM(CASE WHEN [YEAR] = YEAR(@ToMonth) AND [MONTH] = 10 THEN Commission_EUR_Oct END),0) AS Commission_EUR_Oct
, coalesce(SUM(CASE WHEN [YEAR] = YEAR(@ToMonth) AND [MONTH] = 11 THEN Commission_EUR_Nov END),0) AS Commission_EUR_Nov
, coalesce(SUM(CASE WHEN [YEAR] = YEAR(@ToMonth) AND [MONTH] = 12 THEN Commission_EUR_Dec END),0) AS Commission_EUR_Dec
, coalesce(SUM(CASE WHEN [YEAR] = YEAR(@ToMonth) THEN Commission_EUR_YTD END),0) AS Commission_EUR_YTD
, coalesce(SUM(CASE WHEN [YEAR] = YEAR(@ToMonth) THEN Commission_EUR_YTD END),0)/MONTH(@ToMonth)* 12 AS Commission_EUR_ANN
, coalesce(SUM(CASE WHEN [YEAR] = YEAR(@ToMonth) AND [MONTH] = 1 THEN OrdersJan END),0) AS OrdersJan
, coalesce(SUM(CASE WHEN [YEAR] = YEAR(@ToMonth) AND [MONTH] = 2 THEN OrdersFeb END),0) AS OrdersFeb
, coalesce(SUM(CASE WHEN [YEAR] = YEAR(@ToMonth) AND [MONTH] = 3 THEN OrdersMar END),0) AS OrdersMar
, coalesce(SUM(CASE WHEN [YEAR] = YEAR(@ToMonth) AND [MONTH] = 4 THEN OrdersApr END),0) AS OrdersApr
, coalesce(SUM(CASE WHEN [YEAR] = YEAR(@ToMonth) AND [MONTH] = 5 THEN OrdersMay END),0) AS OrdersMay
, coalesce(SUM(CASE WHEN [YEAR] = YEAR(@ToMonth) AND [MONTH] = 6 THEN OrdersJun END),0) AS OrdersJun
, coalesce(SUM(CASE WHEN [YEAR] = YEAR(@ToMonth) AND [MONTH] = 7 THEN OrdersJul END),0) AS OrdersJul
, coalesce(SUM(CASE WHEN [YEAR] = YEAR(@ToMonth) AND [MONTH] = 8 THEN OrdersAug END),0) AS OrdersAug
, coalesce(SUM(CASE WHEN [YEAR] = YEAR(@ToMonth) AND [MONTH] = 9 THEN OrdersSep END),0) AS OrdersSep
, coalesce(SUM(CASE WHEN [YEAR] = YEAR(@ToMonth) AND [MONTH] = 10 THEN OrdersOct END),0) AS OrdersOct
, coalesce(SUM(CASE WHEN [YEAR] = YEAR(@ToMonth) AND [MONTH] = 11 THEN OrdersNov END),0) AS OrdersNov
, coalesce(SUM(CASE WHEN [YEAR] = YEAR(@ToMonth) AND [MONTH] = 12 THEN OrdersDec END),0) AS OrdersDec
, coalesce(SUM(CASE WHEN [YEAR] = YEAR(@ToMonth) THEN Orders_EUR_YTD END),0) AS Orders_EUR_YTD
,coalesce(SUM(CASE WHEN [YEAR] = YEAR(@ToMonth) THEN Orders_EUR_YTD END),0)/MONTH(@ToMonth) * 12 AS Orders_EUR_ANN
,coalesce(SUM(CASE WHEN [YEAR] = YEAR(@ToMonth) - 1 THEN GrossAmount_EUR_YTD END),0) AS PreviousYear1Turnover
,coalesce(SUM(CASE WHEN [YEAR] = YEAR(@ToMonth) - 1 THEN Commission_EUR_YTD END),0) AS PreviousYear1CommisSion
,coalesce(SUM(CASE WHEN [YEAR] = YEAR(@ToMonth) - 1 THEN Orders_EUR_YTD END),0) AS PreviousYear1Orders
,coalesce(SUM(CASE WHEN [YEAR] = YEAR(@ToMonth) - 2 THEN GrossAmount_EUR_YTD END),0) AS PreviousYear2Turnover
,coalesce(SUM(CASE WHEN [YEAR] = YEAR(@ToMonth) - 2 THEN Commission_EUR_YTD END),0) AS PreviousYear2CommisSion
,coalesce(SUM(CASE WHEN [YEAR] = YEAR(@ToMonth) - 2 THEN Orders_EUR_YTD END),0) AS PreviousYear2Orders
,coalesce(SUM(CASE WHEN [YEAR] = YEAR(@ToMonth) - 3 THEN GrossAmount_EUR_YTD END),0) AS PreviousYear3Turnover
,coalesce(SUM(CASE WHEN [YEAR] = YEAR(@ToMonth) - 3 THEN Commission_EUR_YTD END),0) AS PreviousYear3Commission
,coalesce(SUM(CASE WHEN [YEAR] = YEAR(@ToMonth) - 3 THEN Orders_EUR_YTD END),0) AS PreviousYear3Orders
FROM dbo.SummaryFactTradeOrders
WHERE
[YEAR] <= YEAR(@ToMonth)
AND AssetClassCode = (CASE WHEN @TradeDataType = 'ALL' THEN AssetClassCode ELSE @TradeDataType END)
AND [Broker] = (CASE WHEN @Broker = 'ALL' THEN [Broker]ELSE @Broker END)
AND InstrumentType = (CASE WHEN @InstrumentType = 'ALL' THEN InstrumentType ELSE @InstrumentType END)
AND Trader_Region = (CASE WHEN @TraderRegion = 'ALL' THEN Trader_Region ELSE @TraderRegion END)
GROUP BY
DataSourceName
,CorporateRegion
,Country
,[Broker]
, ParentBrokerCode
,Trader_Number
, Trader_Region
,AssetClassCode
,AssetClassName
,InstrumentType
,TransactionType
ORDER BY
DataSourceName
,CorporateRegion
,Country
,[Broker]
, ParentBrokerCode
,Trader_Number
, Trader_Region
,AssetClassCode
,AssetClassName
,InstrumentType
,TransactionType
ELSE -- PortfolioView
SELECTDataSourceName
,CorporateRegion
,Country
,[Broker]
, ParentBrokerCode as BrokerID
,Trader_Number
, Trader_Region
,UPI AS PortfolioID
,Portfolio
,Boutique
,AssetClassCode
,AssetClassName
,InstrumentType
,TransactionType
,coalesce(SUM(CASE WHEN [YEAR] = YEAR(@ToMonth) -1 AND [MONTH] = 12 THEN GrossAmount_EUR_Dec END),0) AS PreviousYear1_GrossAmount_EUR_Dec
,coalesce(SUM(CASE WHEN [YEAR] = YEAR(@ToMonth) AND [MONTH] = 1 THEN GrossAmount_EUR_Jan END),0) AS GrossAmount_EUR_Jan
, coalesce(SUM(CASE WHEN [YEAR] = YEAR(@ToMonth) AND [MONTH] = 2 THEN GrossAmount_EUR_Feb END),0)AS GrossAmount_EUR_Feb
, coalesce(SUM(CASE WHEN [YEAR] = YEAR(@ToMonth) AND [MONTH] = 3 THEN GrossAmount_EUR_Mar END),0) AS GrossAmount_EUR_Mar
,coalesce(SUM(CASE WHEN [YEAR] = YEAR(@ToMonth) AND [MONTH] = 4 THEN GrossAmount_EUR_Apr END),0) AS GrossAmount_EUR_Apr
,coalesce(SUM(CASE WHEN [YEAR] = YEAR(@ToMonth) AND [MONTH] = 5 THEN GrossAmount_EUR_May END),0) AS GrossAmount_EUR_May
, coalesce(SUM(CASE WHEN [YEAR] = YEAR(@ToMonth) AND [MONTH] = 6 THEN GrossAmount_EUR_Jun END),0) AS GrossAmount_EUR_Jun
, coalesce(SUM(CASE WHEN [YEAR] = YEAR(@ToMonth) AND [MONTH] = 7 THEN GrossAmount_EUR_Jul END),0) AS GrossAmount_EUR_Jul
, coalesce(SUM(CASE WHEN [YEAR] = YEAR(@ToMonth) AND [MONTH] = 8 THEN GrossAmount_EUR_Aug END),0) AS GrossAmount_EUR_Aug
, coalesce(SUM(CASE WHEN [YEAR] = YEAR(@ToMonth) AND [MONTH] = 9 THEN GrossAmount_EUR_Sep END),0) AS GrossAmount_EUR_Sep
, coalesce(SUM(CASE WHEN [YEAR] = YEAR(@ToMonth) AND [MONTH] = 10 THEN GrossAmount_EUR_Oct END),0) AS GrossAmount_EUR_Oct
, coalesce(SUM(CASE WHEN [YEAR] = YEAR(@ToMonth) AND [MONTH] = 11 THEN GrossAmount_EUR_Nov END),0) AS GrossAmount_EUR_Nov
,coalesce(SUM(CASE WHEN [YEAR] = YEAR(@ToMonth) AND [MONTH] = 12 THEN GrossAmount_EUR_Dec END),0) AS GrossAmount_EUR_Dec
, coalesce(SUM(CASE WHEN [YEAR] = YEAR(@ToMonth)THEN GrossAmount_EUR_YTD END),0) AS GrossAmount_EUR_YTD
, coalesce(SUM(CASE WHEN [YEAR] = YEAR(@ToMonth) THEN GrossAmount_EUR_YTD END),0)/MONTH(@ToMonth) * 12 AS GrossAmount_EUR_ANN
,coalesce(SUM(CASE WHEN [YEAR] = YEAR(@ToMonth) -1 AND [MONTH] = 12 THEN Commission_EUR_Dec END),0) AS PreviousYear1_Commission_EUR_Dec
, coalesce(SUM(CASE WHEN [YEAR] = YEAR(@ToMonth) AND [MONTH] = 1 THEN Commission_EUR_Jan END),0) AS Commission_EUR_Jan
, coalesce(SUM(CASE WHEN [YEAR] = YEAR(@ToMonth) AND [MONTH] = 2 THEN Commission_EUR_Feb END),0) AS Commission_EUR_Feb
, coalesce(SUM(CASE WHEN [YEAR] = YEAR(@ToMonth) AND [MONTH] = 3 THEN Commission_EUR_Mar END),0) AS Commission_EUR_Mar
, coalesce(SUM(CASE WHEN [YEAR] = YEAR(@ToMonth) AND [MONTH] = 4 THEN Commission_EUR_Apr END),0) AS Commission_EUR_Apr
, coalesce(SUM(CASE WHEN [YEAR] = YEAR(@ToMonth) AND [MONTH] = 5 THEN Commission_EUR_May END),0) AS Commission_EUR_May
, coalesce(SUM(CASE WHEN [YEAR] = YEAR(@ToMonth) AND [MONTH] = 6 THEN Commission_EUR_Jun END),0) AS Commission_EUR_Jun
, coalesce(SUM(CASE WHEN [YEAR] = YEAR(@ToMonth) AND [MONTH] = 7 THEN Commission_EUR_Jul END),0) AS Commission_EUR_Jul
, coalesce(SUM(CASE WHEN [YEAR] = YEAR(@ToMonth) AND [MONTH] = 8 THEN Commission_EUR_Aug END),0) AS Commission_EUR_Aug
, coalesce(SUM(CASE WHEN [YEAR] = YEAR(@ToMonth) AND [MONTH] = 9 THEN Commission_EUR_Sep END),0) AS Commission_EUR_Sep
, coalesce(SUM(CASE WHEN [YEAR] = YEAR(@ToMonth) AND [MONTH] = 10 THEN Commission_EUR_Oct END),0) AS Commission_EUR_Oct
, coalesce(SUM(CASE WHEN [YEAR] = YEAR(@ToMonth) AND [MONTH] = 11 THEN Commission_EUR_Nov END),0) AS Commission_EUR_Nov
, coalesce(SUM(CASE WHEN [YEAR] = YEAR(@ToMonth) AND [MONTH] = 12 THEN Commission_EUR_Dec END),0) AS Commission_EUR_Dec
, coalesce(SUM(CASE WHEN [YEAR] = YEAR(@ToMonth) THEN Commission_EUR_YTD END),0) AS Commission_EUR_YTD
, coalesce(SUM(CASE WHEN [YEAR] = YEAR(@ToMonth) THEN Commission_EUR_YTD END),0)/MONTH(@ToMonth)* 12 AS Commission_EUR_ANN
, coalesce(SUM(CASE WHEN [YEAR] = YEAR(@ToMonth) AND [MONTH] = 1 THEN AllocationsJan END),0) AS AllocationsJan
, coalesce(SUM(CASE WHEN [YEAR] = YEAR(@ToMonth) AND [MONTH] = 2 THEN AllocationsFeb END),0) AS AllocationsFeb
, coalesce(SUM(CASE WHEN [YEAR] = YEAR(@ToMonth) AND [MONTH] = 3 THEN AllocationsMar END),0) AS AllocationsMar
, coalesce(SUM(CASE WHEN [YEAR] = YEAR(@ToMonth) AND [MONTH] = 4 THEN AllocationsApr END),0) AS AllocationsApr
, coalesce(SUM(CASE WHEN [YEAR] = YEAR(@ToMonth) AND [MONTH] = 5 THEN AllocationsMay END),0) AS AllocationsMay
, coalesce(SUM(CASE WHEN [YEAR] = YEAR(@ToMonth) AND [MONTH] = 6 THEN AllocationsJun END),0) AS AllocationsJun
, coalesce(SUM(CASE WHEN [YEAR] = YEAR(@ToMonth) AND [MONTH] = 7 THEN AllocationsJul END),0) AS AllocationsJul
, coalesce(SUM(CASE WHEN [YEAR] = YEAR(@ToMonth) AND [MONTH] = 8 THEN AllocationsAug END),0) AS AllocationsAug
, coalesce(SUM(CASE WHEN [YEAR] = YEAR(@ToMonth) AND [MONTH] = 9 THEN AllocationsSep END),0) AS AllocationsSep
, coalesce(SUM(CASE WHEN [YEAR] = YEAR(@ToMonth) AND [MONTH] = 10 THEN AllocationsOct END),0) AS AllocationsOct
, coalesce(SUM(CASE WHEN [YEAR] = YEAR(@ToMonth) AND [MONTH] = 11 THEN AllocationsNov END),0) AS AllocationsNov
, coalesce(SUM(CASE WHEN [YEAR] = YEAR(@ToMonth) AND [MONTH] = 12 THEN AllocationsDec END),0) AS AllocationsDec
, coalesce(SUM(CASE WHEN [YEAR] = YEAR(@ToMonth) THEN Allocations_EUR_YTD END),0) AS Allocations_EUR_YTD
,coalesce(SUM(CASE WHEN [YEAR] = YEAR(@ToMonth) THEN Allocations_EUR_YTD END),0)/MONTH(@ToMonth) * 12 AS Allocations_EUR_ANN
,coalesce(SUM(CASE WHEN [YEAR] = YEAR(@ToMonth)-1 THEN GrossAmount_EUR_YTD END),0) AS PreviousYear1Turnover
,coalesce(SUM(CASE WHEN [YEAR] = YEAR(@ToMonth)-1 THEN Commission_EUR_YTD END),0) AS PreviousYear1CommisSion
,coalesce(SUM(CASE WHEN [YEAR] = YEAR(@ToMonth)-1 THEN Allocations_EUR_YTD END),0) AS PreviousYear1Allocations
,coalesce(SUM(CASE WHEN [YEAR] = YEAR(@ToMonth)-2 THEN GrossAmount_EUR_YTD END),0) AS PreviousYear2Turnover
,coalesce(SUM(CASE WHEN [YEAR] = YEAR(@ToMonth)-2 THEN Commission_EUR_YTD END),0) AS PreviousYear2CommisSion
,coalesce(SUM(CASE WHEN [YEAR] = YEAR(@ToMonth)-2 THEN Allocations_EUR_YTD END),0) AS PreviousYear2Allocations
,coalesce(SUM(CASE WHEN [YEAR] = YEAR(@ToMonth)-3 THEN GrossAmount_EUR_YTD END),0) AS PreviousYear3Turnover
,coalesce(SUM(CASE WHEN [YEAR] = YEAR(@ToMonth)-3 THEN Commission_EUR_YTD END),0) AS PreviousYear3Commission
,coalesce(SUM(CASE WHEN [YEAR] = YEAR(@ToMonth)-3 THEN Allocations_EUR_YTD END),0) AS PreviousYear3Allocations
FROM dbo.SummaryFactTradeAllocations
WHERE
[YEAR] <= YEAR(@ToMonth)
AND AssetClassCode = (CASE WHEN @TradeDataType = 'ALL' THEN AssetClassCode ELSE @TradeDataType END)
AND [Broker] = (CASE WHEN @Broker = 'ALL' THEN [Broker]ELSE @Broker END)
AND InstrumentType = (CASE WHEN @InstrumentType = 'ALL' THEN InstrumentType ELSE @InstrumentType END)
AND Trader_Region = (CASE WHEN @TraderRegion = 'ALL' THEN Trader_Region ELSE @TraderRegion END)
GROUP BY
DataSourceName
,CorporateRegion
,Country
,[Broker]
, ParentBrokerCode
,Trader_Number
, Trader_Region
, UPI
,Portfolio
,Boutique
,AssetClassCode
,AssetClassName
,InstrumentType
,TransactionType
ORDER BY
DataSourceName
,CorporateRegion
,Country
,[Broker]
, ParentBrokerCode
,Trader_Number
, Trader_Region
, UPI
,Portfolio
,Boutique
,AssetClassCode
,AssetClassName
,InstrumentType
,TransactionType
RETURN
With all the years data it runs in about 3 seconds.:-D
Thanks for all the help and ideas and apologies for the confusion in my messages. Now I am off to have a relaxing weekend.
Viewing 12 posts - 31 through 41 (of 41 total)
You must be logged in to reply to this topic. Login to reply