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.
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