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