Buckets
----------------------------------------
Bucket     BucketAccount     TotalAmount
1          GB111             30
2          GB111             50
3          GB222             100
4          GB333             150


Holdings                    (before execution)
------------------------------------------------------------------------------
ID       Account       Amount      Bucket      AmountApplied
1        GB111          50         null            null
2        GB111          40         null            null
3        GB222          30         null            null
4        GB222          40         null            null
5        GB333        -100         null            null
6        GB333         250         null            null
7        GB333          50         null            null

If(OBJECT_ID('tempdb..#buckets') Is Not Null)
Begin
    Drop Table #buckets
End

CREATE TABLE #buckets
(
    Bucket int,
    BucketAccount nvarchar(10),
    TotalAmount Decimal
);

insert into #buckets values
    (1, 'GB111', 30),
    (2, 'GB111', 50),
    (3, 'GB222', 100),
    (4, 'GB333', 150)



If(OBJECT_ID('tempdb..#holdings') Is Not Null)
Begin
    Drop Table #holdings
End

CREATE TABLE #holdings
(
    ID int,
    Account nvarchar(10),
    Amount decimal,
    Bucket int null,
    TotalAmount decimal null
);

insert into #holdings (ID, Account, Amount, Bucket, TotalAmount)
values
    (1, 'GB111', 50, null, null),
    (2, 'GB111', 40, null, null),
    (3, 'GB222', 30, null, null),
    (4, 'GB222', 40, null, null),
    (5, 'GB333', -100, null, null),
    (6, 'GB333', 250, null, null),
    (7, 'GB333', 50, null, null)


select *
from
    (select
        hold.Account,   maxIds.ID as SubTotalId, sum(hold.Amount) as PartAmount
    from #holdings hold
        inner join #holdings maxIds
            on hold.Account = maxIds.Account
            and hold.Id <= maxIds.ID
    group by hold.Account, maxIds.Id) partHoldings

    right join
        (select buckets.BucketAccount, subBuckets.Bucket, sum(buckets.TotalAmount) as PartAmount
        from #buckets buckets
            inner join #buckets subBuckets
                on buckets.BucketAccount = subBuckets.BucketAccount
                and buckets.Bucket <= subBuckets.Bucket
        group by buckets.BucketAccount, subBuckets.Bucket) partBuckets

        on partHoldings.Account = partBuckets.BucketAccount
        and partHoldings.PartAmount >= partBuckets.PartAmount


select
    -- * ,
    BucketAccount, Bucket, ID as holdingId,
    case
        when MinHoldingCoveringBucket < Id and Id < MaxHoldingCoveringBucket then Amount
        when MinHoldingCoveringBucket = Id and Id = MaxHoldingCoveringBucket then PartAmount - prevTotalPartAmount
        when MinHoldingCoveringBucket = Id and Id <> MaxHoldingCoveringBucket then holdPartAmount - prevTotalPartAmount
        when MinHoldingCoveringBucket <> Id and Id = MaxHoldingCoveringBucket then PartAmount - holdPrevPartAmount
        else null
    end as AmountApplied
