How to use "groupby" with 7 past days and not "real week" in SQL

user_14076099 0
Viewed 56

I have an SQL pb. I need to group some bills by "sliding weeks" (my customer need 7 past days on the whole year, so starting on the current day) and I found nothing about it.. I'm quite sure I just don't have good keywords but still it's being 2days I'm searching.. So I have my sql request below which is working with normal WEEK use:

SELECT 
  WEEK(billing_date) as billed_week
, ROUND(sum(price) * 1.1, 2) as billed_amount
, billing_date as billing_date 
FROM bills 
JOIN missions m 
  ON bills.mission_id = m.id 
WHERE customer_id = $customer_id 
  AND status = 2 AND YEAR(billing_date) = YEAR(CURRENT_DATE) 
GROUP BY week(billing_date)

By example, is there anyway to update WEEK's function parameters?

group-by sql
1 Answers

You can achieve the results you want by shifting the date passed to WEEK according to the day of the week of today's date. That will result in WEEK returning a value which changes on that day of the week. Given you're using PHP I'm going to assume this is MySQL, in which case you would rewrite your query as:

SELECT 
  WEEK(billing_date - INTERVAL DAYOFWEEK(billing_date) DAY, 0) as billed_week
, ROUND(sum(price) * 1.1, 2) as billed_amount
, billing_date as billing_date 
FROM bills 
JOIN missions m 
  ON bills.mission_id = m.id 
WHERE customer_id = $customer_id 
  AND status = 2 AND YEAR(billing_date) = YEAR(CURRENT_DATE) 
GROUP BY WEEK(billing_date - INTERVAL DAYOFWEEK(billing_date) DAY, 0)

Note that I use 0 as the mode parameter to WEEK function so that the result it returns is based on the start of week being Sunday, which is the day of week corresponding to the minimum value returned by DAYOFWEEK.

Note also that as was pointed out by others in the comments, you should not be directly including PHP variables in your query, as that leaves you vulnerable to SQL injection. Instead, use prepared statements with place-holders for the variables you need. For example, something like this (assuming the MySQLi interface with a connection $conn):

$sql = 'SELECT 
      WEEK(billing_date - INTERVAL DAYOFWEEK(billing_date) DAY, 0) as billed_week
    , ROUND(sum(price) * 1.1, 2) as billed_amount
    , billing_date as billing_date 
    FROM bills 
    JOIN missions m 
      ON bills.mission_id = m.id 
    WHERE customer_id = ? 
      AND status = 2 AND YEAR(billing_date) = YEAR(CURRENT_DATE) 
    GROUP BY WEEK(billing_date - INTERVAL DAYOFWEEK(billing_date) DAY, 0)';
$stmt = $conn->prepare($sql);
$stmt->bind_param('i', $customer_id);
$stmt->execute();
user_94737640
Related