Here's a quick query that I believe will give you your aging buckets along with basic information required to produce an AR Aging report. Try this in Query Analyzer first, then create it as a view if you find it works OK;
SELECT DebtorNumber AS Debtor_Number, ci.debcode AS Cus_Number, ci.cmp_name AS Cus_Name, SUM(ROUND((CASE WHEN bt.AmountDC > 0 AND
bt.Type = 'W' THEN bt.AmountDC ELSE (CASE WHEN bt.Type = 'S' AND bt.AmountDC < 0 THEN - bt.AmountDC ELSE NULL END) END), 2)) AS Debit,
SUM(ROUND((CASE WHEN bt.AmountDC < 0 AND bt.Type = 'W' THEN - bt.AmountDC ELSE (CASE WHEN bt.Type = 'S' AND
bt.AmountDC > 0 THEN bt.AmountDC ELSE NULL END) END), 2)) AS Credit, SUM(ROUND((CASE WHEN DATEDIFF(dd, ISNULL(bt.InvoiceDate,
bt.ProcessingDate), GetDate()) < 31 AND bt.Type = 'W' THEN bt.AmountDC ELSE (CASE WHEN DATEDIFF(dd, bt.ValueDate, GetDate()) < 31 AND
bt.Type = 'S' THEN - bt.AmountDC ELSE NULL END) END), 2)) AS T1, SUM(ROUND((CASE WHEN DATEDIFF(dd, ISNULL(bt.InvoiceDate,
bt.ProcessingDate), GetDate()) BETWEEN 31 AND 60 AND bt.Type = 'W' THEN bt.AmountDC ELSE (CASE WHEN DATEDIFF(dd, bt.ValueDate, GetDate())
BETWEEN 31 AND 60 AND bt.Type = 'S' THEN - bt.AmountDC ELSE NULL END) END), 2)) AS T2, SUM(ROUND((CASE WHEN DATEDIFF(dd,
ISNULL(bt.InvoiceDate, bt.ProcessingDate), GetDate()) BETWEEN 61 AND 90 AND bt.Type = 'W' THEN bt.AmountDC ELSE (CASE WHEN DATEDIFF(dd,
bt.ValueDate, GetDate()) BETWEEN 61 AND 90 AND bt.Type = 'S' THEN - bt.AmountDC ELSE NULL END) END), 2)) AS T3,
SUM(ROUND((CASE WHEN DATEDIFF(dd, ISNULL(bt.InvoiceDate, bt.ProcessingDate), GetDate()) > 90 AND
bt.Type = 'W' THEN bt.AmountDC ELSE (CASE WHEN DATEDIFF(dd, bt.ValueDate, GetDate()) > 90 AND bt.Type = 'S' THEN - bt.AmountDC ELSE NULL
END) END), 2)) AS T4, SUM(ROUND((CASE WHEN bt.Type = 'W' THEN bt.AmountDC ELSE - bt.AmountDC END), 2)) AS Total, COUNT(*) AS Term_Code,
AVG(DATEDIFF(dd, bt.InvoiceDate, GetDate())) AS Age, MAX(addr.AddressLine1) AS Address, MAX(addr.City) AS City, MAX(addr.Postcode) AS Zip,
MAX(addr.StateCode) AS State, ci.cmp_fctry AS Country, MAX(addr.Phone) AS Phone, MAX(addr.Fax) AS Fax
FROM ((SELECT '' AS Empty, bt.ID, DebtorNumber, CreditorNumber, ValueDate, AmountDC, AmountTC, ProcessingDate, InvoiceDate, Type, OffSetName,
PaymentType, SupplierInvoiceNumber, CAST(Description AS VARCHAR(400)) AS Description, TransactionType, OffsetReference,
OffSetLedgerAccountNumber, bt.Blocked, DocumentID, OrderNumber, InvoiceNumber, DueDate, TcCode, bt.Status, MatchID, BatchNumber,
OwnBankAccount, EntryNumber
FROM BankTransactions bt INNER JOIN
cicmpy ci ON bt.DebtorNumber = ci.debnr
WHERE Type = 'W' AND bt.Status IN ('C', 'A', 'P', 'J') AND EntryNumber IS NOT NULL AND MatchID IS NULL AND DebtorNumber IS NOT NULL AND
ROUND(AmountDC, 2) <> 0 AND (ci.cmp_type = 'C')
UNION ALL
SELECT '' AS Empty, s.ID, s.DebtorNumber, s.CreditorNumber, s.ValueDate, (IsNull(s.AmountDC, 0) - IsNull(W2.AmountDC, 0)) AS AmountDC,
(IsNull(s.AmountTC, 0) - IsNull(W2.AmountTC, 0)) AS AmountTC, s.ProcessingDate, s.InvoiceDate, s.Type, s.OffSetName, s.PaymentType,
s.SupplierInvoiceNumber, CAST(s.Description AS VARCHAR(400)) AS Description, s.TransactionType, s.OffsetReference,
s.OffSetLedgerAccountNumber, s.Blocked, s.DocumentID, s.OrderNumber, ISNULL(s.InvoiceNumber,
(SELECT TOP 1 g.faktuurnr
FROM gbkmut g
WHERE g.BankTransactionGuid = s.sysguid)) AS InvoiceNumber, s.DueDate, s.TCCode, s.Status, s.MatchID, s.BatchNumber,
s.OwnBankAccount, s.EntryNumber
FROM BankTransactions s INNER JOIN
cicmpy ci ON s.DebtorNumber = ci.debnr LEFT OUTER JOIN
(SELECT MatchID, ROUND(SUM(ROUND(ISNULL(AmountDC, 0), 2)), 2) AS AmountDC, ROUND(SUM(ROUND(ISNULL(AmountTC, 0), 2)), 2)
AS AmountTC
FROM BankTransactions w
WHERE w.Type = 'W' AND w.Status IN ('C', 'A', 'P', 'J') AND w.EntryNumber IS NOT NULL
GROUP BY MatchID
HAVING MatchID IS NOT NULL) AS W2 ON W2.MatchID = S.ID
WHERE s.Type = 'S' AND s.Status <> 'V' AND s.DebtorNumber IS NOT NULL AND ROUND(s.AmountDC, 2) <> 0 AND (IsNull(s.AmountDC, 0)
- IsNull(W2.AmountDC, 0)) <> 0 AND (ci.cmp_type = 'C'))) bt INNER JOIN
cicmpy ci ON bt.DebtorNumber = ci.debnr AND bt.DebtorNumber IS NOT NULL INNER JOIN
addresses addr ON ci.cmp_wwn = addr.account AND addr.Main = 1 AND addr.Type = 'INV' LEFT OUTER JOIN
cicntp cp ON cp.cnt_id = ci.cnt_id
WHERE ci.debcode IS NOT NULL AND ci.cmp_type = 'C'
GROUP BY ci.debcode, bt.Debtornumber, ci.cmp_name, ci.cmp_type, ci.cmp_status, ci.cmp_fctry
Peter Shirley
