I don't have a CR report for this, but I have a fairly detailed SQL script
that I run in ISQL/w that has this info. We have two billing groups
("Trade" and "Charity") that we need to separate from the regular
payments/sales/etc., so that's why they're listed separately below.
Josh Hillman
hillman@talstar.com
/* Daily Payments (Emerald 2.5) */
/* Lists payment and credit details with totals for all billing groups */
SET NOCOUNT ON
DECLARE @GroupId int, @PaymentDate datetime
SELECT @PaymentDate = '7/14/1999'
SELECT "Payment Date"=CONVERT(char(10), @PaymentDate, 101),
"Date Printed"=GetDate()
PRINT ''
DECLARE BillingGroup_cursor CURSOR
FOR SELECT GroupID
FROM Groups
ORDER BY SortOrder
OPEN BillingGroup_cursor
FETCH NEXT FROM BillingGroup_cursor INTO @GroupID
WHILE (@@fetch_status <> -1)
BEGIN /* beginning of main loop */
/* Don't display empty listings for the current billing group */
IF EXISTS (SELECT ma.GroupID
FROM MasterAccounts ma, Payments p
WHERE p.CustomerID = ma.CustomerID
AND ma.GroupID = @GroupID
AND DATEDIFF(Day, p.Date, @PaymentDate) = 0)
BEGIN /* Display detailed payment listings */
SET NOCOUNT ON
SELECT ''=GroupName
FROM Groups
WHERE GroupID = @GroupID
SET NOCOUNT OFF
SELECT DISTINCT "Payment Type"=p.Type,
ii.InvoiceID,
"Amount Paid"=p.Amount,
p.PayInfo,
Name=(ma.FirstName+' '+ma.LastName),
ma.Company
FROM MasterAccounts ma, Payments p, InvoiceItems ii
WHERE DATEDIFF(Day, p.Date, @PaymentDate) = 0
AND ma.GroupID = @GroupID
AND ma.CustomerID = p.CustomerID
AND p.PaymentID = ii.PaymentID
/* Include payments that are missing associations with InvoiceIDs */
UNION
SELECT DISTINCT "Payment Type"=p.Type,
"InvoiceID "=null,
"Amount Paid"=p.Amount,
p.PayInfo,
Name=(ma.FirstName+' '+ma.LastName),
ma.Company
FROM MasterAccounts ma, Payments p
WHERE DATEDIFF(Day, p.Date, @PaymentDate) = 0
AND ma.CustomerID = p.CustomerID
AND ma.GroupID = @GroupID
AND NOT EXISTS (SELECT ii.InvoiceID
FROM InvoiceItems ii
WHERE p.PaymentID = ii.PaymentID)
ORDER BY p.Type, ii.InvoiceID
SET NOCOUNT ON
END /* end of detailed payment listings */
FETCH NEXT FROM BillingGroup_cursor INTO @GroupID
END /* end of main loop */
CLOSE BillingGroup_cursor
DEALLOCATE BillingGroup_cursor
/* TOTALS */
/* Payments */
SELECT "Payments"=g.Groupname,
p.Type,
Total=sum(p.Amount)
FROM Payments p, MasterAccounts ma, Groups g
WHERE DATEDIFF(Day, p.Date, @PaymentDate) = 0
AND ma.CustomerID = p.CustomerID
AND g.GroupID = ma.GroupID
AND (p.Type NOT LIKE '%Trade%' AND p.Type NOT LIKE '%Charity%')
AND p.Amount > 0
GROUP BY g.GroupName, p.Type
ORDER BY g.GroupName, p.Type
Compute Sum(sum(p.Amount))
/* Trade / Charity */
/* Don't display blank totals if there aren't any */
IF EXISTS (SELECT PaymentID
FROM Payments
WHERE DATEDIFF(Day, Date, @PaymentDate) = 0
AND Amount > 0
AND (Type LIKE '%Trade%' OR Type LIKE '%Charity%'))
BEGIN
SELECT "Trade/Charity"=Type,
Total=sum(Amount)
FROM Payments p
WHERE DATEDIFF(Day, Date, @PaymentDate) = 0
AND (Type LIKE '%Trade%' OR Type LIKE '%Charity%')
AND Amount > 0
GROUP BY Type
ORDER BY Type
Compute Sum(sum(Amount))
END
ELSE PRINT 'Trade / Charity: 0.00'
/* Credits */
/* Don't display blank totals if there aren't any */
IF EXISTS (SELECT p.PaymentID
FROM Payments p, MasterAccounts ma, Groups g
WHERE DATEDIFF(Day, p.Date, @PaymentDate) = 0
AND p.CustomerID = ma.CustomerID
AND ma.GroupID = g.GroupID
AND p.Amount < 0)
BEGIN
SELECT Credits=g.GroupName,
p.Type,
Total=sum(p.Amount)
FROM Payments p, MasterAccounts ma, Groups g
WHERE DATEDIFF(Day, p.Date, @PaymentDate) = 0
AND ma.CustomerID = p.CustomerID
AND g.GroupID = ma.GroupID
AND p.Amount < 0
GROUP BY g.GroupName, p.Type
ORDER BY g.GroupName, p.Type
Compute Sum(sum(p.Amount))
END
ELSE PRINT 'Credits: 0.00'
For more information about this list (including removal) go to:
http://www.iea-software.com/support/maillists/liststart