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
| SELECT G_T1.ID_Client ,
G_T1.ID_Nom ,
G_T1.ID_Age ,
G_T0.N_Fail ,
G_T2.Date_MOIS ,
G_T5.G_F1 ,
G_T3.Flag
FROM tab1 G_T0,
tab2 G_T1,
tab3 G_T2,
tab3 G_T3,
tab5 G_T4,
(SELECT SUM(G_T7.Taux * G_T6.nmbre ) AS G_F1,
G_T8.ID_Age AS G_F2,
G_T6.ID_Nom AS G_F3
FROM tab2 G_T6,
tab1 G_T7,
tab5 G_T8
WHERE ( G_T6.ID_Nom = G_T7.ID_Nom AND G_T6.ID_Client = G_T7.ID_Client AND G_T7.Date_MOIS = 201312 )
AND ( G_T8.ID_Age = G_T6.ID_Age )
AND ( ( G_T6.Date_MOIS BETWEEN 201401 AND TO_NUMBER(TO_CHAR(DECODE ( 'm' ,'d', G_T8.Date_2 + - 1 ,'dd', G_T8.Date_2 + - 1 ,'ddd', G_T8.Date_2 + - 1 ,'dddd', G_T8.Date_2 + - 1 ,'m', ADD_MONTHS(G_T8.Date_2,- 1 ),'mm', ADD_MONTHS(G_T8.Date_2,- 1 ),'mmm', ADD_MONTHS(G_T8.Date_2,- 1 ),'mmmm', ADD_MONTHS(G_T8.Date_2,- 1 ),'y', ADD_MONTHS(G_T8.Date_2,- 1 *12),'yy', ADD_MONTHS(G_T8.Date_2,- 1 *12),'yyyy', ADD_MONTHS(G_T8.Date_2,- 1 *2),'h', G_T8.Date_2 + 1/24,'hh', G_T8.Date_2 + 1/24,'n', G_T8.Date_2 + 1/1440,'nn', G_T8.Date_2 + 1/1440,'s',G_T8.Date_2 + 1/86400,'ss',G_T8.Date_2 + 1/86400) ,'yyyymm' ) ) ) AND ( G_T8.C_STAT_NEGOCE = 2 AND TO_NUMBER(TO_CHAR(G_T8.Date_2,'yyyymm' ) ) <= 201402 ) )
GROUP BY G_T8.ID_Age ,
G_T6.ID_Nom
) G_T5
WHERE ( G_T1.ID_Client = G_T3.ID_Client AND G_T1.ID_Nom = G_T3.ID_Nom AND G_T3.Flag= 'C' )
AND ( G_T2.Date_MOIS_CUM = G_T1.Date_MOIS AND G_T2.Date_MOIS = 201402 )
AND ( G_T0.ID_Client = G_T1.ID_Client AND G_T0.ID_Nom = G_T1.ID_Nom AND G_T0.Date_MOIS = 201312 )
AND ( G_T1.ID_Age = G_T4.ID_Age )
AND ( G_T4.ID_Age = G_T5.G_F2 )
AND ( ( G_T4.C_STAT_NEGOCE = 2 AND TO_NUMBER(TO_CHAR(G_T4.Date_2,'yyyymm' ) ) <= 201402 ))
GROUP BY G_T1.ID_Client ,
G_T1.ID_Nom ,
G_T1.ID_Age ,
G_T0.N_Fail ,
G_T2.Date_MOIS ,
G_T5.G_F1 ,
G_T3.Flag |
Partager