Slow Running Stored Proceudre

Question

Post reply

Slow Running Stored Proceudre

Viewing 15 posts - 16 through 30 (of 41 total)

You must be logged in to reply to this topic. Login to reply

eseosaoregie SSCertifiable Points: 6889 More actions · Answer 1

I will dsend the data first thing tomorrow morning. I have just left the office.

WayneS SSC Guru Points: 95405 More actions · Answer 2

1. Add an index to the #temp table for the columns that you are grouping by.

I see a lot of MONTH(CONVERT(SMALLDATEDIME, CONVERT(CHAR(8), TradeDateKey))) calls. It looks like TradeDateKey is an INT. So, I'd suggest:

2. Changing TradeDateKey to a smalldatetime data type.

3. Since this is a data warehouse, perhaps a date fact table that breaks a date up into year, month and day parts may help your query out.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

Lynn Pettis SSC Guru Points: 442447 More actions · Answer 3

TradeDateKey is defined as an integer, what and how is stored in this column?

Lynn Pettis SSC Guru Points: 442447 More actions · Answer 4

Oh, two things. Your code for your tables only works if people running it have the same databases and configurations as you do. Wouldn't run as is on my system due to missing database(s) and filegroup(s).

Second, this is not a trivial issue to resolve. Although some of us my be willing to work this, you may be better off hiring a contractor to rework your procedure. After taking a cursory look at it I think it could be rewritten to work more effeciently, but it isn't a simple task. There is actually a lot going on in the code.

Lynn Pettis SSC Guru Points: 442447 More actions · Answer 5

Am I missing something in the code of the main stored procedure? There is an IF ELSE determining what data is being returned, but both segments of code after the IF @ReportView = 'Broker' and the ELSE look the same to me.

LutzM SSC Guru Points: 107049 More actions · Answer 6

After cleaning up the schema name as well as filegroup reference all I could figure out is an obviously not exsiting column PortfolioKey in table DimPortfolio (called by [sp_TradeDetail]).

Gave up right there.

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

eseosaoregie SSCertifiable Points: 6889 More actions · Answer 7

Apologies for the late response. i had a delay on my train home. I will try and fill in the blanks.

With regard to the TradeDateKey it is an INT. user calls these stored procedures from a macro in excel. users enter dates of the yyyymmdd. However for this particular provision they enter YYYYMM. That is why I do the conversion to get the last day of each month to capture all data in a given period.

With regards to the portfolio key i hav actually given the ddl for the stg_porfolio dimension and nod the actual dimension. I will amend that once i get into the office tomorrow.

The if else statement determines the type of aggregation. If it = Broker the data returned is grouped by broker. if it <> 'broker' it is grouped by portfolio and boutique.

Regarding getting a consultant that is not feasible at the moment as there is no budget.

Lynn Pettis SSC Guru Points: 442447 More actions · Answer 8

eseosaoregie (12/5/2011)
Apologies for the late response. i had a delay on my train home. I will try and fill in the blanks.
With regard to the TradeDateKey it is an INT. user calls these stored procedures from a macro in excel. users enter dates of the yyyymmdd. However for this particular provision they enter YYYYMM. That is why I do the conversion to get the last day of each month to capture all data in a given period.
With regards to the portfolio key i hav actually given the ddl for the stg_porfolio dimension and nod the actual dimension. I will amend that once i get into the office tomorrow.
The if else statement determines the type of aggregation. If it = Broker the data returned is grouped by broker. if it <> 'broker' it is grouped by portfolio and boutique.
Regarding getting a consultant that is not feasible at the moment as there is no budget.

If you take a close look at the posted code, all the group bys in the main stored procedure group by the following first four columns:

DataSourceName,

CorporateRegion,

Country,

Broker,

eseosaoregie SSCertifiable Points: 6889 More actions · Answer 9

indeed that is right. I cannot do anything from home but i will chase this up tomorrow. I have a feeling i have posted the wrong version of the stored proc.

Apologies for the confusion

eseosaoregie SSCertifiable Points: 6889 More actions · Answer 10

Morning,

these are the correct scripts for my dimension tables

