Re: How should I config the my.ini(or my.cnf) file for mysql?
Maybe this one:
CREATE TEMPORARY TABLE IF NOT EXISTS Surveys_Temp AS (
Select SN.Id_Rep,COALESCE((Sum(Case When SN.Score < 7 Then -100
When SN.Score >= 7 and Score < 9 Then 0
When SN.Score >= 9 Then 100 End)/Count(Score)),0) as NRS, SW.WTR,Count(Score) As surveys From surveys SN
Inner join (Select Id_Rep,COALESCE((Sum(Case When Score < 7 Then -100
When Score >= 7 and Score < 9 Then 0
When Score >= 9 Then 100 End)/Count(Score)),0) as WTR From surveys
Where Survey_Type = 'WTR'
Group by id_rep) SW ON SW.ID_Rep = SN.ID_Rep
Where SN.Survey_Type = 'NRS'
Group by SN.id_rep);
CREATE TEMPORARY TABLE IF NOT EXISTS Orders_Temp AS (
Select Id_Rep, Sum(Cast(OrderNumber As Decimal(2,0))) As Orders From Orders
Group by Id_Rep);
CREATE TEMPORARY TABLE IF NOT EXISTS Chats_Temp AS (
Select id_rep,
TIME_FORMAT(SEC_TO_TIME(Cast(Cast(sum(response_time * -1)/
count( id_session) As Decimal(5,2)) As Char(6))),'%H : %i : %s')As response_time
From chats where chat_type = 1
group by id_rep
order by id_rep);
Select R.Rep_Name,
Count(Distinct R.Id_Session) As Chats,
COALESCE(O.Orders,0) as Orders,
COALESCE(Cast( (O.Orders/Count(Distinct R.Id_Session)) * 100 as decimal(5,2)),0) As Conversion,
Cast(S.NRS as Decimal(5,2)) as NRS,
Cast(S.WTR as Decimal(5,2)) as WTR,
S.surveys as total_surveys,
Replace(C.response_time,' ', '') as response_time
From Reps R
Left Join Surveys_Temp As S ON S.Id_Rep = R.Id_Rep
Left Join Orders_Temp As O ON O.Id_Rep = R.Id_Rep
Left Join Chats_Temp As C on c.id_rep = R.Id_Rep
Where R.Rep_Country in('D.R','U.S')
Group By R.Rep_Name,O.Orders,S.NRS,S.WTR,S.Surveys,C.response_time
Order By R.rep_name;
drop table Surveys_Temp;
drop table Orders_Temp;
drop table Chats_Temp;