This output is correct because package 120000 has only code associated with it. The other packages have 2 or more different codes. The output is required to be the packages...

After a lunch break a managaed to get the necessary output with the following query

SELECT

td.Package

,MAX(B.[Broker]) OVER (PARTITION BY td.Package) AS [Broker]

,td.Portfolio

,td.GrossAmount_EUR

,td.Commission_EUR

,A.GrossAmount_EUR

,A.Commission_EUR

FROM #TradeDetail td

LEFT JOIN #Broker b

ON B.[Broker] =...

many thanks for that. Have been staring at the screen all day.

Cheers.

I actually managed to get the result with this query 😛

SELECT

td.Package

,MAX(B.[Broker]) OVER (PARTITION BY td.Package) AS [Broker]

,td.Portfolio

,td.GrossAmount_EUR

,td.Commission_EUR

FROM #TradeDetail td

LEFT JOIN #Broker b

ON B.[Broker] = td.[Broker]

WHERE EXISTS(SELECT 1 FROM #Portfolio...

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...

@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...

@Lynn,

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...

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...

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...

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...

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...

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...

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]...

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...

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...