Hi
I have my accounting in a database, and I have a problem with subqueries,
here is what I have :
SELECT f.numero,
f.id_client,
f.date_creation,
(f.date_creation + (f.echeance_paiement||' days')::interval)::date
AS echeance,
f.montant_ttc,
ROUND(CASE WHEN remise IS NULL THEN 0 ELSE remise END - CASE WHEN
facture IS NULL THEN 0 ELSE facture END,2) AS solde,
CASE WHEN (f.date_creation + (f.echeance_paiement||'
days')::interval)::date < 'now'::date
THEN round(f.montant_ttc * 10 / 100 * ('now'::date -
(f.date_creation + (f.echeance_paiement||' days')::interval)::date)::int /
365, 2)
ELSE NULL
END AS penalite
FROM facture AS f
JOIN (SELECT ff.id_client,
SUM(ff.montant_ttc / df.taux) AS facture
FROM facture AS ff
JOIN devise AS df USING (id_devise)
GROUP BY ff.id_client
) AS fff USING (id_client)
LEFT OUTER JOIN (SELECT rr.id_client,
SUM(rr.montant / dr.taux) AS remise
FROM remise AS rr
JOIN devise AS dr USING (id_devise)
GROUP BY rr.id_client
) AS rrr USING (id_client)
WHERE ROUND(CASE WHEN remise IS NULL THEN 0 ELSE remise END - CASE WHEN
facture IS NULL THEN 0 ELSE facture END,2) < 0
GROUP BY f.numero, f.date_creation, f.date_creation +
(f.echeance_paiement||' days')::interval, f.id_client, f.montant_ttc,
rrr.remise, fff.facture
ORDER BY f.id_client, f.numero
Table "facture"
Column | Type
-------------------+-----------------------
id_facture | integer
date_creation | date
date_modif | date
echeance_paiement | integer
id_client | integer
id_devise | integer
genere | integer
montant_ht | double precision
montant_tva | double precision
montant_ttc | double precision
solde_anterieur | double precision
total_a_payer | double precision
numero | character varying(15)
ref | character varying(60)
responsable | character varying(60)
contact | character varying(60)
num_tva | character varying(60)
adresse | text
pied | text
commentaire | text
email | text
Table "remise"
Column | Type
----------------+------------------
id_remise | integer
date_paiement | date
date_remise | date
id_client | integer
id_type_remise | integer
id_devise | integer
id_banque | integer
montant | double precision
commentaire | text
Table "devise"
Column | Type
-----------+-----------------------
id_devise | integer
taux | double precision
devise | character varying(30)
symbole | character varying(15)
It finds the invoices (facture) from my customers who forgot to pay me.
but, the probem is that it gives me all the invoices and not only the ones
which are not paid, so, I wanted to add something like :
WHERE ff.date_creation <= f.date_creation
in the first subselect, and
WHERE rr.date_paiement <= f.date_creation
in the second subselect, but I can't because postgresql does not seem to be
able to do it. Any idea ?
--
Mathieu Arnold
---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
