My current data set looks like following
I have a second table that records a "Rolling rank" of each of these which looks like this:
I perform this by looking for all records in Table 1 where they do not have a subsequent record in Table 2
SELECT Key ,Group FROM [Table 1] [T1] LEFT JOIN [Table 2] [T2] ON [T1].Key = [T2].KeySK WHERE [T2].Key IS NULL
Then, using SSIS ForEach Loop, I run the following stored procedure:
SELECT @InstalmentSequence = [Sequence] FROM [Table 1] [T1] WHERE [T1].Key = @Key INSERT INTO [Table 2] SELECT [T1].[Key] AS 'KeySK' ,RANK() OVER (ORDER BY [T1].[Sequence] DESC) AS 'Rank' ,SUM(ISNULL([T1].Amount,0.00)) OVER (ORDER BY [T1].[Sequence] DESC) AS 'TotalAmount' ,ISNULL([T1].Amount, 0.00) AS 'Amount' FROM [Table 1] [T1] WHERE [T1].AccountSK = @Group AND [T1].[Sequence] <= @Sequence
All of this works and produces the correct outcome, but the performance is very poor. I am wondering if these is a better solution to this that I am not thinking of.