CODEWITHSUNDEEP
mysqlmysql-5.5
Ankit Doshi
Ankit Doshi

Reputation: 1174

get count of rows from start and end time

i have table in which have two columns. one column(callstart) is showing that call start time. Another column(callend) which is showing that call end time.

Now i would like to make result for single day that will be in where condition like how many calls are running on that time. How many calls has been started on that time.

Table : 

+-------------+-------------+------+-----+---------------------+-------+
| Field       | Type        | Null | Key | Default             | Extra |
+-------------+-------------+------+-----+---------------------+-------+
| id          | int(11)     | NO   |     | NULL                |       |
| user_id     | int(11)     | YES  |     | 0                   |       |
| callstart   | datetime    | NO   |     | 0000-00-00 00:00:00 |       |
| callend     | datetime    | NO   |     | 0000-00-00 00:00:00 |       |
+-------------+-------------+------+-----+---------------------+-------+
4 rows in set (0.00 sec)

Expected result :

+----+-----------+---------------------+----+-----+
| id | user_id   | date                | cc | cps |
+----+-----------+---------------------+----+-----+
|  1 |         3 | 2018-04-02 19:47:27 |  0 |   1 |
|  2 |         3 | 2018-04-02 19:48:52 |  1 |   1 |
|  3 |         3 | 2018-04-02 19:48:53 |  1 |   0 |
|  4 |         3 | 2018-04-02 19:48:54 |  1 |   0 |
|  5 |         3 | 2018-04-02 19:48:55 |  1 |   0 |
+----+-----------+---------------------+----+-----+

In above result :

cc : concurrent calls running on particular time. cps : calls which has been started on particular time.

Note :

  1. mysql version is 5.5.

  2. In above table,Data will be available only for single day( A call which has been ended on day upto call has been started on a day).

  3.I have make it working with php script.But its take long time to manage 6 months record.So i would like to make query which can gives result for every single day.

Fiddle : http://sqlfiddle.com/#!9/4c042/5

Expecting result on time : 2018-08-20 12:20:34 needs to show two rows

accountid | callstart           | cc |cps|
--------------------------------------------
56        | 2018-08-20 12:20:24 | 1  | 1 |
56        | 2018-08-20 12:20:25 | 1  | 0 |
56        | 2018-08-20 12:20:26 | 1  | 0 |
56        | 2018-08-20 12:20:27 | 1  | 0 |
56        | 2018-08-20 12:20:28 | 1  | 0 |
56        | 2018-08-20 12:20:29 | 1  | 0 |
56        | 2018-08-20 12:20:30 | 1  | 0 |
56        | 2018-08-20 12:20:31 | 1  | 0 |
56        | 2018-08-20 12:20:32 | 1  | 0 |
56        | 2018-08-20 12:20:33 | 1  | 0 |
56        | 2018-08-20 12:20:34 | 3  | 2 |
56        | 2018-08-20 12:20:35 | 3  | 0 |
56        | 2018-08-20 12:20:36 | 3  | 0 |
56        | 2018-08-20 12:20:37 | 3  | 0 |
56        | 2018-08-20 12:20:38 | 3  | 0 |
56        | 2018-08-20 12:20:39 | 3  | 0 |
56        | 2018-08-20 12:20:40 | 3  | 0 |
56        | 2018-08-20 12:20:41 | 2  | 0 |
56        | 2018-08-20 12:20:42 | 2  | 0 |
56        | 2018-08-20 12:20:43 | 2  | 0 |
56        | 2018-08-20 12:20:44 | 2  | 0 |
56        | 2018-08-20 12:20:45 | 2  | 0 |
56        | 2018-08-20 12:20:46 | 2  | 0 |
56        | 2018-08-20 12:20:47 | 2  | 0 |
56        | 2018-08-20 12:20:48 | 2  | 0 |
56        | 2018-08-20 12:20:49 | 2  | 0 |
56        | 2018-08-20 12:20:50 | 0  | 0 |
58        | 2018-08-20 12:20:29 | 1  | 1 |
58        | 2018-08-20 12:20:30 | 1  | 0 |
58        | 2018-08-20 12:20:31 | 1  | 0 |
58        | 2018-08-20 12:20:32 | 1  | 0 |
58        | 2018-08-20 12:20:33 | 1  | 0 |
58        | 2018-08-20 12:20:34 | 3  | 2 |
58        | 2018-08-20 12:20:35 | 3  | 0 |
58        | 2018-08-20 12:20:36 | 3  | 0 |
58        | 2018-08-20 12:20:37 | 3  | 0 |
58        | 2018-08-20 12:20:38 | 3  | 0 |
58        | 2018-08-20 12:20:39 | 3  | 0 |
58        | 2018-08-20 12:20:40 | 3  | 0 |
58        | 2018-08-20 12:20:41 | 3  | 0 |
58        | 2018-08-20 12:20:42 | 3  | 0 |
58        | 2018-08-20 12:20:43 | 3  | 0 |
58        | 2018-08-20 12:20:44 | 3  | 0 |
58        | 2018-08-20 12:20:45 | 3  | 0 |
58        | 2018-08-20 12:20:46 | 3  | 0 |
58        | 2018-08-20 12:20:47 | 3  | 0 |
58        | 2018-08-20 12:20:48 | 3  | 0 |
58        | 2018-08-20 12:20:49 | 2  | 0 |
58        | 2018-08-20 12:20:50 | 2  | 0 |
58        | 2018-08-20 12:20:51 | 2  | 0 |
58        | 2018-08-20 12:20:52 | 2  | 0 |
58        | 2018-08-20 12:20:53 | 2  | 0 |
58        | 2018-08-20 12:20:54 | 2  | 0 |
58        | 2018-08-20 12:20:55 | 2  | 0 |
58        | 2018-08-20 12:20:56 | 2  | 0 |
--------------------------------------------

Anyone please share some suggestion.

Upvotes: 0

Views: 404

Answers (1)

Ary Jazz
Ary Jazz

Reputation: 1656

EDIT: I didn't understand the question before, but seeing the expected results clarified it for me. In order to have a row for every second during a time window, you need to create a table with all the times you want to show.

Something like this:

CREATE TABLE `time_window` (
  `second` datetime NOT NULL DEFAULT '0000-00-00 00:00:00'
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO `time_window` (`second`) VALUES
('2018-08-20 00:00:00'),
('2018-08-20 00:00:01'),
 ...
('2018-08-20 23:59:59');

Then, you will need to run the following query:

SELECT 
  b.accountid,
  a.second,
  SUM(CASE WHEN b.callstart <= a.second
      AND b.callend >= a.second THEN 1 ELSE 0 END) AS cc,
  SUM(CASE WHEN b.callstart = a.second THEN 1 ELSE 0 END) AS cps
FROM time_window a
LEFT JOIN custom_data b 
ON 1=1
GROUP BY 1 ,2
ORDER BY b.accountid, a.second

Here's the SQLFiddle

Hope it helps

Upvotes: 1

Related Questions