CODEWITHSUNDEEP
phpmysql
Nikola Ristivojevic
Nikola Ristivojevic

Reputation: 179

MySql query to calculate number of peaks in moment

I need a help, I need to calculate number of active calls at the moment. This is my table:

+---------------------+---------+
| calldate            | billsec |
+---------------------+---------+
| 2013-05-14 09:40:30 |      29 |
| 2013-05-14 09:40:31 |      24 |
| 2013-05-14 09:40:31 |      30 |
| 2013-05-14 09:40:33 |      20 |
| 2013-05-14 09:40:34 |      21 |
| 2013-05-14 09:40:35 |      30 |
| 2013-05-14 09:40:36 |      30 |
| 2013-05-14 09:40:37 |      30 |
| 2013-05-14 09:40:37 |      25 |
| 2013-05-14 09:40:39 |      22 |
| 2013-05-14 09:40:39 |      19 |
| 2013-05-14 09:40:40 |      22 |
| 2013-05-14 09:40:41 |      22 |

For example, If I want to calculate number of active calls in '2013-05-14 09:40:34', I should get 5 records, because for example, for the first row call is between '2013-05-14 09:40:30' and '2013-05-14 09:40:59' (calldate+billsec).

Thanks

Upvotes: 2

Views: 150

Answers (1)

revoua
revoua

Reputation: 2059

select count(*) from calls 
where '2013-05-14 09:40:34' between
  calldate and DATE_ADD(calldate,INTERVAL billsec SECOND)

SQLFIDDLE

Update:
In order to see amounts for each calldate you can use subquery 

select B.calldate
  ,(select count(*) from calls A
  where B.calldate between
    A.calldate and DATE_ADD(A.calldate,INTERVAL A.billsec SECOND)) numcalls
from calls B
group by B.calldate

SQLFIDDLE

Upvotes: 2

Related Questions