How can I get the date range of last seven weeks /months from the current date, i have already done with last seven days
fetch last seven days, weeks and month sales total grouped by 7 particular days, 7 particular weeks and 7 particular months
ex:
Today : 08/18/2021
Last seven weeks
08/08/2021 - 08/14/2021 08/01/2021 - 08/07/2021 etc.
Last seven Months
07/01/2021 - 07/31/2021 06/01/2021 - 06/30/2021 etc.
The below query I have done for last seven days
$today = date("Y-m-d");
$lastsevenDays = date('Y-m-d', strtotime('-7 days'));
SELECT
sales.salesDay as sales_day,
ROUND(sum( IFNULL( payment.tmpNetTotal, 0 ) ),2)AS sales,
payment.createdDateTime as Day_date
FROM
srp_erp_pos_menusalesmaster AS sales
JOIN ( SELECT SUM( IFNULL( amount, 0 ) ) AS tmpNetTotal,createdDateTime, menuSalesID FROM srp_erp_pos_menusalespayments WHERE createdDateTime BETWEEN '" . $lastsevenDays . "' AND '" . $today . "' GROUP BY menuSalesID ) AS payment ON sales.menuSalesID = payment.menuSalesID
WHERE isHold = 0 AND sales.isVoid = 0 AND companyID = $companyID GROUP BY
sales.salesDay ORDER BY
payment.createdDateTime
Now as the same way, I am thinking a way to fetch last 7 weeks and last seven months.
Advertisement
Answer
If you have each single “day” summarized in a single table, then it is easy to summarize that over the last N days, whether there is several weeks or months.
If the summary table has sum_dollars, count_items, then
SELECT
SUM(sum_dollars) -- total dollars over the larger period
SUM(count_items) -- how many items there were
SUM(sum_dollars) / SUM(count_items) -- average price
FROM summary_table
WHERE date ...
(Note that I did not have avg_price in the summary table because AVG(avg_..) is usually the wrong formula.)
So, build and maintain (nightly) a summary table by day. See http://mysql.rjweb.org/doc.php/summarytables
PS. “today” is CURDATE() in SQL. More specifically, that is midnight this morning. “7 months ago” is CURDATE() - INTERVAL 7 MONTH
Likely BUG: Don’t use BETWEEN; it is ‘inclusive’. Instead, do something like
WHERE createdDateTime >= CURDATE() - INTERVAL 7 MONTH AND createdDateTime < CURDATE()