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”.

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