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
| SELECT
TO_CHAR(ICONTACTMANAGEMENT.DATEOPERATION,'MM-YYY'),
SUM(TABLE_NOTSOB.NB_X) as "Nb SC create by User",
SUM(TABLE_SOB.NB_Y) as "Nb SC create by SOB"
FROM
ICONTACTMANAGEMENT,
(
SELECT
TO_CHAR(ICM1.DATEOPERATION,'MM-YYY') as "MonthYear",
COUNT(SC1.ID) as NB_X,
0 as NB_Y
FROM
ICONTACTMANAGEMENT ICM1,
SUPPLIERCONTACT SC1
WHERE
ICM1.CONTACTID = SC1.ID
AND ICM1.EVENT like 'New%'
AND ICM1.USEROPERATION not like 'SOB:%'
GROUP BY TO_CHAR(ICM1.DATEOPERATION,'MM-YYY')
) TABLE_NOTSOB,
(
SELECT
TO_CHAR(ICM2.DATEOPERATION,'MM-YYY') as "MonthYear",
0 as NB_X,
COUNT(SC2.ID) as NB_Y
FROM
ICONTACTMANAGEMENT ICM2,
SUPPLIERCONTACT SC2
WHERE
ICM2.CONTACTID = SC2.ID
AND ICM2.EVENT like 'New%'
AND ICM2.USEROPERATION like 'SOB:%'
GROUP BY TO_CHAR(ICM2.DATEOPERATION,'MM-YYY')
) TABLE_SOB
GROUP BY TO_CHAR(ICONTACTMANAGEMENT.DATEOPERATION,'MM-YYY') |
Partager