SQL question

We are on SQL Server 2012.
We have three tables. TB_MASTER, TB_DETAIL and TB_FINAL
   
TB_MASTER	
OWNER_ID	OVER_AMOUNT_NO
123	      400
234	      300

TB_DETAIL
OWNER_ID	WORKER_ID	tobe_paid_AMOUNT_NO
123	  555	             200
123	  666	             300
234	  777	             200

Open in new window


I want the TB_FINAL to have the inserted rows and TB_MASTER to have updated rows as below:
TB_FINAL		
OWNER_ID	WORKER_ID	tobe_paid_AMOUNT_NO
123	555	              0
123	666	             100
234	777	             100

TB_MASTER
OWNER_ID	OVER_AMOUNT_NO
123	     0
234	     200

Open in new window


The explanation to the desired output is as follows:
* Owner 123 owes 400 to the agency and so, the amount to be paid 200, to worker  555 will be deducted from 400.
* After the above deduction, Owner 123 still owes 200 (400-200). Since Worker 666 needs to be paid 300, he will be paid 100 (300 - 200).
* Owner 234 owes 300 to the agency and the worker 777 is to be paid 200. So, worker 234 gets paid only 100.
* TB_MASTER table needs to update the outstanding overpayment for the Owners.

Can someone let me know of an easy way of achieving this?
pvsbandiAsked:
Who is Participating?
 
Pawan KumarDatabase ExpertCommented:
Please use this solution as per your requirement. You need to use the recursive approach here. Please use and let us know in case any changes required.

--

IF OBJECT_ID('tempdb..#TempPawan1') IS NOT NULL
    DROP TABLE #TempPawan1

;WITH CTE AS
(
	SELECT m.OWNER_ID,m.OVER_AMOUNT_NO payingamt , d.WORKER_ID, d.tobe_paid_AMOUNT_NO topay
	 , ROW_NUMBER() OVER (PARTITION BY m.OWNER_ID ORDER BY m.OWNER_ID) rnk FROM TB_MASTER m
	INNER JOIN TB_DETAIL d on m.OWNER_ID = d.OWNER_ID
)
,CTE1 AS
(
	SELECT * FROM CTE WHERE rnk = 1
)
,CTE2 AS
(
	SELECT OWNER_ID, payingamt AmtChanging , payingamt - topay  CurrentAmount ,WORKER_ID,topay,rnk 
	, 1 lvl	,CASE WHEN payingamt >= topay THEN 0 ELSE ABS(payingamt - topay) END tobePaid
	FROM CTE1 m
	UNION ALL
	SELECT c.OWNER_ID , c1.CurrentAmount  AmtChanging, CurrentAmount - c.topay CurrentAmount , c.WORKER_ID 
	,c.topay,c1.rnk+1 
	, lvl+1 lvl , CASE WHEN CurrentAmount >= c.topay THEN 0 ELSE ABS(CurrentAmount - c.topay) END tobePaid FROM CTE c 
	INNER JOIN CTE2 c1 ON c1.OWNER_ID = c.OWNER_ID AND c1.rnk < c.rnk
)
SELECT * ,tobePaid+CurrentAmount OVER_AMOUNT_NO 
, ROW_NUMBER() OVER (PARTITION BY OWNER_ID order by lvl desc) rnk1 
INTO #TempPawan1
from CTE2	

--

Open in new window


/*Updating the TB_FINAL Table */

--

UPDATE F
SET F.tobe_paid_AMOUNT_NO = T.tobePaid
FROM TB_FINAL F
INNER JOIN #TempPawan1 T 
ON 
		T.OWNER_ID = F.OWNER_ID 
	AND T.WORKER_ID = F.WORKER_ID


--

Open in new window


OUTPUT of TB_FINAL Table

--

/*------------------------
SELECT * FROM TB_FINAL
------------------------*/
OWNER_ID    WORKER_ID   tobe_paid_AMOUNT_NO
----------- ----------- -------------------
123         555         0
123         666         100
234         777         0

(3 row(s) affected)

--

Open in new window


/*Updating the TB_MASTER Table */


--

UPDATE M
SET M.OVER_AMOUNT_NO = T.OVER_AMOUNT_NO
FROM TB_MASTER M 
INNER JOIN #TempPawan1 T  
	ON T.OWNER_ID = M.OWNER_ID
WHERE rnk1 = 1


--

Open in new window


OUTPUT of TB_FINAL Table

--

/*------------------------
SELECT * FROm TB_MASTER
------------------------*/
OWNER_ID    OVER_AMOUNT_NO
----------- --------------
123         0
234         100

(2 row(s) affected)


--

Open in new window

0
 
Mark WillsTopic AdvisorCommented:
Back in a moment, will populate some test data and go through the different ways...
0
 
