Resolved: Postgres update table using CTE with 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
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	Leave
1	2022-05-01	202205	PL
1	2022-05-02	202205	PL
1	2022-05-03	202205	SL
1	2022-05-04	202205

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 ?
here is a fiddle https://www.db-fiddle.com/f/4jyoMCicNSZpjMt4jFYoz5/4952
Thanks

Answer:

If the last attendance record in your fiddle is in error and removed, then my approach would be:

Expand the leave balance for the month into rows using generate_series() and assign row numbers based on the priority
Assign row numbers to absences within a month
Calculate changes by left join from absences to leave records

with leave_rows as (
  select b.*, 
         row_number() over (partition by emp_code, yearmonth
                                order by priority) as use_order
    from emp_leave_balance b
         cross join lateral generate_series(1, b.balance, 1)
), absence_rows as (
  select a.*, 
         row_number() over (partition by emp_code, yearmonth
                                order by date) as use_order
    from attendance a
   where attendance = 0
), changes as (
  select a.emp_code, a.date, a.yearmonth, a.attendance, l.leave_type
  from absence_rows a
       left join leave_rows l
         on (l.emp_code, l.yearmonth, l.use_order) = 
               (a.emp_code, a.yearmonth, a.use_order)
)
update attendance 
   set leave = c.leave_type
  from changes c
 where (c.emp_code, c.date) = (attendance.emp_code, attendance.date)
;

Your updated fiddle

If you have better answer, please add a comment about this, thank you!

Resolved: Postgres update table using CTE with priority

Resolved: how to get and read an xml file in a zip file using xml.etree

Resolved: The ‘Access-Control-Allow-Origin’ header contains multiple values ‘, ’, but only one is allowed. cors error not resolving

Resolved: SQLSTATE[42S02]: Base table or view not found: 1146 Table ‘crm.email’ doesn’t exist (Connection: mysql, SQL: select count(*) as aggregate from `email`

Leave A Reply