CREATE TABLE [dbo].[DimPortfolio](

[PortfolioKey] [int] IDENTITY(1,1) NOT NULL,

[UPI] [varchar](20) NOT NULL,

[OfficialName] [varchar](120) NOT NULL,

[ISIN] [char](12) NOT NULL,

[MF_Code] [varchar](20) NOT NULL,

[SC_Code] [varchar](20) NOT NULL,

[BBH_Code] [varchar](20) NOT NULL,

[BNP_Code] [varchar](20) NOT NULL,

[DEX_Code] [varchar](20) NOT NULL,

[CR_Code] [varchar](20) NOT NULL,

[FK_Code] [varchar](50) NOT NULL,

[FKFX_Code] [varchar](50) NOT NULL,

[FKPF_Code] [varchar](50) NOT NULL,

[FKCS_Code] [varchar](50) NOT NULL,

[PA_Code] [varchar](20) NOT NULL,

[PME_Code] [varchar](20) NOT NULL,

[UBS_Code] [varchar](20) NOT NULL,

[Umbrella] [varchar](120) NOT NULL,

[UmbrellaType] [varchar](120) NOT NULL,

[PortfolioType] [varchar](15) NOT NULL,

[PortfolioSubType] [varchar](30) NOT NULL,

[ProprietaryFlag] [char](1) NOT NULL,

[Domicile] [char](3) NOT NULL,

[PortfolioCurrency] [char](3) NOT NULL,

[InceptionDate] [int] NOT NULL,

[LiquidationDate] [int] NOT NULL,

[LiquidationReason] [varchar](120) NOT NULL,

[MultiTeamFlag] [char](1) NOT NULL,

[SecuritiesLendingFlag] [char](1) NOT NULL,

[LMS_Flag] [char](1) NOT NULL,

[SustainableFlag] [char](1) NOT NULL,

[PoolingFlag] [char](1) NOT NULL,

[FundOfFundFlag] [char](1) NOT NULL,

[ActivelyManagedFlag] [char](1) NOT NULL,

[OfficialBenchmark] [varchar](120) NOT NULL,

[ReferenceBenchmark] [varchar](120) NOT NULL,

[ReferenceShareClass] [varchar](20) NOT NULL,

[Notes] [varchar](500) NOT NULL,

[LoadDate] [datetime] NOT NULL,

[BoutiqueA] [varchar](120) NULL,

[InvestmentTeamA] [varchar](120) NULL,

[AssetClassificationA] [varchar](50) NOT NULL,

[StrategyA] [varchar](120) NOT NULL,

[GeographicFocusA] [varchar](120) NOT NULL,

[SectorA] [varchar](120) NOT NULL,

[TargetAlphaA] [varchar](60) NOT NULL,

[TargetTrackingErrorA] [varchar](60) NOT NULL,

[TargetInfoRatioA] [varchar](60) NOT NULL,

[ReportedBenchmarkA] [varchar](250) NOT NULL,

[ReportedManagerNamesA] [varchar](250) NOT NULL,

[ManagerCorporateKey1A] [varchar](6) NOT NULL,

[ManagerCorporateKey2A] [varchar](6) NOT NULL,

[ManagerCorporateKey3A] [varchar](6) NOT NULL,

[ManagerCorporateKey4A] [varchar](6) NOT NULL,

[ManagerCorporateKey5A] [varchar](6) NOT NULL,

[ReportedPortfolioSpecialistNameA] [varchar](250) NOT NULL,

[PortfolioSpecialistCorporateKey1A] [varchar](6) NOT NULL,

[PortfolioSpecialistCorporateKey2A] [varchar](6) NOT NULL,

[PortfolioSpecialistCorporateKey3A] [varchar](6) NOT NULL,

[PortfolioSpecialistCorporateKey4A] [varchar](6) NOT NULL,

[PortfolioSpecialistCorporateKey5A] [varchar](6) NOT NULL,

[AllocationPercentA] [int] NOT NULL,

[ManagedFlagA] [char](1) NOT NULL,

[TeamManagedFlagA] [char](1) NOT NULL,

[AdvisoryFlagA] [char](1) NOT NULL,

[CommentA] [varchar](500) NULL,

[BoutiqueB] [varchar](120) NULL,

[InvestmentTeamB] [varchar](120) NULL,

[AssetClassificationB] [varchar](50) NOT NULL,

[StrategyB] [varchar](120) NOT NULL,

[GeographicFocusB] [varchar](120) NOT NULL,

[SectorB] [varchar](120) NOT NULL,

[TargetAlphaB] [varchar](60) NOT NULL,

[TargetTrackingErrorB] [varchar](60) NOT NULL,

[TargetInfoRatioB] [varchar](60) NOT NULL,

[ReportedBenchmarkB] [varchar](250) NOT NULL,

[ReportedManagerNamesB] [varchar](250) NOT NULL,

[ManagerCorporateKey1B] [varchar](6) NOT NULL,

[ManagerCorporateKey2B] [varchar](6) NOT NULL,

[ManagerCorporateKey3B] [varchar](6) NOT NULL,

[ManagerCorporateKey4B] [varchar](6) NOT NULL,

[ManagerCorporateKey5B] [varchar](6) NOT NULL,

[ReportedPortfolioSpecialistNameB] [varchar](250) NOT NULL,

[PortfolioSpecialistCorporateKey1B] [varchar](6) NOT NULL,

[PortfolioSpecialistCorporateKey2B] [varchar](6) NOT NULL,

[PortfolioSpecialistCorporateKey3B] [varchar](6) NOT NULL,

[PortfolioSpecialistCorporateKey4B] [varchar](6) NOT NULL,

[PortfolioSpecialistCorporateKey5B] [varchar](6) NOT NULL,

[AllocationPercentB] [int] NOT NULL,

[ManagedFlagB] [char](1) NOT NULL,

[TeamManagedFlagB] [char](1) NOT NULL,

[AdvisoryFlagB] [char](1) NOT NULL,

[CommentB] [varchar](500) NULL,

[BoutiqueC] [varchar](120) NULL,

[InvestmentTeamC] [varchar](120) NULL,

[AssetClassificationC] [varchar](50) NOT NULL,

[StrategyC] [varchar](120) NOT NULL,

[GeographicFocusC] [varchar](120) NOT NULL,

[SectorC] [varchar](120) NOT NULL,

[TargetAlphaC] [varchar](60) NOT NULL,

[TargetTrackingErrorC] [varchar](60) NOT NULL,

[TargetInfoRatioC] [varchar](60) NOT NULL,

[ReportedBenchmarkC] [varchar](250) NOT NULL,

[ReportedManagerNamesC] [varchar](250) NOT NULL,

[ManagerCorporateKey1C] [varchar](6) NOT NULL,

[ManagerCorporateKey2C] [varchar](6) NOT NULL,

[ManagerCorporateKey3C] [varchar](6) NOT NULL,

[ManagerCorporateKey4C] [varchar](6) NOT NULL,

[ManagerCorporateKey5C] [varchar](6) NOT NULL,

[ReportedPortfolioSpecialistNameC] [varchar](250) NOT NULL,

[PortfolioSpecialistCorporateKey1C] [varchar](6) NOT NULL,

[PortfolioSpecialistCorporateKey2C] [varchar](6) NOT NULL,

[PortfolioSpecialistCorporateKey3C] [varchar](6) NOT NULL,

[PortfolioSpecialistCorporateKey4C] [varchar](6) NOT NULL,

[PortfolioSpecialistCorporateKey5C] [varchar](6) NOT NULL,

[AllocationPercentC] [int] NOT NULL,

[ManagedFlagC] [char](1) NOT NULL,

[TeamManagedFlagC] [char](1) NOT NULL,

[AdvisoryFlagC] [char](1) NOT NULL,

[CommentC] [varchar](500) NULL,

[CorporateRegion] [char](3) NOT NULL,

[InferredMember] [bit] NOT NULL,

[CurrentFlag] [char](1) NOT NULL,

[EffectiveFromDate] [datetime] NOT NULL,

[EffectiveToDate] [datetime] NULL,

[AddDateKey] [int] NULL,

[LastChangeDateKey] [int] NULL,

[LastChangeReason] [varchar](200) NULL,

[DataSourceKey] [int] NOT NULL,

[AuditKey] [int] NOT NULL,

CONSTRAINT [PK_dbo_DimPortfolio] PRIMARY KEY CLUSTERED

(

[PortfolioKey] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [Dimensions]

) ON [Dimensions]

GO

SET ANSI_PADDING OFF

GO

ALTER TABLE [dbo].[DimPortfolio] ADD CONSTRAINT [DF_DimPortfolio_X_InferredMember] DEFAULT ((0)) FOR [InferredMember]

GO

ALTER TABLE [dbo].[DimPortfolio] ADD CONSTRAINT [DF_DimPortfolio_X_CurrentFlag] DEFAULT ('Y') FOR [CurrentFlag]

GO

ALTER TABLE [dbo].[DimPortfolio] ADD CONSTRAINT [DF_DimPortfolio_X_AddDateKey] DEFAULT (CONVERT([int],getdate(),(112))) FOR [AddDateKey]

GO

SELECT [OrganizationKey]

,[OrganizationCode]

,[OrganizationName]

,[CorporateRegion]

,[Domicile]

,[UCI]

,[BIC]

,[Notes]

,[DataSourceKey]

,[InferredMember]

,[CurrentFlag]

,[EffectiveFromDate]

,[EffectiveToDate]

,[AddDateKey]

,[LastChangeDateKey]

,[LastChangeReason]

,[AuditKey]

FROM [GEAR].[dbo].[DimOrganization]

GO

SELECT [InstrumentTypeKey]

,[AssetClassCode]

,[AssetClassName]

,[AC_Description]

,[InstrumentType]

,[CFI_Code]

,[CFI_Name]

,[CFI_Description]

,[DerivativeFlag]

,[AlternativeFlag]

,[DataSourceKey]

,[InferredMember]

,[CurrentFlag]

,[EffectiveFromDate]

,[EffectiveToDate]

,[AddDateKey]

,[LastChangeDateKey]

,[LastChangeReason]

,[AuditKey]

FROM [GEAR].[dbo].[DimInstrumentType]

GO

SELECT [InstrumentKey]

,[UII]

,[Sedol]

,[ISIN]

,[Cusip]

,[Ticker]

,[InstrumentName]

,[IssuerCode]

,[IssuerName]

,[Industry]

,[IssueCountry]

,[IssueDate]

,[AnnouncementDate]

,[BloombergMarketSector]

,[BloombergSecurityType]

,[BloombergSecuritySubType]

,[BloombergCollateralType]

,[InstrumentType]

,[InstrumentCategory]

,[AssetClassCode]

,[AssetClassName]

,[AC_Description]

,[CFI_Code]

,[CFI_Name]

,[CFI_Description]

,[DerivativeFlag]

,[AlternativeFlag]

,[OECD_MemberFlag]

,[EmergingMarketFlag]

,[DevelopedMarketFlag]

,[CreditGrade]

,[CreditRatingMoody]

,[CreditRatingSP]

,[CreditRatingFitch]

,[DataSourceKey]

,[InferredMember]

,[CurrentFlag]

,[EffectiveFromDate]

,[EffectiveToDate]

,[AddDateKey]

,[LastChangeDateKey]

,[LastChangeReason]

,[AuditKey]

FROM [GEAR].[dbo].[DimInstrument]

GO

SELECT [EmployeeKey]

,[CorporateRegion]

,[Domicile]

,[OrganizationName]

,[DepartmentName]

,[FinanceCostCenterCode]

,[FinanceCostCenterName]

,[MDVS_CostCenterCode]

,[MDVS_CostCenterName]

,[EmployeeNumber]

,[EmployeeCorporateKey]

,[EmployeeName]

,[Gender]

,[MangerCorporateKey]

,[ManagerName]

,[CIO_CorporateKey]

,[CIO_Name]

,[JobFamilyDescription]

,[JobDescription]

,[HireDateKey]

,[ExperienceDateKey]

,[BirthDateKey]

,[ExitDateKey]

,[JobGrade]

,[JobCoverage]

,[Education]

,[Biography]

,[CFA_Flag]

,[MBA_Flag]

,[PHD_Flag]

,[CAIA_Flag]

,[Notes]

,[DataSourceKey]

,[InferredMember]

,[CurrentFlag]

,[EffectiveFromDate]

,[EffectiveToDate]

,[AddDateKey]

,[LastChangeDateKey]

,[LastChangeReason]

,[AuditKey]

FROM [GEAR].[dbo].[DimEmployee]

GO

THE relevant stored procedure is:

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 int

,TradeOrderNumber varchar(50)

,TradeTransactionNumber varchar(50)

,TransactionType varchar(20)

,ISIN char(12)

,IssueCountry char(3)

,UPI varchar(20)

,Portfolio varchar(120)

,Boutique varchar(120)

,Strategy varchar(120)

,Broker varchar(120)

,ParentBrokerCode varchar(120)

,AssetClassCode char(1)

,AssetClassName varchar(120)

,InstrumentType varchar(120)

,PortfolioManager_Number varchar(18)

,PortfolioManager_Name varchar(120)

,Trader_Number varchar(18)

,Trader_Name varchar(120)

,Trader_Region varchar(100)

,DebtCategory varchar(120)

,InstrumentAttributes varchar(120)

,OECD_MemberFlag char(1)

,CreditGrade varchar(120)

,GrossAmount_EUR numeric(18,6)

,Commission_EUR numeric(18,6)

,GrossAmount_USD numeric(18,6)

,Commission_USD numeric(18,6)

,Quantity numeric(18,6)

)

