The `customer` and `company` fields represents their telephone numbers
The requiremen 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:
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
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'
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)