Add a 1 week date range to existing MySQL query

Question

Just wondering where in the following query one might add the code to limit the request to a week from the current date, as as it currently sits, it requests EVERY record, and takes forever to load - not my code, i just inherited it and am not super comfy with MySQL beyond basic queries

  $sql = "SELECT tbase.property_name, 
  tbase.room_number, 
  tbase.customer_first_name, 
  tbase.customer_last_name, 
  tbase.amount_paid, 
  tbase.payment_date, 
  tbase.payment_method, 
  CASE 
    WHEN tbase.RowNumber = 1 THEN tbase.refund_amount 
    ELSE '' 
  END AS refundamount, 
  tbase.recipt_number, 
  tbase.room_standard_weekly_rate, 
  tbase.discount_amount, 
  tbase.Weekly_tariff 
FROM ( 
  SELECT 
    base.property_name, 
    base.room_number, 
    base.customer_first_name, 
    base.customer_last_name, 
    base.amount_paid, 
    base.payment_date, 
    base.payment_method, 
    ROW_NUMBER() OVER (PARTITION BY base.property_name, base.room_number, base.refund_amount ORDER BY base.property_name, base.room_number, base.refund_amount) AS RowNumber, 
    base.refund_amount, 
    base.recipt_number, 
    base.room_standard_weekly_rate, 
    base.discount_amount, 
    base.Weekly_tariff 
  FROM ( 
    SELECT 
      DISTINCT 
      hmprop.property_name AS property_name, 
      hmroom.room_number AS room_number, 
      hmcust.customer_first_name AS customer_first_name, 
      hmcust.customer_last_name AS customer_last_name, 
      hmpayment.amount_paid AS amount_paid, 
      hmpayment.payment_date AS payment_date, 
      hmpayment.payment_method AS payment_method, 
      hmbook.refund_amount AS refund_amount, 
      hmpayment.recipt_number AS recipt_number, 
      hmroom.room_standard_weekly_rate AS room_standard_weekly_rate, 
      discount.discount_amount AS discount_amount, 
      CASE 
        WHEN hmroom.room_standard_weekly_rate <> hmbook.weekly_tariff THEN 'Yes' 
        ELSE 'No' 
      END AS Weekly_tariff 
    FROM 
      hm_booking AS hmbook 
      JOIN hm_room AS hmroom ON hmbook.room_id = hmroom.room_id 
      INNER JOIN hm_customer AS hmcust ON hmbook.customer_id = hmcust.customer_id 
      INNER JOIN hm_booking_payment AS hmpayment ON hmbook.booking_id = hmpayment.booking_id 
      INNER JOIN hm_property AS hmprop ON hmprop.property_id = hmroom.property_id 
      LEFT JOIN hm_booking_discount AS discount ON discount.id = hmpayment.discount_id 
    ORDER BY 
      hmprop.property_name, 
      hmroom.room_number ASC 
  ) AS base 
) AS tbase";

thanks in advance for any assistance

Answer 1

you have to specify a WHERE condition to limit it to 7days

tablename.tablecolumnfordate >= CURDATE() - INTERVAL 7 DAY