J
JMorrell
This isn't going to be an easy explanation, but here goes... Writing it out
helps me formulate what I need, what I have, and how it’s derived. I’m
having a difficult time writing the sql to pull this data together.
What I need:
To write a report showing beginning monthly balance, leave used, leave
accrued, and ending balance.
What I have:
1. The ‘original’ leave balance,
2. All leave taken,
3. All leave accrued,
4. The reporting month.
How it’s all derived:
Beginning monthly balance = ((beginning prior month balance – leave taken
before current month) + leave accrued in prior month)
Leave taken = sum of leave used in month
Leave accrued = leave factor rate (e.g. 12 hours annual leave/month)
Ending monthly balance = ((beginning monthly balance – leave used) + accrued
leave)
Example:
The month-end report for September should show a September beginning
balance, all leave used, and an ending balance.
((‘original’ balance – all leave used prior to September) + all leave
accrued prior to September) – ((sum of all leave used in month of September)
+ leave accrued for September)
Input method & format:
1. A very user friendly, no-brain button that takes care of everything. I
see a form with a single button; clicking the button will capture the current
month (e.g. October) which will run the report for every employee for the
month of September.
2. Or, the user can enter parameters for employee (or all employees) and
month (or all months) for which the report should be run.
When I was programming in ABAP for a large SAP application, I could write
the needed data to temporary tables, manipulate the data and write it all out.
Any thoughts as to the best way to write the sql for this?
tia
helps me formulate what I need, what I have, and how it’s derived. I’m
having a difficult time writing the sql to pull this data together.
What I need:
To write a report showing beginning monthly balance, leave used, leave
accrued, and ending balance.
What I have:
1. The ‘original’ leave balance,
2. All leave taken,
3. All leave accrued,
4. The reporting month.
How it’s all derived:
Beginning monthly balance = ((beginning prior month balance – leave taken
before current month) + leave accrued in prior month)
Leave taken = sum of leave used in month
Leave accrued = leave factor rate (e.g. 12 hours annual leave/month)
Ending monthly balance = ((beginning monthly balance – leave used) + accrued
leave)
Example:
The month-end report for September should show a September beginning
balance, all leave used, and an ending balance.
((‘original’ balance – all leave used prior to September) + all leave
accrued prior to September) – ((sum of all leave used in month of September)
+ leave accrued for September)
Input method & format:
1. A very user friendly, no-brain button that takes care of everything. I
see a form with a single button; clicking the button will capture the current
month (e.g. October) which will run the report for every employee for the
month of September.
2. Or, the user can enter parameters for employee (or all employees) and
month (or all months) for which the report should be run.
When I was programming in ABAP for a large SAP application, I could write
the needed data to temporary tables, manipulate the data and write it all out.
Any thoughts as to the best way to write the sql for this?
tia