Complexe SQL Query

Question

I have 3 tables:

Table Invoices(Invoice, InvoiceAmount(float), Other infos ...), table Payments(Payment, PaymentAmount(float), Other infos ...) and table PaymentsDet(Id, Invoice, Payment, Amount(float)). The table PaymentsDet link an invoice and a payment with an amount (the portion of the invoice paid by that payment).

I need a query that returns informations about each invoice +

IF (there is exactly 1 payement for that invoice)

Payment,SUM(PayementsDet.Amount), Other Payment infos ...

ELSE (More than 1 payement or no payement at all)

Count(Payment), SUM(PayementsDet.Amount), Complete other Payment infos with NULL values or ''.

Thank you for your time, and hope there is someone smart enough that can help me on this.

EDIT :

    SELECT        Factures.Facture, Factures.Client, Factures.DateFacture, Factures.MoisFacture, Factures.DateRéception, Factures.Echéance, Factures.Montant, Factures.TxTVA, 
                         Factures.Activité,
                             (SELECT        CASE WHEN SUM(Montant) IS NULL THEN '0' ELSE SUM(Montant) END AS Expr1
                               FROM            RèglementsDet
                               WHERE        (Facture = Factures.Facture) AND (Validé = 1)) AS MontantRegl,
                             (SELECT        CASE WHEN COUNT(DISTINCT Règlements.Règlement) > '1' THEN COUNT(DISTINCT Règlements.Règlement) 
                                                         WHEN COUNT(DISTINCT Règlements.Règlement) = '1' THEN
                                                             (SELECT        MIN(Règlements.Règlement) AS Expr1
                                                               FROM            Règlements INNER JOIN
                                                                                         RèglementsDet ON Règlements.Règlement = RèglementsDet.Règlement
                                                               WHERE        (RèglementsDet.Facture = Factures.Facture)) END AS Règlement
                               FROM            Règlements INNER JOIN
                                                         RèglementsDet AS RèglementsDet_2 ON Règlements.Règlement = RèglementsDet_2.Règlement
                               WHERE        (RèglementsDet_2.Facture = Factures.Facture) AND (RèglementsDet_2.Validé = 1)) AS Règlement
FROM            Factures LEFT OUTER JOIN
                         RèglementsDet AS RèglementsDet_1 ON Factures.Facture = RèglementsDet_1.Facture
GROUP BY Factures.Facture, Factures.Client, Factures.DateFacture, Factures.MoisFacture, Factures.DateRéception, Factures.Echéance, Factures.Montant, Factures.TxTVA, 
                         Factures.Activité

I think I figured it out, if anyone got a better (more readable) query.

You should show us your query as far as it got and tell us where you got stuck, so we can give you the appropriate hint how to complete it. — Thorsten Kettner, May 21 '14 at 11:30

score 0 · Answer 1 · answered May 21 '14 at 15:26

Because, you're using CASE statement, i guess you should understand this SQL query easily and adjust names to your fields names:

(SELECT P.Payement,SUM(D.Amount), P.CreatedAt, P.Expired FROM Invoices I, Payements P, PayementsDet D WHERE 1 = (
    SELECT(
             CASE 
                  WHEN (SELECT count(D.Payement) FROM Invoices I, Payements P WHERE ( D.Invoice = I.Invoice AND P.Payement = D.Payement ) GROUP BY I.Invoice) = 1 
                     THEN 1 
                  ELSE 0 
             END)
    FROM  Invoices I, Payements P, PayementsDet D
))
UNION
(SELECT P.Payement,SUM(D.Amount), null, null FROM Invoices I, Payements P, PayementsDet D WHERE 0 = (
    SELECT(
             CASE 
                  WHEN (SELECT count(D.Payement) FROM Invoices I, Payements P WHERE ( D.Invoice = I.Invoice AND P.Payement = D.Payement ) GROUP BY I.Invoice) = 1 
                     THEN 1 
                  ELSE 0 
             END)
    FROM  Invoices I, Payements P, PayementsDet D
));

Database schema

score 0 · Accepted Answer · answered May 22 '14 at 09:45

Thank you Fares for your time and effort.

The problem in my case wasn't when there is 1 or 0 payment, but the real problem was when I got multiple payments for the same invoice. At last I figured out I way to do it even if it is kinda complexe, but here is the solution I found hope it will help someone else out there.

