Query help: dates and calculations

[vacoder]

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.

 

[feherke]

Hi

I would start with this query, then calculate the differences in the application itself :

[b]select[/b]
tag[teal],[/teal]extract[teal]([/teal]year_month [b]from[/b] [maroon]date[/maroon][teal])[/teal] datepart[teal],[/teal]sum[teal]([/teal]mileage[teal])[/teal]

[b]from[/b] sampletable

[b]where[/b] [maroon]date[/maroon][teal]>=[/teal]date_sub[teal]([/teal]curdate[teal](),[/teal]interval [purple]1[/purple] [maroon]year[/maroon][teal])[/teal]

[b]group[/b] [b]by[/b] datepart

[b]order[/b] [b]by[/b] datepart

Feherke.

http://free.rootshell.be/~feherke/

 

[vacoder]

Thanks.
Can you tell me what the sum(mileage) is for? The number that is in the mileage field is the odometer reading for that month.

 

[feherke]

Hi

I have to say, I did not understood the role of that field. I supposed the values are the distances and their sum gives the monthly traffic.


Feherke.

http://free.rootshell.be/~feherke/