INSERT INTO @TradeDetail

EXEC [prv].[sp_TradeDetail] '20100101', @ToMonth,'ALL','ALL','ALL',@Broker,@TradeDataType,@InstrumentType

-- ====================================================================================

-- (4) Prepare Cross Tab dependent on the Reportview variable i.e Portfolio or Broker

-- ====================================================================================

IF @ReportView = 'Broker'

SELECT DataSourceName,

CorporateRegion,

Country,

Broker,

ParentBrokerCode AS BrokerID,

Trader_Number AS TraderID,

Trader_Region AS TraderRegion,

AssetClassCode,

AssetClassName,

InstrumentType,

TransactionType,

coalesce(SUM(PreviousYear1_GrossAmount_EUR_Dec),0) AS PreviousYear1_GrossAmount_EUR_Dec,

coalesce(MAX(GrossAmount_EUR_Jan),0) AS GrossAmount_EUR_Jan,

coalesce(MAX(GrossAmount_EUR_Feb),0)AS GrossAmount_EUR_Feb,

coalesce(MAX(GrossAmount_EUR_Mar),0) AS GrossAmount_EUR_Mar,

coalesce(MAX(GrossAmount_EUR_Apr),0) AS GrossAmount_EUR_Apr,

coalesce(MAX(GrossAmount_EUR_May),0) AS GrossAmount_EUR_May,

