Below is a sample SQL statement that will return all amendments on a specific gift as well as their variance, frequency, and schedule_spacing:
 
SELECT
g.DATEADDED,
g2f.Variance,
g.INSTALLMENT_FREQUENCY,
g.Schedule_Spacing,
CASE g.INSTALLMENT_FREQUENCY
WHEN 1 THEN 'Anually'
WHEN 2 THEN 'Semi-Anually'
WHEN 3 THEN 'Quarterly'
WHEN 4 THEN 'Bi-Monthly'
WHEN 5 THEN 'Monthly'
WHEN 6 THEN 'Semi-Monthly'
WHEN 7 THEN 'Bi-Weekly'
WHEN 8 THEN 'Weekly'
WHEN 9 THEN 'Irregular'
WHEN 10 THEN 'Single'
WHEN 11 THEN 'Daily'
END AS 'FreqTranslation',
g.FrequencyDescription

FROM dbo.GiftAmendment ga
JOIN dbo.GIFT g ON g.ID = ga.AmendmentId
JOIN dbo.Gift2Fields g2f ON g2f.ID = g.ID

WHERE ga.GiftId = '12345'



When a gift is entered as a recurring gift and a schedule is set for Monthly, Semi-Monthly, or Weekly the spacing can be specified.  If this gift is then amended to Biweekly, Bimonthly, Quarterly, Semi-Annually, or Annually, the gift will keep the Schedule_Spacing but RE will ignore the value.

This means that Schedule_Spacing for INSTALLMENT_FREQUENCY-ies of 1, 2, 3, 4, and 7 should be ignored if performing calculations based on spacing.


Disclaimer: We provide programming examples for illustration only, without warranty either expressed or implied, including, but not limited to, the implied warranties of merchantability and/or fitness for a particular purpose. This article assumes you are familiar with Structured Query Language and the tools used to create and modify SQL statements and Crystal Reports. Our Customer Support may help explain the functionality of a particular procedure, but we will not modify, or assist you with modifying, these examples to provide additional functionality.