Message-ID: <200401021014370921.15309DB8@mail.powersupply.net> Date: Fri, 02 Jan 2004 10:14:37 -0500 From: "PowerNet Support" <psc5@powersupply.net> Subject: [Emerald] Emerald Script HELP.
I have this script that works, but what I would really like is for this to
only show me payments of $100 or more.
Can anyone change this to do that? I need this ASAP if anyone can help.
/* Monthly 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 = '3/1/2003'
SELECT "Monthly Payments"=(datename(month, @Paymentdate)+',
'+datename(year, @Paymentdate)),
"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(Month, 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 Date"=p.Date,
ii.InvoiceID,
"Amount Paid"=p.Amount,
"Payment Type"=p.Type,
p.PayInfo,
Name=(ma.FirstName+' '+ma.LastName),
ma.Company
FROM MasterAccounts ma, Payments p, InvoiceItems ii
WHERE DATEDIFF(Month, 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 Date"=p.Date,
"InvoiceID "=null,
"Amount Paid"=p.Amount,
"Payment Type"=p.Type,
p.PayInfo,
Name=(ma.FirstName+' '+ma.LastName),
ma.Company
FROM MasterAccounts ma, Payments p
WHERE DATEDIFF(Month, 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 Name, 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(Month, 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(Month, 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(Month, 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(Month, 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(Month, 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'