Complex SQL Update on 2 interdependent tables

Question

I have a database with several tables keeping track of phone calls/sms/data and allowances and I'm trying to work out if it is possible to allocate calls to allowances without resorting to cursors, but I can't figure out a way of structuring the SQL to do so. I don't have any useful SQL from my attempts as I can't seem to get my head around how to approach it! The problem is that to me this seems like an inherently iterative process and I can't work out if there is a sensible way to translate it into a set based approach. I've considered using windowing functions, but I can't see how to do that when we're tracking cumulative totals in 2 tables and the totals are interdependent. I'm trying to minimise the time to run this process and the impact on other queries as we'd like to rerun it fairly frequently and the tables are getting pretty big.

This is a simplified structure...

Call

logs all of the calls

• ID
• ContractID
• ChargeGroupID
• DateTime
• Quantity int
• QuantityFromAllowances int (this is what I want to populate)
• FirstAllowanceUsedID (FK to Allowance) (this is what I want to populate)

Allowance

What different allowances are available on each contract

• ID
• ContractID
• Priority (1 if it is to be used first, otherwise 0)
• Quantity int
• QuantityUsed int (initially set to 0 - can be used to keep track of how much is used as we go or not)

AllowanceChargeGroup

How the allowances are allowed to be used - this is a junction table listing allowable combinations

• ID
• AllowanceID
• ChargeGroupID

I've intentionally not documented all details to keep it simple. I hope everything is obvious but if not then let me know.

If I was dealing with this iteratively, my psueodocode would be something like: -

For each Call ordered by DateTime
    Declare a as Allowance
    Do

        Set a = First Allowance Where Allowance.ContractID=Call.ContractID And Allowance.QuantityUsed<Allowance.Quantity Order by Priority Descending
        If a != NULL
            Declare n as Integer
            Set n = a.Quantity-a.QuantityUsed
            If Call.Quantity-Call.QuantityFromAllowances<n
                Set n = Call.Quantity-Call.QuantityFromAllowances
            End if
            Set Call.QuantityFromAllowances = Call.QuantityFromAllowances + n
            If Call.FirstAllowanceUsedID == NULL Then 
                Set Call.FirstAllowanceUsedID = a.ID
            End if
            Set a.QuantityUsed = a.QuantityUsed + n

        End if

    Loop while a != NULL AND Call.QuantityFromAllowances<Call.Quantity

Next Call

Feel free to tell me that I'm approaching the problem wrong or that this actually is a good candidate for cursors. I'm just looking for the best solution.

As an example: -

Call
ID   ContractID   ChargeGroupID   DateTime   Quantity   QuantityFromAllowances   FirstAllowanceUsedID 
1    1            1               2016-11-01 100        0                        NULL
2    1            2               2016-11-02 500        0                        NULL
3    1            1               2016-11-03 500        0                        NULL
4    1            3               2016-11-04 100        0                        NULL
5    1            1               2016-11-05 100        0                        NULL
6    2            1               2016-11-01 100        0                        NULL

Allowance
ID   ContractID   Priority Quantity   QuantityUsed
1    1            1        500        0
2    1            0        500        0
3    2            1        500        0
4    2            0        500        0

AllowanceChargeGroup
ID   AllowanceID   ChargeGroupID
1    1             1
2    1             2
3    2             1
4    2             2
5    3             1

In my example, I would calculate it as follows: -

1. Call ID 1 matches Allowance ID 1 (via junction table in AllowanceChargeGroup) - QuantityFromAllowances=100, FirstAllowanceUsedID=1, Allowance.QuantityUsed=100 (0+100)
2. Call ID 2 matches Allowance ID 1, but only 400 still left on allowance, so QuantityFromAllowances=400, FirstAllowanceUsedID=1, Allowance.QuantityUsed=500 (100+400)
3. Call ID 2 matches Allowance ID 2 (none left on 1) - QuantityFromAllowances=500 (400+100), FirstAllowanceUsedID=1 (already set above so not changed), Allowance.QuantityUsed=100 (0+100)
4. Call ID 3 matches Allowance ID 2 (none left on 1) - , but only 400 still left on allowance, so QuantityFromAllowances=400, FirstAllowanceUsedID=2, Allowance.QuantityUsed=500 (100+400).
5. Call ID 4 does not match any allowances so no change
6. Call ID 5 does not match any allowances (all used up) so no change
7. Call ID 6 matches Allowance ID 3 QuantityFromAllowances=100, FirstAllowanceUsedID=3, Allowance.QuantityUsed=100 (0+100)

