Here is the dummy data:
http://tny.cz/a1b7941a , it's a calls record data table.
This is a glimpse of it:
| call_id | customer | company | call_start |
|-----------|--------------|-------------|---------------------|
|1411482360 | 001143792042 | 08444599175 | 2014-07-31 13:55:03 |
|1476992122 | 001143792042 | 08441713191 | 2014-07-31 14:05:10 |
The `customer` and `company` fields represents their telephone numbers
The requirement is to compute the total 'gain' and total 'lost' values based on the following logic, and store them in a temporary table:
-Customer A calls Company A.
-If customer A calls Company B then Company B will have +1 gain and Company A will have +1 lost.
-If customer A calls Company C then Company C will have +1 gain and Company B will have +1 lost.
-The gain/lost only comes into play once a 2nd call has been made by Client A.
-If a customer calls companies in this order: A, B, B, C, A, A, C, B, D the process should be like this:
A ->
B -> B +1 gain, A +1 spill
B -> B +1 gain, B +1 spill
C -> C +1 gain, B +1 spill
A -> A +1 gain, C +1 spill
A -> A +1 gain, A +1 spill
C -> C +1 gain, A +1 spill
B -> B +1 gain, C +1 spill
D -> D +1 gain, B +1 spill
After above process we should have the total values as:
Company Total gain Total spill
A 2 3
B 3 3
C 2 2
D 1 0
I started working on this but it's wrong, it's just an ideea, it doesn't give me separate incremented gain and lost values based on the above conditions:
DROP TABLE IF EXISTS GetTotalGainAndLost;
CREATE TEMPORARY TABLE IF NOT EXISTS GetTotalGainAndLost
AS
(
SELECT SUM(count) as 'TotalGainAndLost', `date`, DAY(`date`) as 'DAY'
FROM (SELECT count(*) as 'count', customer, `date`
FROM (SELECT customer, company, count(*) AS 'count', DATE_FORMAT(`call_end`,'%Y-%m-%d') as 'date'
FROM callstats.calls
WHERE `call_end` LIKE CONCAT(2014, '-', RIGHT(CAST(concat('0', 01) AS CHAR),2),'-%')
GROUP BY customer, company, DAY(`call_end`) ORDER BY `call_end` ASC)
as tbl1 group by customer, `date` having count(*) > 1)
as tbl2 GROUP by `date`
);
Select * from GetTotalGainAndLost;
DROP TABLE GetTotalGainAndLost;
This query doesn't show any results.
The desired output would be something like below:
Should be one row per company and date (total gain and lost calls by day in e.g. january)
| company | totalGain | totallost | date | day |
|-------------|------------|-------------|--------------|-------|
| 08444599175 | 17 | 6 | 2014-07-01 | 1 |
| 08444599175 | 12 | 10 | 2014-07-02 | 2 |
| 08444599175 | 3 | 6 | 2014-07-02 | 3 |
| 08444599175 | .... | ... | ... | ... |
| 08444599175 | 7 | 6 | 2014-07-31 | 31 |