Mark WillsTopic AdvisorCommented:
Ummm, not sure i understand the maths.

234 has $300
234 Owes 777 $200

Therefore FINAL should be
 234 777  $0

and Master should be
234 $100

Am I missing something ?
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
pvsbandiAuthor Commented:
Hi Mark,

   This owing the amount, is to the Agency; not to the workers.
So, when the agency is paying the workers, it will look at the existing overpayment and deduct from the amount to paid to the workers and pay the rest to them.
0
 
Mark WillsTopic AdvisorCommented:
OK, that clarifies worker 666 - the agency is kicking in the shortfall from overpaid to agency (as shown in Master)

But if 234 has overpaid $300, and the claim from 777 is only $200, then how do we get the agency paying $100 ?

Maybe 234 had overpay of $200 and 777 was owed $300 - then it makes sense.

Apologies for the confusion....
0
 
pvsbandiAuthor Commented:
You are right. My apologies for the confusion.
For worker 777, shouldn't get paid anything and the outstanding overpayment for worker 234 should be 100
0
 
Pawan KumarDatabase ExpertCommented:
So what is the final OUTPUT for TB_FINAL and TB_MASTER?
0
 
Mark WillsTopic AdvisorCommented:
If you are SQL2012, then we can use window functions for aggregates...

For example :

;with CTE as
(SELECT m.owner_id ,
        m.over_amount_no ,
        d.WORKER_ID ,
        d.tobe_paid_AMOUNT_NO ,
        SUM(d.tobe_paid_AMOUNT_NO) OVER ( PARTITION BY d.owner_id ORDER BY d.worker_id ) AS RunningTotal 
from TB_MASTER m
inner join TB_DETAIL d on m.owner_id = d.OWNER_ID
) 
select Owner_id, over_amount_no, WORKER_ID, tobe_paid_AMOUNT_NO, RunningTotal
       ,case when RunningTotal > over_amount_no then RunningTotal - over_amount_no else 0 end as StillOwing
from CTE
ORDER BY 1 , 2 , 3;

Open in new window


It also makes your TBL_Final a different beast. It could be a view...

CREATE VIEW VW_Final as
with CTE as
(SELECT m.owner_id ,
        m.over_amount_no ,
        d.WORKER_ID ,
        d.tobe_paid_AMOUNT_NO ,
        SUM(d.tobe_paid_AMOUNT_NO) OVER ( PARTITION BY d.owner_id ORDER BY d.worker_id ) AS RunningTotal 
from TB_MASTER m
inner join TB_DETAIL d on m.owner_id = d.OWNER_ID
) 
select Owner_id, WORKER_ID, SUM(case when RunningTotal > over_amount_no then RunningTotal - over_amount_no else 0 end) as tobe_paid_AMOUNT_NO
from CTE
group by owner_id, WORKER_ID
GO


select * from vw_final

Open in new window


But before we finish, is there any kind of priority for payments ? Or is it simply by WORKER_ID. I would imagine that we should payout all those amounts that can be paid by the OVER_AMOUNT_NO and that way we might be able to minimise the number of payments the Agency has to do. In which case, instead of order by worker_id  it could become order by tobe_paid_AMOUNT_NO.

Any way the last step is to fix up TB_MASTER. And for that we simply use the CTE to generate the values we need and instead of doing the select at the end, we use update instead. Does that make sense ?

Do you need the query or should we modify that a little bit so we can use it as the update ?

Or we can simply use a simpler CTE :
;with cte_update as
(select m.owner_id, sum(d.tobe_paid_amount_no) as totalpaid
 from TB_MASTER m
 inner join TB_DETAIL d on m.owner_id = d.OWNER_ID
 group by m.owner_id
 )
 UPDATE TB_MASTER   
       set OVER_AMOUNT_NO = Case when OVER_AMOUNT_NO > totalpaid then OVER_AMOUNT_NO - totalpaid else 0 end 
from cte_update u
where TB_MASTER.owner_id = u.owner_id

Open in new window


Thoughts ? And will need thorough testing - I have only used the samples you provided, and reckon we need quite a bit more test data to make sure it covers all the different possibilities.
0
 
pvsbandiAuthor Commented:
Thank you both!
0
 
Mark WillsTopic AdvisorCommented:
A pleasure to have been able to help.

Just a quick note, be careful with recursive CTE's they can get a bit 'greedy' for resources. Also note that you may need the maxrecursion 0 option, using some of my dummy data, it generated errors.

This is nothing against Pawan's solution, I was working on a similar approach until I saw the SQL2012. More so, just a word of warning about recursion.

Cheers, and happy SQL coding :)
0
 
pvsbandiAuthor Commented:
Thanks Mark!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.