[Emerald] How far in advance to charge credit cards

Josh Hillman ( (no email) )
Fri, 1 Sep 2000 12:59:30 -0400

Here's a stored procedure I wrote lastnight that I have scheduled to run
every night just after midnight. Maybe some of you will find it useful...


CREATE PROCEDURE [Credit card invoice control] AS

/* The BillingCycles.EBillDays field tells Emerald how far in advance
to create invoices for people that pay by credit card or other
electronic payment methods. A value of 1 (day) tells Emerald to
create invoices for customers whos PaidThru date is <= tomorrow's

This script dynamically changes this value (days) so that the
invoice is created (and charged) the miminum amount of days BEFORE
the payment is due, taking into account weekends and holidays (so
long as the holiday dates are listed below). This minimizes
customer complaints that they're being charged too far in advance.
This cannot be avoided when no one is around to batch credit card
transactions on weekends, etc.

DECLARE @Today datetime,
@EBillDays int,
@Counter int
SELECT @Today = convert(varchar(10), getdate(), 1),
@EBillDays = 1,
@Counter = 0

WHILE (@Counter < @EBillDays)

-- Weekends (assuming no one is there to batch credit cards)
IF datename(weekday, dateadd(day, @EBillDays, @Today))
IN ('Saturday', 'Sunday')
SELECT @EBillDays = @EBillDays + 1

-- Holidays (assuming no one will be able to batch credit cards)
ELSE IF dateadd(day, @EBillDays, @Today)
IN (
'1/1/01', -- New Year
'5/28/01', -- Memorial Day
'7/4/01', -- Independence Day
'9/4/00', '9/3/01', -- Labor day
'11/23/00', '11/22/01', -- Thanksgiving
'12/24/00', '12/25/00', '12/24/01', '12/25/01' -- Christmas
SELECT @EBillDays = @EBillDays + 1

SELECT @Counter = @Counter + 1


UPDATE BillingCycles
SET EBillDays = @EBillDays

For more information about this list (including removal) go to: