Postgresql update using CTE and priority

Question

I have a employee leave balance table as follows

emp_code	leave_type	yearmonth	Balance	Priority
1	PL	202205	2	0
1	SL	202205	1	1
2	PL	202205	3	0
2	SL	202205	1	1
3	PL	202205	1	0
3	SL	202205	1	1

and a Attendance Table as follows

emp_code	date	yearmonth	Attendance	Leave
3	2022年05月01日	202205	1
3	2022年05月02日	202205	1
3	2022年05月03日	202205	1
1	2022年05月01日	202205	0
1	2022年05月02日	202205	0
1	2022年05月03日	202205	0
1	2022年05月04日	202205	0
2	2022年05月01日	202205	1
2	2022年05月02日	202205	1

I just wanted to update the attendance table with the respective leave (based on the priority and availability) if the attendance field value is 0

For eg: employee 1 have 3 leave balance and 4 days absent

After the update, the records for emp_code 1 in attendance should be as follows

emp_code	date	yearmonth	Attendance	Leave
1	2022年05月01日	202205	0	PL
1	2022年05月02日	202205	0	PL
1	2022年05月03日	202205	0	SL
1	2022年05月04日	202205	0

I know, we can do this through SP or function. But my company policy does not allow me to create SP or functions (I can update this via my backend code, but there are millions of records there to be updated so I am worried about the performance)

I wonder, is there any ways to achieve this in PG using CTE/Window function/any other means ? Fiddle https://www.db-fiddle.com/f/4jyoMCicNSZpjMt4jFYoz5/4953

Pg version :12

Thanks

• Akina: Enumerate according to the priority (window SUM(Balance) for employee leave balance table and ROW_NUMBER() for attendance table) and use this enumeration as additional joining condition.
• Biju Soman: @Akina can you share a sample code please
• Akina: Provide your data sample as CREATE TABLE + INSERT INTO.
• Biju Soman: @Akina here is the fiddle db-fiddle.com/f/4jyoMCicNSZpjMt4jFYoz5/4952

Answer 1

WITH
cte1 AS (
 SELECT *, SUM(balance) OVER (PARTITION BY emp_code ORDER BY priority) cum_sum
 FROM emp_leave_balance
),
cte2 AS (
 SELECT *, ROW_NUMBER() OVER (PARTITION BY emp_code ORDER BY date) rn
 FROM attendance
)
SELECT cte2.emp_code, 
 cte2.date,
 cte2.yearmonth,
 cte2.attendance,
 cte1.leave_type
FROM cte2
LEFT JOIN cte1 ON cte2.emp_code = cte1.emp_code
 AND cte2.rn <= cte1.cum_sum
 AND cte2.rn > cte1.cum_sum - cte1.balance
WHERE cte2.attendance = 0
ORDER BY emp_code, date;

https://dbfiddle.uk/?rdbms=postgres_14&fiddle=b35c63631a0808e9007d61bc502352a7