April 6, 2009 at 3:14 am
Hi
We have a report showing the customers with outstanding invoices.
But he's showing all customers wether there are outstanding invoices or not.
How can we get these lines out ?
We already tried in the query :
SELECT CustMain.No_, CustMain.Name, CustMain.[Payment Method Code] AS Betalingsconditie, CustMain.[Responsibility Center], Blocked,
(SELECT sum(DCLE.Amount)
FROM [FIN SEPIA$Cust_ Ledger Entry] AS CLE JOIN
dbo.[FIN SEPIA$Sales Invoice Header] AS IH ON CLE.[Document No_] = IH.No_ JOIN
dbo.[FIN SEPIA$Detailed Cust_ Ledg_ Entry] AS DCLE ON DCLE.[Document No_] = CLE.[Document No_]
WHERE (CLE.[Customer No_] = CustMain.No_) AND [Open] = 1) AS [Total],
(SELECT sum(DCLE.Amount)
FROM [FIN SEPIA$Cust_ Ledger Entry] AS CLE JOIN
dbo.[FIN SEPIA$Sales Invoice Header] AS IH ON CLE.[Document No_] = IH.No_ JOIN
dbo.[FIN SEPIA$Detailed Cust_ Ledg_ Entry] AS DCLE ON DCLE.[Document No_] = CLE.[Document No_]
WHERE (CLE.[Customer No_] = CustMain.No_) AND [Open] = 1 AND (IH.[Due Date] between @dtMin30 and @dtNow)) AS [<30]
FROM dbo.[FIN SEPIA$Customer] AS CustMain
where CustMain.[Responsibility Center] in (@Divisie) and (SELECT sum(DCLE.Amount)
FROM [FIN SEPIA$Cust_ Ledger Entry] AS CLE JOIN
dbo.[FIN SEPIA$Sales Invoice Header] AS IH ON CLE.[Document No_] = IH.No_ JOIN
dbo.[FIN SEPIA$Detailed Cust_ Ledg_ Entry] AS DCLE ON DCLE.[Document No_] = CLE.[Document No_]
WHERE (CLE.[Customer No_] = CustMain.No_) AND [Open] = 1) > 0
UNION
SELECT CustMain.No_, CustMain.Name, CustMain.[Payment Method Code] AS Betalingsconditie, CustMain.[Responsibility Center], CustMain.Blocked,
(SELECT sum(DCLE.Amount)
FROM [FIN SEPIA$Cust_ Ledger Entry] AS CLE JOIN
dbo.[FIN SEPIA$Sales Cr_Memo Header] AS SH ON CLE.[Document No_] = SH.No_ JOIN
dbo.[FIN SEPIA$Detailed Cust_ Ledg_ Entry] AS DCLE ON DCLE.[Document No_] = CLE.[Document No_]
WHERE (CLE.[Customer No_] = CustMain.No_) AND [Open] = 1) AS [Total],
(SELECT sum(DCLE.Amount)
FROM [FIN SEPIA$Cust_ Ledger Entry] AS CLE JOIN
dbo.[FIN SEPIA$Sales Cr_Memo Header] AS SH ON CLE.[Document No_] = SH.No_ JOIN
dbo.[FIN SEPIA$Detailed Cust_ Ledg_ Entry] AS DCLE ON DCLE.[Document No_] = CLE.[Document No_]
WHERE (CLE.[Customer No_] = CustMain.No_) AND [Open] = 1 AND (SH.[Due Date] between @dtMin30 and @dtNow)) AS [<30]
FROM dbo.[FIN SEPIA$Customer] AS CustMain where CustMain.[Responsibility Center] in (@Divisie)
and (SELECT sum(DCLE.Amount)
FROM [FIN SEPIA$Cust_ Ledger Entry] AS CLE JOIN
dbo.[FIN SEPIA$Sales Cr_Memo Header] AS SH ON CLE.[Document No_] = SH.No_ JOIN
dbo.[FIN SEPIA$Detailed Cust_ Ledg_ Entry] AS DCLE ON DCLE.[Document No_] = CLE.[Document No_]
WHERE (CLE.[Customer No_] = CustMain.No_) AND [Open] = 1) < 0
Anybody has a clue ?
Kind regards
JV
April 6, 2009 at 3:20 am
As i was posting, i just got the idea 🙂
So don't bother any longer and help some other guys and girlz !!!
Thx !
JV
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply