0

How can I write a query which will return the orders created per day for a time range let me say (according to the example code I've provided) 2016-02-01 - 2016-02-28. In result I want to include all days of this range including the days for which there is no order created (day_with_no_order will return zero as a line result). The problem I face is how to specify this day-counting-range. I'm using MySQL but this should be a general problem not vendor-specific.

DROP TABLE IF EXISTS orders;
CREATE TABLE orders ( id INT NOT NULL, date_created DATE NOT NULL, items_quantity INT NOT NULL );
INSERT INTO orders VALUES 
(1, STR_TO_DATE('2016-02-03','%Y-%m-%d'), 1), 
(2, STR_TO_DATE('2016-02-04','%Y-%m-%d'), 2), 
(3, STR_TO_DATE('2016-02-05','%Y-%m-%d'), 3),
(4, STR_TO_DATE('2016-02-05','%Y-%m-%d'), 1), 
(5, STR_TO_DATE('2016-02-10','%Y-%m-%d'), 2), 
(6, STR_TO_DATE('2016-02-12','%Y-%m-%d'), 3), 
(7, STR_TO_DATE('2016-02-19','%Y-%m-%d'), 1), 
(8, STR_TO_DATE('2016-02-19','%Y-%m-%d'), 2), 
(9, STR_TO_DATE('2016-02-21','%Y-%m-%d'), 3);
nenito
  • 1,214
  • 6
  • 19
  • 33
  • Unfortunatley this is vendor specific. To numerate days in the interval you need a tally table and kind of ROW_NUMBER() see http://stackoverflow.com/questions/1895110/row-number-in-mysql – Serg Mar 02 '17 at 13:05
  • 1
    The question is not vendor specific, but the solution is :) – Shadow Mar 02 '17 at 13:21
  • @Shadow: It looks relevant, but is there a way to solve it without additional table. – nenito Mar 02 '17 at 13:25
  • 1
    There are ways, but they are not that performant. But here you go: http://stackoverflow.com/a/31141256/5389997 Point is: you should have searched SO... – Shadow Mar 02 '17 at 13:38
  • @Shadow: Thanks, that's it. – nenito Mar 02 '17 at 13:56

1 Answers1

0
SELECT *
FROM ORDERS
WHERE date_created > to_date(dd-mon-yyyy, 01-02-2017)
AND date_created < to_date(dd-mon-yyyy, 01-03-2017);

Or do you mean totalling the number of orders for each day? e.g you want output like:

03/02/2017 1

04/02/2017 2

05/02/2017 4

?

HardLeeWorking
  • 195
  • 2
  • 13