December 5, 2011 at 11:36 am
I will dsend the data first thing tomorrow morning. I have just left the office.
December 5, 2011 at 11:36 am
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
December 5, 2011 at 11:38 am
TradeDateKey is defined as an integer, what and how is stored in this column?
December 5, 2011 at 11:43 am
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.
December 5, 2011 at 12:40 pm
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.
December 5, 2011 at 12:50 pm
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.
December 5, 2011 at 1:14 pm
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.
December 5, 2011 at 1:19 pm
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,
December 5, 2011 at 1:33 pm
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
December 6, 2011 at 1:33 am
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,
Trader_Number AS TraderID,
Trader_Region AS TraderRegion,
AssetClassCode,
AssetClassName,
InstrumentType,
TransactionType,
coalesce(SUM(PreviousYear1_GrossAmount_EUR_Dec),0) AS PreviousYear1_GrossAmount_EUR_Dec,
coalesce(MAX(GrossAmount_EUR_Jan),0) AS GrossAmount_EUR_Jan,
coalesce(MAX(GrossAmount_EUR_Feb),0)AS GrossAmount_EUR_Feb,
coalesce(MAX(GrossAmount_EUR_Mar),0) AS GrossAmount_EUR_Mar,
coalesce(MAX(GrossAmount_EUR_Apr),0) AS GrossAmount_EUR_Apr,
coalesce(MAX(GrossAmount_EUR_May),0) AS GrossAmount_EUR_May,
coalesce(MAX(GrossAmount_EUR_Jun),0) AS GrossAmount_EUR_Jun,
coalesce(MAX(GrossAmount_EUR_Jul),0) AS GrossAmount_EUR_Jul,
coalesce(MAX(GrossAmount_EUR_Aug),0) AS GrossAmount_EUR_Aug,
coalesce(MAX(GrossAmount_EUR_Sep),0) AS GrossAmount_EUR_Sep,
coalesce(MAX(GrossAmount_EUR_Oct),0) AS GrossAmount_EUR_Oct,
coalesce(MAX(GrossAmount_EUR_Nov),0) AS GrossAmount_EUR_Nov,
coalesce(MAX(GrossAmount_EUR_Dec),0) AS GrossAmount_EUR_Dec,
coalesce(SUM(GrossAmount_EUR_YTD),0) AS GrossAmount_EUR_YTD,
coalesce(SUM(GrossAmount_EUR_YTD),0)/MONTH(@ToMonth)* 12 AS GrossAmount_EUR_ANN,
coalesce(SUM(PreviousYear1_Commission_EUR_Dec),0) AS PreviousYear1_Commission_EUR_Dec,
coalesce(MAX(Commission_EUR_Jan),0) AS Commission_EUR_Jan,
coalesce(MAX(Commission_EUR_Feb),0) AS Commission_EUR_Feb,
coalesce(MAX(Commission_EUR_Mar),0) AS Commission_EUR_Mar,
coalesce(MAX(Commission_EUR_Apr),0) AS Commission_EUR_Apr,
coalesce(MAX(Commission_EUR_May),0) AS Commission_EUR_May,
coalesce(MAX(Commission_EUR_Jun),0) AS Commission_EUR_Jun,
coalesce(MAX(Commission_EUR_Jul),0) AS Commission_EUR_Jul,
coalesce(MAX(Commission_EUR_Aug),0) AS Commission_EUR_Aug,
coalesce(MAX(Commission_EUR_Sep),0) AS Commission_EUR_Sep,
coalesce(MAX(Commission_EUR_Oct),0) AS Commission_EUR_Oct,
coalesce(MAX(Commission_EUR_Nov),0) AS Commission_EUR_Nov,
coalesce(MAX(Commission_EUR_Dec),0) AS Commission_EUR_Dec,
coalesce(SUM(Commission_EUR_YTD),0) AS Commission_EUR_YTD,
coalesce(SUM(Commission_EUR_YTD),0)/MONTH(@ToMonth)* 12 AS Commission_EUR_ANN,
coalesce(SUM(AllocationJan),0) AS AllocationJan,
coalesce(SUM(AllocationFeb),0) AS AllocationFeb,
coalesce(SUM(AllocationMar),0) AS AllocationMar,
coalesce(SUM(AllocationApr),0) AS AllocationApr,
coalesce(SUM(AllocationMay),0) AS AllocationMay,
coalesce(SUM(AllocationJun),0) AS AllocationJun,
coalesce(SUM(AllocationJul),0) AS AllocationJul,
coalesce(SUM(AllocationAug),0) AS AllocationAug,
coalesce(SUM(AllocationSep),0) AS AllocationSep,
coalesce(SUM(AllocationOct),0) AS AllocationOct,
coalesce(SUM(AllocationNov),0) AS AllocationNov,
coalesce(SUM(AllocationDec),0) AS AllocationDec,
coalesce(SUM(Allocation_EUR_YTD),0) AS Allocation_EUR_YTD,
coalesce(SUM(Allocation_EUR_YTD),0)/MONTH(@ToMonth)* 12 AS Allocation_EUR_ANN,
coalesce(SUM(PreviousYear1Turnover),0) AS PreviousYear1Turnover,
coalesce(SUM(PreviousYear1Commission),0) AS PreviousYear1Commission,
coalesce(SUM(PreviousYear1Allocation),0) AS PreviousYear1Allocation,
coalesce(SUM(PreviousYear2Turnover),0) AS PreviousYear2Turnover,
coalesce(SUM(PreviousYear2Commission),0) AS PreviousYear2Commission,
coalesce(SUM(PreviousYear2Allocation),0) AS PreviousYear2Allocation,
coalesce(SUM(PreviousYear3Turnover),0) AS PreviousYear3Turnover,
coalesce(SUM(PreviousYear3Commission),0) AS PreviousYear3Commission,
coalesce(SUM(PreviousYear3Allocation),0) AS PreviousYear3Allocation
FROM
(SELECT DataSourceName,
CorporateRegion,
Country,
Broker,
ParentBrokerCode,
UPI,
Portfolio,
Boutique,
Trader_Number,
Trader_Region,
AssetClassCode,
AssetClassName,
InstrumentType,
TransactionType,
SUM(CASE WHEN MONTH(CONVERT(smalldatetime, CONVERT(char(8), TradeDateKey)) ) = 1 AND YEAR(CONVERT(smalldatetime, CONVERT(char(8), TradeDateKey)) ) = YEAR(@ToMonth)THEN GrossAmount_EUR END) AS GrossAmount_EUR_Jan,
SUM(CASE WHEN MONTH(CONVERT(smalldatetime, CONVERT(char(8), TradeDateKey)) ) = 2 AND YEAR(CONVERT(smalldatetime, CONVERT(char(8), TradeDateKey)) ) = YEAR(@ToMonth)THEN GrossAmount_EUR END) AS GrossAmount_EUR_Feb,
SUM(CASE WHEN MONTH(CONVERT(smalldatetime, CONVERT(char(8), TradeDateKey)) ) = 3 AND YEAR(CONVERT(smalldatetime, CONVERT(char(8), TradeDateKey)) ) = YEAR(@ToMonth)THEN GrossAmount_EUR END) AS GrossAmount_EUR_Mar,
SUM(CASE WHEN MONTH(CONVERT(smalldatetime, CONVERT(char(8), TradeDateKey)) ) = 4 AND YEAR(CONVERT(smalldatetime, CONVERT(char(8), TradeDateKey)) ) = YEAR(@ToMonth)THEN GrossAmount_EUR END) AS GrossAmount_EUR_Apr,
SUM(CASE WHEN MONTH(CONVERT(smalldatetime, CONVERT(char(8), TradeDateKey)) ) = 5 AND YEAR(CONVERT(smalldatetime, CONVERT(char(8), TradeDateKey)) ) = YEAR(@ToMonth)THEN GrossAmount_EUR END) AS GrossAmount_EUR_May,
SUM(CASE WHEN MONTH(CONVERT(smalldatetime, CONVERT(char(8), TradeDateKey)) ) = 6 AND YEAR(CONVERT(smalldatetime, CONVERT(char(8), TradeDateKey)) ) = YEAR(@ToMonth)THEN GrossAmount_EUR END) AS GrossAmount_EUR_Jun,
SUM(CASE WHEN MONTH(CONVERT(smalldatetime, CONVERT(char(8), TradeDateKey)) ) = 7 AND YEAR(CONVERT(smalldatetime, CONVERT(char(8), TradeDateKey)) ) = YEAR(@ToMonth)THEN GrossAmount_EUR END) AS GrossAmount_EUR_Jul,
SUM(CASE WHEN MONTH(CONVERT(smalldatetime, CONVERT(char(8), TradeDateKey)) ) = 8 AND YEAR(CONVERT(smalldatetime, CONVERT(char(8), TradeDateKey)) ) = YEAR(@ToMonth)THEN GrossAmount_EUR END) AS GrossAmount_EUR_Aug,
SUM(CASE WHEN MONTH(CONVERT(smalldatetime, CONVERT(char(8), TradeDateKey)) ) = 9 AND YEAR(CONVERT(smalldatetime, CONVERT(char(8), TradeDateKey)) ) = YEAR(@ToMonth)THEN GrossAmount_EUR END) AS GrossAmount_EUR_Sep,
SUM(CASE WHEN MONTH(CONVERT(smalldatetime, CONVERT(char(8), TradeDateKey)) ) = 10 AND YEAR(CONVERT(smalldatetime, CONVERT(char(8), TradeDateKey)) ) = YEAR(@ToMonth)THEN GrossAmount_EUR END) AS GrossAmount_EUR_Oct,
SUM(CASE WHEN MONTH(CONVERT(smalldatetime, CONVERT(char(8), TradeDateKey)) ) = 11 AND YEAR(CONVERT(smalldatetime, CONVERT(char(8), TradeDateKey)) ) = YEAR(@ToMonth)THEN GrossAmount_EUR END) AS GrossAmount_EUR_Nov,
SUM(CASE WHEN MONTH(CONVERT(smalldatetime, CONVERT(char(8), TradeDateKey)) ) = 12 AND YEAR(CONVERT(smalldatetime, CONVERT(char(8), TradeDateKey)) ) = YEAR(@ToMonth)THEN GrossAmount_EUR END) AS GrossAmount_EUR_Dec,
SUM(CASE WHEN CONVERT(smalldatetime, CONVERT(char(8), TradeDateKey)) BETWEEN DATEADD(DAY,0,DATEADD(YEAR,DATEDIFF(YEAR,0,@ToMonth),0)) AND @ToMonth THEN GrossAmount_EUR END)AS GrossAmount_EUR_YTD,
SUM(CASE WHEN CONVERT(smalldatetime, CONVERT(char(8), TradeDateKey))
BETWEEN DATEADD(MONTH,-1,DATEADD(YEAR,DATEDIFF(YEAR,0,@ToMonth),0)) AND DATEADD(DAY,-1,DATEADD(YEAR,DATEDIFF(YEAR,0,@ToMonth),0)) THEN GrossAmount_EUR END) PreviousYear1_GrossAmount_EUR_Dec,
SUM(CASE WHEN MONTH(CONVERT(smalldatetime, CONVERT(char(8), TradeDateKey)) ) = 1 AND YEAR(CONVERT(smalldatetime, CONVERT(char(8), TradeDateKey)) ) = YEAR(@ToMonth)THEN Commission_EUR END) AS Commission_EUR_Jan,
SUM(CASE WHEN MONTH(CONVERT(smalldatetime, CONVERT(char(8), TradeDateKey)) ) = 2 AND YEAR(CONVERT(smalldatetime, CONVERT(char(8), TradeDateKey)) ) = YEAR(@ToMonth)THEN Commission_EUR END) AS Commission_EUR_Feb,
SUM(CASE WHEN MONTH(CONVERT(smalldatetime, CONVERT(char(8), TradeDateKey)) ) = 3 AND YEAR(CONVERT(smalldatetime, CONVERT(char(8), TradeDateKey)) ) = YEAR(@ToMonth)THEN Commission_EUR END) AS Commission_EUR_Mar,
SUM(CASE WHEN MONTH(CONVERT(smalldatetime, CONVERT(char(8), TradeDateKey)) ) = 4 AND YEAR(CONVERT(smalldatetime, CONVERT(char(8), TradeDateKey)) ) = YEAR(@ToMonth)THEN Commission_EUR END) AS Commission_EUR_Apr,
SUM(CASE WHEN MONTH(CONVERT(smalldatetime, CONVERT(char(8), TradeDateKey)) ) = 5 AND YEAR(CONVERT(smalldatetime, CONVERT(char(8), TradeDateKey)) ) = YEAR(@ToMonth)THEN Commission_EUR END) AS Commission_EUR_May,
SUM(CASE WHEN MONTH(CONVERT(smalldatetime, CONVERT(char(8), TradeDateKey)) ) = 6 AND YEAR(CONVERT(smalldatetime, CONVERT(char(8), TradeDateKey)) ) = YEAR(@ToMonth)THEN Commission_EUR END) AS Commission_EUR_Jun,
SUM(CASE WHEN MONTH(CONVERT(smalldatetime, CONVERT(char(8), TradeDateKey)) ) = 7 AND YEAR(CONVERT(smalldatetime, CONVERT(char(8), TradeDateKey)) ) = YEAR(@ToMonth)THEN Commission_EUR END) AS Commission_EUR_Jul,
SUM(CASE WHEN MONTH(CONVERT(smalldatetime, CONVERT(char(8), TradeDateKey)) ) = 8 AND YEAR(CONVERT(smalldatetime, CONVERT(char(8), TradeDateKey)) ) = YEAR(@ToMonth)THEN Commission_EUR END) AS Commission_EUR_Aug,
SUM(CASE WHEN MONTH(CONVERT(smalldatetime, CONVERT(char(8), TradeDateKey)) ) = 9 AND YEAR(CONVERT(smalldatetime, CONVERT(char(8), TradeDateKey)) ) = YEAR(@ToMonth)THEN Commission_EUR END) AS Commission_EUR_Sep,
SUM(CASE WHEN MONTH(CONVERT(smalldatetime, CONVERT(char(8), TradeDateKey)) ) = 10 AND YEAR(CONVERT(smalldatetime, CONVERT(char(8), TradeDateKey)) ) = YEAR(@ToMonth)THEN Commission_EUR END) AS Commission_EUR_Oct,
SUM(CASE WHEN MONTH(CONVERT(smalldatetime, CONVERT(char(8), TradeDateKey)) ) = 11 AND YEAR(CONVERT(smalldatetime, CONVERT(char(8), TradeDateKey)) ) = YEAR(@ToMonth)THEN Commission_EUR END) AS Commission_EUR_Nov,
SUM(CASE WHEN MONTH(CONVERT(smalldatetime, CONVERT(char(8), TradeDateKey)) ) = 12 THEN Commission_EUR END) AS Commission_EUR_Dec,
SUM(CASE WHEN CONVERT(smalldatetime, CONVERT(char(8), TradeDateKey)) BETWEEN DATEADD(DAY,0,DATEADD(YEAR,DATEDIFF(YEAR,0,@ToMonth),0)) AND @ToMonth THEN Commission_EUR END)AS Commission_EUR_YTD,
SUM(CASE WHEN CONVERT(smalldatetime, CONVERT(char(8), TradeDateKey))
BETWEEN DATEADD(MONTH,-1,DATEADD(YEAR,DATEDIFF(YEAR,0,@ToMonth),0)) AND DATEADD(DAY,-1,DATEADD(YEAR,DATEDIFF(YEAR,0,@ToMonth),0)) THEN Commission_EUR END) PreviousYear1_Commission_EUR_Dec,
COUNT(DISTINCT CASE WHEN MONTH(CONVERT(smalldatetime, CONVERT(char(8), TradeDateKey)) ) = 1 AND YEAR(CONVERT(smalldatetime, CONVERT(char(8), TradeDateKey)) ) = YEAR(@ToMonth)THEN TradeOrderNumber END) AS AllocationJan,
COUNT(DISTINCT CASE WHEN MONTH(CONVERT(smalldatetime, CONVERT(char(8), TradeDateKey)) ) = 2 AND YEAR(CONVERT(smalldatetime, CONVERT(char(8), TradeDateKey)) ) = YEAR(@ToMonth)THEN TradeOrderNumber END) AS AllocationFeb,
COUNT(DISTINCT CASE WHEN MONTH(CONVERT(smalldatetime, CONVERT(char(8), TradeDateKey)) ) = 3 AND YEAR(CONVERT(smalldatetime, CONVERT(char(8), TradeDateKey)) ) = YEAR(@ToMonth)THEN TradeOrderNumber END) AS AllocationMar,
COUNT(DISTINCT CASE WHEN MONTH(CONVERT(smalldatetime, CONVERT(char(8), TradeDateKey)) ) = 4 AND YEAR(CONVERT(smalldatetime, CONVERT(char(8), TradeDateKey)) ) = YEAR(@ToMonth)THEN TradeOrderNumber END) AS AllocationApr,
COUNT(DISTINCT CASE WHEN MONTH(CONVERT(smalldatetime, CONVERT(char(8), TradeDateKey)) ) = 5 AND YEAR(CONVERT(smalldatetime, CONVERT(char(8), TradeDateKey)) ) = YEAR(@ToMonth)THEN TradeOrderNumber END) AS AllocationMay,
COUNT(DISTINCT CASE WHEN MONTH(CONVERT(smalldatetime, CONVERT(char(8), TradeDateKey)) ) = 6 AND YEAR(CONVERT(smalldatetime, CONVERT(char(8), TradeDateKey)) ) = YEAR(@ToMonth)THEN TradeOrderNumber END) AS AllocationJun,
COUNT(DISTINCT CASE WHEN MONTH(CONVERT(smalldatetime, CONVERT(char(8), TradeDateKey)) ) = 7 AND YEAR(CONVERT(smalldatetime, CONVERT(char(8), TradeDateKey)) ) = YEAR(@ToMonth)THEN TradeOrderNumber END) AS AllocationJul,
COUNT(DISTINCT CASE WHEN MONTH(CONVERT(smalldatetime, CONVERT(char(8), TradeDateKey)) ) = 8 AND YEAR(CONVERT(smalldatetime, CONVERT(char(8), TradeDateKey)) ) = YEAR(@ToMonth)THEN TradeOrderNumber END) AS AllocationAug,
COUNT(DISTINCT CASE WHEN MONTH(CONVERT(smalldatetime, CONVERT(char(8), TradeDateKey)) ) = 9 AND YEAR(CONVERT(smalldatetime, CONVERT(char(8), TradeDateKey)) ) = YEAR(@ToMonth)THEN TradeOrderNumber END) AS AllocationSep,
COUNT(DISTINCT CASE WHEN MONTH(CONVERT(smalldatetime, CONVERT(char(8), TradeDateKey)) ) = 10 AND YEAR(CONVERT(smalldatetime, CONVERT(char(8), TradeDateKey)) ) = YEAR(@ToMonth)THEN TradeOrderNumber END) AS AllocationOct,
COUNT(DISTINCT CASE WHEN MONTH(CONVERT(smalldatetime, CONVERT(char(8), TradeDateKey)) ) = 11 AND YEAR(CONVERT(smalldatetime, CONVERT(char(8), TradeDateKey)) ) = YEAR(@ToMonth)THEN TradeOrderNumber END) AS AllocationNov,
COUNT(DISTINCT CASE WHEN MONTH(CONVERT(smalldatetime, CONVERT(char(8), TradeDateKey)) ) = 12 AND YEAR(CONVERT(smalldatetime, CONVERT(char(8), TradeDateKey)) ) = YEAR(@ToMonth)THEN TradeOrderNumber END) AS AllocationDec,
CAST(COUNT(DISTINCT CASE WHEN CONVERT(smalldatetime, CONVERT(char(8), TradeDateKey)) BETWEEN DATEADD(DAY,0,DATEADD(YEAR,DATEDIFF(YEAR,0,@ToMonth),0)) AND @ToMonth THEN TradeOrderNumber END)AS FLOAT) AS Allocation_EUR_YTD,
COUNT(DISTINCT CASE WHEN CONVERT(smalldatetime, CONVERT(char(8), TradeDateKey))
BETWEEN DATEADD(MONTH,-1,DATEADD(YEAR,DATEDIFF(YEAR,0,@ToMonth),0)) AND DATEADD(DAY,-1,DATEADD(YEAR,DATEDIFF(YEAR,0,@ToMonth),0)) THEN TradeOrderNumber END) PreviousYear1_Orders_EUR_Dec,
SUM(CASE WHEN MONTH(CONVERT(smalldatetime, CONVERT(char(8), TradeDateKey)) ) >= MONTH(DATEADD(MM,-12,@ToMonth))THEN GrossAmount_EUR END) AS PreviousYear1Turnover,
SUM(CASE WHEN MONTH(CONVERT(smalldatetime, CONVERT(char(8), TradeDateKey)) ) >= MONTH(DATEADD(MM,-12,@ToMonth))THEN Commission_EUR END)AS PreviousYear1Commission,
COUNT(DISTINCT CASE WHEN MONTH(CONVERT(smalldatetime, CONVERT(char(8), TradeDateKey)) ) >= MONTH(DATEADD(MM,-12,@ToMonth))THEN TradeOrderNumber END) AS PreviousYear1Allocation,
SUM(CASE WHEN CONVERT(smalldatetime, CONVERT(char(8), TradeDateKey)) BETWEEN DATEADD(MM,-12,@ToMonth) AND DATEADD(MM,-24,@ToMonth) THEN GrossAmount_EUR END) AS PreviousYear2Turnover,
SUM(CASE WHEN CONVERT(smalldatetime, CONVERT(char(8), TradeDateKey)) BETWEEN DATEADD(MM,-12,@ToMonth) AND DATEADD(MM,-24,@ToMonth) THEN Commission_EUR END) AS PreviousYear2Commission,
COUNT(DISTINCT CASE WHEN CONVERT(smalldatetime, CONVERT(char(8), TradeDateKey)) BETWEEN DATEADD(MM,-12,@ToMonth) AND DATEADD(MM,-24,@ToMonth) THEN TradeOrderNumber END) AS PreviousYear2Allocation,
SUM(CASE WHEN CONVERT(smalldatetime, CONVERT(char(8), TradeDateKey)) BETWEEN DATEADD(MM,-24,@ToMonth) AND DATEADD(MM,-36,@ToMonth) THEN GrossAmount_EUR END) AS PreviousYear3Turnover,
SUM(CASE WHEN CONVERT(smalldatetime, CONVERT(char(8), TradeDateKey)) BETWEEN DATEADD(MM,-24,@ToMonth) AND DATEADD(MM,-36,@ToMonth) THEN Commission_EUR END) AS PreviousYear3Commission,
COUNT(DISTINCT CASE WHEN CONVERT(smalldatetime, CONVERT(char(8), TradeDateKey)) BETWEEN DATEADD(MM,-24,@ToMonth) AND DATEADD(MM,-36,@ToMonth) THEN TradeOrderNumber END) AS PreviousYear3Allocation,
SUM(CASE WHEN CONVERT(smalldatetime, CONVERT(char(8), TradeDateKey)) BETWEEN DATEADD(MM,-36,@ToMonth) AND DATEADD(MM,-48,@ToMonth) THEN GrossAmount_EUR END) AS PreviousYear4Turnover,
SUM(CASE WHEN CONVERT(smalldatetime, CONVERT(char(8), TradeDateKey)) BETWEEN DATEADD(MM,-36,@ToMonth) AND DATEADD(MM,-48,@ToMonth) THEN Commission_EUR END) AS PreviousYear4Commission,
COUNT(DISTINCT CASE WHEN CONVERT(smalldatetime, CONVERT(char(8), TradeDateKey)) BETWEEN DATEADD(MM,-36,@ToMonth) AND DATEADD(MM,-48,@ToMonth) THEN TradeOrderNumber END) AS PreviousYear4Allocation
FROM @TradeDetail
GROUP BY DataSourceName,CorporateRegion,Country,Broker, ParentBrokerCode,UPI,Portfolio,Boutique,Trader_Number,Trader_Region,AssetClassCode, AssetClassName,InstrumentType,TransactionType
) rawData
GROUP BY DataSourceName,CorporateRegion,Country,Broker,ParentBrokerCode,UPI,Portfolio,Boutique,Trader_Number,Trader_Region,AssetClassCode,AssetClassName,InstrumentType,TransactionType
RETURN
December 6, 2011 at 3:22 am
I have converted TradeDateKey to smalldatetime to get rid of all the converts and that has brought some improvement. It now runs in 1.30 - 2mins
I ran the first part alone, prv.sp_TradeDetail , and it takes almost 2 minutes. NOt sure what I can do to speed improve this.
December 6, 2011 at 7:15 am
Did you ever create a Time Dimension table?
Calendar Date
Year
Month
Might be worth testing.
SSAS would be my first choice, Pivot also being something to explore.
December 6, 2011 at 7:18 am
I have opted to create a summary/aggregate table that will store the aggregated fact data. Then I will run the cross tab query of that data. Any advice if this ss this the correct approach?
December 6, 2011 at 7:20 am
eseosaoregie (12/6/2011)
I have opted to create a summary/aggregate table that will store the aggregated fact data. Then I will run the cross tab query of that data. Any advice if this ss this the correct approach?
That's basically the whole idea beind SSAS. Pivoting the data doesn't take much processing power, it's the sorting / aggregating to get the ax value that's killing you here.
Precount that, index correctly and see it fly.
December 6, 2011 at 7:38 am
eseosaoregie (12/5/2011)
I am not really sure on how I to create PIVOT with multiple aggregates. Could you give me some pointers.
How about creating multiple pivots for each kind of aggregate that you need: one for "GrossAmount_EUR_x", another for "Commission_EUR_x", "Orders_x", ...
eseosaoregie (12/6/2011)
I have converted TradeDateKey to smalldatetime to get rid of all the converts and that has brought some improvement. It now runs in 1.30 - 2minsI ran the first part alone, prv.sp_TradeDetail , and it takes almost 2 minutes. NOt sure what I can do to speed improve this.
Do you mean that the detail stored proc is the bottleneck? If so can you upload it's sqlplan please?
_____________________________________________________
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
Viewing 15 posts - 16 through 30 (of 41 total)
You must be logged in to reply to this topic. Login to reply