month order value

Hey Ray

On an 'orders page' I have set up a small 'dashboard' showing the client the value of orders for current month, previous month and previous +1 as well as a 3, 6 and 12 month monthly average as you can see here: http://prntscr.com/qis7k9

The problem I seem to have is that since the roll over to 1 Jan the current month is not showing the correct value (it should be about £1k at the moment rather than the £16k)

Am not sure whats happening - but am assuming that its to do with the date/year change and that its combining values together for some reason but am unsure what.

Have attached the page in question

Many thanks

Jamie

Your Recordsets account for the month but not the year. Try:

<?php

$Month1 = new WA_MySQLi_RS("Month1",$DBConnection);

$Month1->setQuery("SELECT SUM(OrderAmount) AS OrderValue FROM orders WHERE OrderDate >= DATE_FORMAT(CURDATE(), '%Y-%m-01')");

$Month1->execute();

?>

<?php

$Month2 = new WA_MySQLi_RS("Month2",$DBConnection);

$Month2->setQuery("SELECT SUM(OrderAmount) AS OrderValue FROM orders WHERE OrderDate >= DATE_FORMAT(CURDATE(), '%Y-%m-01') - INTERVAL 1 MONTH");

$Month2->execute();

?>

<?php

$Month3 = new WA_MySQLi_RS("Month3",$DBConnection);

$Month3->setQuery("SELECT SUM(OrderAmount) AS OrderValue FROM orders WHERE OrderDate >= DATE_FORMAT(CURDATE(), '%Y-%m-01') - INTERVAL 2 MONTH");

$Month3->execute();

?>

thats great, many thanks

month1 and 2 it seems to be workign for but it looks like month3 is adding month3 and 2 together - http://prntscr.com/qj01qn

I think that month 3 should look more like 12,786.85

I had:
INTERVAL -2 MONTH

I think it should have been:
INTERVAL 2 MONTH

(I've updated the code above)

Sorry, had already changed it to 2 rather than -2 (-2 gives a zero value and 2 gives the 27,512.75 which just doesnt seem *right*

It seems like it would be correct. I'd have to look at the database to debug this. Maybe screen share. Try contacting me via Skype.

Ah I see what its doing...its giving a running total over those months rather than the specific value for each month

To test it, I echo'd 13 months and the 13th month (Jan19) is almost the same as the 12 month total as you can see here http://prntscr.com/qjbftb

So what would I need to get the specific value for each month rather than a running total?

Add :

GROUP BY MONTH(OrderDate ), YEAR(OrderDate)

And then each month and year will have it's own total.

have added that to each of the recordsets and am now getting this http://prntscr.com/qk38lk

If you are trying to show orders per month then you only need one recordset and then a repeat region to show all of the months.