1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49
|
SELECT
kiamorecords_task.CallRef,
kiamorecords_task.StartTime,
kiamorecords_task.EndTime,
kiamorecords_task.CustInfo,
kiamorecords_task.UserQualif,
kiamorecords_task.FirstServiceName,
kiamorecords_task.FirstDistribServiceName,
kiamorecords_task.FirstAgentName,
kiamorecords_task.FirstAgentGroupName,
nombre,
yearweek(kiamorecords_task.starttime, 1) AS Numsemaine,
numcmd
FROM
kiamorecords_task
INNER JOIN
(SELECT
min(id) AS ID, count(btel.id) AS nombre, NULL as numcmd --> ajout d'une troisième colonne, arbitrairement NULL
FROM
(SELECT
CustInfo, Id, starttime
FROM
kiamorecords_task
WHERE
Direction = 'in'
AND FirstAgentGroupId IN ('11' , '20', '9', '10', '8', '7', '23', '26')
AND CustInfo <> '') AS Btel
GROUP BY yearweek(starttime, 1) , CustInfo
UNION ALL
(SELECT
min(id) AS ID, count(Bcmd.id) AS nombre, numcmd
FROM
(SELECT
Id,
starttime,
custinfo,
IF(instr(UserData, 'OrderId') = 0, 'aucun', REPLACE(substring(Userdata, (instr(UserData, 'OrderId') + 33), 10), ']', '')) AS numcmd
FROM
kiamorecords_task
WHERE
Direction = 'in'
AND FirstAgentGroupId IN ('11' , '20', '9', '10', '8', '7', '23', '26')
AND CustInfo = '') AS Bcmd
GROUP BY yearweek(starttime, 1) , numcmd
) AS cool3 USING (id) |
Partager