CODEWITHSUNDEEP
mysql
Report Gladiatech
Report Gladiatech

Reputation: 59

Mysql Customer Table Count Followed Up Records

I am new to mysql , I have a customer care table where the calls from either customer or customer care gets stored. In case of a dropped call from customer number to customer care number (if duration < 10 its dropped) the customer care will check dropped calls within 30 minutes of time and will followup by calling them.

I have to find the total count of the followed up calls.

fromno       tono      startime        endtime            duration
91xxxx       121    yyyy-mm-dd h:i:s  yyyy-mm-dd h:i:s      9
91xxxx       121    yyyy-mm-dd h:i:s  yyyy-mm-dd h:i:s      26
91xxxx       121    yyyy-mm-dd h:i:s  yyyy-mm-dd h:i:s      56
91xxxx       121    yyyy-mm-dd h:i:s  yyyy-mm-dd h:i:s      5
121          91x    yyyy-mm-dd h:i:s  yyyy-mm-dd h:i:s      35

Here row 1 call is dropped and its been followed up by customer care in row 5

My Query:

Select COUNT(*) 
from customer A 
JOIN customer B ON A.tono = B.fromno 
where A.duration < 10 and TIMEDIFF(A.endtime, B.starttime) = "00:30:00";

Its giving the count but i am not sure if this will work correctly for large dataset.

Upvotes: 0

Views: 26

Answers (1)

Siddharth Nayar
Siddharth Nayar

Reputation: 872

You need to select all calls within the 30 minutes duration, hence = changes to <=. Also A.tono = B.fromno should change to A.fromno = B.tono since A.tono and B.fromno in your case is the Customer Care number (Duration check of 10 seconds on table A i.e. customer trying to call the Customer Care).

SELECT COUNT(*) 
FROM customer A 
INNER JOIN customer B
ON A.fromno = B.tono 
WHERE A.duration < 10 and TIMEDIFF(A.endtime, B.starttime) <= "00:30:00";

Upvotes: 1

Related Questions