CREATE VIEW [fact].[MovementFactsWeek]
    WITH SCHEMABINDING
AS
SELECT
    d.[FiscalWeekStartDate],
    d.[FiscalWeekEndDate],
    mf.[FiscalQuarterStartDate],
    mf.[ItemKey],
    mf.[LocationKey],
    SUM(mf.[TotalQuantity]) AS [TotalQuantity],
    SUM(mf.[TotalWeight]) AS [TotalWeight],
    SUM(mf.[TotalCost]) AS [TotalCost],
    SUM(mf.[TotalRetail]) AS [TotalRetail],
    SUM(mf.[WeekMargin]) AS [WeekMargin],
    COUNT_BIG(*) AS [RowCount]
FROM
    [fact].[MovementFacts] mf
    INNER JOIN [dimension].[Date] d
        ON mf.[BusinessDay] = d.[BusinessDay]
GROUP BY
    mf.[ItemKey],
    mf.[LocationKey],
    d.[FiscalWeekStartDate],
    d.[FiscalWeekEndDate],
    mf.[FiscalQuarterStartDate]
GO

CREATE UNIQUE CLUSTERED INDEX [CI_MovementFactsWeek]
ON [fact].[MovementFactsWeek]
(
    [ItemKey],
    [LocationKey],
    [FiscalWeekStartDate],
    [FiscalWeekEndDate],
    [FiscalQuarterStartDate]
)
ON [MovementFactsPartitionScheme]([FiscalQuarterStartDate])
GO

CREATE NONCLUSTERED INDEX [IX_MovementFactsWeek_FiscalWeek]
ON [fact].[MovementFactsWeek]
(
    [FiscalWeekStartDate],
    [FiscalWeekEndDate],
    [FiscalQuarterStartDate]
)
INCLUDE
(
    [ItemKey],
    [LocationKey]
)
GO