Forecast/estimate additional records

[Emir]

Hello,

Basically, I am dealing with a database that has customer [b]subscriptions[/b] to [b]services[/b]. each [b]subscription[/b] has a [u]due date[/u] for their next payment. Each [b]service[/b] has a monthly [u]occurence[/u] field that contains a number for what the payment interval is (1 is monthly, 3 is quarterly, 12 is yearly, etc), and it also the has the payment [u]amount[/u] for each [b]service[/b].

The problem I am having is that I can't figure out how to forecast the data correctly. I can create a query perfectly fine that lets me see [i][b]by month[/b][/i], how much would be due [i]currently[/i]; however, I cannot, for the life of me, figure out how I can forecast the data: I need the [u]amount[/u] for a [u]due date[/u] in January to also be added to also be added to the total in April, for example.

Basically the report that I want generated would appear like this:

[table][tr][td]MONTH NAME[/td][td]TOTAL DUE[/td][/tr]
[tr][td]January[/td][td]2314.00[/td][/tr]
[tr][td]February[/td][td]3251.00[/td][/tr]
[tr][td]March[/td][td]4251.00[/td][/tr]
[tr][td]And so on for a whole year[/td][/tr]
[/table]

I can get this report to generate just fine with the current [u]due dates[/u], but I somehow need to be able to forecast the next payments into the proper months. A quarterly payment that has a [u]due date[/u] in January, needs to also be added to April, July, and October's totals.

If someone could help me from ripping my hair out, I would appreciated it GREATLY! Is this even possible?

Thank you,
Emir

[artacus]

Now is the due_date a moving target? So say someone signs up and first payment is on Nov 12th, after that date is past, does it change to Dec 12 then Jan 12th; or does it stay Nov 12th for ever? If it changes every payment, how do you know when they are done paying? ie. you sign up for 1 year and you've got 6 months left, how do you know that?

[artacus]

Ok, I had to jump thru a bit of a hoop to get this to work. I needed each entry joined 13 times to values 0 thru 12 (for each month) so I just joined it to another table that I know has these values... maybe someone else will have a more elegant solution.
[code]
SELECT sub.id, srv.amount, DATE_ADD(sub.due_date, INTERVAL (srv.occurance * n.id) MONTH) AS pmt_on
FROM subscriptions AS sub
JOIN services AS srv ON sub.service_id = srv.id
JOIN nums AS n ON n.id BETWEEN 0 AND 12 --just joining 0 - 12 here
WHERE  DATE_ADD(sub.due_date, INTERVAL (srv.occurance * n.id) MONTH) < DATE_ADD(NOW(), INTERVAL 13 MONTH)[/code]

I'd use this in a subquery then for something like this:
[code]
SELECT DATE_FORMAT(sq.pmt_on, '%m-%Y') AS the_month, SUM(sq.amount) AS tot_payments,
COUNT(sq.id) AS num_payments
FROM (
...code above...
) AS sq
GROUP BY MONTH(sq.pmt_on)[/code]

[Emir]

The subscription table also has a [u]begin date[/u] as well as a [u]due date[/u]. I am guessing that after the payment is received that someone manually (or a script) changes the [u]begin date[/u] and the [u]due date[/u] of the [b]subscription[/b] to the next interval. Say someone has a [u]begin date[/u] of 01-04-2006 and their payment is [u]due[/u] 04-04-2006. I am guessing that on the [u]due date[/u] or when the payment is received, the data is changed to having the [u]begin date[/u] of 04-04-2006 and the [u]due date[/u] of 07-04-2006. This is all assuming that the [b]subscription[/b] is for a [b]service[/b] that has a quarterly [u]occurence[/u].

Thanks for the SQL, I'll try it to see if it works!

[artacus]

You didn't mention an end_date so I'm assuming that subscriptions are a fixed length of 1 year. Ok, this doesn't change much just the where clause. Instead of
[code]
DATE_ADD(sub.due_date, INTERVAL (srv.occurance * n.id) MONTH) < DATE_ADD(NOW(), INTERVAL 13 MONTH)
-- use --
DATE_ADD(sub.due_date, INTERVAL (srv.occurance * n.id) MONTH) BETWEEN sub.begin_date AND  DATE_ADD(sub.begin_date, INTERVAL 1 YEAR)[/code]

[Emir]

Hmm, what is the "nums" table. As far as I know, I don't have a table equivalent to "nums."
Thanks.

[artacus]

[quote author=Emir link=topic=119350.msg488737#msg488737 date=1166604413]
Hmm, what is the "nums" table. As far as I know, I don't have a table equivalent to "nums."
Thanks.
[/quote]

[quote]Ok, I had to jump thru a bit of a hoop to get this to work. I needed each entry joined 13 times to values 0 thru 12 (for each month) so I just joined it to another table that I know has these values.[/quote]