Many Pies

Many Pies

Wednesday, March 21, 2007

Getting gift information out of Raiser's Edge

This is a very technical Raiser's Edge post, which may be of use to a small number of people. You'll need to understand SQL.

We post gifts out of Raiser's Edge and they undergo a lot of processing afterwards. Despite all the information you can put into the reference when you post out (constituent id, name, fund name etc.) you can't get everything.

If you run the following SQL statement just after posting, putting in the date and times when the post ran, then you can get most of the gifts that were posted (see below for getting the rest). If someone edited a gift in the middle then that would throw it out, but either
a) have a bad on editing gifts or
b) do a reconciliation with the post file, crosschecking constituent ids with the Excel VLOOKUP function, for example.


SELECT GiftGLDistAccount.Debit_Number, GiftGLDistAccount.Credit_Number, GiftGLDistAcctProject.Project_Number,
GiftGLDistAcctProject.Project_Desc, GIFT.DATECHANGED, GiftGLDistAccount.GiftGLDistributionId, GiftGLDistribution.Amount,
GiftGLDistribution.AdjustmentId, GiftGLDistribution.GiftId, RECORDS.CONSTITUENT_ID, GIFT.DTE
FROM GiftGLDistAccount INNER JOIN
GiftGLDistribution ON GiftGLDistAccount.GiftGLDistributionId = GiftGLDistribution.Id INNER JOIN
GiftGLDistAcctProject ON GiftGLDistAccount.Id = GiftGLDistAcctProject.GiftGLDistAccountId INNER JOIN
GIFT ON GiftGLDistribution.GiftId = GIFT.ID INNER JOIN
RECORDS ON GIFT.CONSTIT_ID = RECORDS.ID
WHERE (GIFT.DATECHANGED > CONVERT(DATETIME, '2007-03-20 11:00:00', 102)) AND
(GIFT.DATECHANGED < CONVERT(DATETIME, '2007-03-20 12:00:00', 102))


What the above doesn't do is get the old values for adjusted gifts. For that you need this:

SELECT GiftGLDistAcctProject.Project_Number, GiftGLDistAcctProject.Project_Desc, GIFT.DATECHANGED, GiftGLDistribution.Amount,
GiftGLDistribution.AdjustmentId, GiftGLDistribution.GiftId, RECORDS.CONSTITUENT_ID, GIFT.DTE, GiftSplit.Amount AS Expr1,
GiftPreviousSplit.Amount AS Expr2
FROM GiftGLDistAccount INNER JOIN
GiftGLDistribution ON GiftGLDistAccount.GiftGLDistributionId = GiftGLDistribution.Id INNER JOIN
GiftGLDistAcctProject ON GiftGLDistAccount.Id = GiftGLDistAcctProject.GiftGLDistAccountId INNER JOIN
GIFT ON GiftGLDistribution.GiftId = GIFT.ID INNER JOIN
RECORDS ON GIFT.CONSTIT_ID = RECORDS.ID INNER JOIN
GiftAdjustment ON GIFT.ID = GiftAdjustment.GiftId INNER JOIN
GiftSplit ON GIFT.ID = GiftSplit.GiftId INNER JOIN
GiftPreviousSplit ON GIFT.ID = GiftPreviousSplit.GiftId
WHERE (GIFT.DATECHANGED > CONVERT(DATETIME, '2007-03-20 11:00:00', 102)) AND
(GIFT.DATECHANGED < CONVERT(DATETIME, '2007-03-20 12:00:00', 102))

This query returns duplicate rows for the gifts, so will need tweaking to get just what you want.
Finally, if you want deleted gifts, then look in the GiftGLAudit table.

tags: Raiser's Edge

3 comments:

Anonymous said...

For those US based who want to use this code you may want to change the date conversion parameter from 102 to 101 or 110. This will give month, day, year instead of the UK format of day, month, year.

David

Anonymous said...

SQL will do an implicit conversion from char or nchar to datetime, but not the other way around. I find that it's much easier to read if you let SQL do an implicit conversion rather than specifying an explicit conversion. As far as I know, there is no difference in performance between an implicit and an explicit conversion.

Paul Morriss said...

I constructed the query using SQLServer Enterprise Manager and when it converted it to SQL that's what it generated.

After a bit of experimentation I found that if you use the format yyyy-mm-dd you can just specify the pure date.