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 50 51 52 53 54 55 56 57 58 59 60 61 62 63
|
select
tabA.sem semaine
, decode (tabA.NBRE_MODIFS, null,0,tabA.NBRE_MODIFS) NBRE_MODIFS
, decode (tabB.NBRE_MODIFS_SIEGE ,null,0,tabB.NBRE_MODIFS_SIEGE) NBRE_MODIFS_SIEGE
from
(
SELECT tab1.sem sem, COUNT(C_BPARTNER_ID) NBRE_MODIFS
FROM
(
SELECT DISTINCT
DATEPART('ww',l.created) as sem,
M.C_BPARTNER_ID c_bpartner_id,
COUNT(L.AD_CHANGELOG_ID) NBRE_MODIFS
FROM AD_CHANGELOG L
INNER JOIN AD_TABLE T ON (L.AD_TABLE_ID = T.AD_TABLE_ID)
INNER JOIN AD_COLUMN C ON (L.AD_COLUMN_ID = C.AD_COLUMN_ID)
-- Auteur de la modification
INNER JOIN AD_USER U ON L.CREATEDBY = U.AD_USER_ID
INNER JOIN AD_ORG UUSER ON U.AD_ORG_ID = UUSER.AD_ORG_ID
-- Membre modifié
LEFT JOIN C_BPARTNER M ON L.RECORD_ID = M.C_BPARTNER_ID
LEFT JOIN AD_ORG UT ON M.AD_ORG_ID = UT.AD_ORG_ID
WHERE
ut.ad_org_id = '1000878' and /**/
T.TABLENAME = 'C_BPartner' -- On ne prend que les changements à la fiche Tiers
AND UUSER.VALUE <> '000' -- On ne compte pas les modifs du siège
AND C.COLUMNNAME IN ('IsActive','Sexe','Date_Naiss','Z_Fonction_ID','Email_tiers','Name','Assurance','M_Warehouse_ID','Tel_tiers','CurrentMembre','Handicap','Name2','Membre','Mode_envoi','GSM_tiers','Publimailing')
AND NOT (L.OLDVALUE = 'NULL' AND L.NEWVALUE IS NULL) -- On ignore les erreurs dimport
AND L.CREATED >= to_date('01/09/2010','DD/MM/YYYY') /**/
AND L.CREATED <= to_date('01/10/2010','DD/MM/YYYY') /**/
group by DATEPART('ww',l.created), M.C_BPARTNER_ID
)tab1
group by tab1.sem
)tabA
left join
(SELECT tab2.sem sem, COUNT(C_BPARTNER_ID) NBRE_MODIFS_SIEGE from(
SELECT DISTINCT
DATEPART('ww',l.created) as sem,
M.C_BPARTNER_ID c_bpartner_id,
COUNT(L.AD_CHANGELOG_ID) NBRE_MODIFS
FROM AD_CHANGELOG L
INNER JOIN AD_TABLE T ON (L.AD_TABLE_ID = T.AD_TABLE_ID)
INNER JOIN AD_COLUMN C ON (L.AD_COLUMN_ID = C.AD_COLUMN_ID)
-- Auteur de la modification
INNER JOIN AD_USER U ON L.CREATEDBY = U.AD_USER_ID
INNER JOIN AD_ORG UUSER ON U.AD_ORG_ID = UUSER.AD_ORG_ID
-- Membre modifié
LEFT JOIN C_BPARTNER M ON L.RECORD_ID = M.C_BPARTNER_ID
LEFT JOIN AD_ORG UT ON M.AD_ORG_ID = UT.AD_ORG_ID
WHERE
ut.ad_org_id = '1000878' and /**/
T.TABLENAME = 'C_BPartner' -- On ne prend que les changements à la fiche Tiers
AND UUSER.VALUE = '000' -- On ne compte pas les modifs du siège
AND C.COLUMNNAME IN ('IsActive','Sexe','Date_Naiss','Z_Fonction_ID','Email_tiers','Name','Assurance','M_Warehouse_ID','Tel_tiers','CurrentMembre','Handicap','Name2','Membre','Mode_envoi','GSM_tiers','Publimailing')
AND NOT (L.OLDVALUE = 'NULL' AND L.NEWVALUE IS NULL) -- On ignore les erreurs dimport
AND L.CREATED >= to_date('01/09/2010','DD/MM/YYYY') /**/
AND L.CREATED <= to_date('01/10/2010','DD/MM/YYYY') /**/
group by DATEPART('ww',l.created), M.C_BPARTNER_ID
)tab2
group by tab2.sem
)tabB on tabB.sem = tabA.sem
order by tabA.sem |
Partager