[SQL] Betalingen optellen en achterstand

[Pino]

Ik heb een SQL probleem waar ik al even op zit te broeden.

Ik heb twee tabellen per huurder, één tabel met schulden en 1 tabel met betalingen. Per boekjaar/periode combinatie komen betalingen binnen en worden schulden geboekt, bijv. de huur voor april.

Nu wil ik per periode de betalingsachterstanden kunnen uitrekenen, daarvoor heb ik de volgende query:

SQL:

select
  t.boekjaar,
  t.periode,
  (coalesce(sum(t.te_betalen),0) - coalesce(sum(b.betaald),0)) as tekort
from
  fin_te_betalen t 
  left join fin_betaald b on (b.contract_id = t.contract_id 
        AND b.boekjaar = t.boekjaar 
        AND b.periode = t.periode)
where
  t.contract_id = :nummer
  AND t.verwerkt <> 'T'
group by
  t.boekjaar,
  t.periode

Probleem met deze query is dat bij deze tabellen:

code:

fin_te_betalen:
  boekjaar periode te_betalen 
  ===========================
  2007     3       200       
  2007     4       200
  2007     4       25 

fin_betaald:
  boekjaar periode betaald 
  ========================
  2007     3       200       
  2007     4       100

het saldo van periode 3 goed geteld wordt, te weten 0, maar voor periode 4 de 100 van betaald twee keer gerekend wordt (door de left join denk ik) en er dus een tekort is van 25 ipv 125.

Hoe krijg ik deze berekening goed, zodat de optelling buiten de join gebeurt?

aanvullende info:

Database = Firebird 2.0
De structuur van de tabellen is niet aanpasbaar, ik heb het zo gekregen en zal het er mee moeten doen.

[ Voor 8% gewijzigd door Pino op 11-04-2007 23:32 ]

[P.O. Box]

ff uit mijn hoofd dacht ik meer aan zoiets:

SQL:

select 
  t.boekjaar, 
  t.periode, 
  (sum(q.te_betalen) - sum(b.betaald)) as verschil
from 
  fin_te_betalen t
  left join fin_te_betalen q on (q.contract_id = t.contract_id
    AND q.boekjaar = t.boekjaar
    AND q.periode <= t.periode)
  left join fin_betaald b on (b.contract_id = t.contract_id  
        AND b.boekjaar = t.boekjaar  
        AND b.periode <= t.periode) 
where 
  t.contract_id = :nummer 
  AND t.verwerkt <> 'T' 
group by 
  t.contract_id
  t.boekjaar, 
  t.periode

ik neem aan dat je betalingen en schulden uit eerdere maanden namelijk ook wilt meenemen.... verder lijkt mij de coalesce geen nut te hebben... volgens mij geeft de sum van nul records namelijk gewoon "0" als resultaat.... (maar dat kan ik mis hebben)

[Pino]

Edwardvb schreef op donderdag 12 april 2007 @ 09:03:

ik neem aan dat je betalingen en schulden uit eerdere maanden namelijk ook wilt meenemen.... verder lijkt mij de coalesce geen nut te hebben... volgens mij geeft de sum van nul records namelijk gewoon "0" als resultaat.... (maar dat kan ik mis hebben)

Ik wil idd de betalingen uit eerdere maanden ook weergeven, maar dat doe ik door alle tekorten die geen nul zijn op te tellen.

De coalesce is wel nodig, want anders komt er NULL uit bij een lege tabel en daar kan ik niet verder mee rekenen.

Ik heb de oplossing inmiddels gevonden met hulp van een ander forum maar hier nog even de juiste query:

SQL:

SELECT
  te.boekjaar,
  te.periode,
  (COALESCE(totaal_te_betalen, 0) - COALESCE(totaal_betaald, 0)) AS tekort
FROM
  (SELECT
     t.contract_id,
     t.boekjaar,
     t.periode,
     SUM(t.te_betalen) AS totaal_te_betalen
   FROM
     fin_te_betalen t
   WHERE
     t.verwerkt <> 'T'
   GROUP BY
     t.contract_id,
     t.boekjaar,
     t.periode) te
  LEFT JOIN (
    SELECT
      b.contract_id,
      b.boekjaar,
      b.periode,
      SUM(b.betaald) AS totaal_betaald
    FROM
      fin_betaald b
    GROUP BY
      b.contract_id,
      b.boekjaar,
      b.periode) bet ON
    (bet.contract_id = te.contract_id and bet.boekjaar = te.boekjaar and
     bet.periode = te.periode)
WHERE
  te.contract_id = :nummer