CODEWITHSUNDEEP
mysqlsqldatetimedatemin
user682417
user682417

Reputation: 1518

calculating the total money up to the mention date using mysql query

                   membership table
                         membership start date 2011-01-10
                         membership end date 2012-09-08
                         membership monthly amount £120.00
                         member_Id

                    member table
                       member_id

the member will pay the money on 10 th of every month...can i get the total amount that the member has paid up to this date 2011-05-15 and is it possible using mysql query.

this is the sql query suggested by you guys 

                    SELECT TIMESTAMPDIFF(MONTH, membertomships.memberToMship_StartDate, MIN(membertomships.memberToMship_EndDate,'2011-7-06'))* memberToMship_ChargePerPeriod FROM membertomships WHERE membertomships.member_Id = '1';

but it was giving error at memberToMship_EndDate,'2011-7-06' between dates memberToMship_EndDate and this date '2011-7-06'

would you pls explain why it was giving error

Upvotes: 1

Views: 2011

Answers (3)

rahularyansharma
rahularyansharma

Reputation: 10755

SELECT TIMESTAMPDIFF(MONTH, start_date, MIN(end_date,'2011-05-15')) * amount from membership where membershipId=@memberdshipId

for include current date also.......

 SELECT TIMESTAMPDIFF(MONTH, start_date, MIN(end_date,'2011-05-15')) * amount ,TIMESTAMPDIFF(MONTH, start_date, MIN(end_date,CURDATE())) * amount from membership where membershipId=@memberdshipId

this is working fine for me in ms sqlserver please change for mysql syntex 

select datediff(Month,startdate,enddate)*amount as Enddateamount,
datediff(Month,startdate,'12/6/2010')*amount as amountongivendate,
datediff(Month,startdate,getdate())*amount as amounttoday
 from membership

sql server screen shot

Upvotes: 1

Gedrox
Gedrox

Reputation: 3612

I guess you have only subscription data not all payment table, right? Then you need such statement

SELECT TIMESTAMPDIFF(MONTH, '2011-01-10', MIN('2012-09-08', '2011-05-15')) * 120.00

Of course you should replace the constants with column names and parameters.

This of course assumes that the payment day 10 is dependent on the subscription start date and the customer has paid on time. :)

Upvotes: 0

Raoul
Raoul

Reputation: 3889

Your question isn't very clear, I don't think you've explained the schema properly. If you want to get a sum of everything each has paid:

select sum(amount) from membership group by member_id

Feel free to add a where clause should you wish to restrict it by date or member_id(s):

http://dev.mysql.com/doc/refman/5.0/en/select.html

Upvotes: 0

Related Questions