Hi,
Can anyone help me with this problem please.
I have a table as below:
| Work Order No | Work Order Date | Payment Received Date | Fiscal Year | Payment Value |
|---|
| A001 | 10/03/2015 | 10/03/2015 | 15 | 5000 |
| A001 | 10/03/2015 | 15/04/2015 | 16 | 3000 |
| A002 | 10/07/2016 | 10/07/2015 | 16 | 3000 |
| A002 | 10/07/2016 | 10/06/2016 | 17 | 7000 |
| A002 | 10/07/2016 | 11/07/2016 | 17 | 5000 |
| A003 | - | 10/05/2016 | 17 | 6000 |
| A003 | - | 11/05/2017 | 18 | 1000 |
Now that I wanted Current Fiscal Year and Carry Over Fiscal Year for each Work Order Received. Wherein Current fiscal year is the fiscal year against the work order date. If work order date is not there then lease payment received date should be taken as a work order date and the fiscal year against that row should be current fiscal year.
Now when I Select a work order (or a list of work orders) I should get current fiscal year, carry over fiscal year and payment received in current year and payment received in carry over fiscal year.
Output:
| Work Order No | Current Fiscal Year | Carry Over Fiscal Yr | Current Yr Payment Received Value | Carry Over Yr Payment Recieved |
|---|
| A001 | 15 | 16 | 5000 | 3000 |
| A002 | 16 | 17 | 3000 | 12000 |
| A003 | 17 | 18 | 6000 | 1000 |
