Mysql: dates and calculations help needed

Databases
1

Hi there, hoping someone can help me wrap my head around this query. I need to pull the data from one table and eventually graph the results.
Sample Table Data:
±---------±-----------±-----------+
| tag | mileage | date |
±---------±-----------±-----------+
| G1-222 | 45648 | 2010-06-01 |
| G1-222 | 46897 | 2010-07-01 |
| G1-222 | 47851 | 2010-08-01 |
| G2-321 | 12013 | 2010-06-01 |
| G2-321 | 13478 | 2010-07-01 |
| G2-321 | 14897 | 2010-08-01 |
| G2-321 | 15473 | 2010-09-01 |
| G2-4532 | 100 | 2010-06-01 |
| G2-4532 | 250 | 2010-07-01 |
±---------±-----------±-----------+

I would output for one tag number, a graph for the mileage that car uses each month. I’d like to graph it by month for the previous 12 months, and each line in the graph would be the miles driven for that month, so the calculation would take one month’s mileage (odometer reading) - the previous months mileage and output the miles. I’m just not sure how to write the query to do all those calculations and then output.
Thanks for any help.

2

you want always to specify a single tag number, right?

3

Yes. I have another query on the web page that pulls up details about a certain Tag number vehicle. this would be a chart on the page showing the mileage. It would all be tied to a specific tag.

4

your sample data looks a little wonky (it appears that mileage reading are taken only on the 1st of the month), but BTAIM…

the first challenge is getting the previous 12 months – i’ll assume the current month is to be excluded out :wink:

CURRENT_DATE - INTERVAL DAYOFMONTH(CURRENT_DATE)-1 DAY gives the first day of the current month

  • INTERVAL 1 YEAR gives the first day of the month 12 months earlier

so we generate 12 months like this –


SELECT CURRENT_DATE - INTERVAL DAYOFMONTH(CURRENT_DATE)-1 DAY
                    - INTERVAL 1 YEAR
                    + INTERVAL m.n MONTH AS month_day
  FROM ( SELECT 0 AS n
         UNION ALL SELECT 1 UNION ALL SELECT 2 
         UNION ALL SELECT 3 UNION ALL SELECT 4 
         UNION ALL SELECT 5 UNION ALL SELECT 6 
         UNION ALL SELECT 7 UNION ALL SELECT 8 
         UNION ALL SELECT 9 UNION ALL SELECT 10 
         UNION ALL SELECT 11 ) AS m

now we LEFT OUTER JOIN to the data, and, as suggested, we are going to assume that the mileage readings are available on the 1st of the month –


SELECT m.month_day AS date
     , this_month.mileage
     , prev_month.date AS prev_date
     , prev_month.mileage AS prev_mileage
  FROM ( SELECT CURRENT_DATE - INTERVAL DAYOFMONTH(CURRENT_DATE)-1 DAY
                             - INTERVAL 1 YEAR                        
                             + INTERVAL m.n MONTH AS month_day        
           FROM ( SELECT 0 AS n                                       
                  UNION ALL SELECT 1 UNION ALL SELECT 2               
                  UNION ALL SELECT 3 UNION ALL SELECT 4               
                  UNION ALL SELECT 5 UNION ALL SELECT 6               
                  UNION ALL SELECT 7 UNION ALL SELECT 8               
                  UNION ALL SELECT 9 UNION ALL SELECT 10              
                  UNION ALL SELECT 11 ) AS m   
LEFT OUTER
  JOIN SampleTable AS this_month
    ON this_month.date = m.month_day
   AND this.month.tag = 'G1-222'
LEFT OUTER
  JOIN SampleTable AS prev_month
    ON EXTRACT(YEAR_MONTH FROM prev_month.date) = 
       ( SELECT MAX(EXTRACT(YEAR_MONTH FROM date))
           FROM SampleTable
          WHERE date < this_month.date )
   AND prev.month.tag = 'G1-222'

let me know how you get on with this and we can then try to do the mileage-pre-month calculation