1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16
| SELECT distinct psc.vente_id , pp.msisdn,sum(pp.amount),count(*)
FROM ppc_sim_cust psc, ppc_payments pp
WHERE pp.opsc_ask_state=5
AND pp.opsc_reply=0
AND psc.offre_id ='41'
AND psc.status = 'i'
AND psc.stamsi = 'A'
AND psc.vente_id = pp.vente_id
AND ((pp.opsc_reply_date is null
AND to_date('20061120','YYYYMMDD')<=pp.insert_date )
OR (pp.opsc_reply_date is not null
AND to_date('20061120','YYYYMMDD')<=pp.opsc_reply_date ))
AND ( (pp.payment_object = 'R' AND pp.payment_type='RIB' ) OR ( pp.payment_object = 'R' AND pp.payment_type='CB') OR ( pp.payment_object = 'R' AND pp.payment_type='SC') OR ( pp.payment_object = 'R' AND pp.payment_type='DAB'))
AND psc.vente_id NOT LIKE '%1'
group by psc.vente_id , pp.msisdn
having count(*) > 4; |
Partager