SELECT        Factures.Facture, Factures.Client AS [Code C/F], Factures.DateFacture, Factures.MoisFacture, Factures.DateRéception, Factures.Echéance, Factures.Montant, 
                         Factures.TxTVA, Factures.Activité,
                             (SELECT        CASE WHEN SUM(Montant) IS NULL THEN '0' ELSE SUM(Montant) END AS Expr1
                               FROM            RèglementsDet
                               WHERE        (Facture = Factures.Facture) AND (Validé = 1)) AS MontantRegl,
                             (SELECT        CASE WHEN COUNT(DISTINCT Règlements.Règlement) > '1' THEN COUNT(DISTINCT Règlements.Règlement) 
                                                         WHEN COUNT(DISTINCT Règlements.Règlement) = '1' THEN
                                                             (SELECT        MIN(Règlements.Règlement) AS Expr1
                                                               FROM            Règlements INNER JOIN
                                                                                         RèglementsDet ON Règlements.Règlement = RèglementsDet.Règlement
                                                               WHERE        (RèglementsDet.Facture = Factures.Facture)) END AS Règlement
                               FROM            Règlements INNER JOIN
                                                         RèglementsDet AS RèglementsDet_2 ON Règlements.Règlement = RèglementsDet_2.Règlement
                               WHERE        (RèglementsDet_2.Facture = Factures.Facture) AND (RèglementsDet_2.Validé = 1)) AS Règlement,
                             (SELECT        CASE WHEN COUNT(DISTINCT Règlements_1.Règlement) = '1' THEN
                                                             (SELECT        TOP (1) Règlements.Banque AS Expr1
                                                               FROM            Règlements INNER JOIN
                                                                                         RèglementsDet ON Règlements.Règlement = RèglementsDet.Règlement
                                                               WHERE        (RèglementsDet.Facture = Factures.Facture)) ELSE '' END AS Expr1
                               FROM            Règlements AS Règlements_1 INNER JOIN
                                                         RèglementsDet AS RèglementsDet_2 ON Règlements_1.Règlement = RèglementsDet_2.Règlement
                               WHERE        (RèglementsDet_2.Facture = Factures.Facture) AND (RèglementsDet_2.Validé = 1)) AS Banque,
                             (SELECT        CASE WHEN COUNT(DISTINCT Règlements_1.Règlement) = '1' THEN
                                                             (SELECT        TOP (1) Règlements.ModeDeRèglement AS Expr1
                                                               FROM            Règlements INNER JOIN
                                                                                         RèglementsDet ON Règlements.Règlement = RèglementsDet.Règlement
                                                               WHERE        (RèglementsDet.Facture = Factures.Facture)) ELSE '' END AS Expr1
                               FROM            Règlements AS Règlements_1 INNER JOIN
                                                         RèglementsDet AS RèglementsDet_2 ON Règlements_1.Règlement = RèglementsDet_2.Règlement
                               WHERE        (RèglementsDet_2.Facture = Factures.Facture) AND (RèglementsDet_2.Validé = 1)) AS ModeRegl,
                             (SELECT        CASE WHEN COUNT(DISTINCT Règlements_1.Règlement) = '1' THEN
                                                             (SELECT        TOP (1) Règlements.NumDocument AS Expr1
                                                               FROM            Règlements INNER JOIN
                                                                                         RèglementsDet ON Règlements.Règlement = RèglementsDet.Règlement
                                                               WHERE        (RèglementsDet.Facture = Factures.Facture)) ELSE '' END AS Expr1
                               FROM            Règlements AS Règlements_1 INNER JOIN
                                                         RèglementsDet AS RèglementsDet_2 ON Règlements_1.Règlement = RèglementsDet_2.Règlement
                               WHERE        (RèglementsDet_2.Facture = Factures.Facture) AND (RèglementsDet_2.Validé = 1)) AS NumDocument,
                             (SELECT        CASE WHEN COUNT(DISTINCT Règlements_1.Règlement) = '1' THEN
                                                             (SELECT        TOP (1) Règlements.DateRèglement AS Expr1
                                                               FROM            Règlements INNER JOIN
                                                                                         RèglementsDet ON Règlements.Règlement = RèglementsDet.Règlement
                                                               WHERE        (RèglementsDet.Facture = Factures.Facture)) ELSE NULL END AS Expr1
                               FROM            Règlements AS Règlements_1 INNER JOIN
                                                         RèglementsDet AS RèglementsDet_2 ON Règlements_1.Règlement = RèglementsDet_2.Règlement
                               WHERE        (RèglementsDet_2.Facture = Factures.Facture) AND (RèglementsDet_2.Validé = 1)) AS DateRèglement, Factures.Montant -
                             (SELECT        CASE WHEN SUM(Montant) IS NULL THEN '0' ELSE SUM(Montant) END AS Expr1
                               FROM            RèglementsDet AS RèglementsDet_3
                               WHERE        (Facture = Factures.Facture) AND (Validé = 1)) AS Solde
FROM            Factures LEFT OUTER JOIN
                         RèglementsDet AS RèglementsDet_1 ON Factures.Facture = RèglementsDet_1.Facture
GROUP BY Factures.Facture, Factures.Client, Factures.DateFacture, Factures.MoisFacture, Factures.DateRéception, Factures.Echéance, Factures.Montant, Factures.TxTVA, 
                         Factures.Activité

P.S : Facture = Invoice and Règlement = Payment

Just to clarify my logic, look carefully at **case** statement, it returns **1** if an invoice has exactly one payment, and returns **0** else (ie. when invoice has zero or at least two payments), the **1** and **0** I used doesn't mean the count of payments but we have to take it as boolean flag and not as a counter. — Fares M., May 22 '14 at 14:46
Thank you for the clarification, but in the case of more than 2 payments wich one is selected in the column P.Payement ? — Ismail Karchi, May 23 '14 at 10:34
But you said on your question: **ELSE (More than 1 payement or no payement at all) Count(Payment), SUM(PayementsDet.Amount), Complete other Payment infos** with **NULL** values. — Fares M., May 23 '14 at 10:38
Yeah you are correct except in one little detail, in this case I need COUNT(P.Payment) not P.Payement — Ismail Karchi, May 23 '14 at 11:19

Complexe SQL Query

2 Answers2