-1

I need to get same result which was explained in below query, but i need to do in mysql.

Total of orders open on a given date for each date in a date range

Appreciate for the help.

This is what I tried but it does not return required output: 

CREATE TABLE tracking(id INT(10), open_date TIMESTAMP, close_date TIMESTAMP, severity VARCHAR(10));
INSERT INTO tracking VALUES
(1,'2016-01-01','2016-01-02', '1'),(2,'2016-01-01','2016-01-03', '2'),
(3,'2015-12-31','2016-01-03', '2'),
(4,'2015-12-31','2016-01-04', '1'),
(5,'2015-01-02','2016-01-04', '1');

SELECT COUNT(*), tr.open_date FROMtrackingtr LEFT JOIN ( SELECT open_date FROM tracking WHERE 1=1) t ON tr.open_date=t.open_date
AND DATE(tr.open_date) <= '2015-12-30' AND (DATE(tr.close_date) IS NULL OR DATE( tr.close_date)>= '2016-01-04') GROUP BY tr.open_date ORDER BY tr.open_date` ASC

Community
  • 1
Ramgau
  • 101
  • 2
  • I am trying for mysql but not getting output in mysql SELECT id, days OVER (PARTITION BY id ORDER BY id DESC) active_order_id FROM tracking WHERE DATE(open_date) <= '2015-12-30' AND (DATE(close_date) IS NULL OR DATE(end_date )>= '2016-01-01'); – Ramgau Aug 11 '16 at 15:25

1 Answers1

0

I am giving answer of my own question because I need to complete based on above mentioned for my own project. Finally I got the idea at Simple select query cannot get result due to MySQL don’t have calendar table to use as reference sequential date for given date range. So I have made procedure which can give the number of open order/ticket within given date range.

Sample table creation with data:

 DROP TABLE IF EXISTS  tracking;
CREATE TABLE tracking(id INT(10), open_date TIMESTAMP,
 close_date TIMESTAMP, severity VARCHAR(10));
TRUNCATE TABLE tracking;
INSERT INTO tracking VALUES
(1,'2016-01-01','2016-01-02', '1'),
(2,'2016-01-01','2016-01-03', '2'),
(3,'2015-12-31','2016-01-03', '2'),
(4,'2015-12-31','2016-01-04', '1'),
(5,'2015-01-02','2016-01-04', '1');
Dropping temp table:

DROP PROCEDURE IF EXISTS filldates;
DROP TABLE IF EXISTS MyDates;
DROP TABLE IF EXISTS dates;

procedure Body: 


CREATE PROCEDURE filldates(dateStart DATE, dateEnd DATE)
-- CALL filldates('2016-01-01','2016-01-03');
BEGIN
DECLARE cnt INT DEFAULT 0;
    WHILE dateStart <= dateEnd DO
       SET cnt = (SELECT 
COUNT(*) AS COUNT 
FROM tracking
WHERE 
DATE(open_date) <= dateStart AND DATE(close_date) >= dateStart);
CREATE TEMPORARY TABLE IF NOT EXISTS MyDates (
    myDate DATE,
    COUNT INT
);
  INSERT INTO MyDates (mydate, COUNT) VALUES (dateStart, cnt);
        SET dateStart = DATE_ADD(dateStart, INTERVAL 1 DAY);
    END WHILE;
    SELECT * FROM MyDates;
END;
|
DELIMITER ;
Calling procedure:
CALL filldates('2016-01-01','2016-01-03');
Result will be :
myDate   COUNT
2016-01-01  5
2016-01-02  5
2016-01-03  4
Ramgau
  • 101
  • 2