I'm having a little trouble with a query in RODBA, and I'm hoping someone here can steer me in the right direction.

I'm trying to pull total gift sum, gift count, and donor count per fiscal year for the last five fiscal years. (This is for only certain funds, only certain appeals, and only certain gift types). I've got the gift sum working down to the penny, but the donor and gift counts are off by about 1% or less, and I can't figure out where it's coming from. My hunch is that it has to do with split gifts or adjustments, but I can't prove that yet.

I've created a report in Raiser's Edge, and I'm trying to write a query in RODBA that returns the same numbers so that I can create an external report for this data set. I'm trying to balance the numbers between the RE report and my external report, and that's where I'm seeing the mis-matched data.

Here's what I've done in RODBA so far :

SELECT FY,
COUNT(DISTINCT CONSTIT_ID) AS NUMDONORS,
COUNT(DISTINCT GIFT_ID) AS NUMGIFTS,
SUM(AMOUNT) AS SUMGIFTS

FROM (

SELECT CG.CONSTIT_ID AS CONSTIT_ID, CG.GIFT_ID AS GIFT_ID, GS.AMOUNT, DBO.CSL_GETFY(G.DTE) AS FY
     FROM GIFTSPLIT AS GS
     INNER JOIN GIFT AS G ON GS.GIFTID  =  G.ID --needed for gift date
     INNER JOIN CONSTIT_GIFTS AS CG ON G.ID  =  CG.GIFT_ID --needed for gift & donor counts
     INNER JOIN APPEAL AS A ON GS.APPEALID  =  A.ID --needed for appeals in WHERE clause
WHERE G.DTE BETWEEN '7/1/2002 00:00:00.000' AND '6/30/2008 23:59:59.997'
     AND A.APPEALCATEGORYID IN (SELECT TABLEENTRIESID FROM TABLEENTRIES
                                                        WHERE CODETABLESID = 5042) --all direct appeals
     AND GS.FUNDID NOT IN (SELECT ID FROM FUND
                                            WHERE FUND_CATEGORY = 3537) --exclude "misdirect" fund types
     AND G.TYPE IN (1,2,3,9,10,11,12,13,14,15,16,17,18,19,20,31) --only direct/cash gifts
     AND CG.FLAGS IN (0,1,4) --credit Soft Credits to donor; Matching Gift to company
     AND GS.AMOUNT > 0 --exclude zero dollar gifts

) AS giftsByFY 

GROUP BY FY
ORDER BY FY DESC

I should explain that the csl_fy() function is a homemade scalar-value function that takes any valid date and returns "FYxxxx" where "xxxx" is the corresponding year, e.g. "FY2009" or "FY2008". That and the particular code table IDs will make it hard to copy/paste this into RODBA, but you're welcome to try!

Anyway, the output (in CSV) looks like this:

FY, NUMDONORS, NUMGIFTS, SUMGIFTS
FY2008, 16235, 35874, 20546214
FY2007, 18547, 42687, 26857963
FY2006, 14563, 38541, 19874563
FY2005, 12354, 32569, 18759632
FY2004, 10001, 22587, 12345678

(My Development department would want me to point out that these are made-up numbers!)

The donor and gifts counts in my RODBA results above, when compared to the RE report, are all LOW by between 50 and 200 each. However, the dollar amount (the "sumGifts" column) is exactly right.

As you can see from the first SELECT statement, I'm counting the DISTINCT constituent and gift IDs from the constit_gifts table, and my numbers are under by just a tiny bit. If I remove the DISTINCT clause, the numbers almost double, and that's obviously incorrect, too.

So here's really the question: how should I go about counting up the donor and gift counts? Should I be looking somewhere besides dbo.constit_gifts? Is there a table I forgot to join that factors into this? Do I have an INNER join somewhere that should be an OUTER instead?

I've been looking at this long enough that my brain is Jello. Can someone out there lend a hand? Thanks!

I'm not sure why you're not using a canned report or export to get this information.

The canned reports within RE work great, but some of the key decision makers in our institution don't have RE, and we can't afford to extend the licensing. So we're looking at external reporting solutions like Crystal.

I managed to get this working yesterday, and figured I'd post the revised code in case anyone's curious. Changes have been highlighted in BOLD.

SELECT FY,
COUNT(DISTINCT CONSTIT_ID) AS NUMDONORS,
COUNT(GIFT_ID) AS NUMGIFTS, --took out the DISTINCT here
SUM(AMOUNT) AS SUMGIFTS

FROM (

SELECT G.CONSTIT_ID AS CONSTIT_ID, GS.GIFTID AS GIFT_ID, GS.AMOUNT, DBO.CSL_GETFY(G.DTE) AS FY
     FROM GIFTSPLIT AS GS
     INNER JOIN GIFT AS G ON GS.GIFTID  =  G.ID --needed for gift date
     INNER JOIN CONSTIT_GIFTS AS CG ON G.ID  =  CG.GIFT_ID --needed for gift & donor counts
     INNER JOIN APPEAL AS A ON GS.APPEALID  =  A.ID --needed for appeals in WHERE clause
WHERE G.DTE BETWEEN '7/1/2002 00:00:00.000' AND '6/30/2008 23:59:59.997'
     AND A.APPEALCATEGORYID IN (SELECT TABLEENTRIESID FROM TABLEENTRIES
                                                        WHERE CODETABLESID = 5042) --all direct appeals
     AND GS.FUNDID NOT IN (SELECT ID FROM FUND
                                            WHERE FUND_CATEGORY = 3537) --exclude "misdirect" fund types
     AND G.TYPE IN (1,2,3,9,10,11,12,13,14,15,16,17,18,19,20,31) --only direct/cash gifts
     AND CG.FLAGS IN (0,1,4) --credit Soft Credits to donor; Matching Gift to company
     AND GS.AMOUNT > 0 --exclude zero dollar gifts

) AS giftsByFY 

That's it! The DISTINCT was screwing up my gift counts, and I'm pulling the donor count from gift.constit_id rather than constit_gifts now. Everything else is the same.