2

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?

marcothesane
  • 6,192
  • 1
  • 11
  • 21
voualla
  • 21
  • 2
  • What type of SQL is it? It looks like T-SQL (MS SQL Server) – RBarryYoung Aug 18 '20 at 14:16
  • 1
    Which dbms are you using? (When it comes to date/time, many products are far from ANSI SQL compliant.) – jarlh Aug 18 '20 at 14:16
  • Or is it MySql? I don't recognize what is going on with those apostrophes..? – RBarryYoung Aug 18 '20 at 14:18
  • Hmm, looks like you have some of the client language mixed-in with the SQL and the first editor obscured that by removing some of the apostrophes. – RBarryYoung Aug 18 '20 at 14:21
  • Oh yes sorry, actually that's php and not real sql, i'm concating Sql and actual php variables. And there are unusefull concats (just for it to be more readable on my editor) – voualla Aug 18 '20 at 14:27
  • (answering about apostrophes) – voualla Aug 18 '20 at 14:27
  • 1
    Please show sample data and expected results - I'm not sure I understand the question. – Neville Kuyt Aug 18 '20 at 15:04
  • I reformatted it to a plain SQL - hope that helps. Did I get that right, though? `WEEK('2020-08-16')` would be 34, `WEEK_ISO('2020-08-16')` would be 33. But you seem to want a week number that would be the same from last Monday to today, Tuesday. Would that number be 33, as the first Tuesday this year was 32 weeks ago on 7th Jan? Or do you count from 1 backwards? Or does that not matter at all? – marcothesane Aug 18 '20 at 15:18
  • For security reasons it is generally not a good idea to use string concatenation to add in variables. The standard is to use parameters through the API instead to prevent SQL Injection. – RBarryYoung Aug 18 '20 at 16:58
  • I agree with @NevilleKuyt, it's not exactly clear what you are looking for. I could make a guess, but like most folks I would rather not put a lot of work into an answer based just on my guess. – RBarryYoung Aug 18 '20 at 17:01
  • Ok I think I’m not clear at all haha.. Actually I want, in the results, to have a list of « wednesday to wednesday » for the whole year. As if I had to bill a customer every wednesday.. – voualla Aug 18 '20 at 17:47
  • So I don’t really need my week‘s numbers at the end.. I just did that to begin because I didn’t found any other way.. – voualla Aug 18 '20 at 17:53

1 Answers1

1

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();
Nick
  • 138,499
  • 22
  • 57
  • 95
  • Thanks a lot, I noted what you (and everybody else) told me about how bad it was to inject php variables inside SQL requests. But actually I use Laravel and I never use pure SQL inside this project, it's very rare, on some SQL requests I don't know how to translate into eloquent... so I just use the ::select of laravel and the request needs to be complete.. I'm beginner so I'm quite sure there's a cleaner way to do that and I just don't know it yet :) – voualla Aug 19 '20 at 07:49
  • 1
    @voualla if you are using Laravel you shouldn't have to worry about injection unless you use a raw query. Did this query give you the results you wanted? – Nick Aug 19 '20 at 12:26
  • Oh great, I'm not using raw querys so it should be ok. I tried your way but it did not work either.. I thought maybe it was in the GROUP BY that I had to change INTERVAL stuffs so I put it there and it changed nothing at all again.. So 20minutes ago I gave up and started some dropdown stuffs with a starting date and an ending date – voualla Aug 19 '20 at 15:49
  • 1
    @voualla I'm sorry to hear that - it's always hard though to suggest answers without sample data. If you could make up a small demo of the issue on one of the db fiddle sites (e.g. https://dbfiddle.uk or https://db-fiddle.com) I'd be happy to take a further look at the problem. It's probably a fairly simple fix... – Nick Aug 19 '20 at 23:21