monthly payments

[mrhankey]

hi,

i was wondering is someone could give me an example of how i could do this in sql query.

basically the application has a product table and when they are inserting they have the option to select monthly payments.

put in the start date so say 30/3/2013

what i need to do is show every month the payment to be paid then they can mark as paid and it will then go into the database table as paid. the thing is though i need the query to show this every month based on start date and remember there will be nothing in the payments table until it is paid. also the tricky part is if the user has not logged in for a few months it would need to show all months from the start date until current date.

thanks again

[mrhankey]

i have tried this in my query:

DATE_ADD('TrailStartDate', INTERVAL Now() MONTH) as MonthlyDate

but no joy. just trying to display the months between trailstartdate and currentdate so i can display in a table for users to mark as paid or not.

any thoughts?

thanks again

[adam_k]

SELECT Field1, dateadd(m, num, TrailStartDate) AS date_due
FROM table
CROSS JOIN
(SELECT a.id + b.id AS num FROM (SELECT 1 AS id UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6
UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 UNION SELECT 0) AS a
CROSS JOIN (SELECT 0 AS id UNION SELECT 10 UNION SELECT 20 UNION SELECT 30 UNION SELECT 40 UNION SELECT 50
UNION SELECT 60 UNION SELECT 70 UNION SELECT 80 UNION SELECT 90 ) AS b ) AS x
WHERE dateadd(m, num, TrailStartDate) <= getdate() --you might want to strip out the time
and --you need to figure out how to eliminate the paid months

Unfortunately I didn't have enough info to supply all the criteria (ie how you've named the fields and how do you determine if it's paid).

I haven't tested this and it's with MS SQL syntax, as that's easier for me. If you need help, provide the info and I'll rewrite it for MySQL

[adam_k]

I guess I forgot to mention that the above is limited to 99 months. It can be edited to support more, but I doubt anybody is willing to sell with more than 99 monthly payments - unless it's a house or something.

[mrhankey]

thanks remember it is not in the database until its paid so will this work?

[mrhankey]

SELECT product.PolicyNumber, clients.ClientID, DATE_FORMAT(`TrailStartDate`,'%b') as MonthlyTrail
FROM trail join product on trail.product_ProductID = product.ProductID join clients on product.clients_ClientID = clients.ClientID
WHERE product.TrailYesNo = 'Y'

[adam_k]

thanks remember it is not in the database until its paid so will this work?

Yes. It calculates the dates based on TrailStartDate and a series of numbers from 0 to 99.