I have a dataset where in I need to allocate a value across multiple rows. Below is an example.
id pending_cnt date available_count
1 10 12/5/2022 5
1 10 12/6/2022 3
In this example the pending_cnt has to be allocated among the first 2 rows based on descending order of date and generate 3 new columns(available/not available/status) and a new row has to be inserted with a missing allocation
Expected output
id pending_cnt date available_count available not_available status
1 10 12/6/2022 5 5(out of 10, 5 is available, remaining 5) 0 AVAILABLE
1 10 12/5/2022 3. 3(out of remaining 5, 3 is available, remaining 2) 0 AVAILABLE
1 10 NULL 0 0 2 NOT AVAILABLE
Here is another example
id pending_cnt date available_count
1 10 12/6/2022 5
1 10 12/5/2022 5
Expected output is just 2 rows
id pending_cnt date available_count available not_available STATUS
1 10 12/6/2022 5 5 0 AVAILABLE
1 10 12/5/2022 5 5 0 AVAILABLE
Any help would be appreciated.
Thanks in advance