value_over_time_query.sql

SELECT MAKEDATE(YEAR(CURRENT_DATE()),DAYOFYEAR(CURRENT_DATE()) - rn.id + 1) as `date`,
(
      SELECT SUM(ROUND(rp.amount/(rp.interval * 30) * IF(spay.service_end_date,1,0),2)) as dv
      FROM subscribers sub
      JOIN subscriptions s ON s.subscriber_id = sub.id AND s.is_suspended = false AND s.autobill_failure_count = 0
      JOIN subscription_plans sp ON sp.id = s.subscription_plan_id AND sp.id NOT IN (6,12)
      JOIN renewal_periods rp ON s.renewal_period_id = rp.id
      JOIN subscription_payments spay ON spay.subscription_id = s.id 
      WHERE s.deleted_at IS NULL
      AND DATE(spay.service_begin_date) <= `date` 
      AND DATE(spay.service_end_date) >= `date`
      AND sub.subdomain NOT LIKE 'nb%'
) as daily_value 
FROM reporting.numbers rn
WHERE rn.id > 0 AND rn.id < 31
ORDER BY rn.id DESC;

yields:
+------------+-------------+
| date       | daily_value |
+------------+-------------+
| 2010-11-22 |      468.79 | 
| 2010-11-23 |      470.11 | 
| 2010-11-24 |      473.35 | 
| 2010-11-25 |      472.29 | 
| 2010-11-26 |      479.02 | 
| 2010-11-27 |      479.25 | 
| 2010-11-28 |      486.79 | 
| 2010-11-29 |      479.75 | 
| 2010-11-30 |      495.20 | 
| 2010-12-01 |      492.60 | 
| 2010-12-02 |      491.06 | 
| 2010-12-03 |      492.97 | 
| 2010-12-04 |      498.64 | 
| 2010-12-05 |      497.78 | 
| 2010-12-06 |      500.44 | 
| 2010-12-07 |      498.92 | 
| 2010-12-08 |      509.86 | 
| 2010-12-09 |      509.72 | 
| 2010-12-10 |      510.84 | 
| 2010-12-11 |      515.33 | 
| 2010-12-12 |      518.92 | 
| 2010-12-13 |      520.00 | 
| 2010-12-14 |      515.25 | 
| 2010-12-15 |      525.30 | 
| 2010-12-16 |      530.52 | 
| 2010-12-17 |      531.95 | 
| 2010-12-18 |      541.85 | 
| 2010-12-19 |      552.22 | 
| 2010-12-20 |      555.26 | 
| 2010-12-21 |      559.42 | 
+------------+-------------+
30 rows in set (5.98 sec)