wizzardmr42 wizzardmr42 - 18 days ago 6
SQL Question

Complex SQL Update on 2 interdependent tables

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

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