SQL Puzzle | Pay amount to workers
In this puzzle we have pay the amount recursively to workers present in PDETAIL table based on the OWNER_ID.Please check the sample input and expected output.
Sample Input
PMASTER Table
OWNER_ID | OVER_AMOUNT_NO |
---|---|
123 | 400 |
234 | 300 |
PDETAIL
OWNER_ID | WORKER_ID | tobe_paid_AMOUNT_NO |
---|---|---|
123 | 555 | 200 |
123 | 666 | 300 |
234 | 777 | 200 |
Expected Output
OWNER_ID | WORKER_ID | tobe_paid_AMOUNT_NO |
---|---|---|
123 | 555 | 0 |
123 | 666 | 100 |
234 | 777 | 0 |
Script
Use below script to create table and insert sample data into it.
-- USE [master] GO /****** Object: Table [dbo].[PDETAIL] Script Date: 12/16/2017 2:54:57 PM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[PDETAIL]( [OWNER_ID] [int] NULL, [WORKER_ID] [int] NULL, [tobe_paid_AMOUNT_NO] [int] NULL ) ON [PRIMARY] GO /****** Object: Table [dbo].[PMASTER] Script Date: 12/16/2017 2:54:57 PM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[PMASTER]( [OWNER_ID] [int] NULL, [OVER_AMOUNT_NO] [int] NULL ) ON [PRIMARY] GO INSERT [dbo].[PDETAIL] ([OWNER_ID], [WORKER_ID], [tobe_paid_AMOUNT_NO]) VALUES (123, 555, 200) GO INSERT [dbo].[PDETAIL] ([OWNER_ID], [WORKER_ID], [tobe_paid_AMOUNT_NO]) VALUES (123, 666, 300) GO INSERT [dbo].[PDETAIL] ([OWNER_ID], [WORKER_ID], [tobe_paid_AMOUNT_NO]) VALUES (234, 777, 200) GO INSERT [dbo].[PMASTER] ([OWNER_ID], [OVER_AMOUNT_NO]) VALUES (123, 400) GO INSERT [dbo].[PMASTER] ([OWNER_ID], [OVER_AMOUNT_NO]) VALUES (234, 300) GO -- |
Rules/Restrictions
The solution should be should use “SELECT” statement or “CTE”.
Add your solution(s) in the comments section or send you solution(s) to pawankkmr@gmail.com
Solution – 1
-- ;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 PMASTER m INNER JOIN PDETAIL 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 c.OWNER_ID , c.WORKER_ID, c.tobePaid from CTE2 c ORDER BY OWNER_ID,lvl -- |
Output
-- /*------------------------ ;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 PMASTER m INNER JOIN PDETAIL 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 c.OWNER_ID , c.WORKER_ID, c.tobePaid from CTE2 c ORDER BY OWNER_ID,lvl ------------------------*/ OWNER_ID WORKER_ID tobePaid ----------- ----------- ----------- 123 555 0 123 666 100 234 777 0 (3 row(s) affected) -- |
Add a comment if you have any other solution in mind. I would love to learn it. We all need to learn.
Enjoy !!! Keep Learning
Pawan Khowal
Http://MSBISkills.com
Ramidi Karthik said:
Below is the one more approach using temp table. (Note: Prepared logic over notepad and not executed it)
SELECT A.OWNER_ID,B.Worker_ID,B.tobe_paid_AMOUNT_NO as AmountToBePaid,A.OVER_AMOUNT_NO as AmountHave
,ROW_NUMBER()OVER (PARITION BY A.OWNER_ID ORDER BY A.OWNER_ID)RN
INTO #TEMP
FROM PMaster A
JOIN PDETAIL B
ON A.OWNER_ID = B.OWNER_ID
SELECT T2.OWNER_ID,T2.WORKER_ID,CASE WHEN A.amountHave-A.Cum_Sum>0 THEN 0 ELSE ABS(A.amountHave-A.Cum_Sum)Tobe_paid_AMOUNT_NO
FROM (SELECT T2.OWNER_ID,T2.WORKER_ID,A.amountHave,SUM(T1.AmountToBePaid)Cum_Sum
FROM #TEMP T1
JOIN #TEMP T2
ON T1.Owner_ID = T2.Owner_Id
AND T1.RN<=T2.RN
GROUP BY T2.OWNER_ID,T2.WORKER_ID,A.amountHave)A
LikeLike