[SQL2K] Recursive Query (om openstaande facturen te bekijke)

[Roel Broersma]

Ik heb een datamodel dat er als volgt (vereenvoudigd) uit ziet:

TBL_PAYMENT_DETAILS

payment_detail_id	(int)*
payment_detail_amount	(money)
payment_detail_invoice_id	(int)

(hierin staan alle betalingen die aan facturen hangen, er kunnen 2 (deel)betalingen aan een factuur hangen)


TBL_INVOICES

invoice_id	(int)*
invoice_amount	(money)
invoice_belong_to	(int)

(dit is de facturen tabel, als een (min/credit)factuur aan een andere factuur hangt, dan wordt het veld invoice_belong_to gebruikt, voor de hoofd-factuur staat dit op 0 )


Nu wil ik alle openstaande facturen bekijken.
Ik doe dus:

code:

SELECT    MIN(invoice_id)
              FROM
                        tbl_invoices LEFT JOIN tbl_payment_details ON
                            tbl_invoices.invoice_id=tbl_payment_details.payment_detail_invoice_id
              WHERE     invoice_amount<>0
              GROUP BY  invoice_id
              HAVING    SUM(payment_detail_amount)<>MIN(invoice_amount)

Bovenstaande werkt, echter telt deze er geen (min/credit) facturen bij op.
Stel nu dat ik dus een factuur van 100 EURO heb, hier wordt 80 EURO van betaald en er staat tevens een credit-factuur van 20 EURO voor (die dus met invoice_belong_to is gehangen aan het invoice_id), dan zie ik in bovenstaande query de factuur nog gewoon open staan.
Het kan zelfs voorkomen dat er een factuur aan een factuur aan een factuur hangt, ik moet dus een soort RECURSIVE query erbij optellen of aftrekken.

Ik zit er al een paar dagen naar te kijken en kom er niet meer uit...

[ Voor 29% gewijzigd door Roel Broersma op 26-03-2004 12:52 ]

[Verwijderd]

Ik denk dat je beter even over je datamodel kan nadenken. Recursieve queries kan je in MS SQL niet (eenvoudig) maken. Bovendien is het erg ingewikkeld, onoverzichtelijk en een bron van (data)fouten. Die recursie moet je overal gaan implementeren waar je informatie over facturen wilt hebben (mutatie schermen, raporten enz.) Dat wil je niet op je geweten hebben

Even hardop denken:
Een factuur wordt meestal gestuurd a.d.h.v. van een order. Aan een order kunnen dus 1 of meer facturen hangen (meestal 1). misschien moet je je structuur aanpassen zodat je geen recursie meer hebt maar gewoon een lineair lijst. Op deze manier kan je per order eenvoudig alle facturen totaliseren.

Ik kan me zo voorstellen dat er nog veel meer oplossingen mogelijk zijn. Het hangt een beetje af van de rest van het datamodel.

[ Voor 15% gewijzigd door Verwijderd op 26-03-2004 12:54 ]

[cameodski]

Het is nog maar de vraag of hier sprake is van een recursieve query. Ik twijfel er in ieder geval nog aan.
Het lijkt me tenminste niet logisch dat je aan credit facturen ook weer debet/credit facturen koppelt.
Volgens mij kun je je probleem oplossen door een extra LEFT JOIN te doen op de credit facturen. Overigens moet je dan wel de rest van de query herschrijven, omdat je anders bepaalde bedragen wel eens dubbel kon gaan tellen.

[ Voor 39% gewijzigd door cameodski op 26-03-2004 12:56 ]

[Roel Broersma]

Je kan toch in SQL dingen gebruiken als WHILE...
Dan kun je toch ook recursive queries maken ?

Voor elke recursiviteit komt er dus een LEFT JOIN bij... (zoiets??)
Ik denk dat ik een soort (vereenvoudigd)

code:

WHILE (belong_to>0)
{LEFT JOIN = LEFT JOIN + LEFT JOIN}

loop moet doen

Ik wil het idd wel lineair gaan doen (dan hangt er dus hooguit nog maar 1 factuur aan een andere factuur); maar hoe moet ik de query dan schrijven ? (Een subselect in m;n HAVING ofzo ?)

[ Voor 26% gewijzigd door Roel Broersma op 26-03-2004 12:59 ]

[Roel Broersma]

edit:
(dubbelpost)

