How to get the last 7 weeks, 7 months date range from current date PHP?

I managed to cobble together a query that sort of does what you want for the weeks, I hope. First the whole query:

SELECT
  S.salesDay as sales_day,
  ROUND(sum(IFNULL(P.tmpNetTotal, 0)),2) AS sales,
  P.createdDateTime as Day_date
FROM 
  srp_erp_pos_menusalesmaster AS S
JOIN (SELECT 
        SUM(IFNULL(amount, 0)) AS tmpNetTotal,
        createdDateTime, 
        menuSalesID 
      FROM 
        srp_erp_pos_menusalespayments 
      WHERE 
        createdDateTime > DATE_SUB(NOW(), INTERVAL 7 WEEK)
      GROUP BY 
        menuSalesID) AS P ON S.menuSalesID = P.menuSalesID 
WHERE 
  S.isHold = 0 AND 
  S.isVoid = 0 AND 
  S.companyID = $companyID 
GROUP BY 
   FLOOR((TO_DAYS(CURDATE()) - TO_DAYS(DATE(S.salesDay))) / 7)    
ORDER BY 
  P.createdDateTime

My assumption is that sales.salesDay contains a proper date and time.

I changed two things:

• In the inner SELECT I changed the condition to WHERE createdDateTime > DATE_SUB(NOW(), INTERVAL 7 WEEK), using functions in MySQL instead of in PHP.
• To group by weeks I changed the grouping to: GROUP BY FLOOR((TO_DAYS(CURDATE()) - TO_DAYS(DATE(S.salesDay))) / 7), using the FLOOR() function. I convert date/time to dates and then to a day number. subtracting those give you the days in the past, divide by 7 and taking the FLOOR() gives you weeks.

The same can be done for months.

Code is untested and might not work perfectly, but I hope this gives you a way forward.

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()

You Should Try This:

$today           = date("Y-m-d");
$lastsevenDays   = date('Y-m-d', strtotime('-7 days'));
$lastsevenMonths = date('Y-m-d', strtotime('-7 months'));

// Last seven weeks
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 DATE(createdDateTime) >= DATE($lastsevenDays) AND DATE(createdDateTime) < DATE($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;

// Last seven Months

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 DATE(createdDateTime) >= DATE($lastsevenMonths) AND DATE(createdDateTime) < DATE($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;