MySQL Query - How to Get Data From Current Month and Same Month Last Year?
I have a simple query that sums sales totals for September and compares them with Sept of last year. How can I write this so that it auto updates with the current month month each month?
Select Salesman, SUM(CASE WHEN InvoiceDate Between '2015-9-1' and '2015-9-30' THEN Totsales else 0 end) as Sales15, SUM(CASE WHEN InvoiceDate Between '2016-9-1' and '2016-9-30' THEN Totsales else 0 end) as Sales16 from vCustomerInvoiceDetailGroup by Salesman
I think this is what you asked for - not sure it is what you want. For this to mean anything it would need to run on the last day of the month. Otherwise if you want to look back at the last month it would require some tweaking.
SELECT Salesman, SUM(CASE WHEN InvoiceDate BETWEEN CONCAT(YEAR(NOW())-1, '-', MONTH(NOW()), '-01') AND CONCAT(YEAR(NOW())-1, '-', MONTH(NOW()), '-', DAY(LAST_DAY(NOW()))) THEN Totsales ELSE 0 END) AS Sales15, SUM(CASE WHEN InvoiceDate BETWEEN CONCAT(YEAR(NOW()), '-', MONTH(NOW()), '-01') AND CONCAT(YEAR(NOW()), '-', MONTH(NOW()), '-', DAY(LAST_DAY(NOW()))) THEN Totsales ELSE 0 END) AS Sales16 FROM vCustomerInvoiceDetailGROUP BY Salesman
Open in new window