Afterwards, the tables should look like this (only changes are Call.QuantityFromAllowances, Call.FirstAllowanceUsedID, Allowance.QuantityUsed...

Call
ID   ContractID   ChargeGroupID   DateTime   Quantity   QuantityFromAllowances   FirstAllowanceUsedID 
1    1            1               2016-11-01 100        100                        1
2    1            2               2016-11-02 500        500                        1
3    1            1               2016-11-03 500        400                        2
4    1            3               2016-11-04 100        0                        NULL
5    1            1               2016-11-05 100        0                        NULL
6    2            1               2016-11-01 100        100                        3

Allowance
ID   ContractID   Priority Quantity   QuantityUsed
1    1            1        500        500
2    1            0        500        500
3    2            1        500        100
4    2            0        500        0

AllowanceChargeGroup
ID   AllowanceID   ChargeGroupID
1    1             1
2    1             2
3    2             1
4    2             2
5    3             1

Answer 1

You want to update both call table and allowance table, and each update depends on previous one.
This is not possible with only one sql statement so you need to loop.
You do not need cursors, you can settle it with sequential set operations in a procedure.

First of all some declaration and prepare some data:

declare @todo as table (callID int primary key, qt int, done bit, unique (done, qt, callid))
declare @id1 int, @id2 int, @q1 int, @q2 int

-- prepare job list
insert into @todo
select id, Quantity-QuantityFromAllowances, 0
from [call]
where Quantity>QuantityFromAllowances

Then main loop trough calls:

set @id1=0
set @q1= null
while not(@id1 is null) begin
    set @id1=null
    select top 1 @id1 = callID, @q1=qt from @todo where done=0 and qt>0 order by callID

    if not(@id1 is null) begin

        set @id2 = null
        select top 1 @id2 = a.id, @q2 = a.Quantity - a.QuantityUsed
        from [call] c
        inner join AllowanceChargeGroup g on g.ChargeGroupID = c.ChargeGroupID
        inner join allowance a on (a.ID = g.AllowanceID) and (a.Quantity>a.QuantityUsed)
        where c.ID=@id1
        order by c.ID,[Priority] desc, (a.Quantity-a.QuantityUsed) desc

        if not(@id2 is null) begin

            if @q2 < @q1 set @q1 = @q2

            update a set QuantityUsed = QuantityUsed + @q1
            from allowance a            
            where a.ID=@id2 

            update c set QuantityFromAllowances = QuantityFromAllowances + @q1, FirstAllowanceUsedID = isnull(FirstAllowanceUsedID, @id2)
            from [call] c
            where c.ID=@id1

            update t set qt = qt-@q1, done = IIF(qt-@q1=0,1,0)
            from @todo t
            where t.callID=@id1

        end else begin

            -- unable to complete
            update t set done = 1 
            from @todo t
            where t.callID=@id1

        end
    end
end

And finally the output:

select * from [call]
select * from allowance

same as requested

Answer 2

As I said in my comment, you could get your goal also with a different approach, without loops, but you need disjoint AllowanceChargeGroup, which means one Allowance can be in only one group.

With this unique relation between allowances and charge groups we can couple requests (calls) to allowances.

The idea is to list and weight (order) each single unit needed from calls and to list and weight (order) each single unit available from allowances and finally to couple them side by side.

For example, suppose to have this Calls, Allowances and ChargeGroups:

ID  ChargeGroupID   Quantity    QuantityFromAllowances  FirstAllowanceUsedID
1   1               3           0                       NULL
2   1               3           0                       NULL
3   2               5           0                       NULL

ID  Priority    Quantity    QuantityUsed
1   1           4           0
2   0           1           0
3   0           6           0

ID  AllowanceID ChargeGroupID
1   1           1
2   2           1
4   3           2

Now explode each line in n lines depending on line quantity (so we will have 3 rows for CallID 1 and CallID 2 and 5 rows for CallID 3 ). While exploding, label the rows to identify them (add two different columns with row number for group and for call/allowance)

ChargeGroupID   GroupRowN   CallID  CallRowN
1               1           1       1       
1               2           1       2       
1               3           1       3       
1               4           2       1       
1               5           2       2       
1               6           2       3       
2               1           3       1       
2               2           3       2       
2               3           3       3       
2               4           3       4       
2               5           3       5       

ChargeGroupID   GroupRowN   AllowanceID AllowanceRowN
1               1           1           1
1               2           1           2
1               3           1           3
1               4           1           4
1               5           2           1
2               1           3           1
2               2           3           2
2               3           3           3
2               4           3           4
2               5           3           5
2               6           3           6

Now simply join this exploded sets on group row number (GroupRowN).
Here you can see the distribution of allowances on calls.

• CallID=1 (first 3 rows) is completely covered by first 3 rows of AllowanceID=1
• CallID=2 (second 3 rows) is partially covered by last row of AllowanceID=1 and first (and unique) row of AllowanceID=2
• CallID=3 (last 5 rows) is completely covered by first 5 rows of AllowanceID=3, which is not completely depleted because last row is unmatched (not requested by any call)

(i have added horizontal dashes to better show the distribution by CallIDs):

ChargeGroupID   GroupRowN   CallID  CallRowN    ChargeGroupID   GroupRowN   AllowanceID AllowanceRowN
1               1           1       1           1               1           1           1
1               2           1       2           1               2           1           2
1               3           1       3           1               3           1           3
-----------------------------------------------------------------------------------------------------
1               4           2       1           1               4           1           4
1               5           2       2           1               5           2           1
1               6           2       3           NULL            NULL        NULL        NULL
-----------------------------------------------------------------------------------------------------
2               1           3       1           2               1           3           1
2               2           3       2           2               2           3           2
2               3           3       3           2               3           3           3
2               4           3       4           2               4           3           4
2               5           3       5           2               5           3           5
NULL            NULL        NULL    NULL        2               6           3           6

Now let's aggregate this result to get some totals:

CallID  Max(CallN)  AllowanceID Max(AllowanceN)
1       3           1           3
2       1           1           4
2       2           2           1
3       5           3           5

Finally, from last output we can get info to update calls an allowance table:

CallID  QtUsed  FirstUsed
1       3       1
2       2       1
3       5       3

AllowanceID QtUsed
1           4
2           1
3           5

Ok,
this was the theory, now let's see some code (using above data).

Pay attention to FN_NUMBERS(n), it is a function that returns only one column with numbers from 1 to n, you need it in you database, there are many ways to do it, just google for "tally tables" or look here.
I use the following:

CREATE FUNCTION FN_NUMBERS(
     @MAX INT
)
RETURNS @N TABLE (N INT NOT NULL PRIMARY KEY)  
BEGIN
     WITH
       Pass0 as (select '1' as C union all select '1'),       --2 rows
       Pass1 as (select '1' as C from Pass0 as A, Pass0 as B),--4 rows
       Pass2 as (select '1' as C from Pass1 as A, Pass1 as B),--16 rows
       Pass3 as (select '1' as C from Pass2 as A, Pass2 as B),--256 rows
       Pass4 as (select TOP (@MAX) '1' as C from Pass3 as A, Pass3 as B)    --65536 rows
       ,Tally as (select TOP (@MAX) '1' as C from Pass4 as A, Pass2 as B, Pass1 as C)  --4194304 rows
       --,Tally as (select TOP (@MAX) '1' as C from Pass4 as A, Pass3 as B)               --16777216 rows
       --,Tally as (select TOP (@MAX) '1' as C from Pass4 as A, Pass4 as B)               --4294836225 rows
     INSERT INTO @N
     SELECT TOP (@MAX) ROW_NUMBER() OVER(ORDER BY C) AS N
     FROM Tally
     RETURN
END

Back to the sql..

declare @res as table (id int identity primary key, CallID int, CallN int , AllowanceID int, AllowanceN int, unique (callId, id), unique (allowanceID, id))

;with
cx as (
        select c.ID, c.Quantity, c.ChargeGroupID, n, ROW_NUMBER() over (partition by ChargeGroupID order by id,n) rn
        from [call] c
        join FN_NUMBERS(1000) n on n.N<=(c.Quantity-c.QuantityFromAllowances)
),
ax as (
        select a.ID, a.Quantity, ChargeGroupID, N, ROW_NUMBER() over (partition by g.ChargeGroupID order by [priority] desc, a.id,n) rn
        from Allowance a 
        join AllowanceChargeGroup g on g.AllowanceID = a.ID 
        join FN_NUMBERS(1000) n on n.N <= (a.Quantity-a.QuantityUsed)
),
j as (
select 
    cx.ID CallID, cx.Quantity CallQt, cx.N CallN, cx.rn CallRn, ax.ID AllowanceID, ax.Quantity AllowanceQt, ax.N AllowanceN, ax.rn AllowanceRn
from cx
join ax on cx.rn = ax.rn and (cx.ChargeGroupID = ax.ChargeGroupID)
)
insert into @res
select CallID, MAX(CallN) CallN, AllowanceID, MAX(AllowanceN) AllowanceN
from j
group by CallID,AllowanceID

This will fill @res table with the final aggregate data to use for the updates.

So we only need to perform the actual updates:

-- updates Allowance table
;with
ar as (
    select AllowanceID, MAX(AllowanceN) QtUsed
    from @res
    group by AllowanceID
)
update a set a.QuantityUsed = a.QuantityUsed + ar.QtUsed
select ar.*
from Allowance a
join ar on a.ID = ar.AllowanceID

-- updates Call table
;with
fu as (
    select CallID id, min(calln) FirstUsed
    from @res
    group by CallID 
),
cr as (
    select CallID, MAX(CallN) QtUsed, MIN(AllowanceID) FirstUsed
    from @res r1
    left join fu r2 on r1.CallID=r2.id and r1.CallN = r2.FirstUsed
    group by CallID
)
update c set QuantityFromAllowances = c.QuantityFromAllowances + QtUsed, FirstAllowanceUsedID = ISNULL(FirstAllowanceUsedID, FirstUsed)
select cr.*
from [call] c
join cr on c.ID = cr.CallID

That's all, one insert in a temp table and two updates, no loops, no cursors..

Answer 3

It's always hard to answer a question without table designs, sample data and expected output. If this answer does not help you take a look at this guide from the help pages and this answer from Meta, for tips on how you could edit your question.

I suspect this is a question of grain. Because the level of granularity in each table differs, quite rightly, it can be hard to combine.

I'd recommend you follow this basic pattern:

1. Aggregate your call table, to match the PKey in Allowance.
2. Join to Allowance.

Something like:

-- Update allowance with usage.
WITH Used AS 
    (
        -- Retrieve usage.
        SELECT
            ID,
            ContractID,
            AllowanceID,
            SUM(Quantity) AS Quantity
        FROM
            Call
        GROUP BY
            ID,
            ContractID,
            AllowanceID
    ),
UPDATE
    a
SET
    a.QuantityUsed = a.QuantityUsed + u.Quantity 
FROM
    Allowance AS a
        INNER JOIN Used AS u        ON  u.ID            = a.ID
                                    AND u.ContractID    = a.ContractID
                                    AND u.AllowanceID   = a.AllowanceID
;

Of course, you'll need to flesh this out. I could not see a space for charges applied, so I have not included the AllowanceChargeGroup table.

It looks like your allowance table is pulling double duty. It houses both the terms of the contract (which probably don't change often) and the volume used (which will change more often - perhaps monthly?). I would suggest you split these functions.

I understand it's a simplified model, so apologies if a simplified answer does not suffice.