SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Guru
    Join Date
    Sep 2004
    Location
    Provo, UT
    Posts
    858
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Forecasting payments for the rest of the month

    I have a subscription based website and store all recurring monthly payments in the table called payments. The following query will return the day of the month, total payments made as well as total dollars received.

    select DATE_FORMAT(payDate, '%d') as dayofmonth, count(*) as totalPayments, sum(amount) as totalAmount from payments
    where frequency = 'Monthly' group by dayofmonth limit 500;

    I need some help figuring out how to modify this so I get the number of payments for a given day of the month, instead of the total for that day. For example:

    04/01/2011 - 3 new payments
    05/01/2011 - 3 recurring payments and no new ones
    06/01/2011 - 1 new payment plus the 3 recurring payments
    07/01/2011 - 4 new payments plus 4 recurring payments

    So the first row returned, which is 01 for the 1st of the month, should show that I have 8 recurring payments, not a total of 19 payments.

    Thanks!
    Convert your dollars into silver coins. www.convert2silver.com

  2. #2
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,496
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    How about adding a condition to the WHERE statement checking for the given month?

  3. #3
    SitePoint Guru
    Join Date
    Sep 2004
    Location
    Provo, UT
    Posts
    858
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    But I need all months taken into consideration, not just the given month.
    Convert your dollars into silver coins. www.convert2silver.com

  4. #4
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,496
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    I don't understand. If you want to know the number of recurring payments on a certain day of a certain month, isn't it enough to take the number of payments on that day of that month?

    In your example data, the number of payments made on the 1st of July is 8, and that is the number you want.

    Or am I missing something?

  5. #5
    SitePoint Guru
    Join Date
    Sep 2004
    Location
    Provo, UT
    Posts
    858
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    The problem is that I'm using this as a forecasting tool to determine how much money will likely come in by the end of the month. In the example months that I gave in my original post the number of recurring payments is actually eight. However, my query is adding up all payments and is returning 18. As you can see, 18 is not really the number of recurring payments. It's simply the total of all payments I have ever received on the 1st, and that does not help me pinpoint how much revenue will be coming in for the days remaining in the month.
    Convert your dollars into silver coins. www.convert2silver.com


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •