I have a employee leave balance table as follows

and a Attendance Table as follows

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

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

CodePudding user response：

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

1. Expand the leave balance for the month into rows using generate_series() and assign row numbers based on the priority
2. Assign row numbers to absences within a month
3. 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