coalesce(MAX(GrossAmount_EUR_Jun),0) AS GrossAmount_EUR_Jun,

coalesce(MAX(GrossAmount_EUR_Jul),0) AS GrossAmount_EUR_Jul,

coalesce(MAX(GrossAmount_EUR_Aug),0) AS GrossAmount_EUR_Aug,

coalesce(MAX(GrossAmount_EUR_Sep),0) AS GrossAmount_EUR_Sep,

coalesce(MAX(GrossAmount_EUR_Oct),0) AS GrossAmount_EUR_Oct,

coalesce(MAX(GrossAmount_EUR_Nov),0) AS GrossAmount_EUR_Nov,

coalesce(MAX(GrossAmount_EUR_Dec),0) AS GrossAmount_EUR_Dec,

coalesce(SUM(GrossAmount_EUR_YTD),0) AS GrossAmount_EUR_YTD,

coalesce(SUM(GrossAmount_EUR_YTD),0)/MONTH(@ToMonth)* 12 AS GrossAmount_EUR_ANN,

coalesce(SUM(PreviousYear1_Commission_EUR_Dec),0) AS PreviousYear1_Commission_EUR_Dec,

coalesce(MAX(Commission_EUR_Jan),0) AS Commission_EUR_Jan,

coalesce(MAX(Commission_EUR_Feb),0) AS Commission_EUR_Feb,

coalesce(MAX(Commission_EUR_Mar),0) AS Commission_EUR_Mar,

coalesce(MAX(Commission_EUR_Apr),0) AS Commission_EUR_Apr,

coalesce(MAX(Commission_EUR_May),0) AS Commission_EUR_May,

coalesce(MAX(Commission_EUR_Jun),0) AS Commission_EUR_Jun,

coalesce(MAX(Commission_EUR_Jul),0) AS Commission_EUR_Jul,

coalesce(MAX(Commission_EUR_Aug),0) AS Commission_EUR_Aug,

coalesce(MAX(Commission_EUR_Sep),0) AS Commission_EUR_Sep,

coalesce(MAX(Commission_EUR_Oct),0) AS Commission_EUR_Oct,