[ Voor 133% gewijzigd door Roel Broersma op 26-03-2004 13:02 ]

[Feyd-Rautha]

Deze SQL-syntax ken ik niet hoor. Dit zal dus wel geen ANSI-SQL zijn, maar 1 of andere specifieke variant. Zoals Transact-SQL of PL-SQL.

_{ik blijf dat toch maar een ranzige oplossing vinden}

[cameodski]

Roel Broersma schreef op 26 maart 2004 @ 12:56:
Je kan toch in SQL dingen gebruiken als WHILE...

Dan kun je toch ook recursive queries maken ?

Ik wil het idd wel lineair gaan doen (dan hangt er dus hooguit nog maar 1 factuur aan een andere factuur); maar hoe moet ik de query dan schrijven ? (Een subselect in m;n HAVING ofzo ?)

Je krijgt zoiets

code:

SELECT invoice_id
FROM tbl_invoices
LEFT JOIN ( SELECT invoice_id,
                   SUM (pd_amount) AS paid_amount
            FROM payment
            GROUP BY invoice_id
            ) AS paid ON (...)
LEFT JOIN ( SELECT invoice_belong_to,
                   SUM (invoice_amount) as credit_amount
            FROM invoice
            GROUP BY invoice_belong_to
            ) AS credit ON (tbl_invoices.invoice_id = credit.invoice_belong_to)

Roel Broersma schreef op 26 maart 2004 @ 12:56:
Je kan toch in SQL dingen gebruiken als WHILE...
Dan kun je toch ook recursive queries maken ?

Voor elke recursiviteit komt er dus een LEFT JOIN bij... (zoiets??)
Ik denk dat ik een soort (vereenvoudigd)

code:

WHILE (belong_to>0)
{LEFT JOIN = LEFT JOIN + LEFT JOIN}

loop moet doen

Ik wil het idd wel lineair gaan doen (dan hangt er dus hooguit nog maar 1 factuur aan een andere factuur); maar hoe moet ik de query dan schrijven ? (Een subselect in m;n HAVING ofzo ?)

MSSQL2000 kent geen recursieve queries.

[Verwijderd]

code:

WHILE (belong_to>0)
{LEFT JOIN = LEFT JOIN + LEFT JOIN}

Dat gaat niet in SQL. Een while lus in het FROM gedeelte van je query heb ik nog nooit gezien. Ik denk dat je moet afstappen van het recursieve idee.

Misschien moet je je afvragen of een factuur wel aan een andere factuur "gehangen" kan worden.
Zoals ik al eerder voorstelde; Waarom hangen niet alle facturen aan een order (via een koppeltabel). Een order van EUR 60 gulden kan dan 1 factuur hebben van EUR 80 en vervolgens nog 1 van EUR -20. Even alle facturen totaliseren en je hebt de som EUR 60 (=gelijk aan de order).

Ik zeg niet dat je het zo moet maken maar misschien dat het zo wel zou kunnen werken.

[ Voor 3% gewijzigd door Verwijderd op 26-03-2004 13:17 ]

[P_de_B]

Hier heb je echt geen recursieve functie voor nodig. De meeste dingen die je denkt recursief op te moeten lossen zijn simpel setbased op te lossen met een join.

Onderstaande query geeft het bedrag,alle betalingen en alle credits weer. Je kunt simpelweg de SUMs optellen om het openstaande bedrag te bepalen:

code:

SELECT 
    I.invoice_id ,SUM(i.invoice_amount) as TotalAmount,
    SUM(p.payment_detail_amount) as TotalPaid,
    SUM(c.invoice_amount) as TotalCredit
FROM 
    TBL_INVOICES I
INNER JOIN 
    TBL_PAYMENT_DETAILS P ON i.invoice_id = P.payment_detail_invoice_id
LEFT OUTER JOIN
    TBL_INVOICES C ON I.invoice_id = C.INVOICE_BELONG_TO
WHERE
    I.invoice_belong_to = 0 -- alleen hoofdfacturen
GROUP BY i.invoice_id

[cameodski]

P_de_B schreef op 26 maart 2004 @ 13:29:
Hier heb je echt geen recursieve functie voor nodig. De meeste dingen die je denkt recursief op te moeten lossen zijn simpel setbased op te lossen met een join.

Onderstaande query geeft het bedrag,alle betalingen en alle credits weer. Je kunt simpelweg de SUMs optellen om het openstaande bedrag te bepalen:

