SQL Example 1 - Shows gift information as well as GL account numbers

SELECT DISTINCT RECORDS.FULL_NAME, GIFT.DTE, GIFT.TYPE, GiftSplit.Amount, FUND.DESCRIPTION, GLDISTRIBUTIONDETAIL.GLDEBIT_NUMBER, GLDISTRIBUTIONDETAIL.GLCREDIT_NUMBER
FROM GLDISTRIBUTIONDETAIL
INNER JOIN (((((RECORDS INNER JOIN (CONSTIT_GIFTS INNER JOIN GIFT ON CONSTIT_GIFTS.GIFT_ID = GIFT.ID) ON RECORDS.ID = CONSTIT_GIFTS.CONSTIT_ID) INNER JOIN GiftSplit ON GIFT.ID = GiftSplit.GiftId) INNER JOIN FUND ON GiftSplit.FundId = FUND.ID) INNER JOIN FundDistribution ON FUND.ID = FundDistribution.FundID) INNER JOIN GLDISTRIBUTION ON FundDistribution.GLDistributionID = GLDISTRIBUTION.DISTRIBUTION_ID) ON GLDISTRIBUTIONDETAIL.DISTRIBUTION_ID = GLDISTRIBUTION.DISTRIBUTION_ID
WHERE FULL_NAME <> ''


OR

SELECT DISTINCT REC.FULL_NAME, GF.DTE as 'GIFT DATE', GF.Type, GS.Amount, FN.DESCRIPTION,
GLDT.GLDEBIT_NUMBER, GLDT.GLCREDIT_NUMBER
FROM GLDISTRIBUTIONDETAIL GLDT
INNER JOIN GLDISTRIBUTION GLD ON GLDT.Distribution_ID =
GLD.Distribution_ID
INNER JOIN FUNDDISTRIBUTION FD on GLD.Distribution_ID = FD.GLDistributionID
INNER JOIN FUND FN ON FD.FundId = FN.ID
INNER JOIN GIFTSPLIT GS ON FN.ID = GS.FundId
INNER JOIN GIFT GF ON GF.ID = GS.GiftID
INNER JOIN RECORDS REC ON GF.CONSTIT_ID = REC.ID
INNER JOIN CONSTIT_GIFTS CG on REC.ID = CG.Constit_ID
WHERE REC.FULL_NAME <> ''

SQL Example 2 - Shows gift information as well as installment payments

SELECT DISTINCT REC.FULL_NAME as 'DONOR NAME', GF.DTE as 'GIFT DATE', GF.Type as 'GIFT TYPE',
GS.Amount as 'GIFT AMT', INS.Amount as 'INSTALL AMT', INS.Dte as 'INSTALL DATE'
FROM GIFT GF
INNER JOIN GIFTSPLIT GS ON GF.ID = GS.GiftId
INNER JOIN RECORDS REC ON GF.CONSTIT_ID = REC.ID
INNER JOIN CONSTIT_GIFTS CG on REC.ID = CG.Constit_ID
LEFT OUTER JOIN INSTALLMENT INS on GF.ID = INS.PledgeId
WHERE REC.FULL_NAME <> ''


NOTE: The query in Example 1 will display every account number for the fund associated to the gift. If the account number for a specific gift type is needed a new table must be created from scratch that compares the FundDistribution.GiftTypeID to the gift type on the gift. The FundDistribution.GiftTypeID is the table entry id of the GL gift type, so you can link it to the TABLEENTRIES table to determine the corresponding gift types. For example:

select DISTINCT FD.GiftTypeID, TE.TABLEENTRIESID, TE.LONGDESCRIPTION
from FundDistribution FD
INNER JOIN TABLEENTRIES TE on FD.GiftTypeID = TE.TABLEENTRIESID

So this will give you results similar to the ones below, which shows the FundDistribution.GiftTypeID and the type description :

GiftTypeIDTABLEENTRIESIDLONGDESCRIPTION
660660Cash
663663Gift-In-Kind

 



You can then create a Cross Reference Table like below:

Gift TypeDescriptionFund Gift Type
1CASH660
2GIFT-IN-KIND663

 





Every database has a different FundDistribution.GiftTypeID so a universal table can not be created.

The example below shows how to use the Cross Reference table to display gift information from a specific batch with the associated account number.

SELECT GF.BATCH_NUMBER, RE.CONSTITUENT_ID,
CASE RE.KEY_INDICATOR WHEN 'I' THEN (CASE WHEN RE.MIDDLE_NAME IS NULL THEN RE.FIRST_NAME + ' ' + RE.LAST_NAME ELSE RE.FIRST_NAME + ' ' + RE.MIDDLE_NAME + ' ' + RE.LAST_NAME END)
ELSE RE.ORG_NAME END AS DONOR_NAME, GF.DTE AS GIFT_DATE, GF.Amount, FN.DESCRIPTION, GD.GLDEBIT_NUMBER, GD.GLCREDIT_NUMBER
FROM GIFT GF
INNER JOIN RECORDS RE ON GF.CONSTIT_ID = RE.ID
INNER JOIN GiftSplit GS ON GF.ID = GS.GiftId
INNER JOIN FUND FN ON GS.FundId = FN.ID
INNER JOIN FundDistribution FD on FN.ID = FD.FundID
INNER JOIN GLDISTRIBUTIONDETAIL GD ON FD.GLDistributionID = GD.DISTRIBUTION_ID
INNER JOIN GiftTypeXref GX ON GF.TYPE = GX.GiftType AND FD.GiftTypeID = GX.FundGiftType -- Using the Cross Reference Table
WHERE ((GF.GiftSubType IS NULL AND FD.GiftSubTypeID IS NULL) OR (GF.GiftSubType = FD.GiftSubTypeID))
AND GF.BATCH_NUMBER = '1111' -- Batch Number
  Disclaimer: Blackbaud provides 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. Blackbaud 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.