Comprehensive SQL update on 2 interdependent tables

Question

I have a database with several tables that track phone calls / sms / data and premiums, and I'm trying to figure out if you can assign calls for benefits without resorting to cursors, but I cannot figure out how to structure SQL for this. I do not have any useful SQL from my attempts, since I cannot figure out how to approach it! The problem is that for me it seems like an inherently iterative process, and I can't figure it out if there is a reasonable way to translate it into a set-based approach. I examined the use of window functions, but I can’t figure out how to do this when we track aggregate totals in 2 tables and the totals are interdependent. I am trying to minimize the time to start this process and influence other queries, since we would like to restart it quite often, and the tables become quite large.

This is a simplified structure ...

Call

logs all calls

• ID
• Contractid
• ChargeGroupID
• Datetime
• Int quantity
• QuantityFromAllowments int (this is what I want to fill)
• FirstAllowanceUsedID (FK to Allowance) (this is what I want to fill in)

Reserve

What different discounts are available for each contract?

• ID
• Contractid
• Priority (1 if it should be used first, otherwise 0)
• Int quantity
• NumberUsed int (initially set to 0 - can be used to track how much is used on the go or not)

AllowanceChargeGroup

How To Use Tolerances - This is a join table with valid combinations.

• ID
• AllowanceID
• ChargeGroupID

I intentionally did not document all the details to make it simple. I hope everything will be obvious, but if not, let me know.

If I were dealing with this iterative, 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 am approaching the problem incorrectly or that it is really 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: -

• The identifier of number 1 corresponds to the identifier of account 1 (through the connection table in AllowanceChargeGroup) - QuantityFromAllowances = 100, FirstAllowanceUsedID = 1, Allowance.QuantityUsed = 100 (0 + 100)
• Call ID 2 ID Allowance ID 1, but only 400 left, so QuantityFromAllowances = 400, FirstAllowanceUsedID = 1, Allowance.QuantityUsed = 500 (100 + 400)
• Call ID 2 ID Allow Allow ID 2 (not 1 left) - QuantityFromAllowances = 500 (400 + 100), FirstAllowanceUsedID = 1 (already set above, therefore not changed), Allowance.QuantityUsed = 100 (0 + 100)
• Call ID 3 corresponds to Account ID 2 (1 left) - but only 400 remains, so QuantityFromAllowances = 400, FirstAllowanceUsedID = 2, Allowance.QuantityUsed = 500 (100 + 400).
• Call ID 4 does not correspond to any discounts, therefore no changes
• Call ID 5 does not match any discounts (all expended), so no changes
• Call ID 6 corresponds to account ID 3 Number of elements: 1 to 100, FirstAllowanceUsedID = 3, Allowance.QuantityUsed = 100 (0 + 100)

Then the tables should look like this (only changes: 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 the call table and permission table, and each update depends on the previous one.
This is not possible with just one sql statement, so you need to execute a loop.
You do not need cursors; you can set it using sequential operations in the procedure.

First of all, some declarations and preparation of 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 the main loop 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 conclusion:

 select * from [call] select * from allowance 

same as requested

Answer 2

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

Due to this unique relationship between premiums and charge groups, we can associate requests (calls) with premiums.

The idea is to list and weigh (order) each individual unit needed from calls, and list and weigh (order) each individual unit available from premiums, and finally bind them next to each other.

For example, suppose you have these 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 blow each line in n lines depending on the number of lines (so we will have 3 lines for CallID 1 and CallID 2 and 5 lines for CallID 3). During the explosion, name the rows to identify them (add two different columns with the row number for the group and for the call / manual)

 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 just attach these blasted sets to the group number (GroupRowN).
Here you can see the distribution of premiums on calls.

• CallID = 1 (first 3 lines) is completely covered by the first three lines AllowanceID = 1
• CallID = 2 (second 3 rows) partially covers the last row AllowanceID = 1 and the first (and unique) row AllowanceID = 2
• CallID = 3 (last 5 lines) is completely covered by the first 5 lines of AllowIDID = 3, which is not exhausted completely, since the last line is unsurpassed (not requested by any call)

(I added horizontal strokes to better show the distribution by CallID):

 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 results:

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

Finally, from the last output, we can obtain information for updating calls in the table of assumptions:

 CallID QtUsed FirstUsed 1 3 1 2 2 1 3 5 3 AllowanceID QtUsed 1 4 2 1 3 5 

Ok
it was a theory, now you can see some code (using the above data).

Pay attention to FN_NUMBERS (n), this is a function that returns only one column with numbers from 1 to n, you need it in your database, there are many ways to do this, just google for the “table tables” or see here .
I am using 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 

Return to 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 nN<=(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 nN <= (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 populate the @res table with the final summary data that will be used for updates.

Therefore, 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 into the temporary table and two updates, no loops, no cursors.

Answer 3

It is always difficult to answer a question without tabular constructions, sample data and the expected result. If this answer does not help you take a look at this guide on the help pages and this answer from Meta , for tips on how you could change your question.

I suspect this is a matter of grain. Since the level of detail in each table is different, quite rightly, it is difficult to combine.

I would recommend you follow this basic pattern:

• A set of call tables to match PKey in resolution.
• Join the permission.

Sort of:

 -- 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 need to do this. I did not see a place for applications, so I did not include the AllowanceChargeGroup table.

It looks like your allowance table is pulling a double duty. It contains both the terms of the contract (which probably does not change often) and the volume used (which will change more often - perhaps monthly?). I would suggest you separate these functions.

I understand the simplified model, so I apologize if the simplified answer is not enough.