USE AdventureWorksDW

GO

DECLARE @RunningTotalAantal INT
DECLARE @RunningTotalBedrag MONEY
DECLARE @KlantNaam AS VARCHAR(128)

DECLARE @Sales TABLE (WeekNaam VARCHAR(128), KlantNaam VARCHAR(128), Aantal INT, Bedrag MONEY, AantalCumulatief INT, BedragCumulatief MONEY)

INSERT INTO @Sales 
SELECT  DT.CalendarYear * 100 + DT.WeekNumberOfYear AS WeekNaam
	  , DC.FirstName + ' ' + DC.LastName  + ' (' + CAST(DC.CustomerKey AS VARCHAR(16)) + ')' AS KlantNaam	  
	  , SUM(FIS.OrderQuantity) AS Aantal
	  , SUM(FIS.SalesAmount) AS Bedrag
	  , 0
	  , 0 	  
FROM FactInternetSales AS FIS
INNER JOIN DimProduct AS DP ON FIS.ProductKey = DP.ProductKey
INNER JOIN DimCustomer AS DC ON FIS.CustomerKey = DC.CustomerKey
INNER JOIN DimTime AS DT ON FIS.OrderDateKey = DT.TimeKey
GROUP BY DC.FirstName,DC.LastName, DC.CustomerKey
	   , DT.CalendarYear,DT.WeekNumberOfYear
-- Sorting is very important here!	   
ORDER BY DC.FirstName,DC.LastName
	   , DT.CalendarYear,DT.WeekNumberOfYear

UPDATE  @Sales
SET     @RunningTotalAantal = AantalCumulatief = 
		(CASE WHEN KlantNaam = @KlantNaam THEN @RunningTotalAantal + Aantal
         ELSE Aantal  END)
       ,@RunningTotalBedrag = BedragCumulatief = 
       (CASE WHEN KlantNaam = @KlantNaam THEN @RunningTotalBedrag + Bedrag
         ELSE Bedrag   END)
       ,@KlantNaam = KlantNaam
FROM    @Sales AS S


-- Hier bekijken we het resultaat
SELECT *
FROM @Sales