code:

SELECT 
    I.invoice_id ,SUM(i.invoice_amount) as TotalAmount,
    SUM(p.payment_detail_amount) as TotalPaid,
    SUM(c.invoice_amount) as TotalCredit
FROM 
    TBL_INVOICES I
INNER JOIN 
    TBL_PAYMENT_DETAILS P ON i.invoice_id = P.payment_detail_invoice_id
LEFT OUTER JOIN
    TBL_INVOICES C ON I.invoice_id = C.INVOICE_BELONG_TO
WHERE
    I.invoice_belong_to = 0 -- alleen hoofdfacturen
GROUP BY i.invoice_id

Deze query gaat niet altijd goed! Probeer het maar eens uit als je twee betalingen en twee credit facturen hebt.
En de JOIN op TBL_PAYMENT_DETAILS moet een LEFT OUTER JOIN zijn anders krijg je facturen waar nog niets voor betaald is niet te zien.

[ Voor 7% gewijzigd door cameodski op 26-03-2004 13:36 ]

[P_de_B]

cameodski schreef op 26 maart 2004 @ 13:31:
[...]

Deze query gaat niet altijd goed! Probeer het maar eens uit als je twee betalingen en twee credit facturen hebt.

.

Je hebt gelijk. Had ik geen rekening mee gehouden

edit: zo dan:

code:

SELECT 
    I.invoice_id ,SUM(i.invoice_amount) as TotalAmount,
    SUM(p.payment_detail_amount) as TotalPaid,
    SUM(c.invoice_amount) as TotalCredit
FROM 
    TBL_INVOICES I
LEFT OUTER JOIN 
        (SELECT Payment_detail_invoice_id, SUM(payment_detail_amount) as payment_detail_amount 
         FROM TBL_PAYMENT_DETAILS GROUP BY Payment_detail_invoice_id) P 
    ON i.Invoice_id = p.Payment_detail_invoice_id
LEFT OUTER JOIN
        (SELECT Invoice_belong_to, SUM(invoice_amount) as invoice_amount
        FROM TBL_INVOICES GROUP BY Invoice_belong_to ) C 
    ON I.invoice_id = C.INVOICE_BELONG_TO
WHERE I.invoice_belong_to = 0
GROUP BY i.invoice_id

[ Voor 55% gewijzigd door P_de_B op 26-03-2004 13:42 ]

[cameodski]

P_de_B schreef op 26 maart 2004 @ 13:35:
[...]
.

Je hebt gelijk. Had ik geen rekening mee gehouden

edit: zo dan:

Zo gaat ie volgens mij inderdaad beter werken. Alleen zitten er in de LEFT JOIN's al een GROUP BY op invoice_id, dus daarbuiten hoeft dat niet meer.
En dan heb je de SUM's in de select-list ook niet meer nodig.

[ Voor 46% gewijzigd door cameodski op 26-03-2004 13:49 ]

[P_de_B]

cameodski schreef op 26 maart 2004 @ 13:49:
[...]

En dan heb je de SUM's in de select-list ook niet meer nodig.

hmm, die stonden er nog ja

[jvdmeer]

P_de_B schreef op 26 maart 2004 @ 13:35:
[...]
.

Je hebt gelijk. Had ik geen rekening mee gehouden
[...]

Je kan natuurlijk ook alle creditfacturen optellen bij de betalingen om te achterhalen wat er nog open staat. Bijv: (ongetest uit m'n hoofd):

code:

SELECT I.invoice_id,
       SUM(i.invoice_amount) as TotalAmount,
       SUM(c.payment_detail_amount) as StillToPay
  FROM TBL_INVOICES I
  LEFT OUTER JOIN 
   (SELECT Invoice_id, SUM(payment_detail_amount) as payment_detail_amount 
      FROM 
       (
         (SELECT Payment_detail_invoice_id AS Invoice_id, SUM(payment_detail_amount) as payment_detail_amount 
            FROM TBL_PAYMENT_DETAILS 
            GROUP BY Payment_detail_invoice_id)
         UNION
         (SELECT Invoice_belong_to AS Invoice_id, SUM(invoice_amount) as payment_detail_amount
            FROM TBL_INVOICES 
            GROUP BY Invoice_belong_to)
       ) C
    ) p 
  ON i.Invoice_id = p.Invoice_id
GROUP BY i.invoice_id