Amortised financial reports - average daily amount

[gregg]

The problem in a nutshell:

I work for an online service provider whose products allow access for a customer for a set period of time.

I can get sql results for our product purchases which comprise the following information:

product id, purchase amount, product period (days), date of purchase

What the finance team want is an amortised financial report whereby for each calendar day, the total amount of the all products active over that period. So for example a product for 3 months, for simplicities sake say 90 days. If the product cost £90 then the amortised amount is £1 per day from the start and end date of that product subscription.

If there were two identical products with same start and end date period then for that 3 month period, the daily amortized amount would be £2. What is wanted is a total amount for each calendar day based on the above calculation.

Just using the community edition jasper server and ireports 3.5 at this point in time (shouldn't be a hinderance to being able to develop a solution though I hope).

Thanks in advance for any suggestions.

EDIT: DUPLICATE ACTIVE POST IS located jasperforge.org/plugins/espforum/view.php

Post Edited by gregg at 12/02/2009 09:03

Post Edited by gregg at 12/02/2009 09:04

[spera]

Here's a thought:

Create a dummy table with just one column: DAY_COUNT (day_no number).  This table should contain 366 records (day_no = 1,2,3,4,...366). For simplicity's sake we'll assume you'll only want to generate these reports for at most a year.

Then, let's say you want to generate the report from: 1 jan 2009 (initial_date) - 1 march 2009 (initial_date + 59).

You join the DAY_COUNT table with YOUR_TABLE(product_id, amount, start_date, period) and select the following:

initial_date + day_no,  --> date for which you compute the amount

sum((amount/period)) price_per_day --> the sum for that day

where (initial_date + day_no) between start_date and (start_date +period) --> join condition

and day_no < 59  --> limited to 59 days from initial_date

group by initial_date + day_no ;

 

It's a little rough around the edges, but in theory it seems like it should work.

In practice, depending on the amount of data involved, the hardware on your database server, and how many times a day you want to run it, this may crash your application.

Or you could use Crosstabs (see the demo sample Crosstabs) instead of Grouping. But I'm guessing you have more products than you could fit on an A4 page, so this probably won't do.

 

Hope that helps

 

 

 

[gregg]

Thanks Spera,

 

I see where you are coming from. I will investigate this potential way of solving it and post up a response in next few days on how I am going.