Compare values of two rows

Case
I get the cumulative sales each week, but I want to know the sales per week.

This is what I have:

WeekNr	Bike	Sales
1	Red bike	4
1	Green bike	2
1	Black bike	5
1	Blue bike	1
2	Red bike	6
2	Green bike	7
2	Black bike	7
2	Blue bike	4
3	Red bike	7
3	Green bike	8
3	Black bike	9
3	Blue bike	6

And this is what I want:

WeekNr	Bike	CumuSales	WeekSales
1	Black bike	5	5
2	Black bike	7	2
3	Black bike	9	2
1	Blue bike	1	1
2	Blue bike	4	3
3	Blue bike	6	2
1	Green bike	2	2
2	Green bike	7	5
3	Green bike	8	1
1	Red bike	4	4
2	Red bike	6	2
3	Red bike	7	1

Solution
One of the solutions is to sort the rows on the key column (Bike in this case) and then on the week number. Then you can compare each row to the previous row to calculate the week sales.

1) Source and Sorting
This example uses a CSV source, so I have to add a Sort Transformation. If your source is a database you can add an ORDER BY clause in the source query. Sort the rows on Bike (1) and WeekNr (2).
The WeekNr and Sales are integers and the Bike is a varchar/string column.

Sorting is important for this solution.

2) Script Component
Add a Script Component (type transformation) and add all rows as readonly input columns.

ReadOnly Input Columns

3) Add new output column
On the Inputs and Outputs tab, add a new integer (DT_I4) column named WeekSales to store the sales per week.

Add new output column

4) The Script
Go to the Script tab and add the following C# code:

// C# Code
using System;
using System.Data;
using Microsoft.SqlServer.Dts.Pipeline.Wrapper;
using Microsoft.SqlServer.Dts.Runtime.Wrapper;

[Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute]
public class ScriptMain : UserComponent
{
    // Variables to store the previous row
    string Bike = "";
    int WeekNr = 0;
    int Sales = 0;

    public override void Input0_ProcessInputRow(Input0Buffer Row)
    {
        // Compare current key with previous key
        if (Row.Bike == Bike)
        {
            // Keys match, so you can compare current sales with previous sales
            Row.WeekSales = Row.Sales - Sales;
        }
        else
        {
            // Keys don't match, so this is the first week
            Row.WeekSales = Row.Sales;
        }

        // Store current row values in the variables for the next row
        Bike = Row.Bike;
        WeekNr = Row.WeekNr;
        Sales = Row.Sales;
    }
}

or VB.net code:

' VB.net Code
Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
Imports Microsoft.SqlServer.Dts.Runtime.Wrapper

<microsoft .sqlserver.dts.pipeline.ssisscriptcomponententrypointattribute=".sqlserver.dts.pipeline.ssisscriptcomponententrypointattribute"> _
<clscompliant false="false"> _
Public Class ScriptMain
    Inherits UserComponent

    ' Variables to store the previous row
    Dim Bike As String = ""
    Dim WeekNr As Integer = 0
    Dim Sales As Integer = 0

    Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
        ' Compare current key with previous key
        If (Row.Bike = Bike) Then
            ' Keys match, so you can compare current sales with previous sales
            Row.WeekSales = Row.Sales - Sales
        Else

            ' Keys don't match, so this is the first week
            Row.WeekSales = Row.Sales
        End If

        ' Store current row values in the variables for the next row
        Bike = Row.Bike
        WeekNr = Row.WeekNr
        Sales = Row.Sales
    End Sub
End Class

5) The result
I added an empty Derived Column and a Data Viewer for testing purposes.

The result