from
    (select
        holdingsBuckets.BucketAccount, holdingsBuckets.Bucket, holdingsBuckets.PartAmount, holdingsBuckets.prevTotalPartAmount
        , IsNull(MinHoldingCoveringBucket, minAccountHoldingId) as MinHoldingCoveringBucket
        , IsNull(MaxHoldingCoveringBucket, maxAccountHoldingId) as MaxHoldingCoveringBucket

        , hold.ID, hold.Amount
        , partHoldings.PartAmount as holdPartAmount
        , partHoldings.prevPartAmount as holdPrevPartAmount
    from
        (select
            topLimits.*
            , min(botLimits.SubTotalId) as MinHoldingCoveringBucket
        from
            (select
                partBuckets.*
                , min(partHoldings.SubTotalId) as MaxHoldingCoveringBucket
            from
                (select subBuckets.BucketAccount, subBuckets.Bucket, sum(buckets.TotalAmount) as PartAmount, sum(buckets.TotalAmount) - subBuckets.TotalAmount as prevTotalPartAmount
                from #buckets buckets
                    inner join #buckets subBuckets
                        on buckets.BucketAccount = subBuckets.BucketAccount
                        and buckets.Bucket <= subBuckets.Bucket
                group by subBuckets.BucketAccount, subBuckets.Bucket, subBuckets.TotalAmount) partBuckets

                left join
                    (select
                        hold.Account,   maxIds.ID as SubTotalId, sum(hold.Amount) as PartAmount
                    from #holdings hold
                        inner join #holdings maxIds
                            on hold.Account = maxIds.Account
                            and hold.Id <= maxIds.ID
                    group by hold.Account, maxIds.Id) partHoldings

                on partHoldings.Account = partBuckets.BucketAccount
                and partHoldings.PartAmount >= partBuckets.PartAmount


                left join
                    (select
                        hold.Account,   maxIds.ID as SubTotalId, sum(hold.Amount) as PartAmount
                    from #holdings hold
                        inner join #holdings maxIds
                            on hold.Account = maxIds.Account
                            and hold.Id <= maxIds.ID
                    group by hold.Account, maxIds.Id) partHoldings2

                on partBuckets.BucketAccount = partHoldings2.Account
                and partHoldings.SubTotalId >= partHoldings2.SubTotalId
                and partHoldings2.PartAmount > partBuckets.prevTotalPartAmount

            group by partBuckets.BucketAccount, partBuckets.Bucket, partBuckets.PartAmount, partBuckets.prevTotalPartAmount) topLimits

            left join
                (select
                    hold.Account,   maxIds.ID as SubTotalId, sum(hold.Amount) as PartAmount
                from #holdings hold
                    inner join #holdings maxIds
                        on hold.Account = maxIds.Account
                        and hold.Id <= maxIds.ID
                group by hold.Account, maxIds.Id) botLimits

                on topLimits.BucketAccount = botLimits.Account
                and botLimits.PartAmount > topLimits.prevTotalPartAmount
                and botLimits.SubTotalId < topLimits.MaxHoldingCoveringBucket

        group by topLimits.BucketAccount, topLimits.Bucket, topLimits.PartAmount, topLimits.prevTotalPartAmount, topLimits.MaxHoldingCoveringBucket) holdingsBuckets

        inner join
            (select Account, min(Id) as minAccountHoldingId, max(id) as maxAccountHoldingId
            from #holdings
            group by Account) edgeAccountHoldings

            on holdingsBuckets.BucketAccount = edgeAccountHoldings.Account

        right join #holdings hold
            on holdingsBuckets.BucketAccount = hold.Account
            and IsNull(MinHoldingCoveringBucket, minAccountHoldingId) <= hold.ID
            and hold.ID <= IsNull(MaxHoldingCoveringBucket, maxAccountHoldingId)

        left join
            (
            select
                hold.Account,   maxIds.ID as SubTotalId, sum(hold.Amount) as PartAmount, sum(hold.Amount) - maxIds.Amount as prevPartAmount
            from #holdings hold
                inner join #holdings maxIds
                    on hold.Account = maxIds.Account
                    and hold.Id <= maxIds.ID
            group by hold.Account, maxIds.Id, maxIds.Amount
            ) partHoldings

            on partHoldings.Account = holdingsBuckets.BucketAccount
            and hold.ID = partHoldings.SubTotalId) selectionData

Holdings
--------------------------------------------------------------------------------------------------------------------------------------------
ID       Account       Amount      Bucket      AmountApplied      Comments
1        GB111          50           1              30          Applied 30. Bucket 1 is filled with 20 leftover, move to next bucket of same account
2        GB111          40           2              20          Insert new record. Applied 20 (from leftover in Bucket 1), and there is 30 leftover to cover in Bucket 2
3        GB222          30           2              30          Applied 30, 10 leftover in Bucket 2. We are out of holdings for this account, move on to next account
4        GB222          40           3              30          Applied 30, 70 leftover in Bucket 3
5        GB333        -100           3            **70**        Applied 40, 30 leftover in bucket 3. Bucket is not filled and we are out of holdings for this account. Move on to next account
6        GB333         250           4            -100          Applied -100, 250 leftover in Bucket 4
7        GB333          50           4             250          Applied 250, Bucket 4 is filled with 0 leftover, move on to next account
8        GB333          50          null          null          Skip as Bucket 4 is already filled