coalesce(MAX(Commission_EUR_Nov),0) AS Commission_EUR_Nov,

coalesce(MAX(Commission_EUR_Dec),0) AS Commission_EUR_Dec,

coalesce(SUM(Commission_EUR_YTD),0) AS Commission_EUR_YTD,

coalesce(SUM(Commission_EUR_YTD),0)/MONTH(@ToMonth)* 12 AS Commission_EUR_ANN,

coalesce(SUM(OrdersJan),0) AS OrdersJan,

coalesce(SUM(OrdersFeb),0) AS OrdersFeb,

coalesce(SUM(OrdersMar),0) AS OrdersMar,

coalesce(SUM(OrdersApr),0) AS OrdersApr,

coalesce(SUM(OrdersMay),0) AS OrdersMay,

coalesce(SUM(OrdersJun),0) AS OrdersJun,

coalesce(SUM(OrdersJul),0) AS OrdersJul,

coalesce(SUM(OrdersAug),0) AS OrdersAug,

coalesce(SUM(OrdersSep),0) AS OrdersSep,

coalesce(SUM(OrdersOct),0) AS OrdersOct,

coalesce(SUM(OrdersNov),0) AS OrdersNov,

coalesce(SUM(OrdersDec),0) AS OrdersDec,

coalesce(SUM(Orders_EUR_YTD),0) AS Orders_EUR_YTD,

SUM(Orders_EUR_YTD /MONTH(@ToMonth)* 12) AS Orders_EUR_ANN,

coalesce(MAX(PreviousYear1Turnover),0) AS PreviousYear1Turnover,

coalesce(SUM(PreviousYear1Commission),0) AS PreviousYear1Commission,

coalesce(SUM(PreviousYear1Orders),0) AS PreviousYear1Orders,

coalesce(SUM(PreviousYear2Turnover),0) AS PreviousYear2Turnover,

coalesce(SUM(PreviousYear2Commission),0) AS PreviousYear2Commission,

coalesce(SUM(PreviousYear2Orders),0) AS PreviousYear2Orders,

coalesce(SUM(PreviousYear3Turnover),0) AS PreviousYear3Turnover,

coalesce(SUM(PreviousYear3Commission),0) AS PreviousYear3Commission,

coalesce(SUM(PreviousYear3Orders),0) AS PreviousYear3Orders

FROM

