Is it possible to loop the result of a query that gets each revenue for the last 8 weeks based on the input date of the user?

Question

User input = '2017-03-12'

Let say I have this tableRevenue

   date        revenue   
  ----------   ---------
 2017-01-01     100
 2017-01-08     100
 2017-01-15     100
 2017-01-22     100 
 2017-01-29     100
 2017-01-05     100
 2017-01-12     100
 2017-02-19     100
 2017-02-26     100
 2017-03-05     100
 2017-03-12     100

And another tableHolidays which contains

 date
----------
2017-01-15
2017-02-19
2017-03-05

I want to display it like this:

   date        revenue   
  ----------   ---------
 2017-01-01     100
 2017-01-08     100
 2017-01-22     100 
 2017-01-29     100
 2017-01-05     100
 2017-01-12     100
 2017-02-26     100
 2017-03-12     100

I want to display the revenue each of the last 8 weeks and I want to skip all the dates that are existing in tableHolidays using a loop. Is this possible in PHP?

I do not see any link between these dates, could you be more explicit on what you want to achieve? Something like https://stackoverflow.com/questions/14208958/select-data-from-date-range-between-two-dates ? — ka_lin, Aug 01 '17 at 07:40

Esteban P. · Accepted Answer · 2017-08-02T09:08:07.300

0

mention: you didn't tag any specific database - my answer will refer to SQL-Server:

assuming @UserDate is a variable with the user input date

Use Date-Functions (specific to every DB system) to calculate the date range. in your case to subtract the 8 weeks.
Select all rows within this date range
exclude (NOT IN) all dates from your resultset which occur in your tableHolidays table
GROUP BY weeks (calculate weeknumber with WEEK) and SUM the revenue

Query:

  SELECT WEEK(tR.date) as weeknr
        ,SUM(tR.revenue)
    FROM tableRevenue tR
   WHERE tR.date >= DATEADD(wk,-8,@UserDate)
     AND tR.date <= @UserDate
     AND tR.date NOT IN (SELECT date FROM tableHolidays)
GROUP BY WEEK(tR.date)

edited Aug 02 '17 at 09:08

answered Aug 01 '17 at 07:57

Esteban P.

2,789
2
27
43

It still display all the dates per day. I just want to show the revenue of the same day but past week. – Lanz Joshua Aug 02 '17 at 01:40
@LanzJoshua: now i know what you're want to do. you finally just want to group all your rows by week. i just updated my answer. have a look – Esteban P. Aug 02 '17 at 07:26
MYSQL said: #1582 - Incorrect parameter count in the call to native function 'DATEDIFF' – Lanz Joshua Aug 02 '17 at 08:14
@LanzJoshua i updated my answer again. for the future: please always tag the database system you are using. in example, my solution included the DATEDIFF function, which is MS SQL specific and not recognized in mysql. i changed it now to the function WEEK() which is known by mysql – Esteban P. Aug 02 '17 at 09:09

score 0 · Answer 2 · answered Aug 01 '17 at 08:13

0

you can use the 'ANY' which is a mysql operator
for more information you can visit this link https://www.w3schools.com/sql/sql_any_all.asp

$userInput = '2017-03-12';
$sql = "SELECT  `date`, `revenue`
FROM `tableRevenue`
WHERE ( 
 (`date` = ANY 
   (SELECT `date` FROM `tableHolidays` WHERE DATE(date)<='{$userInput}'))  
 AND (DATE(date) <='{$userInput}')
)";

answered Aug 01 '17 at 08:13

Fahmi B.

758
2
10
23

I can't use "any" because I need the specific dates: (1)2017-03-05, (2)2017-2-26, (3)2017-02-19, (4)2017-02-12, (5)2017-02-05, (6)2017-01-29, (7)2017-01-22, (8)2017-01-15 – Lanz Joshua Aug 02 '17 at 08:09

Is it possible to loop the result of a query that gets each revenue for the last 8 weeks based on the input date of the user?

2 Answers2