SELECT 
[Extent1].[ValuationDateID] AS [ValuationDateID], 
[Extent3].[TransactionRef] AS [TransactionRef], 
[Extent3].[Counterparty] AS [Counterparty], 
[Extent2].[BookName] AS [BookName], 
[Extent2].[Category] AS [Category], 
[Extent2].[Type] AS [Type], 
[Extent2].[LifeLicence] AS [LifeLicence], 
[Extent2].[Manager] AS [Manager], 
[Extent2].[TaxFund] AS [TaxFund], 
[Extent2].[HiPortName] AS [HiPortName], 
[Extent2].[AliasPortfolioName] AS [AliasPortfolioName], 
[Extent4].[ParallelShift] AS [ParallelShift], 
[Extent5].[Tenor] AS [Tenor], 
[Extent1].[Value] AS [Value]
FROM     (SELECT 
      [fctSwapDualShift].[ValuationDateID] AS [ValuationDateID], 
      [fctSwapDualShift].[Currency] AS [Currency], 
      [fctSwapDualShift].[BookID] AS [BookID], 
      [fctSwapDualShift].[HiPortNameID] AS [HiPortNameID], 
      [fctSwapDualShift].[ContractTypeID] AS [ContractTypeID], 
      [fctSwapDualShift].[TypeID] AS [TypeID], 
      [fctSwapDualShift].[ManagerID] AS [ManagerID], 
      [fctSwapDualShift].[TransactionID] AS [TransactionID], 
      [fctSwapDualShift].[ParallelShiftID] AS [ParallelShiftID], 
      [fctSwapDualShift].[SwapTenorID] AS [SwapTenorID], 
      [fctSwapDualShift].[Value] AS [Value], 
      [fctSwapDualShift].[ClientID] AS [ClientID]
      FROM [Reporting].[fctSwapDualShift] AS [fctSwapDualShift]) AS [Extent1]
INNER JOIN (SELECT 
      [vwAllMappingWithDescriptions].[BookID] AS [BookID], 
      [vwAllMappingWithDescriptions].[ValuationDateID] AS [ValuationDateID], 
      [vwAllMappingWithDescriptions].[BookName] AS [BookName], 
      [vwAllMappingWithDescriptions].[Category] AS [Category], 
      [vwAllMappingWithDescriptions].[LifeLicence] AS [LifeLicence], 
      [vwAllMappingWithDescriptions].[TaxFund] AS [TaxFund], 
      [vwAllMappingWithDescriptions].[Type] AS [Type], 
      [vwAllMappingWithDescriptions].[HiPortName] AS [HiPortName], 
      [vwAllMappingWithDescriptions].[AliasPortfolioName] AS [AliasPortfolioName], 
      [vwAllMappingWithDescriptions].[Manager] AS [Manager], 
      [vwAllMappingWithDescriptions].[RasHiPortCode] AS [RasHiPortCode], 
      [vwAllMappingWithDescriptions].[LiquidityRiskClassification] AS [LiquidityRiskClassification], 
      [vwAllMappingWithDescriptions].[IncludeForRisk] AS [IncludeForRisk]
      FROM [configuration].[vwAllMappingWithDescriptions] AS [vwAllMappingWithDescriptions]) AS [Extent2] ON [Extent1].[BookID] = [Extent2].[BookID]
INNER JOIN [Reporting].[dimTransaction] AS [Extent3] ON [Extent1].[TransactionID] = [Extent3].[ID]
INNER JOIN [Reporting].[dimParallelShift] AS [Extent4] ON [Extent1].[ParallelShiftID] = [Extent4].[ID]
INNER JOIN [Reporting].[dimSwapTenor] AS [Extent5] ON [Extent1].[SwapTenorID] = [Extent5].[ID]
WHERE (20130314 = [Extent1].[ValuationDateID]) AND (20130314 = [Extent2].[ValuationDateID])

/****** Object:  Index [idx_fctSwapDualShift_ValueAggregation]    Script Date: 03/15/2013 12:34:24 ******/
CREATE NONCLUSTERED INDEX [idx_fctSwapDualShift_ValueAggregation] ON [Reporting].[fctSwapDualShift] 
(
	[ValuationDateID] ASC,
	[BookID] ASC,
	[HiPortNameID] ASC,
	[ContractTypeID] ASC,
	[TypeID] ASC,
	[ManagerID] ASC,
	[ParallelShiftID] ASC,
	[SwapTenorID] ASC,
	[ClientID] ASC
)
INCLUDE ( [Value]) 
WHERE ([Value]<>(0))
WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO

SELECT 
[Extent1].[ValuationDateID] AS [ValuationDateID], 
[Extent3].[TransactionRef] AS [TransactionRef], 
[Extent3].[Counterparty] AS [Counterparty], 
[Extent2].[BookName] AS [BookName], 
[Extent2].[Category] AS [Category], 
[Extent2].[Type] AS [Type], 
[Extent2].[LifeLicence] AS [LifeLicence], 
[Extent2].[Manager] AS [Manager], 
[Extent2].[TaxFund] AS [TaxFund], 
[Extent2].[HiPortName] AS [HiPortName], 
[Extent2].[AliasPortfolioName] AS [AliasPortfolioName], 
[Extent4].[ParallelShift] AS [ParallelShift], 
[Extent5].[Tenor] AS [Tenor], 
[Extent1].[Value] AS [Value]
FROM  [Reporting].[fctSwapDualShift] AS [fctSwapDualShift] AS [Extent1]
INNER JOIN [configuration].[vwAllMappingWithDescriptions] AS [vwAllMappingWithDescriptions] AS [Extent2] 
ON [Extent1].[BookID] = [Extent2].[BookID]
INNER JOIN [Reporting].[dimTransaction] AS [Extent3] 
ON [Extent1].[TransactionID] = [Extent3].[ID]
INNER JOIN [Reporting].[dimParallelShift] AS [Extent4] 
ON [Extent1].[ParallelShiftID] = [Extent4].[ID]
INNER JOIN [Reporting].[dimSwapTenor] AS [Extent5] 
ON [Extent1].[SwapTenorID] = [Extent5].[ID]
WHERE (20130314 = [Extent1].[ValuationDateID]) 
AND (20130314 = [Extent2].[ValuationDateID])

CREATE NONCLUSTERED INDEX test
ON [Reporting].[fctSwapDualShift] ([ValuationDateID])
INCLUDE ([BookID],[TransactionID],[ParallelShiftID],[SwapTenorID],[Value])