(SELECTDataSourceName,

CorporateRegion,

Country,

Broker,

ParentBrokerCode,

Trader_Number,

Trader_Region,

AssetClassCode,

AssetClassName,

InstrumentType,

TransactionType,

SUM(CASE WHEN MONTH(CONVERT(smalldatetime, CONVERT(char(8), TradeDateKey)) ) = 1 AND YEAR(CONVERT(smalldatetime, CONVERT(char(8), TradeDateKey)) ) = YEAR(@ToMonth)THEN GrossAmount_EUR END) AS GrossAmount_EUR_Jan,

SUM(CASE WHEN MONTH(CONVERT(smalldatetime, CONVERT(char(8), TradeDateKey)) ) = 2 AND YEAR(CONVERT(smalldatetime, CONVERT(char(8), TradeDateKey)) ) = YEAR(@ToMonth)THEN GrossAmount_EUR END) AS GrossAmount_EUR_Feb,

SUM(CASE WHEN MONTH(CONVERT(smalldatetime, CONVERT(char(8), TradeDateKey)) ) = 3 AND YEAR(CONVERT(smalldatetime, CONVERT(char(8), TradeDateKey)) ) = YEAR(@ToMonth)THEN GrossAmount_EUR END) AS GrossAmount_EUR_Mar,

SUM(CASE WHEN MONTH(CONVERT(smalldatetime, CONVERT(char(8), TradeDateKey)) ) = 4 AND YEAR(CONVERT(smalldatetime, CONVERT(char(8), TradeDateKey)) ) = YEAR(@ToMonth)THEN GrossAmount_EUR END) AS GrossAmount_EUR_Apr,

SUM(CASE WHEN MONTH(CONVERT(smalldatetime, CONVERT(char(8), TradeDateKey)) ) = 5 AND YEAR(CONVERT(smalldatetime, CONVERT(char(8), TradeDateKey)) ) = YEAR(@ToMonth)THEN GrossAmount_EUR END) AS GrossAmount_EUR_May,

SUM(CASE WHEN MONTH(CONVERT(smalldatetime, CONVERT(char(8), TradeDateKey)) ) = 6 AND YEAR(CONVERT(smalldatetime, CONVERT(char(8), TradeDateKey)) ) = YEAR(@ToMonth)THEN GrossAmount_EUR END) AS GrossAmount_EUR_Jun,

SUM(CASE WHEN MONTH(CONVERT(smalldatetime, CONVERT(char(8), TradeDateKey)) ) = 7 AND YEAR(CONVERT(smalldatetime, CONVERT(char(8), TradeDateKey)) ) = YEAR(@ToMonth)THEN GrossAmount_EUR END) AS GrossAmount_EUR_Jul,

SUM(CASE WHEN MONTH(CONVERT(smalldatetime, CONVERT(char(8), TradeDateKey)) ) = 8 AND YEAR(CONVERT(smalldatetime, CONVERT(char(8), TradeDateKey)) ) = YEAR(@ToMonth)THEN GrossAmount_EUR END) AS GrossAmount_EUR_Aug,

SUM(CASE WHEN MONTH(CONVERT(smalldatetime, CONVERT(char(8), TradeDateKey)) ) = 9 AND YEAR(CONVERT(smalldatetime, CONVERT(char(8), TradeDateKey)) ) = YEAR(@ToMonth)THEN GrossAmount_EUR END) AS GrossAmount_EUR_Sep,

SUM(CASE WHEN MONTH(CONVERT(smalldatetime, CONVERT(char(8), TradeDateKey)) ) = 10 AND YEAR(CONVERT(smalldatetime, CONVERT(char(8), TradeDateKey)) ) = YEAR(@ToMonth)THEN GrossAmount_EUR END) AS GrossAmount_EUR_Oct,

SUM(CASE WHEN MONTH(CONVERT(smalldatetime, CONVERT(char(8), TradeDateKey)) ) = 11 AND YEAR(CONVERT(smalldatetime, CONVERT(char(8), TradeDateKey)) ) = YEAR(@ToMonth)THEN GrossAmount_EUR END) AS GrossAmount_EUR_Nov,

SUM(CASE WHEN MONTH(CONVERT(smalldatetime, CONVERT(char(8), TradeDateKey)) ) = 12 AND YEAR(CONVERT(smalldatetime, CONVERT(char(8), TradeDateKey)) ) = YEAR(@ToMonth)THEN GrossAmount_EUR END) AS GrossAmount_EUR_Dec,

SUM(CASE WHEN CONVERT(smalldatetime, CONVERT(char(8), TradeDateKey)) BETWEEN DATEADD(DAY,0,DATEADD(YEAR,DATEDIFF(YEAR,0,@ToMonth),0)) AND @ToMonth THEN GrossAmount_EUR END)AS GrossAmount_EUR_YTD,

SUM(CASE WHEN CONVERT(smalldatetime, CONVERT(char(8), TradeDateKey))

BETWEEN DATEADD(MONTH,-1,DATEADD(YEAR,DATEDIFF(YEAR,0,@ToMonth),0)) AND DATEADD(DAY,-1,DATEADD(YEAR,DATEDIFF(YEAR,0,@ToMonth),0)) THEN GrossAmount_EUR END) PreviousYear1_GrossAmount_EUR_Dec,

SUM(CASE WHEN MONTH(CONVERT(smalldatetime, CONVERT(char(8), TradeDateKey)) ) = 1 AND YEAR(CONVERT(smalldatetime, CONVERT(char(8), TradeDateKey)) ) = YEAR(@ToMonth)THEN Commission_EUR END) AS Commission_EUR_Jan,

SUM(CASE WHEN MONTH(CONVERT(smalldatetime, CONVERT(char(8), TradeDateKey)) ) = 2 AND YEAR(CONVERT(smalldatetime, CONVERT(char(8), TradeDateKey)) ) = YEAR(@ToMonth)THEN Commission_EUR END) AS Commission_EUR_Feb,

SUM(CASE WHEN MONTH(CONVERT(smalldatetime, CONVERT(char(8), TradeDateKey)) ) = 3 AND YEAR(CONVERT(smalldatetime, CONVERT(char(8), TradeDateKey)) ) = YEAR(@ToMonth)THEN Commission_EUR END) AS Commission_EUR_Mar,

SUM(CASE WHEN MONTH(CONVERT(smalldatetime, CONVERT(char(8), TradeDateKey)) ) = 4 AND YEAR(CONVERT(smalldatetime, CONVERT(char(8), TradeDateKey)) ) = YEAR(@ToMonth)THEN Commission_EUR END) AS Commission_EUR_Apr,

SUM(CASE WHEN MONTH(CONVERT(smalldatetime, CONVERT(char(8), TradeDateKey)) ) = 5 AND YEAR(CONVERT(smalldatetime, CONVERT(char(8), TradeDateKey)) ) = YEAR(@ToMonth)THEN Commission_EUR END) AS Commission_EUR_May,

SUM(CASE WHEN MONTH(CONVERT(smalldatetime, CONVERT(char(8), TradeDateKey)) ) = 6 AND YEAR(CONVERT(smalldatetime, CONVERT(char(8), TradeDateKey)) ) = YEAR(@ToMonth)THEN Commission_EUR END) AS Commission_EUR_Jun,

SUM(CASE WHEN MONTH(CONVERT(smalldatetime, CONVERT(char(8), TradeDateKey)) ) = 7 AND YEAR(CONVERT(smalldatetime, CONVERT(char(8), TradeDateKey)) ) = YEAR(@ToMonth)THEN Commission_EUR END) AS Commission_EUR_Jul,

SUM(CASE WHEN MONTH(CONVERT(smalldatetime, CONVERT(char(8), TradeDateKey)) ) = 8 AND YEAR(CONVERT(smalldatetime, CONVERT(char(8), TradeDateKey)) ) = YEAR(@ToMonth)THEN Commission_EUR END) AS Commission_EUR_Aug,

SUM(CASE WHEN MONTH(CONVERT(smalldatetime, CONVERT(char(8), TradeDateKey)) ) = 9 AND YEAR(CONVERT(smalldatetime, CONVERT(char(8), TradeDateKey)) ) = YEAR(@ToMonth)THEN Commission_EUR END) AS Commission_EUR_Sep,

SUM(CASE WHEN MONTH(CONVERT(smalldatetime, CONVERT(char(8), TradeDateKey)) ) = 10 AND YEAR(CONVERT(smalldatetime, CONVERT(char(8), TradeDateKey)) ) = YEAR(@ToMonth)THEN Commission_EUR END) AS Commission_EUR_Oct,

SUM(CASE WHEN MONTH(CONVERT(smalldatetime, CONVERT(char(8), TradeDateKey)) ) = 11 AND YEAR(CONVERT(smalldatetime, CONVERT(char(8), TradeDateKey)) ) = YEAR(@ToMonth)THEN Commission_EUR END) AS Commission_EUR_Nov,

SUM(CASE WHEN MONTH(CONVERT(smalldatetime, CONVERT(char(8), TradeDateKey)) ) = 12 THEN Commission_EUR END) AS Commission_EUR_Dec,

SUM(CASE WHEN CONVERT(smalldatetime, CONVERT(char(8), TradeDateKey)) BETWEEN DATEADD(DAY,0,DATEADD(YEAR,DATEDIFF(YEAR,0,@ToMonth),0)) AND @ToMonth THEN Commission_EUR END)AS Commission_EUR_YTD,

SUM(CASE WHEN CONVERT(smalldatetime, CONVERT(char(8), TradeDateKey))

BETWEEN DATEADD(MONTH,-1,DATEADD(YEAR,DATEDIFF(YEAR,0,@ToMonth),0)) AND DATEADD(DAY,-1,DATEADD(YEAR,DATEDIFF(YEAR,0,@ToMonth),0)) THEN Commission_EUR END) PreviousYear1_Commission_EUR_Dec,

COUNT(DISTINCT CASE WHEN MONTH(CONVERT(smalldatetime, CONVERT(char(8), TradeDateKey)) ) = 1 AND YEAR(CONVERT(smalldatetime, CONVERT(char(8), TradeDateKey)) ) = YEAR(@ToMonth)THEN TradeOrderNumber END) AS OrdersJan,

COUNT(DISTINCT CASE WHEN MONTH(CONVERT(smalldatetime, CONVERT(char(8), TradeDateKey)) ) = 2 AND YEAR(CONVERT(smalldatetime, CONVERT(char(8), TradeDateKey)) ) = YEAR(@ToMonth)THEN TradeOrderNumber END) AS OrdersFeb,

COUNT(DISTINCT CASE WHEN MONTH(CONVERT(smalldatetime, CONVERT(char(8), TradeDateKey)) ) = 3 AND YEAR(CONVERT(smalldatetime, CONVERT(char(8), TradeDateKey)) ) = YEAR(@ToMonth)THEN TradeOrderNumber END) AS OrdersMar,

COUNT(DISTINCT CASE WHEN MONTH(CONVERT(smalldatetime, CONVERT(char(8), TradeDateKey)) ) = 4 AND YEAR(CONVERT(smalldatetime, CONVERT(char(8), TradeDateKey)) ) = YEAR(@ToMonth)THEN TradeOrderNumber END) AS OrdersApr,

COUNT(DISTINCT CASE WHEN MONTH(CONVERT(smalldatetime, CONVERT(char(8), TradeDateKey)) ) = 5 AND YEAR(CONVERT(smalldatetime, CONVERT(char(8), TradeDateKey)) ) = YEAR(@ToMonth)THEN TradeOrderNumber END) AS OrdersMay,

COUNT(DISTINCT CASE WHEN MONTH(CONVERT(smalldatetime, CONVERT(char(8), TradeDateKey)) ) = 6 AND YEAR(CONVERT(smalldatetime, CONVERT(char(8), TradeDateKey)) ) = YEAR(@ToMonth)THEN TradeOrderNumber END) AS OrdersJun,

COUNT(DISTINCT CASE WHEN MONTH(CONVERT(smalldatetime, CONVERT(char(8), TradeDateKey)) ) = 7 AND YEAR(CONVERT(smalldatetime, CONVERT(char(8), TradeDateKey)) ) = YEAR(@ToMonth)THEN TradeOrderNumber END) AS OrdersJul,

COUNT(DISTINCT CASE WHEN MONTH(CONVERT(smalldatetime, CONVERT(char(8), TradeDateKey)) ) = 8 AND YEAR(CONVERT(smalldatetime, CONVERT(char(8), TradeDateKey)) ) = YEAR(@ToMonth)THEN TradeOrderNumber END) AS OrdersAug,

COUNT(DISTINCT CASE WHEN MONTH(CONVERT(smalldatetime, CONVERT(char(8), TradeDateKey)) ) = 9 AND YEAR(CONVERT(smalldatetime, CONVERT(char(8), TradeDateKey)) ) = YEAR(@ToMonth)THEN TradeOrderNumber END) AS OrdersSep,

COUNT(DISTINCT CASE WHEN MONTH(CONVERT(smalldatetime, CONVERT(char(8), TradeDateKey)) ) = 10 AND YEAR(CONVERT(smalldatetime, CONVERT(char(8), TradeDateKey)) ) = YEAR(@ToMonth)THEN TradeOrderNumber END) AS OrdersOct,

COUNT(DISTINCT CASE WHEN MONTH(CONVERT(smalldatetime, CONVERT(char(8), TradeDateKey)) ) = 11 AND YEAR(CONVERT(smalldatetime, CONVERT(char(8), TradeDateKey)) ) = YEAR(@ToMonth)THEN TradeOrderNumber END) AS OrdersNov,

COUNT(DISTINCT CASE WHEN MONTH(CONVERT(smalldatetime, CONVERT(char(8), TradeDateKey)) ) = 12 AND YEAR(CONVERT(smalldatetime, CONVERT(char(8), TradeDateKey)) ) = YEAR(@ToMonth)THEN TradeOrderNumber END) AS OrdersDec,

CAST(COUNT(DISTINCT CASE WHEN CONVERT(smalldatetime, CONVERT(char(8), TradeDateKey)) BETWEEN DATEADD(DAY,0,DATEADD(YEAR,DATEDIFF(YEAR,0,@ToMonth),0)) AND @ToMonth THEN TradeOrderNumber END)AS FLOAT ) AS OrderS_EUR_YTD,

COUNT(DISTINCT CASE WHEN CONVERT(smalldatetime, CONVERT(char(8), TradeDateKey))

BETWEEN DATEADD(MONTH,-1,DATEADD(YEAR,DATEDIFF(YEAR,0,@ToMonth),0)) AND DATEADD(DAY,-1,DATEADD(YEAR,DATEDIFF(YEAR,0,@ToMonth),0)) THEN TradeOrderNumber END) PreviousYear1_Orders_EUR_Dec,

SUM(CASE WHEN MONTH(CONVERT(smalldatetime, CONVERT(char(8), TradeDateKey)) ) >= MONTH(DATEADD(MM,-12,@ToMonth))THEN GrossAmount_EUR END) AS PreviousYear1Turnover,

SUM(CASE WHEN MONTH(CONVERT(smalldatetime, CONVERT(char(8), TradeDateKey)) ) >= MONTH(DATEADD(MM,-12,@ToMonth))THEN Commission_EUR END)AS PreviousYear1Commission,

COUNT(DISTINCT CASE WHEN MONTH(CONVERT(smalldatetime, CONVERT(char(8), TradeDateKey)) ) >= MONTH(DATEADD(MM,-12,@ToMonth))THEN TradeOrderNumber END) AS PreviousYear1Orders,

SUM(CASE WHEN CONVERT(smalldatetime, CONVERT(char(8), TradeDateKey)) BETWEEN DATEADD(MM,-12,@ToMonth) AND DATEADD(MM,-24,@ToMonth) THEN GrossAmount_EUR END) AS PreviousYear2Turnover,

SUM(CASE WHEN CONVERT(smalldatetime, CONVERT(char(8), TradeDateKey)) BETWEEN DATEADD(MM,-12,@ToMonth) AND DATEADD(MM,-24,@ToMonth) THEN Commission_EUR END) AS PreviousYear2Commission,

COUNT(DISTINCT CASE WHEN CONVERT(smalldatetime, CONVERT(char(8), TradeDateKey)) BETWEEN DATEADD(MM,-12,@ToMonth) AND DATEADD(MM,-24,@ToMonth) THEN TradeOrderNumber END) AS PreviousYear2Orders,

SUM(CASE WHEN CONVERT(smalldatetime, CONVERT(char(8), TradeDateKey)) BETWEEN DATEADD(MM,-24,@ToMonth) AND DATEADD(MM,-36,@ToMonth) THEN GrossAmount_EUR END) AS PreviousYear3Turnover,

SUM(CASE WHEN CONVERT(smalldatetime, CONVERT(char(8), TradeDateKey)) BETWEEN DATEADD(MM,-24,@ToMonth) AND DATEADD(MM,-36,@ToMonth) THEN Commission_EUR END) AS PreviousYear3Commission,

COUNT(DISTINCT CASE WHEN CONVERT(smalldatetime, CONVERT(char(8), TradeDateKey)) BETWEEN DATEADD(MM,-24,@ToMonth) AND DATEADD(MM,-36,@ToMonth) THEN TradeOrderNumber END) AS PreviousYear3Orders,

SUM(CASE WHEN CONVERT(smalldatetime, CONVERT(char(8), TradeDateKey)) BETWEEN DATEADD(MM,-36,@ToMonth) AND DATEADD(MM,-48,@ToMonth) THEN GrossAmount_EUR END) AS PreviousYear4Turnover,

SUM(CASE WHEN CONVERT(smalldatetime, CONVERT(char(8), TradeDateKey)) BETWEEN DATEADD(MM,-36,@ToMonth) AND DATEADD(MM,-48,@ToMonth) THEN Commission_EUR END) AS PreviousYear4Commission,

COUNT(DISTINCT CASE WHEN CONVERT(smalldatetime, CONVERT(char(8), TradeDateKey)) BETWEEN DATEADD(MM,-36,@ToMonth) AND DATEADD(MM,-48,@ToMonth) THEN TradeOrderNumber END) AS PreviousYear4Orders

FROM @TradeDetail

GROUP BY DataSourceName,CorporateRegion,Country,Broker, ParentBrokerCode,Trader_Number,Trader_Region,AssetClassCode, AssetClassName,InstrumentType,TransactionType

) rawData

GROUP BY DataSourceName,CorporateRegion,Country,Broker,ParentBrokerCode,Trader_Number,Trader_Region,AssetClassCode,AssetClassName,InstrumentType,TransactionType

ELSE

SELECTDataSourceName,

CorporateRegion,

Country,

Broker,

ParentBrokerCode AS BrokerID,

UPI AS PortfolioID,

Portfolio,

Boutique,