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
DATE_FOURNISS_PRIX,
max(QRY.PDOUBLE_STD) AS DOUBLE_STANDARD,
max(QRY.PSINGLE_STD) AS SINGLE_STANDARD,
max(QRY.PTRIPLE_STD) AS TRIPLE_STANDARD,
max(QRY.PQUAD_STD) AS QUAD_STANDARD,
max(QRY.PDOUBLE_DELUX) AS DOUBLE_DELUX,
max(QRY.PSINGLE_DELUX) AS SINGLE_DELUX,
max(QRY.PTRIPLE_DELUX) AS TRIPLE_DELUX,
max(QRY.PQUAD_DELUX) AS QUAD_DELUX,
max(QRY.PDOUBLE_JUNIO) AS DOUBLE_JUNIOR,
max(QRY.PSINGLE_JUNIO) AS SINGLE_JUNIOR,
max(QRY.PTRIPLE_JUNIO) AS TRIPLE_JUNIOR,
max(QRY.PQUAD_JUNIO) AS QUAD_JUNIOR
from ( select DATE_FOURNISS_PRIX,
if(IDTYPE_SERVICE = 20090 and fourniss_prix.cdexcursio is null,min(service_prix.PV1_SERVICE_PRIX) ,0) as PDOUBLE_STD,
if(IDTYPE_SERVICE = 20475 and fourniss_prix.cdexcursio is null,min(service_prix.PV1_SERVICE_PRIX) ,0) as PSINGLE_STD,
if(IDTYPE_SERVICE = 20091 and fourniss_prix.cdexcursio is null,min(service_prix.PV1_SERVICE_PRIX) ,0) as PTRIPLE_STD,
if(IDTYPE_SERVICE = 20093 and fourniss_prix.cdexcursio is null,min(service_prix.PV1_SERVICE_PRIX) ,0) as PQUAD_STD,
if(IDTYPE_SERVICE = 20090 and fourniss_prix.cdexcursio ='DELUX',min(service_prix.PV1_SERVICE_PRIX) ,0) as PDOUBLE_DELUX,
if(IDTYPE_SERVICE = 20475 and fourniss_prix.cdexcursio ='DELUX',min(service_prix.PV1_SERVICE_PRIX) ,0) as PSINGLE_DELUX,
if(IDTYPE_SERVICE = 20091 and fourniss_prix.cdexcursio ='DELUX',min(service_prix.PV1_SERVICE_PRIX) ,0) as PTRIPLE_DELUX,
if(IDTYPE_SERVICE = 20093 and fourniss_prix.cdexcursio ='DELUX',min(service_prix.PV1_SERVICE_PRIX) ,0) as PQUAD_DELUX,
if(IDTYPE_SERVICE = 20090 and fourniss_prix.cdexcursio ='JUNIO',min(service_prix.PV1_SERVICE_PRIX) ,0) as PDOUBLE_JUNIO,
if(IDTYPE_SERVICE = 20475 and fourniss_prix.cdexcursio ='JUNIO',min(service_prix.PV1_SERVICE_PRIX) ,0) as PSINGLE_JUNIO,
if(IDTYPE_SERVICE = 20091 and fourniss_prix.cdexcursio ='JUNIO',min(service_prix.PV1_SERVICE_PRIX) ,0) as PTRIPLE_JUNIO,
if(IDTYPE_SERVICE = 20093 and fourniss_prix.cdexcursio ='JUNIO',min(service_prix.PV1_SERVICE_PRIX) ,0) as PQUAD_JUNIO
from fourniss_prix,service_prix
where fourniss_prix.IDFOURNISS_PRIX = service_prix.IDFOURNISS_PRIX
and CDFOURNISS_FOURNISS_PRIX ='xx'
and CDCONTRAT_FOURNISS_PRIX ='xx'
and CDCLIENT is null
and DATE_FOURNISS_PRIX between 20091101 and 20101031
group by DATE_FOURNISS_PRIX,IDTYPE_SERVICE,CDEXCURSIO
order by DATE_FOURNISS_PRIX
)
as QRY
group by
DATE_FOURNISS_PRIX
order by DATE_FOURNISS_PRIX |
Partager