I have a subscription table which holds information about how often customer has to pay for a subscription and how much.
F.x. can a customer have subscription that can be paid monthly, quarterly, every half year or once a year.
In the table there's a field called periodlength to represent these i.e.: 1, 3, 6, 12. And there's a field that holds the subscriptionprice per month
I need to create a report that requests users to enter in a startingdate and an endingdate.
In between these two dates I have to find out if a subscription has been made, i.e. if a user writes 010105..310205 and a subscription has a quarterly payment, then the price should not be shown (if the payment is made on 010405), but if a subscription has a monthly payment, then obviously the report needs to show the total price for the 2 months given.
So my problem is that I have absolutely no idea how to write this piece of code, as I'm really finding it hard coding with dates.
Anyone that can help me :shock: !
Thanks Ann
0
Comments
We have a subscription/contract add on and the date calculation can get very complicated.
Basicly what we did is to make a period date table in which we store the payment dates, this table you can filter out and if you count the records you know how many payments can be made.
This table can be populated defining period codes with date formula's so customers can decide for themselves what kind of payment terms they have, they can also define 2 monthts payments/ 2 weeks, 1 week etc.
You can also make a date table temporary and then filter it out.
Succes with solving this issue!
But what you are saying is that I should still be able to calculate the price by making a temporary table?
Ann
1-1-05
1-2-05
1-3-05
When you filter this table
1-1-05..1-2-05 you can count 2 records, so 2 payments.
You can also use the date table from navision, but then you have a problem with half year payments.